Обновление и удаление записей

sqlite> UPDATE rubr SET name_rubr='Кино' WHERE id_rubr=3;

sqlite> SELECT * FROM rubr;

1|Программирование

2|Музыка

3|Кино

 

 

sqlite> DELETE FROM rubr WHERE id_rubr=3;

sqlite> SELECT * FROM rubr;

1|Программирование

2|Музыка

Изменение структуры таблицы

sqlite>.tables

rubr sqlite_sequence tmp1 user

site table tmp2

sqlite> ALTER TABLE user RENAME TO users;

sqlite>.tables

rubr sqlite_sequence tmp1 users

site table tmp2

 

 

sqlite> ALTER TABLE site ADD COLUMN iq INTEGER DEFAULT 0;

sqlite> PRAGMA table_info(site);

0|id_site|INTEGER|0||1

1|id_user|INTEGER|0||0

2|id_rubr|INTEGER|0||0

3|url|TEXT|0||0

4|title|TEXT|0||0

5|msg|TEXT|0||0

6|iq|INTEGER|0|0|0

sqlite> SELECT * FROM site;

1|1|1|http://wwwadmin.ru|Название|Описание|0

Выбор записей

sqlite> SELECT id_rubr, name_rubr FROM rubr;

1|Программирование

2|Музыка

 

 

sqlite> SELECT id_rubr, name_rubr FROM rubr WHERE id_rubr=1;

1|Программирование

 

 

sqlite> SELECT * FROM rubr;

1|Программирование

2|Музыка

 

 

sqlite> SELECT 10 + 5;

 

 

sqlite> SELECT (10 + 5) AS expr1, (70 * 2) AS expr2;

15|140

 

 

sqlite> SELECT s.url, r.name_rubr FROM site AS s, rubr AS r

...> WHERE s.id_rubr = r.id_rubr;

http://wwwadmin.ru|Программирование

 

 

sqlite> INSERT INTO rubr VALUES (3, 'Поисковые порталы');

sqlite> INSERT INTO site (id_user, id_rubr, url, title, msg, iq)

...> VALUES (1, 1, 'http://python.org', 'Python', '', 1000);

sqlite> INSERT INTO site (id_user, id_rubr, url, title, msg, iq)

...> VALUES (1, 3, 'http://google.ru', 'Гугль', '', 3000);

 

 

sqlite> SELECT id_rubr, COUNT(id_rubr) FROM site

...> GROUP BY id_rubr;

1|2

3|1

 

 

sqlite> SELECT id_rubr FROM site

...> GROUP BY id_rubr HAVING COUNT(id_rubr)>1;

 

 

sqlite> SELECT COUNT(*) FROM site;

 

 

sqlite> SELECT MIN(iq) FROM site;

 

 

sqlite> SELECT MAX(iq) FROM site;

 

 

sqlite> SELECT AVG(iq) FROM site;

1333.33333333333

 

 

sqlite> SELECT SUM(iq) FROM site;

 

 

sqlite> SELECT TOTAL(iq) FROM site;

4000.0

 

 

sqlite> SELECT GROUP_CONCAT(name_rubr) FROM rubr;

Программирование,Музыка,Поисковые порталы

sqlite> SELECT GROUP_CONCAT(name_rubr, ' | ') FROM rubr;

Программирование | Музыка | Поисковые порталы

 

 

sqlite> SELECT * FROM rubr ORDER BY name_rubr;

2|Музыка

3|Поисковые порталы

1|Программирование

sqlite> SELECT * FROM rubr ORDER BY name_rubr DESC;

1|Программирование

3|Поисковые порталы

2|Музыка

 

 

sqlite> CREATE TEMP TABLE tmp3 (id INTEGER);

sqlite> INSERT INTO tmp3 VALUES(1);

sqlite> INSERT INTO tmp3 VALUES(2);

sqlite> INSERT INTO tmp3 VALUES(3);

sqlite> INSERT INTO tmp3 VALUES(4);

sqlite> INSERT INTO tmp3 VALUES(5);

sqlite> SELECT * FROM tmp3 LIMIT 3; -- Эквивалентно LIMIT 0, 3

sqlite> SELECT * FROM tmp3 LIMIT 2, 3;

sqlite> SELECT * FROM tmp3 LIMIT 3 OFFSET 2;

sqlite> DROP TABLE tmp3;

Выбор записей из нескольких таблиц

sqlite> SELECT site.url, rubr.name_rubr, users.email

...> FROM rubr, users, site

...> WHERE site.id_rubr=rubr.id_rubr AND

...> site.id_user=users.id_user;

http://wwwadmin.ru|Программирование|[e-mail]

http://python.org|Программирование|[e-mail]

http://google.ru|Поисковые порталы|[e-mail]

 

 

