sqlite3 — интерфейс DB-API 2.0 для баз данных SQLite

SQLite — это библиотека C, предоставляющая лёгкую базу данных на диске, которая не требует отдельного процесса сервера и позволяет получать доступ к базе данных с использованием нестандартного варианта языка запросов SQL. Некоторые приложения могут использовать SQLite для внутренней организации данных. Также можно прототипировать приложение с помощью SQLite, а затем перенести код на большую базу данных, такую как PostgreSQL или Oracle.

Модуль sqlite3 был написан Герхардом Харингом. Он предоставляет интерфейс SQL, соответствующий спецификации DB-API 2.0, описанной в PEP 249.

Для использования модуля необходимо сначала создать объект Connection, который представляет базу данных. Здесь данные будут храниться в файле example.db:

import sqlite3
conn = sqlite3.connect('example.db')

Также можно использовать специальное имя :memory: для создания базы данных в оперативной памяти.

После получения объекта Connection можно создать объект Cursor и вызвать его метод execute() для выполнения SQL-команд:

c = conn.cursor()

# Create table
c.execute('''CREATE TABLE stocks
             (date text, trans text, symbol text, qty real, price real)''')

# Insert a row of data
c.execute("INSERT INTO stocks VALUES ('2006-01-05','BUY','RHAT',100,35.14)")

# Save (commit) the changes
conn.commit()

# We can also close the connection if we are done with it.
# Just be sure any changes have been committed or they will be lost.
conn.close()

Сохранённые данные постоянны и доступны в последующих сессиях:

import sqlite3
conn = sqlite3.connect('example.db')
c = conn.cursor()

