Выполнение команд SQL#
Для выполнения команд SQL в модуле есть несколько методов:
- execute — метод для выполнения одного выражения SQL
- executemany — метод позволяет выполнить одно выражение SQL для последовательности параметров (или для итератора)
- executescript — метод позволяет выполнить несколько выражений SQL за один раз
Метод execute#
Метод execute позволяет выполнить одну команду SQL.
Сначала надо создать соединение и курсор:
In [1]: import sqlite3 In [2]: connection = sqlite3.connect('sw_inventory.db') In [3]: cursor = connection.cursor()
Создание таблицы switch с помощью метода execute:
In [4]: cursor.execute("create table switch (mac text not NULL primary key, hostname text, model text, location text)") Out[4]: sqlite3.Cursor at 0x1085be880>
Выражения SQL могут быть параметризированы — вместо данных можно подставлять специальные значения. За счет этого можно использовать одну и ту же команду SQL для передачи разных данных.
Например, таблицу switch нужно заполнить данными из списка data:
In [5]: data = [ . : ('0000.AAAA.CCCC', 'sw1', 'Cisco 3750', 'London, Green Str'), . : ('0000.BBBB.CCCC', 'sw2', 'Cisco 3780', 'London, Green Str'), . : ('0000.AAAA.DDDD', 'sw3', 'Cisco 2960', 'London, Green Str'), . : ('0011.AAAA.CCCC', 'sw4', 'Cisco 3750', 'London, Green Str')]
Для этого можно использовать запрос вида:
In [6]: query = "INSERT into switch values (?, ?, ?, ?)"
Знаки вопроса в команде используются для подстановки данных, которые будут передаваться методу execute.
Теперь можно передать данные таким образом:
In [7]: for row in data: . : cursor.execute(query, row) . :
Второй аргумент, который передается методу execute, должен быть кортежем. Если нужно передать кортеж с одним элементом, используется запись (value, ) .
Чтобы изменения были применены, нужно выполнить commit (обратите внимание, что метод commit вызывается у соединения):
Теперь при запросе из командной строки sqlite3, можно увидеть эти строки в таблице switch:
$ litecli sw_inventory.db Version: 1.0.0 Mail: https://groups.google.com/forum/#!forum/litecli-users Github: https://github.com/dbcli/litecli sw_inventory.db> SELECT * from switch; +----------------+----------+------------+-------------------+ | mac | hostname | model | location | +----------------+----------+------------+-------------------+ | 0000.AAAA.CCCC | sw1 | Cisco 3750 | London, Green Str | | 0000.BBBB.CCCC | sw2 | Cisco 3780 | London, Green Str | | 0000.AAAA.DDDD | sw3 | Cisco 2960 | London, Green Str | | 0011.AAAA.CCCC | sw4 | Cisco 3750 | London, Green Str | +----------------+----------+------------+-------------------+ 4 rows in set Time: 0.039s sw_inventory.db>
Метод executemany#
Метод executemany позволяет выполнить одну команду SQL для последовательности параметров (или для итератора).
С помощью метода executemany в таблицу switch можно добавить аналогичный список данных одной командой.
Например, в таблицу switch надо добавить данные из списка data2:
In [9]: data2 = [ . : ('0000.1111.0001', 'sw5', 'Cisco 3750', 'London, Green Str'), . : ('0000.1111.0002', 'sw6', 'Cisco 3750', 'London, Green Str'), . : ('0000.1111.0003', 'sw7', 'Cisco 3750', 'London, Green Str'), . : ('0000.1111.0004', 'sw8', 'Cisco 3750', 'London, Green Str')]
Для этого нужно использовать аналогичный запрос вида:
In [10]: query = "INSERT into switch values (?, ?, ?, ?)"
Теперь можно передать данные методу executemany:
In [11]: cursor.executemany(query, data2) Out[11]: sqlite3.Cursor at 0x10ee5e810> In [12]: connection.commit()
После выполнения commit данные доступны в таблице:
$ litecli sw_inventory.db Version: 1.0.0 Mail: https://groups.google.com/forum/#!forum/litecli-users Github: https://github.com/dbcli/litecli sw_inventory.db> SELECT * from switch; +----------------+----------+------------+-------------------+ | mac | hostname | model | location | +----------------+----------+------------+-------------------+ | 0000.AAAA.CCCC | sw1 | Cisco 3750 | London, Green Str | | 0000.BBBB.CCCC | sw2 | Cisco 3780 | London, Green Str | | 0000.AAAA.DDDD | sw3 | Cisco 2960 | London, Green Str | | 0011.AAAA.CCCC | sw4 | Cisco 3750 | London, Green Str | | 0000.1111.0001 | sw5 | Cisco 3750 | London, Green Str | | 0000.1111.0002 | sw6 | Cisco 3750 | London, Green Str | | 0000.1111.0003 | sw7 | Cisco 3750 | London, Green Str | | 0000.1111.0004 | sw8 | Cisco 3750 | London, Green Str | +----------------+----------+------------+-------------------+ 8 rows in set Time: 0.034s
Метод executemany подставил соответствующие кортежи в команду SQL, и все данные добавились в таблицу.
Метод executescript#
Метод executescript позволяет выполнить несколько выражений SQL за один раз.
Особенно удобно использовать этот метод при создании таблиц:
In [13]: connection = sqlite3.connect('new_db.db') In [14]: cursor = connection.cursor() In [15]: cursor.executescript(''' . create table switches( . hostname text not NULL primary key, . location text . ); . . create table dhcp( . mac text not NULL primary key, . ip text, . vlan text, . interface text, . switch text not null references switches(hostname) . ); . ''') Out[15]: sqlite3.Cursor at 0x10efd67a0>
Методы execute, executemany, executescript, commit, rollback
На предыдущих занятиях мы с вами рассмотрели основы языка SQL для взаимодействия с СУБД SQLite. Теперь поговорим о методах пакета sqlite3, то есть, об API данной СУБД. Некоторые моменты мы уже затрагивали и отмечали, что для базовой работы с БД можно использовать менеджер контекста:
import sqlite3 as sq with sq.connect("cars.db") as con: cur = con.cursor() cur.execute("""CREATE TABLE IF NOT EXISTS cars ( car_id INTEGER PRIMARY KEY AUTOINCREMENT, model TEXT, price INTEGER )""")
- con.commit() – применение всех изменений в таблицах БД;
- con.close() – закрытие соединения с БД.
Методы execute, executemany и executescript
cur.execute("INSERT INTO cars VALUES(1,'Audi',52642)") cur.execute("INSERT INTO cars VALUES(2,'Mercedes',57127)") cur.execute("INSERT INTO cars VALUES(3,'Skoda',9000)") cur.execute("INSERT INTO cars VALUES(4,'Volvo',29000)") cur.execute("INSERT INTO cars VALUES(5,'Bentley',350000)")
В результате, таблица будет содержать данные: Однако, когда мы программируем на Python, то данные, как правило, хранятся в каких-либо коллекциях, например, так:
cars = [ ('Audi', 52642), ('Mercedes', 57127), ('Skoda', 9000), ('Volvo', 29000), ('Bentley', 350000) ]
И мы бы хотели брать значения из этого списка и передавать их в SQL-запрос. Для этого запрос следует записывать в виде следующего шаблона:
cur.execute("INSERT INTO cars VALUES(NULL, ?, ?)", cars[0])
Здесь вместо знаков вопроса будут подставлены соответствующие данные из первого кортежа списка. Соответственно, весь набор ранее приведенных строчек, можно заменить циклом:
for car in cars: cur.execute("INSERT INTO cars VALUES(NULL, ?, ?)", car)
cur.executemany("INSERT INTO cars VALUES(NULL, ?, ?)", cars)
Сразу же здесь отмечу, что помимо знаков вопроса можно использовать именованные параметры (плейсхолдеры). Для этого в запросе перед ними ставится двоеточие, а затем, указывается словарь, где имя – это ключ, вместо которого будет подставлено его значение:
cur.execute("UPDATE cars SET price = :Price WHERE model LIKE 'A%'", {'Price': 0})
Далее, если нужно выполнить несколько отдельных SQL-команд, то можно передать их СУБД с помощью метода executescript:
cur.executescript("""DELETE FROM cars WHERE model LIKE 'A%'; UPDATE cars SET price = price+1000 """)
Мы здесь сначала удаляем все записи, у которых модель начинается на букву A, а затем у оставшихся записей увеличиваем цену на 1000. Причем, команды должны отделяться друг от друга точкой с запятой. У этого метода есть одно ограничение: здесь нельзя использовать шаблоны запросов, как мы это делали в предыдущих методах. В executescript буквально записываются SQL-запросы как есть со всеми данными.
Методы commit и rollback
con = None try: con = sq.connect("cars.db") cur = con.cursor() cur.executescript("""CREATE TABLE IF NOT EXISTS cars ( car_id INTEGER PRIMARY KEY AUTOINCREMENT, model TEXT, price INTEGER ); BEGIN; INSERT INTO cars VALUES(NULL,'Audi',52642); INSERT INTO cars VALUES(NULL,'Mercedes',57127); INSERT INTO cars VALUES(NULL,'Skoda',9000); INSERT INTO cars VALUES(NULL,'Volvo',29000); INSERT INTO cars VALUES(NULL,'Bentley',350000); UPDATE cars SET price = price+1000 """) con.commit() except sq.Error as e: if con: con.rollback() print("Ошибка выполнения запроса") finally: if con: con.close()
В чем преимущество такого подхода? Смотрите, мы здесь сами «вручную» вызываем методы commit и close. Если операции с таблицами прошли успешно, то они будут сохранены, если же возникли какие-либо ошибки (исключения), то будет вызван метод rollback, который откатывает состояние БД в состояние отметки BEGIN, то есть, все внесенные изменения применены не будут. Например, укажем в команде UDPATE неверное имя таблицы:
UPDATE cars2 SET price = price+1000
При запуске программы произойдет ошибка выполнения запроса и состояние БД не изменится, то есть, все новые добавленные записи не появятся в таблице cars. А вот если вместо rollback указать commit, то увидим добавление записей. То есть, при использовании менеджера контекста в данном случае не выполнилась бы только последняя команда, но отката состояния БД не произошло бы. Вот так более тонко можно управлять состоянием таблиц в БД. Если при работе с БД предполагается сохранять вносимые изменения сразу после выполнения SQL-запроса, то это можно сделать с помощью метода connect, установив в нем параметр isolation_level=None:
with sq.connect("cars.db", isolation_level=None) as con: cur = con.cursor() cur.executescript("""INSERT INTO cars VALUES(NULL,'Audi',52642); INSERT INTO cars VALUES(NULL,'Mercedes',57127); INSERT INTO cars VALUES(NULL,'Skoda',9000); INSERT INTO cars VALUES(NULL,'Volvo',29000); INSERT INTO cars VALUES(NULL,'Bentley',350000); """)
Однако так делать без особой надобности не стоит, т.к. это уменьшает скорость работы с БД из-за постоянной записи данных непосредственно в файл. Без изменения этого параметра все изменения сохраняются в памяти, а потому работа происходит куда быстрее.
Свойство lastrowid
Давайте теперь представим, что у нас есть еще одна таблица cust, которая содержит покупателей машин. Причем, если происходит покупка по «trade-in», то прежняя машина владельца добавляется в конец таблицы cars, а в таблице cust появляется запись с именем покупателя, идентификатором машины сданной в «trade-in» и id новой купленной машины: Чтобы реализовать SQL-запрос добавления записи в таблицу cust, нам нужно знать car_id автомобиля сданного в «trade-in». Предположим, что Федор еще не совершил покупку и таблица cars не содержит запись с его сданным автомобилем. Добавим ее. Выполним следующий запрос вот в такой программе:
with sq.connect("cars.db") as con: cur = con.cursor() cur.executescript("""CREATE TABLE IF NOT EXISTS cars ( car_id INTEGER PRIMARY KEY AUTOINCREMENT, model TEXT, price INTEGER ); CREATE TABLE IF NOT EXISTS cust(name TEXT, tr_in INTEGER, buy INTEGER); """) cur.execute("INSERT INTO cars VALUES(NULL,'Запорожец', 1000)")
Мы здесь создаем еще одну таблицу cust с тремя полями и, затем, добавляем в таблицу cars автомобиль «Запорожец», который сдает покупатель Федор. Как теперь нам узнать car_id этой записи? Для этого можно воспользоваться специальным свойством:
last_row_id = cur.lastrowid
которое содержит значение rowid последней добавленной записи. В нашем случае поля car_id и rowid будут совпадать, поэтому воспользуемся этим значением и сформируем еще один запрос на добавление записи во вторую таблицу:
buy_car_id = 2 cur.execute("INSERT INTO cust VALUES('Федор', ?, ?)", (last_row_id, buy_car_id))
Теперь, при выполнении нашей программы в таблице cust увидим искомую запись. Вот так используется свойство lastrowid. На этом завершим это занятие. На следующем продолжим рассматривать функционал API для работы с СУБД SQLite.