|
Методы 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 )""") Когда контекстный менеджер завершает свою работу, он автоматически выполняет два метода:
Это необходимые действия для сохранения внесенных изменений в БД. Методы execute, executemany и executescriptДавайте добавим в таблицу cars несколько записей. В самом простом случае это можно сделать так: 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) Или, еще проще, воспользоваться методом executemany, который специально для этого и существует: 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Давайте теперь реализуем соединение с БД через блок обработки исключений try/except/finally: 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. Видео по теме |