Обычно для SQL-операций необходимо использовать значения из переменных Python. Не следует собирать свой запрос с использованием строковых операций Python, поскольку это небезопасно; это делает вашу программу уязвимой для атаки типа SQL-инъекции (см. https://xkcd.com/327/ для юмористического примера того, что может пойти не так).

Вместо этого используйте подстановку параметров DB-API. Поместите ? в качестве заполнителя везде, где нужно использовать значение, а затем передайте кортеж значений в качестве второго аргумента метода курсора execute(). (Другие модули баз данных могут использовать другой заполнителя, например, %s или :1.) Например:

# Never do this -- insecure!
symbol = 'RHAT'
c.execute("SELECT * FROM stocks WHERE symbol = '%s'" % symbol)

# Do this instead
t = ('RHAT',)
c.execute('SELECT * FROM stocks WHERE symbol=?', t)
print c.fetchone()

# Larger example that inserts many records at a time
purchases = [('2006-03-28', 'BUY', 'IBM', 1000, 45.00),
             ('2006-04-05', 'BUY', 'MSFT', 1000, 72.00),
             ('2006-04-06', 'SELL', 'IBM', 500, 53.00),
            ]
c.executemany('INSERT INTO stocks VALUES (?,?,?,?,?)', purchases)

Для получения данных после выполнения оператора SELECT можно либо рассматривать курсор как итератор, либо вызвать метод курсора fetchone() для получения одной соответствующей строки или вызвать fetchall() для получения списка соответствующих строк.

В этом примере используется форма итератора:

>>> for row in c.execute('SELECT * FROM stocks ORDER BY price'):
        print row

(u'2006-01-05', u'BUY', u'RHAT', 100, 35.14)
(u'2006-03-28', u'BUY', u'IBM', 1000, 45.0)
(u'2006-04-06', u'SELL', u'IBM', 500, 53.0)
(u'2006-04-05', u'BUY', u'MSFT', 1000, 72.0)

1. Функции и константы модуля

sqlite3.version

Номер версии этого модуля в виде строки. Это не версия библиотеки SQLite.

sqlite3.version_info

Номер версии этого модуля в виде кортежа целых чисел. Это не версия библиотеки SQLite.

sqlite3.sqlite_version

Номер версии библиотеки SQLite времени выполнения в виде строки.

sqlite3.sqlite_version_info

Номер версии библиотеки SQLite времени выполнения в виде кортежа целых чисел.

sqlite3.PARSE_DECLTYPES

Эта константа предназначена для использования с параметром detect_types функции connect().

Установка этой константы заставляет модуль sqlite3 анализировать объявленный тип для каждого возвращаемого столбца. Он проанализирует первое слово объявленного типа, например, для «integer primary key» он проанализирует «integer», а для «number(10)» он проанализирует «number». Затем для этого столбца он обратится к словарю конвертеров и воспользуется функцией преобразования, зарегистрированной для этого типа.

sqlite3.PARSE_COLNAMES

Эта константа предназначена для использования с параметром detect_types функции connect().

Установка этого значения заставляет интерфейс SQLite анализировать имя столбца для каждого возвращаемого столбца. Он будет искать строку вида [mytype] и затем решит, что ‘mytype’ — это тип столбца. Он попытается найти запись ‘mytype’ в словаре конвертеров и затем использовать найденную функцию преобразования для возврата значения. Имя столбца, найденное в Cursor.description, — это только первое слово имени столбца, т. е. если вы используете что-то вроде 'as "x [datetime]"' в своём SQL, то мы проанализируем всё до первой пробелы для имени столбца: имя столбца будет просто «x».

sqlite3.connect(database[, timeout, detect_types, isolation_level, check_same_thread, factory, cached_statements])

Открывает подключение к файлу базы данных SQLite database. Можно использовать ":memory:" для открытия подключения к базе данных, находящейся в оперативной памяти, а не на диске.

При доступе к базе данных с помощью нескольких подключений, и если один из процессов изменяет базу данных, база данных SQLite блокируется до тех пор, пока эта транзакция не будет подтверждена. Параметр timeout определяет, сколько времени подключение должно ожидать, пока блокировка не исчезнет, прежде чем сгенерировать исключение. По умолчанию значение параметра timeout — 5.0 (пять секунд).

Для параметра isolation_level см. свойство Connection.isolation_level объектов Connection.

SQLite по своей природе поддерживает только типы TEXT, INTEGER, REAL, BLOB и NULL. Если вы хотите использовать другие типы, вам нужно добавить их поддержку самостоятельно. Параметр detect_types и использование настраиваемых преобразователей, зарегистрированных с помощью функции уровня модуля register_converter(), позволяют легко это сделать.

detect_types по умолчанию равен 0 (т. е. выключен, нет обнаружения типа), можно установить его в любое сочетание PARSE_DECLTYPES и PARSE_COLNAMES для включения обнаружения типа.

По умолчанию модуль sqlite3 использует свой класс Connection для вызова connect. Однако вы можете подклассифицировать класс Connection и заставить connect() использовать ваш класс вместо него, передав ваш класс в качестве параметра factory.

Подробности см. в разделе Типы SQLite и Python этого руководства.

Модуль sqlite3 внутренне использует кэш операторов для избежания накладных расходов на синтаксический разбор SQL. Если вы хотите явно задать количество операторов, которые кэшируются для подключения, можно установить параметр cached_statements. Текущее значение по умолчанию — кэширование 100 операторов.

sqlite3.register_converter(typename, callable)

Регистрирует вызываемый объект для преобразования байтовой строки из базы данных в настраиваемый тип Python. Вызываемый объект будет вызываться для всех значений базы данных, которые имеют тип typename. См. параметр detect_types функции connect() для получения информации о работе обнаружения типов. Обратите внимание, что typename и имя типа в запросе сопоставляются без учёта регистра.

sqlite3.register_adapter(type, callable)

Регистрирует вызываемый объект для преобразования настраиваемого типа Python type в один из поддерживаемых SQLite типов. Вызываемый объект callable принимает в качестве единственного параметра значение Python и должен возвращать значение одного из следующих типов: int, long, float, str (кодировка UTF-8), unicode или буфер.

sqlite3.complete_statement(sql)

Возвращает True, если строка sql содержит один или несколько полных SQL-операторов, завершённых точкой с запятой. Он не проверяет, является ли SQL синтаксически корректным, а только то, что нет незакрытых строковых литералов и оператор завершён точкой с запятой.

Это можно использовать для создания оболочки для SQLite, как в следующем примере:

# A minimal SQLite shell for experiments

import sqlite3

con = sqlite3.connect(":memory:")
con.isolation_level = None
cur = con.cursor()

buffer = ""

print "Enter your SQL commands to execute in sqlite3."
print "Enter a blank line to exit."

while True:
    line = raw_input()
    if line == "":
        break
    buffer += line
    if sqlite3.complete_statement(buffer):
        try:
            buffer = buffer.strip()
            cur.execute(buffer)

            if buffer.lstrip().upper().startswith("SELECT"):
                print cur.fetchall()
        except sqlite3.Error as e:
            print "An error occurred:", e.args[0]
        buffer = ""

con.close()

sqlite3.enable_callback_tracebacks(flag)

По умолчанию вы не получите никаких отладочных сообщений в пользовательских функциях, агрегатах, преобразователях, обработчиках авторизации и т. д. Если вы хотите их отладить, вы можете вызвать эту функцию со значением flag, установленным в True. После этого вы получите отладочные сообщения из обратных вызовов для sys.stderr. Используйте False, чтобы отключить эту функцию.

2. Объекты подключений

class sqlite3.Connection

Подключение к базе данных SQLite имеет следующие атрибуты и методы:

isolation_level

Получение или установка текущего уровня изоляции. None для режима автозаписи или одно из значений «DEFERRED», «IMMEDIATE» или «EXCLUSIVE». Подробнее см. раздел Управление транзакциями.

cursor(factory=Cursor)

Метод cursor принимает один необязательный параметр factory. Если он указан, он должен быть вызываемым объектом, возвращающим экземпляр Cursor или его подклассов.

commit()

Этот метод подтверждает текущую транзакцию. Если вы не вызываете этот метод, все ваши действия с момента последнего вызова commit() не будут видны другим подключениям к базе данных. Если вы не видите данных, которые вы записали в базу данных, проверьте, не забыли ли вы вызвать этот метод.

rollback()

Этот метод откатывает все изменения в базе данных с момента последнего вызова commit().

close()

Закрывает подключение к базе данных. Обратите внимание, что это не автоматически вызывает commit(). Если вы просто закроете подключение к базе данных без предварительного вызова commit(), ваши изменения будут потеряны!

execute(sql[, parameters])

Это нестандартный сокращенный метод, который создаёт промежуточный объект курсора, вызывая метод cursor, а затем вызывает метод курсора execute с заданными параметрами.

executemany(sql[, parameters])

Это нестандартный сокращенный метод, который создаёт промежуточный объект курсора, вызывая метод cursor, а затем вызывает метод курсора executemany с заданными параметрами.

executescript(sql_script)

Это нестандартный сокращенный метод, который создаёт промежуточный объект курсора, вызывая метод cursor, а затем вызывает метод курсора executescript с заданными параметрами.

create_function(name, num_params, func)

Создаёт пользовательскую функцию, которую можно использовать в дальнейшем в SQL-запросах под именем name. num_params — количество параметров, принимаемых функцией, а func — вызываемый Python-объект, который используется как SQL-функция.

Функция может возвращать любые типы, поддерживаемые SQLite: unicode, str, int, long, float, buffer и None.

Пример:

import sqlite3
import md5

def md5sum(t):
    return md5.md5(t).hexdigest()

con = sqlite3.connect(":memory:")
con.create_function("md5", 1, md5sum)
cur = con.cursor()
cur.execute("select md5(?)", ("foo",))
print cur.fetchone()[0]

create_aggregate(name, num_params, aggregate_class)

Создаёт пользовательскую агрегатную функцию.

Класс агрегата должен реализовывать метод step, который принимает количество параметров num_params, и метод finalize, который вернёт конечный результат агрегата.

Метод finalize может возвращать любые типы, поддерживаемые SQLite: unicode, str, int, long, float, buffer и None.

Пример:

import sqlite3

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.cursor()
cur.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]

