sqlite3
— Интерфейс DB-API 2.0 для баз данных SQLite¶
Исходный код: Lib/sqlite3/
SQLite - это библиотека на языке Си, которая предоставляет облегченную дисковую базу данных, не требующую отдельного серверного процесса и позволяющую обращаться к базе данных с использованием нестандартного варианта языка запросов SQL. Некоторые приложения могут использовать SQLite для внутреннего хранения данных. Также можно создать прототип приложения с помощью SQLite, а затем перенести код в более крупную базу данных, такую как PostgreSQL или Oracle.
Модуль sqlite3
был написан Герхардом Херингом. Он предоставляет интерфейс SQL, совместимый со спецификацией DB-API 2.0, описанной в PEP 249, и требует SQLite версии 3.7.15 или новее.
Этот документ включает в себя четыре основных раздела:
Руководство учит, как пользоваться модулем
sqlite3
.Ссылка описывает классы и функции, определенные этим модулем.
Практические руководства подробно описывает, как выполнять конкретные задачи.
Объяснение предоставляет подробную информацию о контроле транзакций.
См.также
- https://www.sqlite.org
Веб-страница SQLite; документация описывает синтаксис и доступные типы данных для поддерживаемого диалекта SQL.
- https://www.w3schools.com/sql/
Учебное пособие, справочник и примеры для изучения синтаксиса SQL.
- PEP 249 - Спецификация API базы данных 2.0
БОДРЯЩИЙ настрой, написанный Марком-Андре Лембургом.
Руководство¶
В этом руководстве вы создадите базу данных фильмов на Monty Python, используя базовые функции sqlite3
. Предполагается фундаментальное понимание концепций баз данных, включая cursors и transactions.
Во-первых, нам нужно создать новую базу данных и открыть подключение к базе данных, чтобы разрешить sqlite3
работать с ней. Вызовите sqlite3.connect()
, чтобы создать соединение с базой данных tutorial.db
в текущем рабочем каталоге, неявно создав его, если он не существует:
import sqlite3
con = sqlite3.connect("tutorial.db")
Возвращаемый объект Connection
con
представляет собой подключение к базе данных на диске.
Чтобы выполнять инструкции SQL и извлекать результаты из SQL-запросов, нам нужно будет использовать курсор базы данных. Вызовите con.cursor()
для создания Cursor
:
cur = con.cursor()
Теперь, когда у нас есть подключение к базе данных и курсор, мы можем создать таблицу базы данных movie
со столбцами для названия, года выпуска и оценки в обзоре. Для простоты мы можем просто использовать имена столбцов в объявлении таблицы - благодаря функции flexible typing в SQLite, указывать типы данных необязательно. Выполните инструкцию CREATE TABLE
, вызвав cur.execute(...)
:
cur.execute("CREATE TABLE movie(title, year, score)")
Мы можем убедиться, что новая таблица создана, выполнив запрос к таблице sqlite_master
, встроенной в SQLite, которая теперь должна содержать запись для определения таблицы movie
(подробнее смотрите в разделе The Schema Table). Выполните этот запрос, вызвав cur.execute(...)
, присвойте результат res
и вызовите res.fetchone()
, чтобы получить результирующую строку:
>>> res = cur.execute("SELECT name FROM sqlite_master")
>>> res.fetchone()
('movie',)
Мы видим, что таблица создана, так как запрос возвращает tuple
, содержащий имя таблицы. Если мы запросим sqlite_master
для несуществующей таблицы spam
, res.fetchone()
будет возвращено None
:
>>> res = cur.execute("SELECT name FROM sqlite_master WHERE name='spam'")
>>> res.fetchone() is None
True
Теперь добавьте две строки данных, предоставленных в виде литералов SQL, выполнив инструкцию INSERT
, еще раз вызвав cur.execute(...)
:
cur.execute("""
INSERT INTO movie VALUES
('Monty Python and the Holy Grail', 1975, 8.2),
('And Now for Something Completely Different', 1971, 7.5)
""")
Оператор INSERT
неявно открывает транзакцию, которую необходимо зафиксировать перед сохранением изменений в базе данных (подробнее см. Контроль транзакций). Вызовите con.commit()
для объекта connection для фиксации транзакции:
con.commit()
Мы можем проверить, что данные были вставлены правильно, выполнив запрос SELECT
. Используйте уже знакомый cur.execute(...)
, чтобы присвоить результат res
, и вызовите res.fetchall()
, чтобы вернуть все результирующие строки:
>>> res = cur.execute("SELECT score FROM movie")
>>> res.fetchall()
[(8.2,), (7.5,)]
Результатом является list
из двух tuple
, по одному на строку, каждая из которых содержит значение score
этой строки.
Теперь вставьте еще три строки, вызвав cur.executemany(...)
:
data = [
("Monty Python Live at the Hollywood Bowl", 1982, 7.9),
("Monty Python's The Meaning of Life", 1983, 7.5),
("Monty Python's Life of Brian", 1979, 8.0),
]
cur.executemany("INSERT INTO movie VALUES(?, ?, ?)", data)
con.commit() # Remember to commit the transaction after executing INSERT.
Обратите внимание, что ?
заполнители используются для привязки data
к запросу. Всегда используйте заполнители вместо string formatting для привязки значений Python к инструкциям SQL, чтобы избежать SQL injection attacks (подробнее см. Как использовать заполнители для привязки значений в SQL-запросах).
Мы можем проверить, что новые строки были вставлены, выполнив запрос SELECT
, на этот раз повторив результаты запроса:
>>> for row in cur.execute("SELECT year, title FROM movie ORDER BY year"):
... print(row)
(1971, 'And Now for Something Completely Different')
(1975, 'Monty Python and the Holy Grail')
(1979, "Monty Python's Life of Brian")
(1982, 'Monty Python Live at the Hollywood Bowl')
(1983, "Monty Python's The Meaning of Life")
Каждая строка состоит из двух элементов tuple
из (year, title)
, соответствующих столбцам, выбранным в запросе.
Наконец, убедитесь, что база данных была записана на диск, вызвав команду con.close()
, чтобы закрыть существующее соединение, открыть новое, создать новый курсор, а затем запросить базу данных:
>>> con.close()
>>> new_con = sqlite3.connect("tutorial.db")
>>> new_cur = new_con.cursor()
>>> res = new_cur.execute("SELECT title, year FROM movie ORDER BY score DESC")
>>> title, year = res.fetchone()
>>> print(f'The highest scoring Monty Python movie is {title!r}, released in {year}')
The highest scoring Monty Python movie is 'Monty Python and the Holy Grail', released in 1975
Теперь вы создали базу данных SQLite с помощью модуля sqlite3
, вставили данные и извлекли из нее значения несколькими способами.
См.также
Практические руководства для дальнейшего чтения:
Объяснение для получения подробной информации о контроле транзакций.
Ссылка¶
Функции модуля¶
- sqlite3.connect(database, timeout=5.0, detect_types=0, isolation_level='DEFERRED', check_same_thread=True, factory=sqlite3.Connection, cached_statements=128, uri=False)¶
Откройте подключение к базе данных SQLite.
- Parameters:
database (path-like object) – Путь к файлу базы данных, который нужно открыть. Вы можете передать
":memory:"
, чтобы создать SQLite database existing only in memory и открыть подключение к нему.timeout (float) – Сколько секунд должно ждать соединение, прежде чем появится сообщение
OperationalError
, когда таблица заблокирована. Если другое соединение открывает транзакцию для изменения таблицы, эта таблица будет заблокирована до тех пор, пока транзакция не будет зафиксирована. По умолчанию пять секунд.detect_types (int) – Контролируйте, выполняются ли поиск типов данных, отличных от natively supported by SQLite, для преобразования в типы Python, и если да, то каким образом, с помощью преобразователей, зарегистрированных в
register_converter()
. Чтобы включить это, установите любое сочетание (используя|
, побитовое или) изPARSE_DECLTYPES
иPARSE_COLNAMES
. Имена столбцов имеют приоритет перед объявленными типами, если установлены оба флага. Типы не могут быть обнаружены для сгенерированных полей (например,max(data)
), даже если задан параметр detect_types; вместо этого будет возвращенstr
. По умолчанию (0
), функция определения типа отключена.isolation_level (str | None) –
isolation_level
соединения, контролирующего, будут ли неявно открываться транзакции и каким образом. Может быть"DEFERRED"
(по умолчанию),"EXCLUSIVE"
или"IMMEDIATE"
; илиNone
, чтобы неявно отключить открытие транзакций. Подробнее см. Контроль транзакций.check_same_thread (bool) – Если
True
(по умолчанию), тоProgrammingError
будет поднято, если подключение к базе данных используется потоком, отличным от того, который его создал. ЕслиFalse
, доступ к соединению может осуществляться в нескольких потоках; пользователю может потребоваться сериализовать операции записи, чтобы избежать повреждения данных. Дополнительную информацию смотрите в разделеthreadsafety
.factory (Connection) – Пользовательский подкласс
Connection
для создания соединения, если это не класс по умолчаниюConnection
.cached_statements (int) – Количество инструкций, которые
sqlite3
должны быть внутренне кэшированы для этого соединения, чтобы избежать дополнительных затрат на синтаксический анализ. По умолчанию 128 инструкций.uri (bool) – Если задано значение
True
, база данных интерпретируется как URI с путем к файлу и необязательной строкой запроса. Часть схемы *должна быть"file:"
, а путь может быть относительным или абсолютным. Строка запроса позволяет передавать параметры в SQLite, позволяя использовать различные Как работать с URI SQLite.
- Result type:
Создает auditing event
sqlite3.connect
с аргументомdatabase
.Создает auditing event
sqlite3.connect/handle
с аргументомconnection_handle
.Изменено в версии 3.4: Добавлен параметр uri.
Изменено в версии 3.7: база данных теперь также может быть path-like object, а не только строкой.
Изменено в версии 3.10: Добавлено событие аудита
sqlite3.connect/handle
.
- sqlite3.complete_statement(statement)¶
Возвращает
True
, если оператор string * содержит одну или несколько полных инструкций SQL. Синтаксическая проверка или анализ любого рода не выполняются, кроме проверки отсутствия незакрытых строковых литералов, и оператор завершается точкой с запятой.Например:
>>> sqlite3.complete_statement("SELECT foo FROM bar;") True >>> sqlite3.complete_statement("SELECT foo") False
Эта функция может быть полезна при вводе данных из командной строки, чтобы определить, является ли введенный текст полной инструкцией SQL или требуется дополнительный ввод перед вызовом
execute()
.
- sqlite3.enable_callback_tracebacks(flag, /)¶
Включите или отключите трассировку обратного вызова. По умолчанию вы не получите никаких результатов отслеживания в пользовательских функциях, агрегатах, конвертерах, обратных вызовах авторизатора и т.д. Если вы хотите отладить их, вы можете вызвать эту функцию с флагом, установленным на
True
. После этого вы получите результаты отслеживания обратных вызовов наsys.stderr
. ИспользуйтеFalse
, чтобы снова отключить эту функцию.Зарегистрируйте
unraisable hook handler
для улучшения процесса отладки:>>> sqlite3.enable_callback_tracebacks(True) >>> con = sqlite3.connect(":memory:") >>> def evil_trace(stmt): ... 5/0 >>> con.set_trace_callback(evil_trace) >>> def debug(unraisable): ... print(f"{unraisable.exc_value!r} in callback {unraisable.object.__name__}") ... print(f"Error message: {unraisable.err_msg}") >>> import sys >>> sys.unraisablehook = debug >>> cur = con.execute("SELECT 1") ZeroDivisionError('division by zero') in callback evil_trace Error message: None
- sqlite3.register_adapter(type, adapter, /)¶
Зарегистрируйте адаптер callable, чтобы адаптировать тип Python type к типу SQLite. Адаптер вызывается с использованием объекта Python типа type в качестве единственного аргумента и должен возвращать значение type that SQLite natively understands.
- sqlite3.register_converter(typename, converter, /)¶
Зарегистрируйте конвертер callable, чтобы преобразовать объекты SQLite типа typename в объект Python определенного типа. Преобразователь вызывается для всех значений SQLite типа typename; ему передается объект
bytes
и он должен возвращать объект желаемого типа Python. Обратитесь к параметру detect_types изconnect()
для получения информации о том, как работает определение типов.Примечание: typename и название типа в вашем запросе совпадают без учета регистра.
Константы модуля¶
- sqlite3.PARSE_COLNAMES¶
Передайте значение этого флага параметру detect_types параметра
connect()
, чтобы найти функцию-преобразователь, используя имя типа, полученное из имени столбца запроса, в качестве ключа словаря преобразователя. Название типа должно быть заключено в квадратные скобки ([]
).SELECT p as "p [point]" FROM test; ! will look up converter "point"
Этот флаг может быть объединен с
PARSE_DECLTYPES
с помощью оператора|
(побитовое или).
- sqlite3.PARSE_DECLTYPES¶
Передайте значение этого флага параметру detect_types параметра
connect()
, чтобы найти функцию преобразования, использующую объявленные типы для каждого столбца. Типы объявляются при создании таблицы базы данных.sqlite3
будет выполнен поиск функции конвертера, использующей первое слово объявленного типа в качестве ключа словаря конвертера. Например:CREATE TABLE test( i integer primary key, ! will look up a converter named "integer" p point, ! will look up a converter named "point" n number(10) ! will look up a converter named "number" )
Этот флаг может быть объединен с
PARSE_COLNAMES
с помощью оператора|
(побитовое или).
- sqlite3.SQLITE_OK¶
- sqlite3.SQLITE_DENY¶
- sqlite3.SQLITE_IGNORE¶
Флаги, которые должны быть возвращены authorizer_callback callable, передаваемые в
Connection.set_authorizer()
, чтобы указать, является ли:Доступ разрешен (
SQLITE_OK
),Инструкция SQL должна быть прервана с ошибкой (
SQLITE_DENY
)Столбец следует рассматривать как значение
NULL
(SQLITE_IGNORE
)
- sqlite3.apilevel¶
Строковая константа, указывающая поддерживаемый уровень DB-API. Требуется для DB-API. Жестко задано значение
"2.0"
.
- sqlite3.paramstyle¶
Строковая константа, указывающая тип форматирования маркера параметра, ожидаемый модулем
sqlite3
. Требуемый DB-API. Жестко заданный в"qmark"
.Примечание
Также поддерживается стиль параметров
named
DB-API.
- sqlite3.sqlite_version_info¶
Номер версии библиотеки SQLite среды выполнения в виде
tuple
изintegers
.
- sqlite3.threadsafety¶
Целочисленная константа, требуемая DB-API 2.0 и указывающая уровень потокобезопасности, поддерживаемый модулем
sqlite3
. Этот атрибут устанавливается на основе значения по умолчанию threading mode, с которым компилируется базовая библиотека SQLite. Потоковыми моделями SQLite являются:Однопоточный: В этом режиме все мьютексы отключены, и SQLite небезопасно использовать более чем в одном потоке одновременно.
Многопоточность: В этом режиме SQLite может безопасно использоваться несколькими потоками при условии, что ни одно соединение с базой данных не используется одновременно в двух или более потоках.
Сериализованный: В сериализованном режиме SQLite может безопасно использоваться несколькими потоками без каких-либо ограничений.
Ниже приведены сопоставления режимов потоковой передачи SQLite с уровнями потокобезопасности DB-API 2.0:
Многопоточный режим SQLite
Значение DB-API 2.0
однопоточный
0
0
Потоки могут не использовать совместно модуль
многопоточный
1
2
Потоки могут совместно использовать модуль, но не соединения
сериализованный
3
1
Потоки могут совместно использовать модуль, соединения и курсоры
Изменено в версии 3.11: Установите threadsafety динамически вместо жесткого кодирования в
1
.
Объекты подключения¶
- class sqlite3.Connection¶
Каждая открытая база данных SQLite представлена объектом
Connection
, который создается с помощьюsqlite3.connect()
. Их основная цель - создание объектовCursor
и Контроль транзакций.См.также
Подключение к базе данных SQLite имеет следующие атрибуты и методы:
- cursor(factory=Cursor)¶
Создайте и верните объект
Cursor
. Метод cursor принимает единственный необязательный параметр factory. Если он указан, это должен быть callable, возвращающий экземплярCursor
или его подклассов.
- blobopen(table, column, row, /, *, readonly=False, name='main')¶
Откройте дескриптор
Blob
для существующего BLOB.- Parameters:
table (str) – Имя таблицы, в которой находится большой двоичный объект.
column (str) – Имя столбца, в котором находится большой двоичный объект.
row (str) – Имя строки, в которой находится большой двоичный объект.
readonly (bool) – Установите значение
True
, если большой двоичный объект должен быть открыт без разрешений на запись. По умолчанию используется значениеFalse
.name (str) – Имя базы данных, в которой находится большой двоичный объект. По умолчанию используется значение
"main"
.
- Exception:
OperationalError – При попытке открыть большой двоичный объект в таблице
WITHOUT ROWID
.- Result type:
Примечание
Размер большого двоичного объекта нельзя изменить с помощью класса
Blob
. Используйте SQL-функциюzeroblob
для создания большого двоичного объекта фиксированного размера.Добавлено в версии 3.11.
- commit()¶
Зафиксируйте любую ожидающую выполнения транзакцию в базе данных. Если открытой транзакции нет, этот метод не работает.
- rollback()¶
Выполните откат к началу любой ожидающей транзакции. Если открытых транзакций нет, этот метод не работает.
- close()¶
Закройте подключение к базе данных. Ни одна ожидающая выполнения транзакция не фиксируется неявно; убедитесь, что перед закрытием указано значение
commit()
, чтобы избежать потери ожидающих изменений.
- execute(sql, parameters=(), /)¶
Создайте новый объект
Cursor
и вызовите для негоexecute()
с заданными параметрами sql и *. Верните новый объект cursor.
- executemany(sql, parameters, /)¶
Создайте новый объект
Cursor
и вызовите для негоexecutemany()
с заданными параметрами sql и *. Верните новый объект cursor.
- executescript(sql_script, /)¶
Создайте новый объект
Cursor
и вызовите для негоexecutescript()
с помощью заданного sql_script. Верните новый объект cursor.
- create_function(name, narg, func, *, deterministic=False)¶
Создайте или удалите пользовательскую SQL-функцию.
- Parameters:
name (str) – Имя SQL-функции.
narg (int) – Количество аргументов, которые может принимать SQL-функция. Если
-1
, то она может принимать любое количество аргументов.func (callback | None) – callable, который вызывается при вызове функции SQL. Вызываемый объект должен возвращать a type natively supported by SQLite. Установите значение
None
, чтобы удалить существующую функцию SQL.deterministic (bool) – Если
True
, то созданная SQL-функция помечается как deterministic, что позволяет SQLite выполнять дополнительную оптимизацию.
- Exception:
NotSupportedError – Если детерминированный используется с версиями SQLite старше 3.8.3.
Изменено в версии 3.8: Добавлен параметр deterministic.
Пример:
>>> import hashlib >>> def md5sum(t): ... return hashlib.md5(t).hexdigest() >>> con = sqlite3.connect(":memory:") >>> con.create_function("md5", 1, md5sum) >>> for row in con.execute("SELECT md5(?)", (b"foo",)): ... print(row) ('acbd18db4cc2f85cedef654fccc4a4d8',)
- create_aggregate(name, n_arg, aggregate_class)¶
Создайте или удалите определяемую пользователем статистическую функцию SQL.
- Parameters:
name (str) – Имя агрегатной функции SQL.
n_arg (int) – Количество аргументов, которые может принимать статистическая функция SQL. Если
-1
, то она может принимать любое количество аргументов.aggregate_class (class | None) – Класс должен реализовывать следующие методы: *
step()
: Добавить строку в агрегат. *finalize()
: Возвращать конечный результат агрегирования в виде a type natively supported by SQLite. Количество аргументов, которые должен принимать методstep()
, определяется параметром n_arg. Установите значениеNone
, чтобы удалить существующую статистическую функцию SQL.
Пример:
class MySum: def __init__(self): self.count = 0 def step(self, value): self.count += value def finalize(self): return self.count con = sqlite3.connect(":memory:") con.create_aggregate("mysum", 1, MySum) cur = con.execute("CREATE TABLE test(i)") cur.execute("INSERT INTO test(i) VALUES(1)") cur.execute("INSERT INTO test(i) VALUES(2)") cur.execute("SELECT mysum(i) FROM test") print(cur.fetchone()[0]) con.close()
- create_window_function(name, num_params, aggregate_class, /)¶
Создайте или удалите пользовательскую функцию агрегатного окна.
- Parameters:
name (str) – Имя оконной функции SQL aggregate, которую нужно создать или удалить.
num_params (int) – Количество аргументов, которые может принимать оконная функция SQL aggregate. Если
-1
, то она может принимать любое количество аргументов.aggregate_class (class | None) – Класс, который должен реализовывать следующие методы: *
step()
: Добавить строку в текущее окно. *value()
: Вернуть текущее значение агрегата. *inverse()
: Удалить строку из текущего окна. *finalize()
: Возвращает конечный результат агрегата в виде a type natively supported by SQLite. Количество аргументов, которые должны принимать методыstep()
иvalue()
, определяется параметром num_params. Установите значениеNone
, чтобы удалить существующую оконную функцию SQL aggregate.
- Exception:
NotSupportedError – При использовании с версией SQLite старше 3.25.0, которая не поддерживает функции aggregate window.
Добавлено в версии 3.11.
Пример:
# Example taken from https://www.sqlite.org/windowfunctions.html#udfwinfunc class WindowSumInt: def __init__(self): self.count = 0 def step(self, value): """Add a row to the current window.""" self.count += value def value(self): """Return the current value of the aggregate.""" return self.count def inverse(self, value): """Remove a row from the current window.""" self.count -= value def finalize(self): """Return the final value of the aggregate. Any clean-up actions should be placed here. """ return self.count con = sqlite3.connect(":memory:") cur = con.execute("CREATE TABLE test(x, y)") values = [ ("a", 4), ("b", 5), ("c", 3), ("d", 8), ("e", 1), ] cur.executemany("INSERT INTO test VALUES(?, ?)", values) con.create_window_function("sumint", 1, WindowSumInt) cur.execute(""" SELECT x, sumint(y) OVER ( ORDER BY x ROWS BETWEEN 1 PRECEDING AND 1 FOLLOWING ) AS sum_y FROM test ORDER BY x """) print(cur.fetchall())
- create_collation(name, callable, /)¶
Создайте параметр сортировки с именем name, используя функцию сортировки callable. Параметру callable передаются два аргумента
string
, и он должен возвращать значениеinteger
:1
если первый элемент упорядочен выше второго-1
если первый упорядочен ниже второго0
если они расположены в одинаковом порядке
В следующем примере показана обратная сортировка параметров сортировки:
def collate_reverse(string1, string2): if string1 == string2: return 0 elif string1 < string2: return 1 else: return -1 con = sqlite3.connect(":memory:") con.create_collation("reverse", collate_reverse) cur = con.execute("CREATE TABLE test(x)") cur.executemany("INSERT INTO test(x) VALUES(?)", [("a",), ("b",)]) cur.execute("SELECT x FROM test ORDER BY x COLLATE reverse") for row in cur: print(row) con.close()
Удалите функцию сортировки, установив для параметра вызываемый значение
None
.Изменено в версии 3.11: Имя параметра сортировки может содержать любой символ Юникода. Ранее были разрешены только символы ASCII.
- interrupt()¶
Вызовите этот метод из другого потока, чтобы прервать любые запросы, которые могут выполняться в соединении. Прерванные запросы вызовут
OperationalError
.
- set_authorizer(authorizer_callback)¶
Зарегистрируйте callable authorizer_callback, который будет вызываться при каждой попытке доступа к столбцу таблицы в базе данных. Обратный вызов должен возвращать одно из значений
SQLITE_OK
,SQLITE_DENY
, илиSQLITE_IGNORE
, чтобы указать, как доступ к столбцу должен обрабатываться базовой библиотекой SQLite.Первый аргумент обратного вызова указывает, какая операция должна быть авторизована. Вторым и третьим аргументами будут arguments или
None
в зависимости от первого аргумента. 4-й аргумент - это имя базы данных («main», «temp» и т.д.), если применимо. 5-й аргумент - это имя самого внутреннего триггера или представления, которое отвечает за попытку доступа, илиNone
, если эта попытка доступа осуществляется непосредственно из входного SQL-кода.Пожалуйста, ознакомьтесь с документацией SQLite о возможных значениях первого аргумента и значении второго и третьего аргументов в зависимости от первого. Все необходимые константы доступны в модуле
sqlite3
.Передача
None
как authorizer_callback приведет к отключению авторизатора.Изменено в версии 3.11: Добавлена поддержка отключения авторизации с помощью
None
.
- set_progress_handler(progress_handler, n)¶
Зарегистрируйте callable progress_handler, который будет вызываться для каждой n команды виртуальной машины SQLite. Это полезно, если вы хотите, чтобы из SQLite выполнялись длительные операции, например, для обновления графического интерфейса.
Если вы хотите удалить любой ранее установленный progresshandler, вызовите метод с
None
для progress_handler.Возврат ненулевого значения из функции-обработчика завершит выполнение текущего запроса и приведет к возникновению исключения
OperationalError
.
- set_trace_callback(trace_callback)¶
Зарегистрируйте callable trace_callback, который будет вызываться для каждой инструкции SQL, фактически выполняемой серверной частью SQLite.
Единственный аргумент, передаваемый обратному вызову, - это выполняемая инструкция (как
str
). Возвращаемое значение обратного вызова игнорируется. Обратите внимание, что серверная часть выполняет не только инструкции, передаваемые в методыCursor.execute()
. Другие источники включают transaction management модуляsqlite3
и выполнение триггеров, определенных в текущей базе данных.Передача
None
как trace_callback отключит обратный вызов трассировки.Примечание
Исключения, возникающие при обратном вызове trace, не распространяются. В качестве вспомогательного средства для разработки и отладки используйте
enable_callback_tracebacks()
, чтобы включить печать результатов отслеживания исключений, возникающих при обратном вызове trace.Добавлено в версии 3.3.
- enable_load_extension(enabled, /)¶
Включите движок SQLite для загрузки расширений SQLite из общих библиотек, если значение включено равно
True
; в противном случае, запретите загрузку расширений SQLite. Расширения SQLite могут определять новые функции, агрегаты или совершенно новые реализации виртуальных таблиц. Одним из хорошо известных расширений является расширение для полнотекстового поиска, распространяемое вместе с SQLite.Примечание
Модуль
sqlite3
по умолчанию не поддерживает загружаемые расширения, поскольку некоторые платформы (в частности, macOS) имеют библиотеки SQLite, которые компилируются без этой функции. Чтобы получить поддержку загружаемого расширения, вы должны изменить параметр--enable-loadable-sqlite-extensions
на configure.Создает auditing event
sqlite3.enable_load_extension
с аргументамиconnection
,enabled
.Добавлено в версии 3.2.
Изменено в версии 3.10: Добавлено событие аудита
sqlite3.enable_load_extension
.con.enable_load_extension(True) # Load the fulltext search extension con.execute("select load_extension('./fts3.so')") # alternatively you can load the extension using an API call: # con.load_extension("./fts3.so") # disable extension loading again con.enable_load_extension(False) # example from SQLite wiki con.execute("CREATE VIRTUAL TABLE recipe USING fts3(name, ingredients)") con.executescript(""" INSERT INTO recipe (name, ingredients) VALUES('broccoli stew', 'broccoli peppers cheese tomatoes'); INSERT INTO recipe (name, ingredients) VALUES('pumpkin stew', 'pumpkin onions garlic celery'); INSERT INTO recipe (name, ingredients) VALUES('broccoli pie', 'broccoli cheese onions flour'); INSERT INTO recipe (name, ingredients) VALUES('pumpkin pie', 'pumpkin sugar flour butter'); """) for row in con.execute("SELECT rowid, name, ingredients FROM recipe WHERE name MATCH 'pie'"): print(row) con.close()
- load_extension(path, /)¶
Загрузите расширение SQLite из общей библиотеки, расположенной по адресу path. Перед вызовом этого метода включите загрузку расширения с помощью
enable_load_extension()
.Создает auditing event
sqlite3.load_extension
с аргументамиconnection
,path
.Добавлено в версии 3.2.
Изменено в версии 3.10: Добавлено событие аудита
sqlite3.load_extension
.
- iterdump()¶
Верните iterator, чтобы выгрузить базу данных в виде исходного кода SQL. Полезно при сохранении базы данных в памяти для последующего восстановления. Аналогично команде
.dump
в командной строке sqlite3.Пример:
# Convert file example.db to SQL dump file dump.sql con = sqlite3.connect('example.db') with open('dump.sql', 'w') as f: for line in con.iterdump(): f.write('%s\n' % line) con.close()
- backup(target, *, pages=-1, progress=None, name='main', sleep=0.250)¶
Создайте резервную копию базы данных SQLite.
Работает, даже если доступ к базе данных осуществляется другими клиентами или одновременно по тому же соединению.
- Parameters:
target (Connection) – Подключение к базе данных, в которую будет сохранена резервная копия.
pages (int) – Количество страниц, которые необходимо скопировать за один раз. Если значение равно или меньше
0
, вся база данных копируется за один шаг. По умолчанию используется значение-1
.progress (callback | None) – Если задано значение callable, оно вызывается с тремя целыми аргументами для каждой итерации резервного копирования: статус последней итерации, оставшееся количество страниц, которые еще нужно скопировать, и общее количество страниц. По умолчанию используется значение
None
.name (str) – Имя базы данных для резервного копирования. Либо
"main"
(по умолчанию) для основной базы данных,"temp"
для временной базы данных, либо имя пользовательской базы данных, которое добавляется с помощью инструкции SQLATTACH DATABASE
.sleep (float) – Количество секунд, в течение которых выполняется переход в спящий режим между последовательными попытками создания резервных копий оставшихся страниц.
Пример 1. скопируйте существующую базу данных в другую:
def progress(status, remaining, total): print(f'Copied {total-remaining} of {total} pages...') src = sqlite3.connect('example.db') dst = sqlite3.connect('backup.db') with dst: src.backup(dst, pages=1, progress=progress) dst.close() src.close()
Пример 2. скопируйте существующую базу данных во временную копию:
src = sqlite3.connect('example.db') dst = sqlite3.connect(':memory:') src.backup(dst)
Добавлено в версии 3.7.
- getlimit(category, /)¶
Получите ограничение на время выполнения подключения.
- Parameters:
category (int) – SQLite limit category, который должен быть запрошен.
- Result type:
- Exception:
ProgrammingError – Если категория не распознается базовой библиотекой SQLite.
Например, запросите максимальную длину инструкции SQL для
Connection
con
( значение по умолчанию равно 1000000000):>>> con.getlimit(sqlite3.SQLITE_LIMIT_SQL_LENGTH) 1000000000
Добавлено в версии 3.11.
- setlimit(category, limit, /)¶
Установите ограничение времени выполнения соединения. Попытки увеличить ограничение выше его жесткого верхнего предела автоматически сокращаются до жесткого верхнего предела. Независимо от того, было ли изменено ограничение, возвращается предыдущее значение ограничения.
- Parameters:
category (int) – Значение SQLite limit category, которое должно быть установлено.
limit (int) – Значение нового лимита. Если значение отрицательное, текущее ограничение остается неизменным.
- Result type:
- Exception:
ProgrammingError – Если категория не распознается базовой библиотекой SQLite.
Например, ограничьте количество подключенных баз данных до 1 для
Connection
con
( ограничение по умолчанию равно 10):>>> con.setlimit(sqlite3.SQLITE_LIMIT_ATTACHED, 1) 10 >>> con.getlimit(sqlite3.SQLITE_LIMIT_ATTACHED) 1
Добавлено в версии 3.11.
- serialize(*, name='main')¶
Преобразуйте базу данных в объект
bytes
. Для обычного файла базы данных на диске сериализация представляет собой просто копию файла на диске. Для базы данных в оперативной памяти или «временной» базы данных сериализация представляет собой ту же последовательность байтов, которая была бы записана на диск, если бы эта база данных была скопирована на диск.- Parameters:
name (str) – Имя базы данных, которое будет сериализовано. По умолчанию используется значение
"main"
.- Result type:
Примечание
Этот метод доступен только в том случае, если базовая библиотека SQLite имеет serialize API.
Добавлено в версии 3.11.
- deserialize(data, /, *, name='main')¶
Десериализуйте базу данных
serialized
вConnection
. Этот метод приводит к отключению соединения с базой данных name и повторному открытию name в качестве базы данных в памяти на основе сериализации, содержащейся в data.- Parameters:
- Exception:
OperationalError – Если подключение к базе данных в данный момент задействовано в транзакции чтения или операции резервного копирования.
DatabaseError – Если data не содержит допустимой базы данных SQLite.
OverflowError – Если
len(data)
больше, чем2**63 - 1
.
Примечание
Этот метод доступен только в том случае, если базовая библиотека SQLite поддерживает API десериализации.
Добавлено в версии 3.11.
- in_transaction¶
Этот атрибут, доступный только для чтения, соответствует низкоуровневому параметру SQLite autocommit mode.
True
если транзакция активна (имеются незафиксированные изменения),False
в противном случае.Добавлено в версии 3.2.
- isolation_level¶
Этот атрибут управляет transaction handling, выполняемым
sqlite3
. Если задано значениеNone
, транзакции никогда не открываются неявным образом. Если задано значение одного из"DEFERRED"
,"IMMEDIATE"
, или"EXCLUSIVE"
, соответствующее базовому SQLite transaction behaviour, выполняется неявное transaction management.Если параметр isolation_level не переопределен значением
connect()
, то по умолчанию используется значение""
, которое является псевдонимом для"DEFERRED"
.
- row_factory¶
Начальный
row_factory
дляCursor
объектов, созданных из этого соединения. Присвоение этого атрибута не влияет наrow_factory
существующих курсоров, принадлежащих этому соединению, а только на новые. По умолчанию используется значениеNone
, что означает, что каждая строка возвращается какtuple
.Смотрите Как создавать и использовать фабрики строк для получения более подробной информации.
- text_factory¶
Параметр callable, который принимает параметр
bytes
и возвращает его текстовое представление. Вызываемый объект вызывается для значений SQLite с типом данныхTEXT
. По умолчанию для этого атрибута установлено значениеstr
.Смотрите Как обрабатывать текстовые кодировки, отличные от UTF-8 для получения более подробной информации.
- total_changes¶
Возвращает общее количество строк базы данных, которые были изменены, вставлены или удалены с момента открытия подключения к базе данных.
Объекты курсора¶
Объект
Cursor
представляет собой database cursor, который используется для выполнения инструкций SQL и управления контекстом операции выборки. Курсоры создаются с помощьюConnection.cursor()
или с помощью любого из connection shortcut methods.Объектами курсора являются iterators, что означает, что если вы выполняете
execute()
запросSELECT
, вы можете просто выполнить итерацию по курсору, чтобы получить результирующие строки:for row in cur.execute("SELECT t FROM data"): print(row)
- class sqlite3.Cursor¶
Экземпляр
Cursor
имеет следующие атрибуты и методы.- execute(sql, parameters=(), /)¶
Выполните один оператор SQL, необязательно привязывая значения Python с помощью placeholders.
- Parameters:
sql (str) – Один оператор SQL.
parameters (
dict
| sequence) – Значения Python для привязки к заполнителям в sql. Adict
, если используются именованные заполнители. A sequence, если используются безымянные заполнители. Смотрите Как использовать заполнители для привязки значений в SQL-запросах.
- Exception:
ProgrammingError – Если sql содержит более одного оператора SQL.
Если
isolation_level
не равноNone
, то sql является операторомINSERT
,UPDATE
,DELETE
, илиREPLACE
и открытая транзакция отсутствует, транзакция неявно открывается перед выполнением sql.Используйте
executescript()
для выполнения нескольких инструкций SQL.
- executemany(sql, parameters, /)¶
Для каждого элемента в parameters повторно выполните инструкцию parameterized DML SQL sql.
Использует ту же неявную обработку транзакций, что и
execute()
.- Parameters:
sql (str) – Один оператор SQL DML.
parameters (iterable) – iterable параметров для привязки к заполнителям в sql. Смотрите Как использовать заполнители для привязки значений в SQL-запросах.
- Exception:
ProgrammingError – Если sql содержит более одного оператора SQL или не является оператором DML.
Пример:
rows = [ ("row1",), ("row2",), ] # cur is an sqlite3.Cursor object cur.executemany("INSERT INTO data VALUES(?)", rows)
Примечание
Все результирующие строки отбрасываются, включая инструкции DML с RETURNING clauses.
- executescript(sql_script, /)¶
Выполните инструкции SQL в sql_script. Если есть ожидающая выполнения транзакция, сначала выполняется неявная инструкция
COMMIT
. Больше неявное управление транзакцией не выполняется; любое управление транзакцией должно быть добавлено в sql_script.sql_script должен быть
string
.Пример:
# cur is an sqlite3.Cursor object cur.executescript(""" BEGIN; CREATE TABLE person(firstname, lastname, age); CREATE TABLE book(title, author, published); CREATE TABLE publisher(name, address); COMMIT; """)
- fetchone()¶
Если
row_factory
равноNone
, верните результирующий набор запроса следующей строки в видеtuple
. В противном случае передайте его в фабрику строк и верните результат. ВернитеNone
, если больше нет доступных данных.
- fetchmany(size=cursor.arraysize)¶
Возвращает следующий набор строк результата запроса в виде
list
. Возвращает пустой список, если больше нет доступных строк.Количество строк, извлекаемых за один вызов, определяется параметром size. Если значение size не задано, то
arraysize
определяет количество извлекаемых строк. Если доступно меньше строк size, то возвращается столько строк, сколько доступно.Обратите внимание, что параметр size влияет на производительность. Для достижения оптимальной производительности обычно лучше всего использовать атрибут arraysize. Если используется параметр size, то лучше всего, чтобы он сохранял одно и то же значение от одного
fetchmany()
вызова до следующего.
- fetchall()¶
Возвращает все (оставшиеся) строки результата запроса в виде
list
. Возвращает пустой список, если строки недоступны. Обратите внимание, что атрибутarraysize
может повлиять на производительность этой операции.
- close()¶
Закройте курсор сейчас (а не всякий раз, когда вызывается
__del__
).С этого момента курсор будет недоступен; при попытке выполнения какой-либо операции с курсором будет генерироваться исключение
ProgrammingError
.
- setinputsizes(sizes, /)¶
Требуется DB-API. Ничего не делает в
sqlite3
.
- setoutputsize(size, column=None, /)¶
Требуется DB-API. Ничего не делает в
sqlite3
.
- arraysize¶
Атрибут чтения/записи, который управляет количеством строк, возвращаемых с помощью
fetchmany()
. Значение по умолчанию равно 1, что означает, что при каждом вызове будет извлекаться одна строка.
- connection¶
Доступный только для чтения атрибут, который указывает, что база данных SQLite
Connection
принадлежит курсору. ОбъектCursor
, созданный с помощью вызоваcon.cursor()
, будет иметь атрибутconnection
, который ссылается на con:>>> con = sqlite3.connect(":memory:") >>> cur = con.cursor() >>> cur.connection == con True
- description¶
Доступный только для чтения атрибут, который предоставляет имена столбцов последнего запроса. Чтобы сохранить совместимость с Python DB API, он возвращает для каждого столбца кортеж из 7 элементов, где последние шесть элементов каждого кортежа равны
None
.Он также устанавливается для операторов
SELECT
без каких-либо совпадающих строк.
- lastrowid¶
Доступный только для чтения атрибут, который предоставляет идентификатор последней вставленной строки. Он обновляется только после успешного выполнения
INSERT
илиREPLACE
инструкций с использованием методаexecute()
. Для других операторов, послеexecutemany()
илиexecutescript()
, или если вставка завершилась неудачно, значениеlastrowid
остается неизменным. Начальное значениеlastrowid
равноNone
.Примечание
Вставки в таблицы
WITHOUT ROWID
не регистрируются.Изменено в версии 3.6: Добавлена поддержка инструкции
REPLACE
.
- rowcount¶
Доступный только для чтения атрибут, который указывает количество измененных строк для операторов
INSERT
,UPDATE
,DELETE
, иREPLACE
; для других операторов, включая запросы CTE, используется значение-1
. Он обновляется только методамиexecute()
иexecutemany()
после завершения выполнения инструкции. Это означает, что все результирующие строки должны быть извлечены для обновленияrowcount
.
- row_factory¶
Управляет тем, как будет представлена строка, выбранная из этого
Cursor
. ЕслиNone
, строка будет представлена какtuple
. Может быть установлено значениеsqlite3.Row
; или callable, которое принимает два аргумента, объектCursor
иtuple
значений строк, и возвращает пользовательский объект, представляющий строку SQLite.По умолчанию используется значение
Connection.row_factory
, установленное при созданииCursor
. Присвоение этого атрибута не влияет наConnection.row_factory
родительского соединения.Смотрите Как создавать и использовать фабрики строк для получения более подробной информации.
Объекты строк¶
- class sqlite3.Row¶
Экземпляр
Row
служит в качестве высокооптимизированногоrow_factory
дляConnection
объектов. Он поддерживает итерацию, проверку на равенство,len()
и mapping доступ по имени столбца и индексу.Два объекта
Row
сравниваются равными, если они имеют одинаковые имена столбцов и значения.Смотрите Как создавать и использовать фабрики строк для получения более подробной информации.
- keys()¶
Возвращает
list
имен столбцов в видеstrings
. Сразу после запроса это первый элемент каждого кортежа вCursor.description
.
Изменено в версии 3.5: Добавлена поддержка нарезки.
Большие двоичные объекты¶
- class sqlite3.Blob¶
Добавлено в версии 3.11.
Экземпляр
Blob
- это file-like object, который может считывать и записывать данные в SQLite BLOB. Вызовитеlen(blob)
, чтобы получить размер (количество байт) большого двоичного объекта. Используйте индексы и slices для прямого доступа к данным большого двоичного объекта.Используйте
Blob
в качестве context manager, чтобы убедиться, что дескриптор большого двоичного объекта закрыт после использования.con = sqlite3.connect(":memory:") con.execute("CREATE TABLE test(blob_col blob)") con.execute("INSERT INTO test(blob_col) VALUES(zeroblob(13))") # Write to our blob, using two write operations: with con.blobopen("test", "blob_col", 1) as blob: blob.write(b"hello, ") blob.write(b"world.") # Modify the first and last bytes of our blob blob[0] = ord("H") blob[-1] = ord("!") # Read the contents of our blob with con.blobopen("test", "blob_col", 1) as blob: greeting = blob.read() print(greeting) # outputs "b'Hello, world!'"
- close()¶
Закройте большой двоичный объект.
С этого момента большой двоичный объект будет недоступен. Исключение
Error
(или подкласс) будет создано при попытке выполнения какой-либо дальнейшей операции с большим двоичным объектом.
- read(length=-1, /)¶
Считайте длину байт данных из большого двоичного объекта в текущей позиции смещения. Если достигнут конец большого двоичного объекта, будут возвращены данные до EOF. Если длина не указана или имеет отрицательное значение,
read()
будет считываться до конца большого двоичного объекта.
- write(data, /)¶
Запишите данные в большой двоичный объект с текущим смещением. Эта функция не может изменить длину большого двоичного объекта. Запись за пределы конца большого двоичного объекта приведет к появлению
ValueError
.
- tell()¶
Возвращает текущее положение доступа к большому двоичному объекту.
- seek(offset, origin=os.SEEK_SET, /)¶
Установите для текущего положения доступа к большому двоичному объекту значение offset. Аргумент origin по умолчанию имеет значение
os.SEEK_SET
(абсолютное положение большого двоичного объекта). Другими значениями для origin являютсяos.SEEK_CUR
(искать относительно текущей позиции) иos.SEEK_END
(искать относительно конца большого двоичного объекта).
Подготовка объектов протокола¶
- class sqlite3.PrepareProtocol¶
Единственная цель типа протокола Prepare - действовать как протокол адаптации стиля PEP 246 для объектов, которые могут быть от adapt themselves до native SQLite types.
Исключения¶
Иерархия исключений определяется с помощью DB-API 2.0 (PEP 249).
- exception sqlite3.Warning¶
Это исключение в настоящее время не вызывается модулем
sqlite3
, но может быть вызвано приложениями, использующимиsqlite3
, например, если пользовательская функция обрезает данные при вставке.Warning
является подклассомException
.
- exception sqlite3.Error¶
Базовый класс для других исключений в этом модуле. Используйте его, чтобы перехватывать все ошибки с помощью одной инструкции
except
.Error
является подклассомException
.Если исключение возникло из библиотеки SQLite, к нему добавляются следующие два атрибута:
- sqlite_errorcode¶
Цифровой код ошибки из SQLite API
Добавлено в версии 3.11.
- sqlite_errorname¶
Символьное название цифрового кода ошибки из SQLite API
Добавлено в версии 3.11.
- exception sqlite3.InterfaceError¶
Исключение вызвано неправильным использованием низкоуровневого SQLite C API. Другими словами, если возникает это исключение, это, вероятно, указывает на ошибку в модуле
sqlite3
.InterfaceError
является подклассомError
.
- exception sqlite3.DatabaseError¶
Исключение создается для ошибок, связанных с базой данных. Это исключение используется в качестве базового для нескольких типов ошибок базы данных. Оно неявно создается только в специализированных подклассах.
DatabaseError
является подклассомError
.
- exception sqlite3.DataError¶
Исключение создается для ошибок, вызванных проблемами с обработанными данными, такими как числовые значения вне диапазона и слишком длинные строки.
DataError
является подклассомDatabaseError
.
- exception sqlite3.OperationalError¶
Исключение возникает при ошибках, которые связаны с работой базы данных и не обязательно находятся под контролем программиста. Например, путь к базе данных не найден или транзакция не может быть обработана.
OperationalError
является подклассомDatabaseError
.
- exception sqlite3.IntegrityError¶
Исключение возникает, когда нарушается реляционная целостность базы данных, например, не выполняется проверка внешнего ключа. Это подкласс
DatabaseError
.
- exception sqlite3.InternalError¶
Исключение возникает, когда SQLite обнаруживает внутреннюю ошибку. Если это происходит, это может указывать на проблему с библиотекой SQLite во время выполнения.
InternalError
является подклассомDatabaseError
.
- exception sqlite3.ProgrammingError¶
Исключение возникает при
sqlite3
ошибках программирования API, например, при указании неправильного количества привязок к запросу или попытке работы с закрытымConnection
.ProgrammingError
является подклассомDatabaseError
.
- exception sqlite3.NotSupportedError¶
Исключение возникает в случае, если метод или API базы данных не поддерживаются базовой библиотекой SQLite. Например, установите значение deterministic равным
True
вcreate_function()
, если базовая библиотека SQLite не поддерживает детерминированные функции.NotSupportedError
является подклассомDatabaseError
.
Типы SQLite и Python¶
SQLite изначально поддерживает следующие типы: NULL
, INTEGER
, REAL
, TEXT
, BLOB
.
Таким образом, следующие типы Python могут быть отправлены в SQLite без каких-либо проблем:
Тип Python |
Тип SQLite |
---|---|
|
|
|
|
|
|
|
|
|
Вот как типы SQLite преобразуются в типы Python по умолчанию:
Тип SQLite |
Тип Python |
---|---|
|
|
|
|
|
|
|
зависит от |
|
Система типов модуля sqlite3
расширяема двумя способами: вы можете сохранять дополнительные типы Python в базе данных SQLite с помощью object adapters, и вы можете позволить модулю sqlite3
преобразовывать типы SQLite в типы Python с помощью converters.
Адаптеры и конвертеры по умолчанию¶
В модуле datetime есть адаптеры по умолчанию для типов date и datetime времени. Они будут отправлены в SQLite как даты/временные метки ISO.
Конвертеры по умолчанию зарегистрированы под именем «дата» для datetime.date
и под именем «временная метка» для datetime.datetime
.
Таким образом, вы можете использовать метки даты/времени из Python без каких-либо дополнительных манипуляций в большинстве случаев. Формат адаптеров также совместим с экспериментальными функциями даты/времени SQLite.
Следующий пример демонстрирует это.
import sqlite3
import datetime
con = sqlite3.connect(":memory:", detect_types=sqlite3.PARSE_DECLTYPES|sqlite3.PARSE_COLNAMES)
cur = con.cursor()
cur.execute("create table test(d date, ts timestamp)")
today = datetime.date.today()
now = datetime.datetime.now()
cur.execute("insert into test(d, ts) values (?, ?)", (today, now))
cur.execute("select d, ts from test")
row = cur.fetchone()
print(today, "=>", row[0], type(row[0]))
print(now, "=>", row[1], type(row[1]))
cur.execute('select current_date as "d [date]", current_timestamp as "ts [timestamp]"')
row = cur.fetchone()
print("current_date", row[0], type(row[0]))
print("current_timestamp", row[1], type(row[1]))
con.close()
Если временная метка, хранящаяся в SQLite, содержит дробную часть длиной более 6 чисел, преобразователь временных меток усечет ее значение с точностью до микросекунды.
Примечание
Преобразователь временных меток по умолчанию игнорирует смещения UTC в базе данных и всегда возвращает наивный объект datetime.datetime
. Чтобы сохранить смещения UTC в временных метках, либо оставьте преобразователи отключенными, либо зарегистрируйте преобразователь, учитывающий смещение, с помощью register_converter()
.
Практические руководства¶
Как использовать заполнители для привязки значений в SQL-запросах¶
В операциях SQL обычно используются значения из переменных Python. Однако остерегайтесь использовать строковые операции Python для сборки запросов, поскольку они уязвимы для SQL injection attacks. Например, злоумышленник может просто заключить одинарную кавычку и ввести OR TRUE
, чтобы выбрать все строки:
>>> # Never do this -- insecure!
>>> symbol = input()
' OR TRUE; --
>>> sql = "SELECT * FROM stocks WHERE symbol = '%s'" % symbol
>>> print(sql)
SELECT * FROM stocks WHERE symbol = '' OR TRUE; --'
>>> cur.execute(sql)
Вместо этого используйте замену параметров в DB-API. Чтобы вставить переменную в строку запроса, используйте в строке заполнитель и подставьте фактические значения в запрос, указав их как tuple
значений во втором аргументе метода cursor execute()
.
В инструкции SQL может использоваться один из двух типов заполнителей: вопросительные знаки (стиль qmark) или именованные заполнители (именованный стиль). Для стиля qmark параметрами должны быть sequence, длина которых должна соответствовать количеству заполнителей, или ProgrammingError
. Для именованного стиля parameters должен быть экземпляром dict
(или подкласса), который должен содержать ключи для всех именованных параметров; любые дополнительные элементы игнорируются. Вот пример обоих стилей:
con = sqlite3.connect(":memory:")
cur = con.execute("CREATE TABLE lang(name, first_appeared)")
# This is the named style used with executemany():
data = (
{"name": "C", "year": 1972},
{"name": "Fortran", "year": 1957},
{"name": "Python", "year": 1991},
{"name": "Go", "year": 2009},
)
cur.executemany("INSERT INTO lang VALUES(:name, :year)", data)
# This is the qmark style used in a SELECT query:
params = (1972,)
cur.execute("SELECT * FROM lang WHERE first_appeared = ?", params)
print(cur.fetchall())
Примечание
PEP 249 числовые заполнители не поддерживаются. Если они используются, они будут интерпретироваться как именованные заполнители.
Как адаптировать пользовательские типы Python к значениям SQLite¶
Изначально SQLite поддерживает только ограниченный набор типов данных. Чтобы сохранить пользовательские типы Python в базах данных SQLite, адаптируйте их к одному из Python types SQLite natively understands.
Существует два способа адаптировать объекты Python к типам SQLite: позволить вашему объекту адаптироваться самому или использовать вызываемый адаптер. Последний вариант будет иметь приоритет над первым. Для библиотеки, которая экспортирует пользовательский тип, может иметь смысл разрешить этому типу адаптироваться самому. Как разработчику приложений, возможно, имеет смысл взять на себя прямое управление, зарегистрировав пользовательские функции адаптера.
Как писать адаптируемые объекты¶
Предположим, у нас есть класс Point
, который представляет пару координат, x
и y
, в декартовой системе координат. Пара координат будет сохранена в базе данных в виде текстовой строки, разделенной точкой с запятой. Это можно реализовать, добавив метод __conform__(self, protocol)
, который возвращает адаптированное значение. Объект, передаваемый в protocol, будет иметь тип PrepareProtocol
.
class Point:
def __init__(self, x, y):
self.x, self.y = x, y
def __conform__(self, protocol):
if protocol is sqlite3.PrepareProtocol:
return f"{self.x};{self.y}"
con = sqlite3.connect(":memory:")
cur = con.cursor()
cur.execute("SELECT ?", (Point(4.0, -3.2),))
print(cur.fetchone()[0])
Как зарегистрировать вызываемые элементы адаптера¶
Другая возможность заключается в создании функции, которая преобразует объект Python в тип, совместимый с SQLite. Затем эту функцию можно зарегистрировать с помощью register_adapter()
.
class Point:
def __init__(self, x, y):
self.x, self.y = x, y
def adapt_point(point):
return f"{point.x};{point.y}"
sqlite3.register_adapter(Point, adapt_point)
con = sqlite3.connect(":memory:")
cur = con.cursor()
cur.execute("SELECT ?", (Point(1.0, 2.5),))
print(cur.fetchone()[0])
Как преобразовать значения SQLite в пользовательские типы Python¶
Создание адаптера позволяет конвертировать * из * пользовательских типов Python * в значения SQLite. Чтобы иметь возможность конвертировать * из * значений SQLite * в* пользовательские типы Python, мы используем конвертеры.
Давайте вернемся к классу Point
. Мы сохранили координаты x и y, разделенные точкой с запятой, в виде строк в SQLite.
Сначала мы определим функцию преобразования, которая принимает строку в качестве параметра и создает из нее объект Point
.
Примечание
Функции конвертера всегда передают объект bytes
, независимо от базового типа данных SQLite.
def convert_point(s):
x, y = map(float, s.split(b";"))
return Point(x, y)
Теперь нам нужно указать sqlite3
, когда он должен преобразовать заданное значение SQLite. Это делается при подключении к базе данных, используя параметр detect_types в connect()
. Есть три варианта:
Неявный: установите для detect_types значение
PARSE_DECLTYPES
Явно: установите для detect_types значение
PARSE_COLNAMES
Оба: установите для detect_types значение
sqlite3.PARSE_DECLTYPES | sqlite3.PARSE_COLNAMES
. Имена столбцов имеют приоритет над объявленными типами.
Следующий пример иллюстрирует неявный и явный подходы:
class Point:
def __init__(self, x, y):
self.x, self.y = x, y
def __repr__(self):
return f"Point({self.x}, {self.y})"
def adapt_point(point):
return f"{point.x};{point.y}"
def convert_point(s):
x, y = list(map(float, s.split(b";")))
return Point(x, y)
# Register the adapter and converter
sqlite3.register_adapter(Point, adapt_point)
sqlite3.register_converter("point", convert_point)
# 1) Parse using declared types
p = Point(4.0, -3.2)
con = sqlite3.connect(":memory:", detect_types=sqlite3.PARSE_DECLTYPES)
cur = con.execute("CREATE TABLE test(p point)")
cur.execute("INSERT INTO test(p) VALUES(?)", (p,))
cur.execute("SELECT p FROM test")
print("with declared types:", cur.fetchone()[0])
cur.close()
con.close()
# 2) Parse using column names
con = sqlite3.connect(":memory:", detect_types=sqlite3.PARSE_COLNAMES)
cur = con.execute("CREATE TABLE test(p)")
cur.execute("INSERT INTO test(p) VALUES(?)", (p,))
cur.execute('SELECT p AS "p [point]" FROM test')
print("with column names:", cur.fetchone()[0])
Рецепты адаптеров и конвертеров¶
В этом разделе приведены рецепты для распространенных адаптеров и преобразователей.
import datetime
import sqlite3
def adapt_date_iso(val):
"""Adapt datetime.date to ISO 8601 date."""
return val.isoformat()
def adapt_datetime_iso(val):
"""Adapt datetime.datetime to timezone-naive ISO 8601 date."""
return val.isoformat()
def adapt_datetime_epoch(val):
"""Adapt datetime.datetime to Unix timestamp."""
return int(val.timestamp())
sqlite3.register_adapter(datetime.date, adapt_date_iso)
sqlite3.register_adapter(datetime.datetime, adapt_datetime_iso)
sqlite3.register_adapter(datetime.datetime, adapt_datetime_epoch)
def convert_date(val):
"""Convert ISO 8601 date to datetime.date object."""
return datetime.date.fromisoformat(val.decode())
def convert_datetime(val):
"""Convert ISO 8601 datetime to datetime.datetime object."""
return datetime.datetime.fromisoformat(val.decode())
def convert_timestamp(val):
"""Convert Unix epoch timestamp to datetime.datetime object."""
return datetime.datetime.fromtimestamp(int(val))
sqlite3.register_converter("date", convert_date)
sqlite3.register_converter("datetime", convert_datetime)
sqlite3.register_converter("timestamp", convert_timestamp)
Как использовать методы быстрого подключения¶
Используя методы execute()
, executemany()
, и executescript()
класса Connection
, ваш код может быть написан более сжато, поскольку вам не нужно создавать (часто лишние) объекты Cursor
явно. Вместо этого объекты Cursor
создаются неявно, и эти методы быстрого доступа возвращают объекты cursor. Таким образом, вы можете выполнить инструкцию SELECT
и выполнить итерацию по ней напрямую, используя только один вызов объекта Connection
.
# Create and fill the table.
con = sqlite3.connect(":memory:")
con.execute("CREATE TABLE lang(name, first_appeared)")
data = [
("C++", 1985),
("Objective-C", 1984),
]
con.executemany("INSERT INTO lang(name, first_appeared) VALUES(?, ?)", data)
# Print the table contents
for row in con.execute("SELECT name, first_appeared FROM lang"):
print(row)
print("I just deleted", con.execute("DELETE FROM lang").rowcount, "rows")
# close() is not a shortcut method and it's not called automatically;
# the connection object should be closed manually
con.close()
Как использовать диспетчер контекста подключения¶
Объект Connection
может использоваться в качестве контекстного менеджера, который автоматически фиксирует или откатывает открытые транзакции при выходе из тела контекстного менеджера. Если тело инструкции with
завершается без исключений, транзакция фиксируется. Если эта фиксация завершается неудачно или если в теле инструкции with
возникает неперехваченное исключение, транзакция откатывается назад.
Если при выходе из тела инструкции with
нет открытой транзакции, контекстный менеджер не работает.
Примечание
Контекстный менеджер не открывает новую транзакцию неявным образом и не закрывает соединение. Если вам нужен закрывающий контекстный менеджер, попробуйте использовать contextlib.closing()
.
con = sqlite3.connect(":memory:")
con.execute("CREATE TABLE lang(id INTEGER PRIMARY KEY, name VARCHAR UNIQUE)")
# Successful, con.commit() is called automatically afterwards
with con:
con.execute("INSERT INTO lang(name) VALUES(?)", ("Python",))
# con.rollback() is called after the with block finishes with an exception,
# the exception is still raised and must be caught
try:
with con:
con.execute("INSERT INTO lang(name) VALUES(?)", ("Python",))
except sqlite3.IntegrityError:
print("couldn't add Python twice")
# Connection object used as context manager only commits or rollbacks transactions,
# so the connection object should be closed manually
con.close()
Как работать с URI SQLite¶
Некоторые полезные приемы работы с URI включают в себя:
Откройте базу данных в режиме только для чтения:
>>> con = sqlite3.connect("file:tutorial.db?mode=ro", uri=True)
>>> con.execute("CREATE TABLE readonly(data)")
Traceback (most recent call last):
OperationalError: attempt to write a readonly database
Не создавайте неявно новый файл базы данных, если он еще не существует; если не удастся создать новый файл, будет выведено значение
OperationalError
:
>>> con = sqlite3.connect("file:nosuchdb.db?mode=rw", uri=True)
Traceback (most recent call last):
OperationalError: unable to open database file
Создайте общую именованную базу данных в оперативной памяти:
db = "file:mem1?mode=memory&cache=shared"
con1 = sqlite3.connect(db, uri=True)
con2 = sqlite3.connect(db, uri=True)
with con1:
con1.execute("CREATE TABLE shared(data)")
con1.execute("INSERT INTO shared VALUES(28)")
res = con2.execute("SELECT data FROM shared")
assert res.fetchone() == (28,)
Более подробную информацию об этой функции, включая список параметров, можно найти в разделе SQLite URI documentation.
Как создавать и использовать фабрики строк¶
По умолчанию sqlite3
представляет каждую строку в виде tuple
. Если tuple
вам не подходит, вы можете использовать класс sqlite3.Row
или пользовательский класс row_factory
.
Хотя row_factory
существует как атрибут как в Cursor
, так и в Connection
, рекомендуется установить Connection.row_factory
, чтобы все курсоры, созданные из соединения, использовали одну и ту же фабрику строк.
Row
обеспечивает индексированный и нечувствительный к регистру именованный доступ к столбцам с минимальными затратами памяти и снижением производительности по сравнению с tuple
. Чтобы использовать Row
в качестве фабрики строк, присвойте ей атрибут row_factory
:
>>> con = sqlite3.connect(":memory:")
>>> con.row_factory = sqlite3.Row
Запросы теперь возвращают Row
объектов:
>>> res = con.execute("SELECT 'Earth' AS name, 6378 AS radius")
>>> row = res.fetchone()
>>> row.keys()
['name', 'radius']
>>> row[0] # Access by index.
'Earth'
>>> row["name"] # Access by name.
'Earth'
>>> row["RADIUS"] # Column names are case-insensitive.
6378
Примечание
Предложение FROM
может быть опущено в операторе SELECT
, как в приведенном выше примере. В таких случаях SQLite возвращает одну строку со столбцами, определенными выражениями, например литералами, с заданными псевдонимами expr AS alias
.
Вы можете создать пользовательский row_factory
, который возвращает каждую строку в виде dict
, а имена столбцов сопоставляются со значениями:
def dict_factory(cursor, row):
fields = [column[0] for column in cursor.description]
return {key: value for key, value in zip(fields, row)}
Используя его, запросы теперь возвращают dict
вместо tuple
:
>>> con = sqlite3.connect(":memory:")
>>> con.row_factory = dict_factory
>>> for row in con.execute("SELECT 1 AS a, 2 AS b"):
... print(row)
{'a': 1, 'b': 2}
Следующая строка factory возвращает значение named tuple:
from collections import namedtuple
def namedtuple_factory(cursor, row):
fields = [column[0] for column in cursor.description]
cls = namedtuple("Row", fields)
return cls._make(row)
namedtuple_factory()
может использоваться следующим образом:
>>> con = sqlite3.connect(":memory:")
>>> con.row_factory = namedtuple_factory
>>> cur = con.execute("SELECT 1 AS a, 2 AS b")
>>> row = cur.fetchone()
>>> row
Row(a=1, b=2)
>>> row[0] # Indexed access.
1
>>> row.b # Attribute access.
2
С некоторыми изменениями приведенный выше рецепт может быть адаптирован для использования dataclass
или любого другого пользовательского класса вместо namedtuple
.
Как обрабатывать текстовые кодировки, отличные от UTF-8¶
По умолчанию sqlite3
использует str
для адаптации значений SQLite к типу данных TEXT
. Это хорошо работает для текста в кодировке UTF-8, но может привести к сбою при использовании других кодировок и недопустимого значения UTF-8. Для обработки таких случаев можно использовать пользовательский text_factory
.
Из-за Sqlite flexible typing нередко можно встретить столбцы таблицы с типом данных TEXT
, содержащие кодировки, отличные от UTF-8, или даже произвольные данные. Чтобы продемонстрировать это, давайте предположим, что у нас есть база данных с текстом в кодировке ISO-8859-2 (латиница-2), например, таблица словарных статей чешско-английского языка. Предполагая, что теперь у нас есть экземпляр Connection
con
, подключенный к этой базе данных, мы можем расшифровать текст в кодировке Latin-2, используя этот text_factory
:
con.text_factory = lambda data: str(data, encoding="latin2")
Для недопустимых значений UTF-8 или произвольных данных, хранящихся в столбцах таблицы TEXT
, вы можете использовать следующий прием, заимствованный из РУКОВОДСТВО по работе с Юникодом:
con.text_factory = lambda data: str(data, errors="surrogateescape")
Примечание
API модуля sqlite3
не поддерживает строки, содержащие суррогаты.
См.также
Объяснение¶
Контроль транзакций¶
Модуль sqlite3
не соответствует правилам обработки транзакций, рекомендованным PEP 249.
Если атрибут подключения isolation_level
не равен None
, новые транзакции неявно открываются перед выполнением execute()
и executemany()
. INSERT
, UPDATE
, <операторы DELETE
или REPLACE
; для других операторов неявная обработка транзакций не выполняется. Используйте методы commit()
и rollback()
соответственно для фиксации и отката отложенных транзакций. Вы можете выбрать базовый SQLite transaction behaviour — то есть, будут ли неявно выполняться BEGIN
инструкции sqlite3
и если да, то какого типа – с помощью атрибута isolation_level
.
Если для параметра isolation_level
установлено значение None
, транзакции неявно не открываются вообще. Это оставляет базовую библиотеку SQLite в autocommit mode, но также позволяет пользователю выполнять собственную обработку транзакций, используя явные инструкции SQL. Базовый режим автоматической фиксации библиотеки SQLite может быть запрошен с помощью атрибута in_transaction
.
Метод executescript()
неявно фиксирует любую ожидающую выполнения транзакцию перед выполнением данного SQL-скрипта, независимо от значения isolation_level
.
Изменено в версии 3.6: sqlite3
используется для неявной фиксации открытой транзакции перед инструкциями DDL. Это больше не так.