sqlite> SELECT url, name_rubr, email

...> FROM rubr AS r, users AS u, site AS s

...> WHERE s.id_rubr=r.id_rubr AND

...> s.id_user=u.id_user;

 

 

sqlite> SELECT url, name_rubr, email

...> FROM rubr JOIN users JOIN site

...> WHERE site.id_rubr=rubr.id_rubr AND

...> site.id_user=users.id_user;

 

 

sqlite> SELECT url, name_rubr, email

...> FROM rubr JOIN users JOIN site

...> ON site.id_rubr=rubr.id_rubr AND

...> site.id_user=users.id_user

...> WHERE site.id_rubr=1;

 

 

sqlite> SELECT url, name_rubr, email

...> FROM rubr JOIN site USING (id_rubr) JOIN users USING (id_user);

 

 

sqlite> SELECT name_rubr, COUNT(id_site)

...> FROM rubr JOIN site USING (id_rubr)

...> GROUP BY rubr.id_rubr;

Программирование|2

Поисковые порталы|1

 

 

sqlite> SELECT name_rubr, COUNT(id_site)

...> FROM rubr LEFT JOIN site USING (id_rubr)

...> GROUP BY rubr.id_rubr

...> ORDER BY rubr.name_rubr;

Музыка|0

Поисковые порталы|1

Программирование|2

17.8. Условия в инструкциях WHERE и HAVING

sqlite> SELECT * FROM rubr WHERE id_rubr=1;

1|Программирование

sqlite> SELECT 10 = 10, 5 = 10, 10 == 10, 5 == 10;

1|0|1|0

 

 

sqlite> CREATE TEMP TABLE tmp3 (p1, p2 TEXT COLLATE NOCASE);

sqlite> INSERT INTO tmp3 VALUES ("abcd", "abcd");

sqlite> SELECT p1 = "ABCD" FROM tmp3; -- Не найдено

sqlite> SELECT p2 = "ABCD" FROM tmp3; -- Найдено

sqlite> DROP TABLE tmp3;

 

 

sqlite> SELECT 's' = 'S', 's' = 'S' COLLATE NOCASE;

0|1

 

 

sqlite> SELECT 'ы' = 'Ы', 'ы' = 'Ы' COLLATE NOCASE;

0|0

 

 

sqlite> SELECT 10!= 10, 5!= 10, 10 <> 10, 5 <> 10;

0|1|0|1

 

 

sqlite> SELECT 100 BETWEEN 1 AND 100;

sqlite> SELECT 101 BETWEEN 1 AND 100;

 

 

sqlite> SELECT 'один' IN ('один', 'два', 'три');

sqlite> SELECT 'Один' IN ('один', 'два', 'три');

 

 

sqlite> SELECT 'test word test' LIKE '%word%';

 

 

sqlite> SELECT 'test word test' LIKE 'test%';

sqlite> SELECT 'test word test' LIKE 'word%';

 

 

sqlite> SELECT 'test word test' LIKE '%es_%wo_d%';

sqlite> SELECT 'test word test' LIKE '%wor%d%';

 

 

sqlite> SELECT '10$' LIKE '10%';

sqlite> SELECT '10$' LIKE '10\%' ESCAPE '\';

sqlite> SELECT '10%' LIKE '10\%' ESCAPE '\';

 

 

sqlite> PRAGMA case_sensitive_like = true;

sqlite> SELECT 's' LIKE 'S';

sqlite> PRAGMA case_sensitive_like = false;

sqlite> SELECT 's' LIKE 'S';

 

 

sqlite> SELECT 'ы' LIKE 'Ы', 'ы' LIKE 'ы';

1|1

 

 

sqlite> SELECT 'г' LIKE 'Ы', 'слово' LIKE 'текст';

1|1

 

 

C:\book>chcp 866

Текущая кодовая страница: 866

 

C:\book>sqlite3.exe testdb.db

SQLite version 3.8.9

Enter ".help" for instructions

Enter SQL statements terminated with a ";"

sqlite> SELECT 'г' LIKE 'Ы', 'слово' LIKE 'текст';

0|0

sqlite> SELECT 'ы' LIKE 'Ы', 'ы' LIKE 'ы';

0|1

 

 

sqlite> SELECT 's' = 'S', NOT ('s' = 'S');

0|1

sqlite> SELECT NOT 'один' IN ('один', 'два', 'три');

Индексы

sqlite> EXPLAIN QUERY PLAN SELECT * FROM site WHERE id_site=1;

0|0|0|SEARCH TABLE site USING INTEGER PRIMARY KEY (rowid=?) (~1 rows)

sqlite> EXPLAIN QUERY PLAN SELECT * FROM site WHERE id_rubr=1;

0|0|0|SCAN TABLE site (~100000 rows)

 

 