create_collation(name, callable)

Создаёт сортировку (collation) со специфицированным именем name и вызываемым объектом callable. Вызываемый объект получит два строковых аргумента. Он должен вернуть -1, если первый аргумент меньше второго в порядке сортировки, 0, если они равны, и 1, если первый больше второго. Обратите внимание, что это управляет сортировкой (ORDER BY в SQL), поэтому ваши сравнения не влияют на другие SQL-операции.

Обратите внимание, что вызываемый объект получит свои параметры как байтовые строки Python, которые обычно закодированы в UTF-8.

Следующий пример демонстрирует пользовательскую сортировку, которая сортирует «неправильным» способом:

import sqlite3

def collate_reverse(string1, string2):
    return -cmp(string1, string2)

con = sqlite3.connect(":memory:")
con.create_collation("reverse", collate_reverse)

cur = con.cursor()
cur.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()

Чтобы удалить сортировку, вызовите create_collation с None в качестве вызываемого объекта:

con.create_collation("reverse", None)

interrupt()

Вы можете вызвать этот метод из другого потока, чтобы прервать любые запросы, которые могут выполняться в подключении. Запрос затем прервётся, и вызывающая сторона получит исключение.

set_authorizer(authorizer_callback)

Эта процедура регистрирует обратный вызов. Обратный вызов вызывается при каждой попытке доступа к столбцу таблицы в базе данных. Обратный вызов должен вернуть SQLITE_OK если доступ разрешён, SQLITE_DENY если весь SQL-запрос должен быть прерван с ошибкой и SQLITE_IGNORE если столбец должен обрабатываться как значение NULL. Эти константы доступны в модуле sqlite3.

