Модуль sqlite3 языка Python
Сами по себе СУБД редко используются для работы с базами данных. В том смысле, что в реальных проектах связки БД + СУБД бывает недостаточно. Обычно с СУБД работают через какой-либо язык программирования. Это позволяет более гибко принимать запросы, обрабатывать ответы перед передачей их куда-либо далее. Ведь у императивного, а не декларативного как SQL, языка программирования средств для работы с данными больше, да и логика богаче.
При этом неизбежна определенная специфика, накладываемая языком программирования и особенностями его работы с СУБД. Есть команды на подключение к БД, использование объекта «курсора», выполнение SQL-запросов, сохранение изменений в БД и др.
Инструменты для работы с конкретной СУБД не являются базовыми командами и объектами самого языка. Обычно они подключаются через импорт модуля или библиотеки. Так модуль sqlite3 входит в установочный пакет языка Python, является компонентом стандартной библиотеки и не требует отдельной загрузки и установки. Однако его все равно надо импортировать:
>>> import sqlite3
Библиотеку SQLite также не требуется устанавливать отдельно. Она есть в установочном пакете Python. Непосредственно модуль sqlite3 – это API к СУБД SQLite. Своего рода адаптер, который переводит команды, написанные на Питоне, в команды, которые понимает SQLite. Как и наоборот, доставляет ответы от SQLite в python-программу.
Модуль sqlite3 содержит много классов, функций и констант. Их перечень можно посмотреть с помощью функции dir().
Вызов функции connect() приводит к созданию объекта-экземпляра от класса Connection. Этот объект обеспечивает связь с файлом базы данных, представляет конкретную БД в программе:
>>> db = sqlite3.connect(‘site.sqlite’) >>> type(db)
Почему объект создается с помощью функции, а не от самого класса? Видимо дело в том, что помимо имени-адреса базы данных может передаваться ряд других необязательных аргументов, первичной обработкой которых занимается функция.
После того как экземпляр Connection создан, чтобы выполнять SQL-команды, над создать еще один объект, но теперь уже от класса Cursor. Делается это с помощью метода cursor() объекта типа Connection:
SQL-команды выполняются с помощью метода execute() и некоторых других. Если запрос длинный, и его удобно разбить на несколько строк, используют тройные кавычки. Создадим таблицы:
>>> cur.execute(»’ . CREATE TABLE sections ( . _id INTEGER PRIMARY KEY, . name TEXT)»’) >>> cur.execute(»’ . CREATE TABLE pages ( . _id INTEGER PRIMARY KEY AUTOINCREMENT, . title TEXT, . url TEXT NOT NULL, . theme INTEGER NOT NULL, . num INTEGER NOT NULL DEFAULT 100, . FOREIGN KEY (theme) . REFERENCES sections(_id)) . »’)
Если нужна поддержка внешнего ключа включим ее:
>>> cur.execute(«PRAGMA foreign_keys = ON»)
Обратим внимание, что в конце SQL-запросов здесь точка с запитой не ставятся. Также метод возвращает сам объект.
Заполнять таблицы можно тоже с помощью execute(). Однако, если требуется вставить несколько записей, лучше воспользоваться методом executemany():
>>> themes = [ . (1, ‘Information’), . (2, ‘Digital Systems’), . (3, ‘Boolean Algebra’)] >>> cur.executemany(»’ . INSERT INTO sections . VALUES (?, ?)»’, themes)
Мы создаем список из кортежей. Каждый кортеж – это отдельная запись таблицы. Метод executemany() выполняет SQL-команду по отношению к каждому элементу списка. При этом данные из кортежа подставляются вместо знаков вопроса. Такая подстановка работает и через execute():
>>> cur.execute(»’ . INSERT INTO sections VALUES . (4, ?)»’, (‘Algorithm’,))
То, что подставляется, должно быть кортежем. Через знаки вопроса кортеж как бы распаковывается.
Есть еще метод executescript(). В качестве аргумента передается скрипт на языке SQL, который может включать несколько запросов, каждый из которых заканчивается точкой с запятой.
У объекта-курсора есть методы fetchone(), fetchmany() и fetchall(), которые позволяют извлекать из него данные, если sql-запрос предполагал их передачу. По-сути они наделяют курсор свойствами объекта-итератора (такой имеет метод __next__()):
>>> cur.execute("SELECT * FROM sections") >>> cur.fetchone() (1, 'Information') >>> cur.fetchone() (2, 'Digital Systems') >>> cur.__next__() (3, 'Boolean Algebra')
Два других метода:
>>> cur.execute("SELECT * FROM sections") >>> cur.fetchall() [(1, 'Information'), (2, 'Digital Systems'), (3, 'Boolean Algebra'), (4, 'Algorithm')] >>> cur.fetchall() [] >>> cur.execute("SELECT * FROM sections") >>> cur.fetchmany(2) [(1, 'Information'), (2, 'Digital Systems')] >>> cur.fetchmany(2) [(3, 'Boolean Algebra'), (4, 'Algorithm')] >>> cur.fetchmany(2) []
Для того, чтобы корректно завершить работу с базой данных, надо применить изменения (выполнить транзакцию) и разорвать соединение. Обратите внимание, это делается по отношению к экземпляру Connection, а не Cursor:
>>> db.commit() >>> db.close()
Закрытие без commit() приведет к потере изменений, сделанных за сессию. Если нужно откатить, а не применить, изменения текущей сессии, используется метод rollback().
X Скрыть Наверх
Введение в реляционные базы данных. SQLite
Драйвер для баз данных SQLite3
SQLite — это библиотека языка C, которая предоставляет легковесную дисковую базу данных. База данных SQLite не требует отдельного серверного процесса и позволяет получить доступ к базе данных, используя нестандартный вариант языка запросов SQL .
Некоторые приложения могут использовать SQLite для внутреннего хранения данных. Также возможно создать прототип приложения с использованием SQLite , а затем перенести код в большую базу данных, такую как PostgreSQL или Oracle .
Модуль sqlite3 обеспечивает интерфейс SQL , совместимый со спецификацией DB-API 2.0, описанной в PEP 249.
Многопоточность.
В старых версиях SQLite возникали проблемы с разделением соединений между потоками. По этому модуль sqlite3 Python запрещает разделять соединения и курсоры между потоками. Если попытаться это сделать, то получим исключение во время выполнения.
Единственным методом, имеющим смысл вызывать только из другого потока является вызов метода connect.interrupt() .
Примеры использования:
Чтобы использовать модуль, необходимо сначала создать объект Connection , который представляет базу данных. В примерах, данные будут храниться в файле example.db :
import sqlite3 conn = sqlite3.connect('example.db')
Можно также указать специальное имя :memory: для создания базы данных в оперативной памяти.
Получив соединение Connection , можно создать объект Cursor и вызвать его метод cursor.execute() для выполнения команд SQL:
cursor = conn.cursor() # Создать таблицу cursor.execute('''CREATE TABLE stocks (date text, trans text, symbol text, qty real, price real)''') # Вставить строку данных cursor.execute("INSERT INTO stocks VALUES ('2006-01-05','BUY','RHAT',100,35.14)") # Сохранить (зафиксировать) изменения conn.commit() # Можно закрыть соединение, если оно больше не нужно. # Убедитесь, что все изменения были зафиксированы, или они будут потеряны. conn.close()
Данные, которые были сохранены, являются постоянными и доступны в следующих сеансах:
import sqlite3 conn = sqlite3.connect('example.db') cursor = conn.cursor()
Обычно операции SQL могут использовать значения из переменных в Python. Нет необходимости собирать запрос, используя строковые операции Python, потому что это небезопасно. Это делает программу уязвимой для атаки SQL-инъекцией.
Вместо этого используйте подстановку параметров DB-API. Поставьте символ ‘?’ в качестве заполнителя везде, где вы хотите использовать значение переменной, а затем предоставьте кортеж значений в качестве второго аргумента метода курсора cursor.execute() . Другие модули базы данных могут использовать другой заполнитель, такой как ‘%s’ или ‘:1’ .
# Никогда не делай этого - небезопасно! symbol = 'RHAT' cursor.execute("SELECT * FROM stocks WHERE symbol = '%s'" % symbol) # Делайте все время так, как показано ниже. t = ('RHAT',) cursor.execute('SELECT * FROM stocks WHERE symbol=?', t) print(cursor.fetchone()) # Larger example that inserts many records at a time purchases = [('2020-03-28', 'BUY', 'IBM', 1000, 45.00), ('2020-04-05', 'BUY', 'MSFT', 1000, 72.00), ('2020-04-06', 'SELL', 'IBM', 500, 53.00), ] cursor.executemany('INSERT INTO stocks VALUES (. )', purchases)
Чтобы получить данные после выполнения оператора SELECT , можно либо обработать курсор как итератор, вызвать метод курсора cursor.fetchone() , чтобы получить единственную совпадающую строку, либо вызвать cursor.fetchall() , чтобы получить список совпадающих строк.
В этом примере используется форма итератора:
>>> for row in cursor.execute('SELECT * FROM stocks ORDER BY price'): . print(row) . # ('2020-01-05', 'BUY', 'RHAT', 100, 35.14) # ('2020-03-28', 'BUY', 'IBM', 1000, 45.0) # ('2020-04-06', 'SELL', 'IBM', 500, 53.0) # ('2020-04-05', 'BUY', 'MSFT', 1000, 72.0)
- КРАТКИЙ ОБЗОР МАТЕРИАЛА.
- Типы SQLite3 и Python
- Хранение типов Python в базах данных SQLite3
- Пример преобразования типов между SQLite и Python
- Встроенные адаптеры и конвертеры модуля sqlite3
- Пользовательские адаптеры типов Python к значениям SQLite3
- Заполнители значений Python в запросах к SQLite3
- Управление транзакциями в модуле sqlite3 Python
- Эффективное использование sqlite3
- Функция connect() модуля sqlite3
- Функция register_converter() модуля sqlite3
- Функция register_adapter() модуля sqlite3
- Методы объекта Connection модуля sqlite3
- Методы объекта Cursor модуля sqlite3
- Объект Row модуля sqlite3
- Сравнение кириллицы в SQLite без учета регистра
- Функции и константы модуля sqlite3
- Исключения модуля sqlite3
- Объект Blob() модуля sqlite3
- Импорт CSV и XLSX(XLS) файла в sqlite3, экспорт данных в CSV
- Как работать с URI SQLite3
- Интерфейс командной строки модуля sqlite3
Python и SQLite
Имеется база данных (файл) sqlite (например файл БД называется sqlitest). Как узнать версию движка sqlite в котором эта база была создана?
# cat sqlitest | less SQLite format 3^@^D^@^A^A^@@ .
В первых 16 байтах написана версия БД. В этом случае версии 3.
P.S. Младшие номера версий и уж тем более номера билдов на структуру файла базы не влияют.
Значение констант БД SQlite
~$ ipython /var/lib/python-support/python2.6/IPython/Magic.py:38: DeprecationWarning: the sets module is deprecated from sets import Set Python 2.6.2 (release26-maint, Apr 19 2009, 01:56:41) Type "copyright", "credits" or "license" for more information. IPython 0.9.1 -- An enhanced Interactive Python. ? -> Introduction and overview of IPython's features. %quickref -> Quick reference. help -> Python's own help system. object? -> Details about 'object'. ?object also works, ?? prints more. In [7]: import sqlite3
apilevel = 2.0 — sqlite поддерживает DB- API 2.0
In [10]: sqlite3.apilevel Out[10]: '2.0'
paramstyle — тип используемых пометок при подстановке параметров. Возможны следующие значения этой константы:
format форматирование в стиле языка ANSI C (например, ‘%s’, ‘%i’)
pyformat использование именованных спецификаторов формата в стиле Python (‘%(item)s’)
qmark использование знаков ‘?’ для пометки мест подстановки параметров
numeric использование номеров позиций (‘:1’)
named использование имен подставляемых параметров (‘:name’)
In [11]: sqlite3.paramstyle Out[11]: 'qmark'
threadsafety — целочисленная константа, описывающая возможности модуля при использовании потоков управления:
0 Модуль не поддерживает потоки. 1 Потоки могут совместно использовать модуль, но не соединения. 2 Потоки могут совместно использовать модуль и соединения. 3 Потоки могут совместно использовать модуль, соединения и курсоры. (Под совместным использованием здесь понимается возможность использования упомянутых ресурсов без применения семафоров).
In [12]: sqlite3.threadsafety Out[12]: 1
Работа с базой данных SQLite
В общем случае последовательность работы с БД выглядит так:
| № | Метод | Note |
|---|---|---|
| 1 | Подключение к базе данных (вызов connect() с получением объекта-соединения) | |
| 2 | Создание одного или нескольких курсоров (вызов метода объекта-соединения cursor() с получением объекта-курсора) | |
| 3 | Исполнение команды или запроса (вызов метода execute() или его вариантов) | |
| 4 | Получение результатов запроса (вызов метода fetchone() или его вариантов) | |
| 5 | Завершение транзакции или ее откат (вызов метода объекта-соединения commit() или rollback()) | commit() автоматически происходит посде закрытия соединения (после метода close()) |
| 6 | Когда все необходимые транзакции произведены, подключение закрывается вызовом метода close() объекта-соединения |
DB-API
Аббревиатура DB- API объединяет два понятия: DB (Database, база данных) и API (Application Program Interface, интерфейс прикладной программы). DB- API определяет интерфейс прикладной программы с базой данных. Этот интерфейс должен реализовывать все модули расширения, которые служат для связи Python-программ с базами данных. Единый API позволяет абстрагироваться от марки используемой базы данных, при необходимости довольно легко менять одну СУБД на другую, изучив всего один набор функций и методов.
Ссылка на описание текущей версии DB- API : Python Database API Specification v2.0
Ссылка на список поддерживаемых БД, через модули расширения DatabaseInterfaces
Модуль расширения pysqlite pysqlite DB API 2.0 Drivers for SQLite
Объект-курсор
Курсор (от англ. cursor - CURrrent Set Of Records, текущий набор записей) служит для работы с результатом запроса. Результатом запроса обычно является одна или несколько прямоугольных таблиц со столбцами-полями и строками-записями. Приложение может читать и обрабатывать полученные таблицы и записи в таблице по одной, поэтому в курсоре хранится информация о текущей таблице и записи. Конкретный курсор в любой момент времени связан с выполнением одной SQL-инструкции. Атрибуты объекта-курсора тоже определены DB-API: • arraysize Атрибут, равный количеству записей, возвращаемых методом fetchmany(). По умолчанию равен 1. • callproc(procname[, params]) Вызывает хранимую процедуру procname с параметрами из изменчивой последовательности params. Хранимая процедура может изменить значения некоторых параметров последовательности. Метод может возвратить результат, доступ к которому осуществляется через fetch-методы. • close() Закрывает объект-курсор. • description Этот доступный только для чтения атрибут является последовательностью из семиэлементных последовательностей. Каждая из этих последовательностей содержит информацию, описывающую один столбец результата: • (name, type_code, display_size, internal_size, precision, scale, null_ok) Первые два элемента (имя и тип) обязательны, а вместо остальных (размер для вывода, внутренний размер, точность, масштаб, возможность задания пустого значения) может быть значение None. Этот атрибут может быть равным None для операций, не возвращающих значения. • execute(operation[, parameters]) Исполняет запрос к базе данных или команду СУБД. Параметры (parameters) могут быть представлены в принятой в базе данных нотации в соответствии с атрибутом paramstyle, описанным выше. • executemany(operation, seq_of_parameters) Выполняет серию запросов или команд, подставляя параметры в заданный шаблон. Параметр seq_of_parameters задает последовательность наборов параметров. • fetchall() Возвращает все (или все оставшиеся) записи результата запроса. • fetchmany([size]) Возвращает следующие несколько записей из результатов запроса в виде последовательности последовательностей. Пустая последовательность означает отсутствие данных. Необязательный параметр size указывает количество возвращаемых записей (реально возвращаемых записей может быть меньше). По умолчанию size равен атрибуту arraysize объекта- курсора. • fetchone() Возвращает следующую запись (в виде последовательности) из результата запроса или None при отсутствии данных. • nextset() Переводит курсор к началу следующего набора данных, полученного в результате запроса (при этом часть записей в предыдущем наборе может остаться непрочитанной). Если наборов больше нет, возвращает None. Не все базы данных поддерживают возврат нескольких наборов результатов за одну операцию. • rowcount Количество записей, полученных или затронутых в результате выполнения последнего запроса. В случае отсутствия execute-запросов или невозможности указать количество записей равен -1. • setinputsizes(sizes) Предопределяет области памяти для параметров, используемых в операциях. Аргумент sizes задает последовательность, где каждый элемент соответствует одному входному параметру. Элемент может быть объектом- типом соответствующего параметра или целым числом, задающим длину строки. Он также может иметь значение None, если о размере входного параметра ничего нельзя сказать заранее или он предполагается очень большим. Метод должен быть вызван до execute-методов. • setoutputsize(size[, column]) Устанавливает размер буфера для выходного параметра из столбца с номером column. Если column не задан, метод устанавливает размер для всех больших выходных параметров. Может использоваться, например, для получения больших бинарных объектов (Binary Large Object, BLOB).
Типы данных в SQLite version 3.0
Типы данных в DB- API :
Спецификация Python Database API Specification v2.0 предусматривает названия для объектов-типов, используемых для описания полей базы данных:
| Объект | Тип |
|---|---|
| STRING | Строка и символ |
| BINARY | Бинарный объект |
| NUMBER | Число |
| DATETIME | Дата и время |
| ROWID | Идентификатор записи |
| None | NULL-значение (отсутствующее значение) |
С каждым типом данных (в реальности это — классы) связан конструктор. Совместимый с DB- API модуль должен определять следующие конструкторы:
Работа с базой данных SQLite в Python – примеры
Разберем работу с SQLite в Python и пример создания базы данных . Сначала вам нужно установить Python и SQLite на свой компьютер.
Установка Python
Используйте следующий код:
sudo apt-get update sudo apt-get upgrade python
Нажмите y, и установка будет завершена в течение нескольких секунд.
Установка SQLite
- введите следующую команду:
sudo apt-get install sqlite3 libsqlite3-dev
- По завершению проверьте установку: терминал sqlite должен выдать вам подсказку и информацию о версии.
sqlite3
- Перейдите в нужную папку и создайте базу данных: sqlite3 database.db. Database.db будет создана в папке, которую вы дали команде.
Чтобы проверить, создана ли ваша база данных, используйте следующую команду в терминале sqlite3:
.databases
Примечание. Чтобы подключить SQLite к Python, вам не нужно устанавливать модуль подключения отдельно, поскольку он поставляется по умолчанию вместе с Python версии 2.5.x и выше.
Соединение SQLite с Python
Создайте файл python “connect.py” со следующим кодом:
#!/usr/bin/python import sqlite3 conn = sqlite3.connect('javatpoint.db') print "Opened database successfully";
Выполните следующий оператор в командной строке:
python connect.py
Соединение создается с базой данных javatpoint. Теперь вы можете создать таблицу.
Создание таблицы
Создайте таблицу «Сотрудники» в базе данных «javatpoint».
Создайте файл python createtable.py со следующим кодом:
#!/usr/bin/python import sqlite3 conn = sqlite3.connect('javatpoint.db') print "Opened database successfully"; conn.execute('''CREATE TABLE Employees (ID INT PRIMARY KEY NOT NULL, NAME TEXT NOT NULL, AGE INT NOT NULL, ADDRESS CHAR(50), SALARY REAL);''') print "Table created successfully"; conn.close()
Выполните следующий оператор в командной строке:
python createtable.py
В базе данных javatpoint создается таблица «Сотрудники».
Вставка записей
Вставьте несколько записей в таблицу «Сотрудники».
Создайте файл python “connection.py” со следующим кодом:
#!/usr/bin/python import sqlite3 conn = sqlite3.connect('javatpoint.db') print "Opened database successfully"; conn.execute("INSERT INTO Employees (ID,NAME,AGE,ADDRESS,SALARY) \ VALUES (1, 'Ajeet', 27, 'Delhi', 20000.00 )"); conn.execute("INSERT INTO Employees (ID,NAME,AGE,ADDRESS,SALARY) \ VALUES (2, 'Allen', 22, 'London', 25000.00 )"); conn.execute("INSERT INTO Employees (ID,NAME,AGE,ADDRESS,SALARY) \ VALUES (3, 'Mark', 29, 'CA', 200000.00 )"); conn.execute("INSERT INTO Employees (ID,NAME,AGE,ADDRESS,SALARY) \ VALUES (4, 'Kanchan', 22, 'Ghaziabad ', 65000.00 )"); conn.commit() print "Records inserted successfully"; conn.close()
Выполните следующий оператор в командной строке:
python connection.py
Записи успешно вставлены.
Выбор записи
Теперь вы можете получать и отображать свои записи из таблицы «Сотрудники» с помощью оператора SELECT.
Создайте файл python “select.py” со следующим кодом:
#!/usr/bin/python import sqlite3 conn = sqlite3.connect('javatpoint.db') data = conn.execute("select * from Employees"); for row in data: print "ID = ", row[0] print "NAME = ", row[1] print "ADDRESS = ", row[2] print "SALARY = ", row[3], "\n" conn.close();
Выполните следующий оператор в командной строке:
python select.py
Просмотрите все записи, которые вы вставили ранее. С помощью тех же процедур вы можете обновить и удалить таблицу в базе данных SQLite, используя Python.