sqlite> EXPLAIN QUERY PLAN SELECT * FROM site WHERE id_rubr=1;

0|0|0|SCAN TABLE site (~100000 rows)

sqlite> CREATE INDEX index_rubr ON site (id_rubr);

sqlite> EXPLAIN QUERY PLAN SELECT * FROM site WHERE id_rubr=1;

0|0|0|SEARCH TABLE site USING INDEX index_rubr (id_rubr=?) (~10 rows)

 

 

sqlite> SELECT * FROM site WHERE id_rubr=1;

1|1|1|http://wwwadmin.ru|Название|Описание|0

2|1|1|http://python.org|Python||1000

 

 

sqlite> SELECT * FROM sqlite_stat1; -- Нет записей

Error: no such table: sqlite_stat1

sqlite> ANALYZE;

sqlite> SELECT * FROM sqlite_stat1;

site|index_rubr|3 2

rubr||3

users||1

Вложенные запросы

sqlite> CREATE TEMP TABLE tmp_rubr AS SELECT * FROM rubr;

sqlite> SELECT * FROM tmp_rubr;

1|Программирование

2|Музыка

3|Поисковые порталы

 

 

sqlite> DELETE FROM tmp_rubr;

sqlite> INSERT INTO tmp_rubr SELECT * FROM rubr WHERE id_rubr<3;

sqlite> SELECT * FROM tmp_rubr;

1|Программирование

2|Музыка

 

 

sqlite> SELECT * FROM site WHERE id_rubr IN (

...> SELECT id_rubr FROM rubr

...> WHERE name_rubr='Программирование');

1|1|1|http://wwwadmin.ru|Название|Описание|0

2|1|1|http://python.org|Python||1000

Транзакции

sqlite> BEGIN TRANSACTION;

sqlite> INSERT INTO rubr VALUES (NULL, 'Кино');

sqlite> INSERT INTO rubr VALUES (NULL, 'Разное');

sqlite> ROLLBACK TRANSACTION; -- Отменяем вставку

sqlite> SELECT * FROM rubr;

1|Программирование

2|Музыка

3|Поисковые порталы

 

 

sqlite> BEGIN TRANSACTION;

sqlite> INSERT INTO rubr VALUES (NULL, 'Кино');

sqlite> INSERT INTO rubr VALUES (3, 'Разное'); -- Ошибка

Error: PRIMARY KEY must be unique

sqlite> COMMIT TRANSACTION;

sqlite> SELECT * FROM rubr;

1|Программирование

2|Музыка

3|Поисковые порталы

4|Кино

 

 

sqlite> BEGIN TRANSACTION;

sqlite> INSERT OR ROLLBACK INTO rubr VALUES (NULL, 'Мода');

sqlite> INSERT OR ROLLBACK INTO rubr VALUES (3, 'Разное');

Error: PRIMARY KEY must be unique

sqlite> COMMIT TRANSACTION; -- Транзакция уже завершена!

Error: cannot commit — no transaction is active

sqlite> SELECT * FROM rubr;

1|Программирование

2|Музыка

3|Поисковые порталы

4|Кино

 

 

sqlite> BEGIN EXCLUSIVE TRANSACTION;

sqlite> -- База данных заблокирована

sqlite> -- Выполняем какие-либо операции с базой

sqlite> COMMIT TRANSACTION;

sqlite> -- Транзакция завершена, и база разблокирована

 

 

sqlite> SAVEPOINT metka1;

sqlite> INSERT INTO rubr VALUES (NULL, 'Мода');

sqlite> INSERT INTO rubr VALUES (NULL, 'Разное');

sqlite> ROLLBACK TO SAVEPOINT metka1;

sqlite> SELECT * FROM rubr;

1|Программирование

2|Музыка

3|Поисковые порталы

4|Кино

18. Доступ к базе данных SQLite
из Python

>>> import sqlite3 # Подключаем модуль

>>> sqlite3.apilevel # Получаем номер спецификации

'2.0'

 

 

>>> sqlite3.sqlite_version

'3.8.3.1'

>>> sqlite3.sqlite_version_info

(3, 8, 3, 1)

18.1. Создание и открытие базы данных

>>> import sqlite3 # Подключаем модуль sqlite3

>>> con = sqlite3.connect("testdb.db") # Открываем базу данных

>>> # Работаем с базой данных

>>> con.close() # Закрываем базу данных

 

 

>>> import sqlite3

>>> # Доступ к базе, хранящейся в файле c:\book\testdb.db

>>> con = sqlite3.connect(r"file:///c:/book/testdb.db", uri = True)

>>> con.close()

>>> # Доступ только для чтения

>>> con = sqlite3.connect(r"file:///c:/book/testdb.db?mode=ro", uri = True)