Первый аргумент обратного вызова указывает тип операции, которая должна быть авторизована. Второй и третий аргументы будут аргументами или None в зависимости от первого аргумента. Четвёртый аргумент — имя базы данных («main», «temp» и т. д.), если применимо. Пятый аргумент — имя внутреннего триггера или представления, ответственного за попытку доступа, или None, если эта попытка доступа происходит непосредственно из входного SQL-кода.

Обратитесь к документации SQLite, чтобы узнать о возможных значениях первого аргумента и значении второго и третьего аргументов в зависимости от первого аргумента. Все необходимые константы доступны в модуле sqlite3.

set_progress_handler(handler, n)

Эта процедура регистрирует обратный вызов. Обратный вызов вызывается для каждой n инструкции виртуальной машины SQLite. Это полезно, если вы хотите быть вызываемым из SQLite во время длительных операций, например, для обновления графического интерфейса.

Если вы хотите очистить ранее установленный обработчик прогресса, вызовите метод с None для handler.

New in version 2.6.

enable_load_extension(enabled)

Эта процедура разрешает/запрещает SQLite-движку загружать SQLite-расширения из общих библиотек. SQLite-расширения могут определять новые функции, агрегаты или целые новые реализации виртуальных таблиц. Хорошо известным расширением является расширение полнотекстового поиска, распространяемое с SQLite.

Загружаемые расширения по умолчанию отключены. См. 1.

New in version 2.7.

import sqlite3

con = sqlite3.connect(":memory:")

# enable extension loading
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

load_extension(path)

Эта процедура загружает SQLite-расширение из общей библиотеки. Вы должны включить загрузку расширений с помощью enable_load_extension() перед использованием этой процедуры.

Загружаемые расширения по умолчанию отключены. См. 1.

New in version 2.7.

row_factory

Вы можете изменить этот атрибут на вызываемый объект, который принимает курсор и исходную строку в виде кортежа и вернёт реальную строку результата. Таким образом, вы можете реализовать более продвинутые способы возврата результатов, например, вернуть объект, который также может получить доступ к столбцам по имени.

Пример:

import sqlite3

def dict_factory(cursor, row):
    d = {}
    for idx, col in enumerate(cursor.description):
        d[col[0]] = row[idx]
    return d

con = sqlite3.connect(":memory:")
con.row_factory = dict_factory
cur = con.cursor()
cur.execute("select 1 as a")
print cur.fetchone()["a"]

Если возвращение кортежа недостаточно, и вам нужен доступ к столбцам по имени, вы должны рассмотреть возможность установки row_factory на высокоэффективный тип sqlite3.Row. Row предоставляет доступ к столбцам как по индексу, так и по имени, не требуя значительных затрат памяти. Вероятно, это будет лучше, чем собственный пользовательский подход на основе словарей или даже решение на базе db_row.

text_factory

С помощью этого атрибута вы можете контролировать, какие объекты возвращаются для типа данных TEXT. По умолчанию этот атрибут установлен на unicode, и модуль sqlite3 вернёт объекты Unicode для TEXT. Если вы хотите возвращать байтовые строки вместо этого, вы можете установить его на str.

Для повышения эффективности также есть способ возвращать объекты Unicode только для данных, не являющихся ASCII, и байтовые строки в противном случае. Для активации установите этот атрибут на sqlite3.OptimizedUnicode.

Вы также можете установить его на любой другой вызываемый объект, который принимает один байтовый строковый параметр и возвращает результирующий объект.

Следующий пример кода иллюстрирует это:

import sqlite3

con = sqlite3.connect(":memory:")
cur = con.cursor()

AUSTRIA = u"\xd6sterreich"

# by default, rows are returned as Unicode
cur.execute("select ?", (AUSTRIA,))
row = cur.fetchone()
assert row[0] == AUSTRIA

# but we can make sqlite3 always return bytestrings ...
con.text_factory = str
cur.execute("select ?", (AUSTRIA,))
row = cur.fetchone()
assert type(row[0]) is str
# the bytestrings will be encoded in UTF-8, unless you stored garbage in the
# database ...
assert row[0] == AUSTRIA.encode("utf-8")

# we can also implement a custom text_factory ...
# here we implement one that will ignore Unicode characters that cannot be
# decoded from UTF-8
con.text_factory = lambda x: unicode(x, "utf-8", "ignore")
cur.execute("select ?", ("this is latin1 and would normally create errors" +
                         u"\xe4\xf6\xfc".encode("latin1"),))
