Учебное пособие по программированию на Python для базы данных SQLite.
Оглавление
SQLite
SQLite – это встроенный движок реляционной базы данных. В документации это называется автономным, бессерверным, с нулевой конфигурацией и транзакционным ядром базы данных SQL. Он очень популярен и сегодня используется сотнями миллионов копий по всему миру. Несколько языков программирования имеют встроенную поддержку SQLite, включая Python и PHP.
Установка SQLite
sudo apt install sqlite3
Запускаем модуль sqlite3 и консоль пригласит для работы с базой данных:
sqlite>
Список команд для работы с базой данных sqlite> .help
Создание базы данных SQLite
Вся база данных SQLite хранится в обычном файле. Используем команду .open dbname.db. Если база не существует — она будет создана, если существует — будет открыта.
sqlite> .open ydb.db
Python SQLite — как подключаться к базе и отправлять запросы
Для примера получим версию базы данных SQLite.
В первом скрипте рассмотрим работу с базой данных классическим способом: откроем базу – выполним запрос – закроем базу и освободим ресурсы.
#!/usr/bin/python
import sqlite3
import sys
con = None
try:
con = sqlite3.connect('ydb.db')
cur = con.cursor()
cur.execute('SELECT SQLITE_VERSION()')
data = cur.fetchone()[0]
print(f"SQLite version: {data}")
except sqlite3.Error as e:
print(f"Error {e.args[0]}")
sys.exit(1)
finally:
if con:
con.close()
SQLite version: 3.31.1
Рассмотрим код подробнее:
import sqlite3
Импортируем библиотеку sqlite3.
con = None
Объявляем переменную con значением None. Если мы не сможем соединиться с базой данных (например, диск переполнен), а у нас не была бы определена переменная соединения — это приведёт к ошибке в блоке finally.
con = sqlite3.connect('ydb.db')
Подключаемся к базе данных ydb.db. Метод connect возвращает объект соединения.
cur = con.cursor()
cur.execute('SELECT SQLITE_VERSION()')
Из соединения мы получаем объект курсора. Курсор используется для обхода записей из полученных результатов. Мы вызываем метод курсора execute и выполняем SQL-запрос.
data = cur.fetchone()[0]
Поскольку мы получаем только одну запись, мы вызываем метод fetchone.
print(f"SQLite version: {data}")
Полученные данные выводим в консоль.
except sqlite3.Error as e:
print(f"Error {e.args[0]}")
sys.exit(1)
В случае ошибки получаем исключение — выводим сообщение об ошибке и выходим из скрипта с кодом ошибки 1.
finally:
if con:
con.close()
con.close() закрывает работу с базой данных и освобождаем ресурсы.
Во втором примере мы снова получаем версию базы данных SQLite. На этот раз мы используем контекстный менеджер with.
#!/usr/bin/python
import sqlite3
with sqlite3.connect('ydb.db') as con:
cur = con.cursor()
cur.execute('SELECT SQLITE_VERSION()')
data = cur.fetchone()[0]
print(f"SQLite version: {data}")
Код выглядит компактнее, читается проще.
Python SQLite — метод execute
Создадим таблицу cars и вставим в нее несколько строк. Для добавления данных используем команду execute.
import sqlite3
with sqlite3.connect('ydb.db') as con:
cur = con.cursor()
cur.execute("DROP TABLE IF EXISTS cars;")
cur.execute("CREATE TABLE cars(id INT, name TEXT, price INT)")
cur.execute("INSERT INTO cars VALUES(1,'Audi',52642)")
cur.execute("INSERT INTO cars VALUES(2,'Mercedes',57127)")
cur.execute("INSERT INTO cars VALUES(3,'Skoda',9000)")
cur.execute("INSERT INTO cars VALUES(4,'Volvo',29000)")
cur.execute("INSERT INTO cars VALUES(5,'Bentley',350000)")
cur.execute("INSERT INTO cars VALUES(6,'Citroen',21000)")
cur.execute("INSERT INTO cars VALUES(7,'Hummer',41400)")
cur.execute("INSERT INTO cars VALUES(8,'Volkswagen',21600)")
Рассмотрим подробнее:
cur.execute("DROP TABLE IF EXISTS cars;")
Удаляем таблицу, если она уже существует.
cur.execute("CREATE TABLE cars(id INT, name TEXT, price INT)")
Создаём новую таблицу cars с тремя столбцами: id, name, price.
cur.execute("INSERT INTO cars VALUES(1,'Audi',52642)")
cur.execute("INSERT INTO cars VALUES(2,'Mercedes',57127)")
Эти две строки вставляют в таблицу два автомобиля. Благодаря использованию контекстного менеджера with изменения фиксируются автоматически. В противном случае нам пришлось бы фиксировать их вручную.
sqlite> .mode column
sqlite> .headers on
Выведем записанные данные в консоль с помощью модуля sqlite3. Изменим способ отображения данных в консоли — используем режим столбцов и отобразим заголовки.
sqlite> select * from cars;
id name price
---------- ---------- ----------
1 Audi 52642
2 Mercedes 57127
3 Skoda 9000
4 Volvo 29000
5 Bentley 350000
6 Citroen 21000
7 Hummer 41400
8 Volkswagen 21600
Python SQLite — метод executemany
Создадим ту же таблицу, но уже с помощью более быстрого метода executemany. С помощью executemany можно вставлять сразу несколько записей.
Python SQLite — executemany INSERT
import sqlite3
cars = (
(1, 'Audi', 52642),
(2, 'Mercedes', 57127),
(3, 'Skoda', 9000),
(4, 'Volvo', 29000),
(5, 'Bentley', 350000),
(6, 'Hummer', 41400),
(7, 'Volkswagen', 21600)
)
with sqlite3.connect('ydb.db') as con:
cur = con.cursor()
cur.execute("DROP TABLE IF EXISTS cars")
cur.execute("CREATE TABLE cars(id INT, name TEXT, price INT)")
cur.executemany("INSERT INTO cars VALUES(?, ?, ?)", cars)
Вставляем сразу восемь строк в таблицу.
cur.executemany("INSERT INTO cars VALUES(?, ?, ?)", cars)
Первый параметр — запрос SQL, второй параметр — кортеж кортежей.
Python SQLite — executemany SELECT
Метод executemany используется только для вставки данных. Иногда ошибочно пытаются получить данные по фильтру из списка значений с помощью executemany. Но для этого используется метод execute с подстановкой значений.
cur.execute('SELECT * FROM cars WHERE name in ({0})'.format(', '.join('?' for _ in names)), names)
Python SQLite — метод executescript
Рассмотрим способ создания таблицы автомобилей с помощью executescript. Будем фиксировать изменения вручную и добавим собственную обработку ошибок.
import sqlite3
import sys
con = None
try:
con = sqlite3.connect('ydb.db')
cur = con.cursor()
cur.executescript("""
DROP TABLE IF EXISTS cars;
CREATE TABLE cars(id INT, name TEXT, price INT);
INSERT INTO cars VALUES(1,'Audi',52642);
INSERT INTO cars VALUES(2,'Mercedes',57127);
INSERT INTO cars VALUES(3,'Skoda',9000);
INSERT INTO cars VALUES(4,'Volvo',29000);
INSERT INTO cars VALUES(5,'Bentley',350000);
INSERT INTO cars VALUES(6,'Citroen',21000);
INSERT INTO cars VALUES(7,'Hummer',41400);
INSERT INTO cars VALUES(8,'Volkswagen',21600);
""")
con.commit()
except sqlite3.Error as e:
if con:
con.rollback()
print(f"Error {e.args[0]}")
sys.exit(1)
finally:
if con:
con.close()
executescript позволяет выполнять сразу несколько сриптов в одном запросе.
con.commit()
Без контекстного менеджера with изменения должны быть зафиксированы с помощью метода commit.
except sqlite.Error as e:
if con:
con.rollback()
print(f"Error {e.args[0]}")
sys.exit(1)
В случае ошибки изменения откатываются con.rollback(), а на терминал выводится сообщение об ошибке.
Python SQLite — метод lastrowid
Иногда требуется получить id последней строки таблицы в базе данных. В Python SQLite используем атрибут lastrowid объекта курсора.
import sqlite3
with sqlite3.connect(':memory:') as con:
cur = con.cursor()
cur.execute("CREATE TABLE friends(id INTEGER PRIMARY KEY, name TEXT);")
cur.execute("INSERT INTO friends(name) VALUES ('Tom');")
cur.execute("INSERT INTO friends(name) VALUES ('Rebecca');")
cur.execute("INSERT INTO friends(name) VALUES ('Jim');")
cur.execute("INSERT INTO friends(name) VALUES ('Robert');")
last_row_id = cur.lastrowid
print(f"The last Id of the inserted row is {last_row_id}")
:memory: — база данных, которая создаётся временно.
Python SQLite — получение данных с помощью fetchall
Метод fetchall извлекает все (или все оставшиеся) строки из набора результатов запроса и возвращает список кортежей.
import sqlite3
with sqlite3.connect('ydb.db') as con:
cur = con.cursor()
cur.execute("SELECT * FROM cars")
rows = cur.fetchall()
for row in rows:
print(f"{row[0]} {row[1]} {row[2]}")
Python SQLite — метод fetchone
Метод fetchone возвращает следующую строку полученных результатов. Возвращает один кортеж или None, когда больше нет доступных данных.
import sqlite3
con = sqlite3.connect('ydb.db')
with con:
cur = con.cursor()
cur.execute("SELECT * FROM cars")
while True:
row = cur.fetchone()
if row == None:
break
print(f"{row[0]} {row[1]} {row[2]}")
Python SQLite — row_factory
По умолчанию курсор возвращает данные в виде кортежа кортежей. Когда мы используем row_factory, данные передаются в виде словарей Python. Таким образом, мы можем ссылаться на данные по именам столбцов.
import sqlite3
con = sqlite3.connect('ydb.db')
with con:
con.row_factory = sqlite3.Row
cur = con.cursor()
cur.execute("SELECT * FROM cars")
rows = cur.fetchall()
for row in rows:
print(f"{row['id']} {row['name']} {row['price']}")Python SQLite — как проверить, существует ли база данных
Невозможно проверить, существует ли файл базы данных с помощью метода connect. Метод просто подключается к базе данных, если файл существует. Если файл не существует, файл базы данных создается. Существование файла базы данных можно проверить с помощью стандартной функции os.path.exist.
import os
import sqlite3
if not os.path.exists('ydb.db'):
con = sqlite3.connect('ydb.db')
with con:
cur = con.cursor()
cur.execute("DROP TABLE IF EXISTS cars")
cur.execute("CREATE TABLE cars(id INT, name TEXT, price INT)")
cur.execute("INSERT INTO cars VALUES(1,'Audi', 52642)")
cur.execute("INSERT INTO cars VALUES(2,'Mercedes', 57127)")
cur.execute("INSERT INTO cars VALUES(3,'Skoda',9000)")
cur.execute("INSERT INTO cars VALUES(4,'Volvo',29000)")
cur.execute("INSERT INTO cars VALUES(5,'Bentley', 350000)")
cur.execute("INSERT INTO cars VALUES(6,'Citroen',21000)")
cur.execute("INSERT INTO cars VALUES(7,'Hummer',41400)")
cur.execute("INSERT INTO cars VALUES(8,'Volkswagen', 21600)")
else:
with sqlite3.connect('ydb.db') as con:
cur = con.cursor()
cur.execute("SELECT * FROM cars")
rows = cur.fetchmany(2)
print(rows)
В этом скрипте мы проверяем, существует ли файл ydb.db. Если файл не существует, он создается и генерируется новая таблица. Если база данных уже существует, мы извлекаем две строки из таблицы.
if not os.path.exists('test.db'):
con = sqlite3.connect('test.db')
...
Python SQLite — подстановка значений в SQL-запрос
Когда мы подставляем значения в запросы, мы используем заполнители вместо того, чтобы напрямую записывать значения. Такие запросы называются параметризованными. Параметризованные запросы повышают безопасность и производительность.
Модуль Python sqlite3 поддерживает два типа заполнителей: вопросительные знаки и именованные параметры.
Подстановка значений с помощью вопросительных знаков
import sqlite3
uId = 1
uPrice = 62300
con = sqlite3.connect('ydb.db')
with con:
cur = con.cursor()
cur.execute("UPDATE cars SET price=? WHERE id=?", (uPrice, uId))
print(f"Number of rows updated: {cur.rowcount}")
Метод rowcount возвращает количество обновленных строк.
Подстановка значений с помощью именованных параметров
import sqlite3
uId = 4
con = sqlite3.connect('ydb.db')
with con:
cur = con.cursor()
cur.execute("SELECT name, price FROM cars WHERE Id=:Id", {"Id": uId})
row = cur.fetchone()
print(f"{row[0]}, {row[1]}")
Python SQLite — хранение изображений в базе данных
Вставка изображений
Рассмотрим хранение изображений в базе данных SQLite. Конечно, сохранять изображения в базе данных нецелесообразно. Общая практика состоит в том, чтобы хранить изображения в каталогах файловой системы и хранить ссылки на изображения в базе данных. Но мы рассмотрим этот приём для общего понимания возможностей SQLite.
sqlite> CREATE TABLE images(id INTEGER PRIMARY KEY, data BLOB);
Для этого примера создаем новую таблицу под названием images. Для изображений используем тип данных BLOB (англ. Binary Large Object — двоичный большой объект) — массив двоичных данных.
import sqlite3
import sys
def readImage():
fin = None
try:
fin = open("sid.jpg", "rb")
img = fin.read()
return img
except IOError as e:
print(e)
sys.exit(1)
finally:
if fin:
fin.close()
con = None
try:
con = sqlite3.connect('ydb.db')
cur = con.cursor()
data = readImage()
binary = sqlite3.Binary(data)
cur.execute("INSERT INTO images(data) VALUES (?)", (binary,) )
con.commit()
except sqlite3.Error as e:
if con:
con.rollback()
print(e)
sys.exit(1)
finally:
if con:
con.close()
Рассмотрим скрипт подробнее:
Открываем и считываем изображения из каталога в базе данных.
try:
fin = open("sid.jpg", "rb")
img = fin.read()
return img
Мы считываем двоичные данные из файловой системы. У нас есть изображение в формате JPG, называемое sid.jpg.
binary = sqlite3.Binary(data)
Данные кодируются с использованием двоичного объекта SQLite.
cur.execute("INSERT INTO images(data) VALUES (?)", (binary,) )
Получение изображений
Выполним обратную операцию: получим изображение из таблицы базы данных.
import sqlite3
import sys
def writeImage(data):
fout = None
try:
fout = open('sid2.jpg','wb')
fout.write(data)
except IOError as e:
print(e)
sys.exit(1)
finally:
if fout:
fout.close()
con = None
try:
con = sqlite3.connect('ydb.db')
cur = con.cursor()
cur.execute("SELECT data FROM images LIMIT 1")
data = cur.fetchone()[0]
writeImage(data)
except sqlite3.Error as e:
print(e)
sys.exit(1)
finally:
if con:
con.close()
Метаданные – это информация о данных в базе данных. Метаданные в SQLite содержат информацию о таблицах и столбцах, в которых мы храним данные.
Метаданные в SQLite можно получить с помощью команды PRAGMA. Объекты SQLite могут иметь атрибуты, которые являются метаданными. Наконец, мы также можем получить конкретные метатеги, запросив таблицу sqlite_master системы SQLite.
import sqlite3
con = sqlite3.connect('ydb.db')
with con:
cur = con.cursor()
cur.execute('PRAGMA table_info(cars)')
data = cur.fetchall()
for d in data:
print(f"{d[0]} {d[1]} {d[2]}")
В этом примере мы выполняем команду PRAGMA table_info(имя_таблицы), чтобы получить некоторую информацию о метаданных нашей таблицы cars.
ur.execute('PRAGMA table_info(cars)')
Команда PRAGMA table_info(имя_таблицы) возвращает по одной строке для каждого столбца в таблице cars. Столбцы в результирующем наборе включают порядковый номер столбца, имя столбца, тип данных, может ли столбец быть нулевым или нет, и значение по умолчанию для столбца.
for d in data:
print(f"{d[0]} {d[1]} {d[2]}")
В следующем примере выведем все строки из таблицы cars с именами их столбцов.
import sqlite3
con = sqlite3.connect('ydb.db')
with con:
cur = con.cursor()
cur.execute('SELECT * FROM cars')
col_names = [cn[0] for cn in cur.description]
rows = cur.fetchall()
print(f"{col_names[0]:3} {col_names[1]:10} {col_names[2]:7}")
for row in rows:
print(f"{row[0]:<3} {row[1]:<10} {row[2]:7}")
Мы выводим содержимое таблицы cars на консоль. Теперь мы также включаем названия столбцов. Записи выровнены по именам столбцов.
col_names = [cn[0] for cn in cur.description]
Получаем имена столбцов из свойства description объекта cursor.
print(f"{col_names[0]:3} {col_names[1]:10} {col_names[2]:7}")
В этой строке выводятся названия трех столбцов таблицы cars.
for row in rows:
print(f"{row[0]:<3} {row[1]:<10} {row[2]:7}")
Печатаем строки с помощью цикла for. Данные выровнены по именам столбцов.
id name price
1 Audi 62300
2 Mercedes 57127
3 Skoda 9000
4 Volvo 29000
5 Bentley 350000
6 Hummer 41400
7 Volkswagen 21600
В последнем примере перечислим все таблицы в базе данных ydb.db.
import sqlite3
con = sqlite3.connect('ydb.db')
with con:
cur = con.cursor()
cur.execute("SELECT name FROM sqlite_master WHERE type='table'")
rows = cur.fetchall()
for row in rows:
print(row[0])
Пример кода выводит на терминал все доступные таблицы в текущей базе данных.
cur.execute("SELECT name FROM sqlite_master WHERE type='table'")
Имена таблиц хранятся внутри системной таблицы sqlite_master.
cars
images
Python SQLite — экспорт данных
Создадим резервную копию таблиц нашей базы данных.
import sqlite3
cars = (
(1, 'Audi', 52643),
(2, 'Mercedes', 57642),
(3, 'Skoda', 9000),
(4, 'Volvo', 29000),
(5, 'Bentley', 350000),
(6, 'Hummer', 41400),
(7, 'Volkswagen', 21600)
)
def writeData(data):
f = open('cars.sql', 'w')
with f:
f.write(data)
con = sqlite3.connect(':memory:')
with con:
cur = con.cursor()
cur.execute("DROP TABLE IF EXISTS cars")
cur.execute("CREATE TABLE cars(id INT, name TEXT, price INT)")
cur.executemany("INSERT INTO cars VALUES(?, ?, ?)", cars)
cur.execute("DELETE FROM cars WHERE price < 30000")
data = 'n'.join(con.iterdump())
writeData(data)
В этом примере воссоздаем таблицу cars в временной базе данных. Удаляем некоторые строки из таблицы и сохраняем текущее состояние таблицы в файл cars.sql. Этот файл будет служить резервной копией текущей таблицы.
def writeData(data):
f = open('cars.sql', 'w')
with f:
f.write(data)
Данные из таблицы записываются в файл.
con = sqlite3.connect(':memory:')
Создаем таблицу во временной базе данных.
cur.execute("DROP TABLE IF EXISTS cars")
cur.execute("CREATE TABLE cars(id INT, name TEXT, price INT)")
cur.executemany("INSERT INTO cars VALUES(?, ?, ?)", cars)
cur.execute("DELETE FROM cars WHERE price < 30000")
Создаём таблицу cars, вставляем значения и удаляем строки, где цена меньше 30000.
data = 'n'.join(con.iterdump())
con.iterdump возвращает итератор для дампа базы данных в текстовом формате SQL. Встроенная функция join принимает итератор и объединяет все строки в итераторе, разделенные новой строкой. Эти данные записываются в файл cars.sql с помощью функции writeData.
Python SQLite — импорт данных
Выполним обратную операцию. Импортируем ранее сохранённую таблицу.
import sqlite3
def readData():
f = open('cars.sql', 'r')
with f:
data = f.read()
return data
con = sqlite3.connect(':memory:')
with con:
cur = con.cursor()
sql = readData()
cur.executescript(sql)
cur.execute("SELECT * FROM cars")
rows = cur.fetchall()
for row in rows:
print(row)
Python SQLite — транзакции
Транзакция – это процесс выполнения операций с базой данных. В одной транзакции может быть несколько операций. Операции в транзакции могут быть либо выполнены целиком и полностью все, либо не выполнены вообще.
Транзакции начинаются с инструкции BEGIN TRANSACTION и завершаются инструкциями COMMIT или ROLLBACK.
SQLite поддерживает три уровня транзакций: DEFERRED, IMMEDIATE and EXCLUSIVE.
- DEFERRED – данный режим блокировки является режимом по умолчанию в SQLite. В режиме DEFERRED SQLite начинает блокировать таблицы только после того, как будет начато выполнение какой-либо команды, при этом другие транзакции могут читать данные из таблицы, но не могут их изменять.
- IMMEDIATE – в данном режим происходит блокировка базы данных, как только будет выполнена команда BEGIN. При это режим IMMEDIATE в SQLIte допускает, что другие транзакции могут читать данные из базы данных, но не записывать.
- EXCLUSIVE – самый высокий уровень блокировки базы данных в SQLite. Режим EXCLUSIVE блокирует базу данных при выполнении команды BEGIN и при этом другие транзакции не могут ни читать данные из базы данных, ни уж тем более изменять данные.
Модуль Python SQLite также поддерживает режим автоматической фиксации, при котором все изменения в таблицах вступают в силу немедленно.
import sqlite3
import sys
con = None
try:
con = sqlite3.connect('ydb.db')
cur = con.cursor()
cur.execute("DROP TABLE IF EXISTS friends")
cur.execute("CREATE TABLE friends(id INTEGER PRIMARY KEY, name TEXT)")
cur.execute("INSERT INTO friends(name) VALUES ('Tom')")
cur.execute("INSERT INTO friends(name) VALUES ('Rebecca')")
cur.execute("INSERT INTO friends(name) VALUES ('Jim')")
cur.execute("INSERT INTO friends(name) VALUES ('Robert')")
#con.commit()
except sqlite3.Error as e:
if con:
con.rollback()
print(e)
sys.exit(1)
finally:
if con:
con.close()
Мы создаем таблицу friends и пытаемся заполнить ее данными. Однако, как мы увидим, данные не фиксируются.
#con.commit()
Метод фиксации con.commit() закомментирован. Если мы раскомментируем строку, данные будут записаны в таблицу.
Python SQLite autocommit
В режиме автоматической фиксации инструкция SQL выполняется немедленно.
import sqlite3
import sys
con = None
try:
con = sqlite3.connect('ydb.db', isolation_level = None)
cur = con.cursor()
cur.execute("DROP TABLE IF EXISTS friends")
cur.execute("CREATE TABLE friends(id INTEGER PRIMARY KEY, name TEXT)")
cur.execute("INSERT INTO friends(name) VALUES ('Tom')")
cur.execute("INSERT INTO friends(name) VALUES ('Rebecca')")
cur.execute("INSERT INTO friends(name) VALUES ('Jim')")
cur.execute("INSERT INTO friends(name) VALUES ('Robert')")
except sqlite3.Error as e:
print(e)
sys.exit(1)
finally:
if con:
con.close()
В этом примере мы подключаемся к базе данных в режиме автоматической фиксации.
con = sqlite3.connect('ydb.db', isolation_level = None)
Смотрите также: Руководство по стилю SQL
Сообщение Python SQLite — учебное пособие на русском языке появились сначала на Веб-студия Юсота.
Source: usota.ru python