>>> con.close()

>>> # Доступ к неизменяемой базе данных

>>> con = sqlite3.connect(r"file:///f:/data.db?immutable=1", uri = True)

>>> con.close()

Выполнение запросов

# -*- coding: utf-8 -*-

import sqlite3

con = sqlite3.connect("catalog.db")

cur = con.cursor() # Создаем объект-курсор

sql = """\

CREATE TABLE user (

id_user INTEGER PRIMARY KEY AUTOINCREMENT,

email TEXT,

passw TEXT

);

CREATE TABLE rubr (

id_rubr INTEGER PRIMARY KEY AUTOINCREMENT,

name_rubr TEXT

);

CREATE TABLE site (

id_site INTEGER PRIMARY KEY AUTOINCREMENT,

id_user INTEGER,

id_rubr INTEGER,

url TEXT,

title TEXT,

msg TEXT,

iq INTEGER

);

"""

try: # Обрабатываем исключения

cur.executescript(sql) # Выполняем SQL-запросы

except sqlite3.DatabaseError as err:

print("Ошибка:", err)

else:

print("Запрос успешно выполнен")

cur.close() # Закрываем объект-курсор

con.close() # Закрываем соединение

input()

 

 

# -*- coding: utf-8 -*-

import sqlite3

con = sqlite3.connect("catalog.db")

cur = con.cursor() # Создаем объект-курсор

sql = """\

INSERT INTO user (email, passw)

VALUES ('[e-mail]', 'password1')

"""

try:

cur.execute(sql) # Выполняем SQL-запрос

except sqlite3.DatabaseError as err:

print("Ошибка:", err)

else:

print("Запрос успешно выполнен")

con.commit() # Завершаем транзакцию

cur.close() # Закрываем объект-курсор

con.close() # Закрываем соединение

input()

 

 

# -*- coding: utf-8 -*-

import sqlite3

con = sqlite3.connect("catalog.db")

cur = con.cursor() # Создаем объект-курсор

t1 = ("Программирование",)

t2 = (2, "Музыка")

d = {"id": 3, "name": """Поисковые ' " порталы"""}

sql_t1 = "INSERT INTO rubr (name_rubr) VALUES (?)"

sql_t2 = "INSERT INTO rubr VALUES (?,?)"

sql_d = "INSERT INTO rubr VALUES (:id,:name)"

try:

cur.execute(sql_t1, t1) # Кортеж из 1-го элемента

cur.execute(sql_t2, t2) # Кортеж из 2-х элементов

cur.execute(sql_d, d) # Словарь

except sqlite3.DatabaseError as err:

print("Ошибка:", err)

else:

print("Запрос успешно выполнен")

con.commit() # Завершаем транзакцию

cur.close() # Закрываем объект-курсор

con.close() # Закрываем соединение

input()

 

 

# -*- coding: utf-8 -*-

import sqlite3

con = sqlite3.connect("catalog.db")

cur = con.cursor() # Создаем объект-курсор

arr = [

(1, 1, "http://wwwadmin.ru", "Название", "", 100),

(1, 1, "http://python.org", "Python", "", 1000),

(1, 3, "http://google.ru", "Гугль", "", 3000)

]

sql = """\

INSERT INTO site (id_user, id_rubr, url, title, msg, iq)

VALUES (?,?,?,?,?,?)

"""

try:

cur.executemany(sql, arr)

except sqlite3.DatabaseError as err:

print("Ошибка:", err)

else:

print("Запрос успешно выполнен")

con.commit() # Завершаем транзакцию

cur.close() # Закрываем объект-курсор

con.close() # Закрываем соединение

input()

 

Листинг 18.1. Использование метода execute()

# -*- coding: utf-8 -*-

import sqlite3

con = sqlite3.connect("catalog.db")

try:

con.execute("""UPDATE rubr SET name_rubr='Поисковые порталы'

WHERE id_rubr=3""")

except sqlite3.DatabaseError as err:

print("Ошибка:", err)

else:

con.commit() # Завершаем транзакцию

print("Запрос успешно выполнен")

con.close() # Закрываем соединение

input()

 

 

# -*- coding: utf-8 -*-

import sqlite3

con = sqlite3.connect("catalog.db")

cur = con.cursor() # Создаем объект-курсор

try:

cur.execute("""INSERT INTO rubr (name_rubr)

VALUES ('Кино')""")

except sqlite3.DatabaseError as err:

print("Ошибка:", err)

else:

con.commit() # Завершаем транзакцию

print("Запрос успешно выполнен")

print("Индекс:", cur.lastrowid)

cur.close() # Закрываем объект-курсор

con.close() # Закрываем соединение

input()

 

 

con = sqlite3.connect("catalog.db")

...

print(con.total_changes)