row = cur.fetchone()
assert type(row[0]) is unicode

# sqlite3 offers a built-in optimized text_factory that will return bytestring
# objects, if the data is in ASCII only, and otherwise return unicode objects
con.text_factory = sqlite3.OptimizedUnicode
cur.execute("select ?", (AUSTRIA,))
row = cur.fetchone()
assert type(row[0]) is unicode

cur.execute("select ?", ("Germany",))
row = cur.fetchone()
assert type(row[0]) is str

total_changes

Возвращает общее количество строк базы данных, которые были изменены, вставлены или удалены с момента открытия подключения к базе данных.

iterdump

Возвращает итератор для вывода базы данных в формате текста SQL. Полезно для сохранения базы данных в памяти для последующего восстановления. Эта функция предоставляет те же возможности, что и команда .dump в оболочке sqlite3.

Новая в версии 2.6.

Пример:

# Convert file existing_db.db to SQL dump file dump.sql
import sqlite3, os

con = sqlite3.connect('existing_db.db')
with open('dump.sql', 'w') as f:
    for line in con.iterdump():
        f.write('%s\n' % line)

3. Объекты курсора

class sqlite3.Cursor

Объект Cursor обладает следующими атрибутами и методами.

execute(sql[, parameters])

Выполняет оператор SQL. Оператор SQL может быть параметризован (т. е. содержать плейсхолдеры вместо литералов SQL). Модуль sqlite3 поддерживает два типа плейсхолдеров: вопросительные знаки (стиль qmark) и именованные плейсхолдеры (именованный стиль).

Вот пример обоих стилей:

import sqlite3

con = sqlite3.connect(":memory:")
cur = con.cursor()
cur.execute("create table people (name_last, age)")

who = "Yeltsin"
age = 72

# This is the qmark style:
cur.execute("insert into people values (?, ?)", (who, age))

# And this is the named style:
cur.execute("select * from people where name_last=:who and age=:age", {"who": who, "age": age})

print cur.fetchone()

execute() выполнит только один оператор SQL. Если вы попытаетесь выполнить более одного оператора с ним, будет выведено предупреждение. Используйте executescript(), если необходимо выполнить несколько операторов SQL одним вызовом.

executemany(sql, seq_of_parameters)

Выполняет SQL-команду для всех последовательностей или отображений параметров, найденных в последовательности sql. Модуль sqlite3 также позволяет использовать итератор, возвращающий параметры вместо последовательности.

import sqlite3

class IterChars:
    def __init__(self):
        self.count = ord('a')

    def __iter__(self):
        return self

    def next(self):
        if self.count > ord('z'):
            raise StopIteration
        self.count += 1
        return (chr(self.count - 1),) # this is a 1-tuple

con = sqlite3.connect(":memory:")
cur = con.cursor()
cur.execute("create table characters(c)")

theIter = IterChars()
cur.executemany("insert into characters(c) values (?)", theIter)

cur.execute("select c from characters")
print cur.fetchall()

Вот более короткий пример с использованием генератора:

import sqlite3
import string

def char_generator():
    for c in string.lowercase:
        yield (c,)

con = sqlite3.connect(":memory:")
cur = con.cursor()
cur.execute("create table characters(c)")

cur.executemany("insert into characters(c) values (?)", char_generator())

cur.execute("select c from characters")
print cur.fetchall()

executescript(sql_script)

Это нестандартный вспомогательный метод для выполнения нескольких SQL-операторов сразу. Он сначала выполняет оператор COMMIT, а затем выполняет SQL-скрипт, полученный в качестве параметра.

sql_script может быть строкой байтов или строкой Юникода.

Пример:

import sqlite3

con = sqlite3.connect(":memory:")
cur = con.cursor()
cur.executescript("""
    create table person(
        firstname,
        lastname,
        age
    );

    create table book(
        title,
        author,
        published
    );

    insert into book(title, author, published)
    values (
        'Dirk Gently''s Holistic Detective Agency',
        'Douglas Adams',
        1987
    );
    """)

fetchone()

Извлекает следующую строку набора результатов запроса, возвращая одну последовательность или None, если больше данных недоступно.

fetchmany([size=cursor.arraysize])

Извлекает следующий набор строк результата запроса, возвращая список. Возвращается пустой список, когда больше строк недоступно.

Количество строк для извлечения за один вызов задается параметром size. Если он не указан, количество строк для извлечения определяется атрибутом arraysize курсора. Метод должен попытаться извлечь указанное количество строк. Если это невозможно из-за того, что указанное количество строк недоступно, может быть возвращено меньше строк.

