Далее, создадим курсор для отправки инструкций SQL серверу баз данных и создадим с его помощью первую таблицу:
> >> curs = conn.cursor()
>>>
> >> tblcmd = ‘create table people (name char(30), job char(10), pay int(4))’
> >> curs.execute(tblcmd)
Последняя команда в этом примере создаст в базе данных таблицу с именем «people» — идентификаторы name, job и pay определяют столбцы в этой таблице и их типы с использованием синтаксических конструкций «тип(размер)» — две строки и целое число. Типы данных могут быть более сложными, чем в нашем примере, но мы пока не будем обращать внимание на такие подробности (обращайтесь к руководствам по языку SQL). В интерфейсе SQLite роль базы данных играет файл, поэтому здесь отсутствует понятие создания и выбора базы данных внутри него, как в некоторых других системах. Теперь у нас имеется в текущем рабочем каталоге простой плоский файл с именем data1 , который содержит двоичные данные и нашу таблицу people.
Добавление записей
К настоящему моменту мы подключились к базе данных (в SQLite это означает — просто открыли локальный файл) и создали таблицу. Теперь запустим новый интерактивный сеанс Python и создадим несколько записей. Существует три основных способа, основанных на инструкциях, которые мы можем использовать здесь: вставлять записи по одной, вставить сразу несколько записей в одной инструкции или задействовать цикл Python. Ниже приводится простейшее решение (я опустил вывод результатов некоторых вызовов, которые не имеют отношения к обсуждаемой теме):
C:\…\PP4E\Dbase\Sql> python
> >> import sqlite3
> >> conn = sqlite3.connect(‘dbase1’)
> >> curs = conn.cursor()
> >> curs.execute(‘insert into people values (?, ?, ?)’, (‘Bob’, ‘dev’, 5000))
>>> curs.rowcount
1
>>> sqlite3.paramstyle
‘qmark‘
Здесь создается объект курсора, как уже было показано выше, чтобы получить возможность отправлять инструкции SQL серверу баз данных. Команда insert в языке SQL добавляет в таблицу единственную запись. После вызова метода execute в атрибуте rowcount курсора возвращается количество записей, созданных или затронутых последней выполненной инструкцией. В некоторых модулях реализации интерфейсов баз данных это же число доступно в виде значения, возвращаемого методом execute, но это не оговаривается в спецификации API баз данных и не реализовано в SQLite. Иными словами, не следует полагаться на это, если необходимо, чтобы сценарий для работы с базой данных был способен взаимодействовать с другими системами баз данных.
Значения параметров для подстановки в инструкцию SQL обычно передаются в виде последовательности (например, в виде списка или кортежа). Обратите внимание на вызов метода paramstyle, который сообщает стиль обозначения параметров в строке с инструкцией. В данном случае qmark означает, что параметры внутри инструкций обозначаются знаками вопроса ?. Другие модули баз данных могут использовать такие стили, как format (параметры обозначаются, как %s), числовые индексы или именованные параметры, — за дополнительной информацией обращайтесь к описанию API базы данных.
Для добавления в одной инструкции сразу несколько записей используется метод executemany и последовательность записей (например, список списков). По своему действию вызов этого метода напоминает вызов метода execute для каждой записи в аргументе и в действительности может быть реализован именно так, однако интерфейсы баз данных могут также использовать приемы, специфичные для конкретной базы данных, чтобы ускорить выполнение:
>>> curs.executemany(‘insert into people values (?, ?, ?)’,
… [ (‘Sue’, ‘mus’, ‘70000’),
… (‘Ann’, ‘mus’, ‘60000’)])
>>> curs.rowcount
2
Последняя инструкция добавит сразу две записи. Немногим больше работы требуется выполнить, чтобы добиться того же результата, вставляя по одной записи в цикле:
>>> rows = [[‘Tom’, ‘mgr’, 100000],
… [‘Kim’, ‘adm’, 30000],
… [‘pat’, ‘dev’, 90000]]
>>> for row in rows:
… curs.execute(‘insert into people values (? , ?, ?)’, row)
…
>>> conn.commit()
Подобное смешивание Python и SQL открывает весьма интересные возможности. Обратите внимание на последнюю команду — чтобы сохранить изменения в базе данных, необходимо всегда вызывать метод commit объекта соединения. В противном случае, когда соединение будет закрыто, изменения будут потеряны. В действительности, пока не будет вызван метод commit, ни одна из добавленных записей не будет видна из других соединений с базой данных.
Технически спецификация API требует, чтобы при закрытии (вручную, вызовом метода close или автоматически в момент утилизации объекта сборщиком мусора) объект соединения автоматически вызывал метод rollback с целью отменить неподтвержденные изменения. Для систем баз данных, не поддерживающих операции подтверждения и отмены транзакций, эти методы могут не выполнять никаких действий. SQLite реализует оба метода, commit и rollback; последний из них откатывает любые изменения до момента последнего вызова метода commit.
Выполнение запросов
Итак, к настоящему моменту мы добавили шесть записей в таблицу базы данных. Выполним запрос SQL, чтобы посмотреть, что у нас получилось:
>>> curs.execute(‘select * from people‘)
>>> curs.fetchall()
[(‘Bob‘, ‘dev‘, 5000), (‘Sue‘, ‘mus‘, 70000), (‘Ann‘, ‘mus‘, 60000), (‘Tom‘, ‘mgr‘, 100000), (‘Kim‘, ‘adm‘, 30000), (‘pat‘, ‘dev‘, 90000)]
Здесь с помощью объекта курсора выполняется инструкция SQL select, которая отбирает все записи, и вызывается метод fetchall курсора, чтобы извлечь их. Записи возвращаются сценарию в виде последовательности последовательностей. В данном модуле это список кортежей — внешний список представляет таблицу результатов, вложенные кортежи представляют записи, а содержимое вложенных кортежей — столбцы данных. Поскольку все эти данные являются обычными данными Python, после получения результатов запроса их можно обрабатывать с помощью обычного программного кода Python. Например, чтобы сделать вывод более удобочитаемым, выполним цикл по результатам:
> >> curs.execute(‘select * from people’)
> >> for row in curs.fetchall():
… print(row)
(‘Bob’, ‘dev’, 5000)
(‘Sue’, ‘mus’, 70000)
(‘Ann’, ‘mus’, 60000)
(‘Tom’, ‘mgr’, 100000)
(‘Kim’, ‘adm’, 30000)
(‘pat‘, ‘dev‘, 90000)
В цикле также удобно использовать операцию распаковывания кортежей для выборки значений столбцов в итерациях. Ниже демонстрируется простой форматированный вывод значений двух столбцов:
> >> curs.execute(‘select * from people’)
> >> for (name, job, pay) in curs.fetchall():
… print(name, ‘:’, pay)
Bob : 5000
Sue : 70000
Ann : 60000
Tom : 100000
Kim : 30000
pat : 90000
Поскольку результатом запроса является последовательность, для ее обработки можно использовать мощные операции над последовательностями и инструменты итераций, имеющиеся в Python. Например, чтобы отобрать значения только из столбца name, можно выполнить более специализированный запрос SQL и получить список кортежей:
> >> curs.execute(‘select name from people’)
> >> names = curs.fetchall()
> >> names
[(‘Bob’,), (‘Sue’,), (‘Ann’,), (‘Tom’,), (‘Kim’,), (‘pat’,)]
Или использовать для выборки желаемых полей генератор списков — используя программный код Python, мы получаем более полный контроль над данными и их форматированием:
> >> curs.execute(‘select * from people’)
>>> names = [rec[0] for rec in curs.fetchall()]
>>> names
[‘Bob’, ‘Sue’, ‘Ann’, ‘Tom’, ‘Kim’, ‘pat’]
Использовавшийся до сих пор метод fetchall извлекает сразу все результаты запроса в виде единой последовательности (в случае отсутствия результатов возвращается пустая последовательность). Это удобно, но такой способ может оказаться достаточно медленным, чтобы временно заблокировать вызывающую программу при большом объеме результатов или необходимости передавать значительные объемы данных по сети, когда взаимодействие выполняется с удаленным сервером (подобные операции в графическом интерфейсе можно было бы производить в параллельном потоке выполнения). Чтобы избежать этого, можно извлекать данные по одной записи или пакетами записей с помощью методов fetchone и fetchmany. Метод fetchone возвращает следующую запись из результатов или None по достижении конца таблицы:
>>> curs.execute(‘select * from people’)
>>> while True:
… row = curs.fetchone()
… if not row: break
… print(row)
|
‘Bob’, |
‘dev’, |
5000) |
|
‘Sue’, |
‘mus’, |
70000) |
|
‘Ann’, |
‘mus’, |
60000) |
|
‘Tom’, |
‘mgr’, |
100000) |
|
‘Kim’, |
‘adm’, |
30000) |
|
‘pat’, |
‘dev’, |
90000) |
Метод fetchmany возвращает последовательность записей из результатов, но не всю таблицу — можно явно указать количество записей, извлекаемых при каждом обращении, или положиться на значение по умолчанию, которое определяется атрибутом arraysize курсора. Каждый вызов возвращает не более указанного числа записей из результатов, или пустую последовательность по достижении конца таблицы:
>>> curs.execute(‘select * from people’)
>>> while True:
rows = curs.fetchmany() # size=N необязательный аргумент if not rows: break
for row in rows:
print(row)
(‘Bob’, ‘dev’, 5000)
(‘Sue’, ‘mus’, 70000)
(‘Ann’, ‘mus’, 60000)
(‘Tom’, ‘mgr’, 100000)
(‘Kim’, ‘adm’, 30000)
(‘pat’, ‘dev’, 90000)
Для этого модуля таблица результатов будет исчерпана, как только метод fetchone или fetchmany вернет значение False. Спецификация API баз данных требует, чтобы метод fetchall возвращал «все оставшиеся записи», поэтому перед извлечением новых данных, как правило, необходимо снова вызвать метод execute, чтобы получить результаты:
>>> curs.fetchone()
>>> curs.fetchmany()
[]
>>> curs.fetchall()
[]
Естественно, есть возможность не только извлекать таблицу целиком — в Python нам доступна вся мощь языка SQL:
>>> curs.execute(‘select name, job from people where pay > 60000′)
>>> curs.fetchall()
[(‘Sue’, ‘mus’), (‘Tom’, ‘mgr’), (‘pat’, ‘dev’)]
Последний запрос извлекает поля name и job для тех сотрудников, которые зарабатывают более $60 000 в год. Следующий фрагмент реализует аналогичную операцию, но передает значение, ограничивающее выбор, в виде параметра и указывает порядок следования результатов:
>>> query = ‘select name, job from people where pay >= ? order by name’
>>> curs.execute(query, [60000])
>>> for row in curs.fetchall(): print(row)
(‘Ann’, ‘mus’)
(‘Sue’, ‘mus’)
(‘Tom’, ‘mgr’)
(‘pat‘, ‘dev‘)
Выполнение обновлений
C:\…\PP4E\Dbase\Sql> python
>>> import sqlite3
>>> conn = sqlite3.connect(‘dbase1’)
>>> curs = conn.cursor()
>>> curs.execute(‘select * from people’)
>>> curs.fetchall()
[(‘Bob’, ‘dev’, 5000), (‘Sue’, ‘mus’, 70000), (‘Ann’, ‘mus’, 60000), (‘Tom’, ‘mgr’, 100000), (‘Kim’, ‘adm’, 30000), (‘pat’, ‘dev’, 90000)]
Инструкция SQL update изменяет записи — следующий пример запишет новое значение 65000 столбца pay в трех записях (Bob, Ann и Kim), потому что зарплата этих сотрудников не превышает $60 000. Как обычно, атрибут rowcount содержит количество изменившихся записей:
>>> curs.execute(‘update people set pay=? where pay <= ?’, [65000, 60000]) >>> curs.rowcount
3
>>> curs.execute(‘select * from people’)
>>> curs.fetchall()
[(‘Bob’, ‘dev’, 65000), (‘Sue’, ‘mus’, 70000), (‘Ann’, ‘mus’, 65000), (‘Tom’, ‘mgr’, 100000), (‘Kim’, ‘adm’, 65000), (‘pat’, ‘dev’, 90000)]
Инструкция SQL delete удаляет записи в соответствии с необязательным условием (чтобы удалить все записи, достаточно опустить условие). Следующий пример удалит запись о сотруднике с именем Bob, а также все другие записи, в которых поле pay имеет значение не меньше $90,000:
>>> curs.execute(‘delete from people where name = ?’, [‘Bob’])
>>> curs.execute(‘delete from people where pay >= ?’,(90000,))
>>> curs.execute(‘select * from people’)
>>> curs.fetchall()
[(‘Sue’, ‘mus’, 70000), (‘Ann’, ‘mus’, 65000), (‘Kim’, ‘adm’, 65000)]
>>> conn.commit()
Наконец, не забывайте подтверждать изменения перед завершением сценария или сеанса Python, если вы предполагали сохранить их. Без подтверждения вызов метода rollback объекта соединения из метода close или деструктора __del__ откатит все неподтвержденные изменения. Объекты соединений автоматически закрываются при утилизации сборщиком мусора, который вызывает деструктор __del__, выполняющий откат изменений, — сборка мусора выполняется автоматически при завершении программы, если не раньше.
Создание словарей записей
Теперь, когда мы познакомились с основами, двинемся дальше и применим их для решения более крупных задач. Спецификация API SQL определяет, что результаты запросов должны возвращаться в виде последовательностей, содержащих последовательности. Одной из наиболее типичных особенностей API, о которой часто забывают, является возможность получить записи в более структурированном виде — в виде словаря или экземпляра класса, например, ключи или атрибуты которых соответствуют именам столбцов. Механизмы ORM, с которыми мы познакомимся в конце этой главы, отображают записи в экземпляры классов, но, поскольку это Python, подобные трансформации совсем несложно реализовать другими способами. Кроме того, API уже дает нам все необходимые инструменты.
Использование описаний таблиц
Например, спецификация API баз данных определяет, что после выполнения запроса методом execute атрибут description курсора должен содержать имена и (для некоторых баз данных) типы столбцов в таблице с результатами. Чтобы увидеть, что возвращается в этом атрибуте, продолжим эксперименты с базой данных, находящейся в том состоянии, в каком мы оставили ее в предыдущем разделе:
>>> curs.execute(‘select * from people’)
>>> curs.description
((‘name’, None, None, None, None, None, None), (‘job’, None, None, None, None, None, None), (‘pay’, None, None, None, None, None, None))
>>> curs.fetchall()
[(‘Sue’, ‘mus’, 70000), (‘Ann’, ‘mus’, 65000), (‘Kim’, ‘adm’, 65000)]
Формально значением атрибута description является последовательность последовательностей с описаниями столбцов, следующих друг за другом. Описание поля type_code можно найти в спецификации API баз данных — оно отображается в объекты на верхнем уровне модуля интерфейса базы данных, но модуль sqlite3 реализует только поле с именем столбца:
(name, type_code, display_size, internal_size, precision, scale, null_ok)
Теперь мы в любой момент сможем использовать эти метаданные, когда потребуется вывести метки столбцов, например при форматированном выводе записей (для начала необходимо повторно выполнить запрос, поскольку результаты прошлого запроса уже были извлечены):
> >> curs.execute(‘select * from people’)
> >> colnames = [desc[0] for desc in curs.description]
> >> colnames
[‘name’, ‘job’, ‘pay’]
> >> for row in curs.fetchall():
… for name, value in zip(colnames, row):
… print(name, ‘\t=>’, value)
… print()
job => mus
pay => 70000
name => Ann
job => mus
pay => 65000
name => Kim
job => adm
pay => 65000
Обратите внимание, что для выравнивания вывода здесь был использован символ табуляции — более удачное решение состоит в том, чтобы определить максимальную длину имени поля (как это сделать, будет показано в примере ниже).
Конструирование словарей записей
Мы можем немного усовершенствовать программный код форматированного вывода, предусмотрев создание словаря для каждой записи, роль ключей в котором будут выполнять имена полей — нам нужно лишь заполнить словарь в процессе обхода:
> >> curs.execute(‘select * from people’)
> >> colnames = [desc[0] for desc in curs.description]
> >> rowdicts = []
> >> for row in curs.fetchall():
… newdict = {}
… for name, val in zip(colnames, row):
… newdict[name] = val
… rowdicts.append(newdict)
…
> >> for row in rowdicts: print(row)
{‘pay’: 70000, ‘job’: ‘mus’, ‘name’: ‘Sue’}
{‘pay’: 65000, ‘job’: ‘mus’, ‘name’: ‘Ann’}
{‘pay’: 65000, ‘job’: ‘adm’, ‘name’: ‘Kim’}
Однако поскольку это Python, существуют более мощные способы конструирования словарей записей. Например, конструктор словарей принимает объединенную последовательность пар имя/значение и на ее основе создает словарь:
> >> curs.execute(‘select * from people’)
> >> colnames = [desc[0] for desc in curs.description]
> >> rowdicts = []
> >> for row in curs.fetchall():
… rowdicts.append( dict(zip(colnames, row)) )
> >> rowdicts[0]
{‘pay’: 70000, ‘job’: ‘mus’, ‘name’: ‘Sue’}
И, наконец, можно использовать генератор списков для объединения словарей в список — получившийся программный код не только компактнее, но и, возможно, выполняется быстрее, чем исходная версия:
> >> curs.execute(‘select * from people’)
> >> colnames = [desc[0] for desc in curs.description]
> >> rowdicts = [dict(zip(colnames, row)) for row in curs.fetchall()]
> >> rowdicts[0]
{‘pay’: 70000, ‘job’: ‘mus’, ‘name’: ‘Sue’}
При переходе к словарям мы потеряли порядок следования полей в записях — если вернуться назад, к результатам, полученным с помощью fetchall, можно заметить, что поля name, job и pay записей в результатах следуют в том же порядке, в каком они были определены при создании таблицы. Поля в нашем словаре следуют в псевдослучайном порядке, что вообще характерно для отображений Python. Пока поля извлекаются по ключу, в этом нет никаких проблем. Таблицы по-прежнему поддерживают свой порядок следования полей и операция создания словарей выполняется безукоризненно — благодаря тому, что кортежи с описанием полей в результате следуют в том же порядке, что и поля в кортежах записей, возвращаемых запросами.
Мы оставим задачу преобразования кортежей записей в экземпляры классов в качестве самостоятельного упражнения, однако я дам две подсказки: модуль collections из стандартной библиотеки Python реализует такие необычные типы данных, как именованные кортежи и упорядоченные словари; и имеется возможность обеспечить возможность обращения к полям как к атрибутам, а не ключам, для чего достаточно просто создать пустой экземпляр класса и присвоить значения его атрибутам с помощью функции Python setattr. Кроме того, классы являются естественным местом размещения наследуемого программного кода, такого как стандартные методы отображения. В действительности, это именно то, что предоставляют нам механизмы ORM, описываемые ниже.
Использованная литература:
Марк Лутц — Программирование на Python, 4-е издание, II том, 2011