Обратите внимание, что использование параметра size влечет за собой некоторые соображения по производительности. Для оптимальной производительности лучше использовать атрибут arraysize. Если используется параметр size, лучше, чтобы он сохранял одно и то же значение от одного вызова fetchmany() к другому.

fetchall()

Извлекает все (оставшиеся) строки результата запроса, возвращая список. Обратите внимание, что атрибут arraysize курсора может повлиять на производительность этой операции. Возвращается пустой список, если строки недоступны.

rowcount

Хотя класс Cursor модуля sqlite3 реализует этот атрибут, собственная поддержка механизма базы данных для определения «измененных строк»/«выбранных строк» специфична.

Для операторов executemany() количество изменений суммируется в rowcount.

В соответствии со спецификацией Python DB API, атрибут rowcount равен «-1 в случае, если ни одна операция executeXX() не была выполнена над курсором или количество строк последней операции не может быть определено интерфейсом». Это включает операторы SELECT, потому что мы не можем определить количество строк, произведённых запросом, пока все строки не будут извлечены.

В версиях SQLite до 3.6.5, rowcount устанавливается в 0, если вы выполняете оператор DELETE FROM table без каких-либо условий.

lastrowid

Этот атрибут только для чтения предоставляет rowid последней изменённой строки. Он устанавливается только если вы выполнили оператор INSERT с помощью метода execute(). Для операций, отличных от INSERT или когда вызывается executemany(), lastrowid устанавливается в None.

description

Этот атрибут только для чтения предоставляет имена столбцов последнего запроса. Для совместимости с Python DB API он возвращает 7-кортеж для каждого столбца, где последние шесть элементов каждого кортежа являются None.

Он устанавливается и для операторов SELECT без соответствий строк.

connection

Этот атрибут только для чтения предоставляет подключение к SQLite-базе данных Connection, используемое объектом Cursor. Объект Cursor, созданный при вызове con.cursor(), будет иметь атрибут connection, ссылающийся на con:

>>> con = sqlite3.connect(":memory:")
>>> cur = con.cursor()
>>> cur.connection == con
True

4. Объекты строк

class sqlite3.Row

Объект Row служит высокооптимизированной фабрикой строк row_factory для объектов Connection. Он пытается имитировать кортеж по большинству функций.

Он поддерживает доступ по имени столбца и индексу, итерацию, представление, проверку на равенство и len().

Если два объекта Row имеют точно такие же столбцы и их члены равны, они сравниваются как равные.

Изменено в версии 2.6: Добавлена итерация и равенство (хешируемость).

keys()

Этот метод возвращает список имён столбцов. Сразу после запроса это первый элемент каждого кортежа в Cursor.description.

Новая в версии 2.6.

Предположим, мы инициализируем таблицу, как показано в примере выше:

conn = sqlite3.connect(":memory:")
c = conn.cursor()
c.execute('''create table stocks
(date text, trans text, symbol text,
 qty real, price real)''')
c.execute("""insert into stocks
          values ('2006-01-05','BUY','RHAT',100,35.14)""")
conn.commit()
c.close()

Теперь мы подключаем Row:

>>> conn.row_factory = sqlite3.Row
>>> c = conn.cursor()
>>> c.execute('select * from stocks')
<sqlite3.Cursor object at 0x7f4e7dd8fa80>
>>> r = c.fetchone()
>>> type(r)
<type 'sqlite3.Row'>
>>> r
(u'2006-01-05', u'BUY', u'RHAT', 100.0, 35.14)
>>> len(r)
5
>>> r[2]
u'RHAT'
>>> r.keys()
['date', 'trans', 'symbol', 'qty', 'price']
>>> r['qty']
100.0
>>> for member in r:
...     print member
...
2006-01-05
BUY
RHAT
100.0
35.14

5. Типы SQLite и Python

5.1. Введение

SQLite изначально поддерживает следующие типы: NULL, INTEGER, REAL, TEXT, BLOB.

Следующие типы Python могут быть отправлены в SQLite без проблем:

Вот как типы SQLite преобразуются в типы Python по умолчанию:

Система типов модуля sqlite3 расширяется двумя способами: вы можете хранить дополнительные типы Python в базе данных SQLite с помощью адаптации объектов, и вы можете позволить модулю sqlite3 преобразовывать типы SQLite в разные типы Python с помощью конвертеров.

5.2. Использование адаптеров для хранения дополнительных типов Python в базах данных SQLite

Как описано ранее, SQLite поддерживает только ограниченный набор типов в родном виде. Чтобы использовать другие типы Python с SQLite, вы должны адаптировать их к одному из поддерживаемых типов модуля sqlite3 для SQLite: NoneType, int, long, float, str, unicode, buffer.

Есть два способа разрешить модулю sqlite3 адаптировать пользовательский тип Python к одному из поддерживаемых.

5.2.1. Адаптация объекта

Это хороший подход, если вы сами пишете класс. Предположим, у вас есть класс такого вида:

class Point(object):
    def __init__(self, x, y):
        self.x, self.y = x, y

Теперь вы хотите сохранить точку в одном столбце SQLite. Сначала вам нужно выбрать один из поддерживаемых типов для представления точки. Возьмем str и разделим координаты точкой с запятой. Затем вам нужно добавить в класс метод __conform__(self, protocol), который должен возвращать преобразованное значение. Параметр protocol будет PrepareProtocol.

import sqlite3

class Point(object):
    def __init__(self, x, y):
        self.x, self.y = x, y

    def __conform__(self, protocol):
        if protocol is sqlite3.PrepareProtocol:
            return "%f;%f" % (self.x, self.y)

con = sqlite3.connect(":memory:")
cur = con.cursor()

p = Point(4.0, -3.2)
cur.execute("select ?", (p,))
print cur.fetchone()[0]

5.2.2. Регистрация вызываемой функции адаптера

Другая возможность — создать функцию, которая преобразует тип в строковое представление, и зарегистрировать эту функцию с помощью register_adapter().

import sqlite3

class Point(object):
    def __init__(self, x, y):
        self.x, self.y = x, y

def adapt_point(point):
    return "%f;%f" % (point.x, point.y)

sqlite3.register_adapter(Point, adapt_point)

con = sqlite3.connect(":memory:")
cur = con.cursor()

p = Point(4.0, -3.2)
cur.execute("select ?", (p,))
print cur.fetchone()[0]

Модуль sqlite3 имеет два адаптера по умолчанию для встроенных типов Python datetime.date и datetime.datetime. Теперь предположим, что мы хотим хранить объекты datetime.datetime не в формате ISO, а в виде Unix-временных меток.

import sqlite3
import datetime, time

def adapt_datetime(ts):
    return time.mktime(ts.timetuple())

sqlite3.register_adapter(datetime.datetime, adapt_datetime)

con = sqlite3.connect(":memory:")
cur = con.cursor()

now = datetime.datetime.now()
cur.execute("select ?", (now,))
print cur.fetchone()[0]

5.3. Преобразование значений SQLite в пользовательские типы Python

Написание адаптера позволяет передавать пользовательские типы Python в SQLite. Но для того, чтобы это было действительно полезно, нам нужно сделать цикл «Python — SQLite — Python» работоспособным.

Используем конвертеры.

Вернёмся к классу Point. Мы сохранили координаты x и y, разделённые точкой с запятой, как строки в SQLite.

Сначала определим функцию-конвертер, которая принимает строку в качестве параметра и строит объект Point из неё.

def convert_point(s):
    x, y = map(float, s.split(";"))
    return Point(x, y)

Теперь вы должны сообщить модулю sqlite3, что то, что вы выбираете из базы данных, на самом деле является точкой. Существует два способа сделать это:

• Неявный способ, используя объявленный тип
• Явный способ, используя имя столбца

Оба способа описаны в разделе Функции и константы модуля, в записях для констант PARSE_DECLTYPES и PARSE_COLNAMES.

Следующий пример демонстрирует оба подхода.

import sqlite3

class Point(object):
    def __init__(self, x, y):
        self.x, self.y = x, y

    def __repr__(self):
        return "(%f;%f)" % (self.x, self.y)

def adapt_point(point):
    return "%f;%f" % (point.x, point.y)

def convert_point(s):
    x, y = map(float, s.split(";"))
    return Point(x, y)

# Register the adapter
sqlite3.register_adapter(Point, adapt_point)

# Register the converter
sqlite3.register_converter("point", convert_point)

p = Point(4.0, -3.2)

#########################
# 1) Using declared types
con = sqlite3.connect(":memory:", detect_types=sqlite3.PARSE_DECLTYPES)
cur = con.cursor()
cur.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()

#######################
# 1) Using column names
con = sqlite3.connect(":memory:", detect_types=sqlite3.PARSE_COLNAMES)
cur = con.cursor()
cur.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]
cur.close()
con.close()

5.4. Адаптеры и конвертеры по умолчанию

Для типов date и datetime в модуле datetime существуют адаптеры по умолчанию. Они будут отправляться в SQLite как даты/временные метки ISO.

Конвертеры по умолчанию зарегистрированы под именем «date» для datetime.date и под именем «timestamp» для 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])

Если временная метка, хранящаяся в SQLite, имеет дробную часть, превышающую 6 цифр, её значение будет усечено до микросекундной точности конвертером временных меток.

6. Управление транзакциями

По умолчанию, модуль sqlite3 неявно открывает транзакции перед оператором языка манипулирования данными (DML) (например, INSERT/UPDATE/DELETE/REPLACE), и неявно подтверждает транзакции перед оператором, не относящимся к DML и запросу (то есть, любой оператор, кроме SELECT или упомянутых выше).

Таким образом, если вы находитесь внутри транзакции и выдаёте команду типа CREATE TABLE ..., VACUUM, PRAGMA, модуль sqlite3 неявно подтвердит её перед выполнением этой команды. Причины этого заключаются в том, что некоторые из этих команд не работают внутри транзакций. Другая причина — sqlite3 необходимо отслеживать состояние транзакции (активна транзакция или нет).

Вы можете контролировать типы операторов DML, которые sqlite3 неявно выполняет (или вообще не выполняет), используя параметр isolation_level при вызове connect() или свойство isolation_level подключений.

Если вам нужна режим автоматического подтверждения, установите isolation_level в None.

В противном случае оставьте значение по умолчанию, что приведёт к простому оператору «BEGIN», или установите его на одно из поддерживаемых SQLite уровней изоляции: «DEFERRED», «IMMEDIATE» или «EXCLUSIVE».

7. Эффективное использование sqlite3

7.1. Использование сокращённых методов

Использование нестандартных методов execute(), executemany() и executescript() объекта Connection, код может быть написан более лаконично, поскольку вам не нужно явно создавать (часто излишние) объекты Cursor. Вместо этого, объекты Cursor создаются неявно, и эти сокращённые методы возвращают объекты курсора. Таким образом, вы можете выполнить оператор SELECT и перебирать его непосредственно, используя только один вызов объекта Connection.

import sqlite3

persons = [
    ("Hugo", "Boss"),
    ("Calvin", "Klein")
    ]

con = sqlite3.connect(":memory:")

# Create the table
con.execute("create table person(firstname, lastname)")

# Fill the table
con.executemany("insert into person(firstname, lastname) values (?, ?)", persons)

# Print the table contents
for row in con.execute("select firstname, lastname from person"):
    print row

print "I just deleted", con.execute("delete from person").rowcount, "rows"

7.2. Доступ к столбцам по имени вместо индекса

Одной из полезных функций модуля sqlite3 является встроенный класс sqlite3.Row, предназначенный для использования в качестве фабрики строк.

Строки, обернутые этим классом, могут быть доступны как по индексу (как кортежи), так и по имени в регистронезависимом формате:

import sqlite3

con = sqlite3.connect(":memory:")
con.row_factory = sqlite3.Row

cur = con.cursor()
cur.execute("select 'John' as name, 42 as age")
for row in cur:
    assert row[0] == row["name"]
    assert row["name"] == row["nAmE"]
    assert row[1] == row["age"]
    assert row[1] == row["AgE"]

7.3. Использование соединения как менеджера контекста

Объекты соединения могут использоваться как менеджеры контекста, которые автоматически подтверждают или отменяют транзакции. В случае возникновения исключения транзакция отменяется; в противном случае транзакция подтверждается:

import sqlite3

con = sqlite3.connect(":memory:")
con.execute("create table person (id integer primary key, firstname varchar unique)")

# Successful, con.commit() is called automatically afterwards
with con:
    con.execute("insert into person(firstname) values (?)", ("Joe",))

# 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 person(firstname) values (?)", ("Joe",))
except sqlite3.IntegrityError:
    print "couldn't add Joe twice"

8. Общие проблемы

8.1. Многопоточность

У более старых версий SQLite были проблемы с совместным использованием соединений между потоками. Вот почему модуль Python запрещает совместное использование соединений и курсоров между потоками. Если вы всё же попытаетесь это сделать, произойдёт исключение во время выполнения.

Исключением является вызов метода interrupt(), который имеет смысл вызывать только из другого потока.

Примечания

1(1,2)

Модуль sqlite3 по умолчанию не создаётся с поддержкой подключаемых расширений, потому что на некоторых платформах (особенно Mac OS X) библиотеки SQLite скомпилированы без этой функции. Чтобы получить поддержку подключаемых расширений, необходимо изменить setup.py и удалить строку, которая устанавливает SQLITE_OMIT_LOAD_EXTENSION.