Подключение к БД, создание и удаление таблиц
Для добавления возможности использования СУБД SQLite в программе на Python необходимо импортировать модуль:
import sqlite3
После чего станут доступны API-функции этого расширения. Подробнее о них мы будем говорить на последующих занятиях. А вначале воспользуемся вот такой простой заготовкой для создания и управления БД:
import sqlite3 as sq con = sq.connect("saper.db") cur = con.cursor() cur.execute(""" """) con.close()
Смотрите, в первую очередь мы должны вызвать метод connect, чтобы установить связь с определенной БД. В данном случае – это файл saper.db, который должен располагаться в том же каталоге, что и файл программы на Питоне. В качестве расширений этого файла, обычно, используют следующие:
*.db, *.db3, *.sqlite и *.sqlite3
Я взял первый вариант, т.к. он короткий и понятный. При выполнении команды connect файл saper.db либо будет открыт, либо будет создан, если он не существует. В результате создается (или открывается) БД с именем saper.db.
При успешном соединении с БД метод connect возвращает экземпляр объекта Connection, на который ссылается переменная con. И, далее, мы должны использовать объект Cursor для взаимодействия с БД и выполнения SQL-запросов. Например, это можно сделать с помощью метода execute, которому в качестве аргумента как раз и передается строка с SQL-запросом. Но мы пока там ничего указывать не будем. В конце программы при завершении работы с БД необходимо закрыть соединение. Это делается с помощью метода close.
Вот так в двух словах происходит взаимодействие с СУБД SQLite. Запустим программу, видим, что никаких ошибок не возникло и, кроме того, была создана БД saper.db в виде отдельного файла в каталоге с исполняемым файлом Питона.
Однако, соединяться с БД лучше все-таки через менеджер контекста:
with sq.connect("saper.db") as con: cur = con.cursor() cur.execute(""" """)
Он автоматически сохраняет данные в БД (вызывает метод commit()) даже при возникновении ошибочных ситуаций. Поэтому, в дальнейшем мы им и будем пользоваться. Конечно, после него, когда соединение с БД уже не нужно, его нужно закрыть все той же командой close().
Давайте теперь перейдем в программу DB Browser и откроем эту БД. На экране увидим вот такое окно и, как видите, у нас пока здесь нет ни одной таблицы.
Создание и удаление таблиц
- name – строка с именем игрока;
- sex – число, пол игрока (1 – мужской; 2 – женский);
- old – число, возраст игрока;
- score – суммарное число набранных очков за все игры.
- NULL – значение NULL;
- INTEGER – целочисленный тип (занимает от 1 до 8 байт);
- REAL – вещественный тип (8 байт в формате IEEE);
- TEXT – строковый тип (в кодировке данных базы, обычно UTF-8);
- BLOB (двоичные данные, хранятся «как есть», например, для небольших изображений).
cur.execute("""CREATE TABLE users ( name TEXT, sex INTEGER, old INTEGER, score INTEGER )""")
Мы здесь указываем команду CREATE TABLE (создать таблицу), затем, имя таблицы – users и, далее, в круглых скобках через запятую перечисляем поля с указанием их типов. Названия команд и типов принято писать заглавными буквами, а имена таблиц и их полей – строчными. Мы будем придерживаться этого негласного правила. Запустим программу, снова откроем БД в DB Browser и увидим созданную таблицу заданной структуры. Это будет наше первое хранилище данных. Однако, смотрите, если запустить программу еще раз, то появится ошибка, т.к. мы пытаемся создать таблицу, которая уже существует. Поэтому SQL-запрос лучше записать в таком виде:
cur.execute("""CREATE TABLE IF NOT EXISTS users ( name TEXT, sex INTEGER, old INTEGER, score INTEGER )""")
То есть, создавать таблицу только если она не существует. Теперь, запуская программу, никаких ошибок появляться не будет и, кроме того, мы точно будем уверены, что таблица users присутствует в нашей БД. Давайте в программе DB Browser добавим несколько записей. Для этого нужно нажать на кнопку «Добавить запись» и ввести значения: Алексей 1 22 1000
Миша 1 19 800
Федор 1 26 1100
Маша 2 18 1500 После этого обязательно нажать на кнопку «Записать изменения» и данные будут сохранены в БД. Затем, в этой же программе на вкладке «SQL», мы можем прописывать SQL-запросы и выполнять их. В качестве примера выполним очень распространенную команду SELECT для выбора записей из таблицы users. В самом простом варианте это можно сделать так:
SELECT * FROM users
Здесь * указывает взять все поля из таблицы users. Подробнее мы еще поговорим об этом запросе, а пока я покажу следующее. Каждая таблица SQLite содержит скрытое поле rowid, хранящее уникальный идентификатор записи. Выведем его на экран с помощью запроса:
SELECT rowid, * FROM users
Теперь мы видим значения этого поля для каждой записи. Используя rowid, в частности, можно осуществлять связывание нескольких таблиц между собой для формирования сводного отчета. И в дальнейшем мы увидим как это делается. Наконец, если требуется удалить таблицу, то для этого прописывается такой SQL-запрос:
cur.execute("DROP TABLE users")
Как видите, все довольно просто.
PRIMARY KEY, AUTOINCREMENT, NOT NULL и DEFAULT
При необходимости можно самостоятельно создать поле подобное rowid в любой таблице. Для этого, при создании ее структуры, указывается следующая строчка: user_id INTEGER PRIMARY KEY AUTOINCREMENT Здесь ограничитель PRIMARY KEY (первичный ключ) означает, что поле user_id должно содержать уникальные значения, а ограничитель AUTOINCREMENT указывает СУБД автоматически увеличивать значение user_id при добавлении новой записи. Удалим таблицу users из БД, выполним программу и увидим, что автоматически создается еще одна вспомогательная таблица sqlite_sequence, которая будет хранить служебную информацию для поля user_id. Но, в нашей работе она не нужна, мы по-прежнему пользуемся только одной таблицей users. Далее, если нам нужно указать, что поле обязательно должно содержать какие-либо данные, то ему следует добавить ограничитель NOT NULL, а для задания значения по умолчанию – ограничитель DEFAULT, например, так:
cur.execute("""CREATE TABLE IF NOT EXISTS users ( name TEXT NOT NULL, sex INTEGER DEFAULT 1, old INTEGER, score INTEGER )""")
Теперь, при добавлении новой записи, обязательно нужно будет указывать имя игрока, но можно не указывать его пол, если предполагается мужской:
INSERT INTO users (name, old, score) VALUES('Алексей', 18, 1000)
В результате получим запись: Алексей 1 18 1000 Часто ограничители NOT NULL и DEFAULT объединяют между собой и пишут так:
sex INTEGER NOT NULL DEFAULT 1,
Тогда у нас поле sex обязательно будет содержать значение и по умолчанию оно будет равно 1. Вот так происходит обращение к БД и создание таблиц в SQLite. На следующем занятии мы продолжим изучение команд языка SQL.
Удаление записей из таблицы SQLite
Этот материал посвящен выполнению SQL-операции DELETE для SQLite-таблицы из Python-приложения.
В этой статье мы рассмотрим:
- Удаление одной, нескольких или всех строк или колонок из SQLite-таблицы с помощью Python;
- Использование запроса с параметрами для выполнения операции удаления из SQLite;
- Коммит и отмена последней операции;
- Массовое удаление в один запрос.
Подготовка
Перед выполнением следующих операций нужно знать название таблицы SQLite, а также ее колонок. В этом материале будет использоваться таблица sqlitedb_developers .
Пример удаления одной строки из SQLite-таблицы
Сейчас таблица sqlitedb_developers содержит шесть строк, а удалять будем разработчика, чей id равен 6. Вот что для этого нужно сделать:
- Присоединиться к SQLite из Python;
- Создать объект Cursor с помощью полученного в прошлом шаге объекта соединения SQLite;
- Создать DELETE-запрос для SQLite. Именно на этом этапе нужно знать названия таблицы и колонок;
- Выполнить DELETE-запрос с помощью cursor.execute() ;
- После выполнения запроса необходимо закоммитить изменения в базу данных;
- Закрыть соединение с базой данных;
- Также важно не забыть перехватить исключения, которые могут возникнуть;
- Наконец, проверить результат операции.
import sqlite3
def delete_record():
try:
sqlite_connection = sqlite3.connect('sqlite_python.db')
cursor = sqlite_connection.cursor()
print("Подключен к SQLite")
sql_delete_query = """DELETE from sqlitedb_developers where /> cursor.execute(sql_delete_query)
sqlite_connection.commit()
print("Запись успешно удалена")
cursor.close()
except sqlite3.Error as error:
print("Ошибка при работе с SQLite", error)
finally:
if sqlite_connection:
sqlite_connection.close()
print("Соединение с SQLite закрыто")
delete_record()Вывод: таблица sqlitedb_developers после удаления строки из Python.
Подключен к SQLite Запись успешно удалена Соединение с SQLite закрытоРазбор примера в подробностях
- На этой строке модуль sqlite3 импортируется в программу;
- С помощью классов и методов sqlite3 можно взаимодействовать с базой данных SQLite.
sqlite3.connect() и connection.cursor() :
- С помощью sqlite3.connect() устанавливается соединение с базой данных SQLite из Python;
- Дальше используется sqliteConnection.cursor() для получения объекта Cursor .
После этого создается DELETE-запрос для удаления шестой строки в таблице (для разработчика с id равным 6). В запросе этот разработчик упоминается.
- Выполняется операция из DELETE-запроса с помощью метода execute() объекта Cursor ;
- После успешного удаления записи изменения коммитятся в базу данных с помощью connection.commit() .
Наконец, закрываются Cursor и SQLite-соединение в блоке finally .
Примечание: если выполняется несколько операций удаления, и есть необходимость отменить все изменения в случае неудачи хотя бы с одной из них, нужно использовать функцию rollback() класса соединения для отмены. Эту функцию стоит применять внутри блока except .
Использование переменных в запросах для удаления строки
В большинстве случаев удалять строки из таблицы SQLite нужно с помощью ключа, который передается уже во время работы программы. Например, когда пользователь удаляет свою подписку, запись о нем нужно удалить из таблицы.
В таких случаях требуется использовать запрос с параметрами. В таких запросах на месте будущих значений ставятся заполнители ( ? ). Это помогает удалять записи, получая значения во время работы программы, и избегать проблем SQL-инъекций. Вот пример с удалением разработчика с id=5 .
import sqlite3
def delete_sqlite_record(dev_id):
try:
sqlite_connection = sqlite3.connect('sqlite_python.db')
cursor = sqlite_connection.cursor()
print("Подключен к SQLite")
sql_update_query = """DELETE from sqlitedb_developers where /> cursor.execute(sql_update_query, (dev_id, ))
sqlite_connection.commit()
print("Запись успешно удалена")
cursor.close()
except sqlite3.Error as error:
print("Ошибка при работе с SQLite", error)
finally:
if sqlite_connection:
sqlite_connection.close()
print("Соединение с SQLite закрыто")
delete_sqlite_record(5)Вывод: таблица sqlitedb_developers после удаления строки с помощью запроса с параметрами.
Подключен к SQLite Запись успешно удалена Соединение с SQLite закрытоРазберем последний пример:
- Запрос с параметрами использовался, чтобы получить id разработчика во время работы программы и подставить его на место ? . Он определяет id записи, которая будет удалена.
- После этого создается кортеж данных с помощью переменных Python.
- Дальше DELETE-запрос вместе с данными передается в метод cursor.execute() .
- Наконец, изменения сохраняются в базе данных с помощью метода commit() класса Connection .
Операция Delete для удаления нескольких строк
В примере выше был использован метод execute() объекта Cursor для удаления одной записи, но часто приходится удалять сразу несколько одновременно.
Вместо выполнения запроса DELETE каждый раз для каждой записи, можно выполнить операцию массового удаления в одном запросе. Удалить несколько записей из SQLite-таблицы в один запрос можно с помощью метода cursor.executemany() .
Метод cursor.executemany(query, seq_param) принимает два аргумента: SQL-запрос и список записей для удаления.
Посмотрим на следующий пример. В нем удаляются сразу три разработчика.
import sqlite3
def delete_multiple_records(ids_list):
try:
sqlite_connection = sqlite3.connect('sqlite_python.db')
cursor = sqlite_connection.cursor()
print("Подключен к SQLite")
sqlite_update_query = """DELETE from sqlitedb_developers where /> cursor.executemany(sqlite_update_query, ids_list)
sqlite_connection.commit()
print("Удалено записей:", cursor.rowcount)
sqlite_connection.commit()
cursor.close()
except sqlite3.Error as error:
print("Ошибка при работе с SQLite", error)
finally:
if sqlite_connection:
sqlite_connection.close()
print("Соединение с SQLite закрыто")
ids_to_delete = [(4,),(3,)]
delete_multiple_records(ids_to_delete)Подключен к SQLite далено записей: 2 Соединение с SQLite закрытоРазберем последний пример:
- После соединения с базой данных SQLite готовится SQL-запрос с параметрами и одним заполнителем. Вместе с ним также передается список id в формате кортежа.
- Каждый элемент списка — это всего лишь кортеж каждой строки. Каждый кортеж содержит id разработчика. В этом примере три кортежа — то есть, три разработчика.
- Дальше вызывается cursor.executemany(sqlite_delete_query, ids_list) для удаления нескольких записей из таблицы. И запрос, и список id передаются cursor.executemany() в качестве аргументов.
- Чтобы увидеть количество затронутых записей, можно использовать метод cursor.rowcount . Наконец, изменения сохраняются в базу данных с помощью метода commit класса Connection .
Как удалить таблицу sqlite3 python
Команда DELETE удаляет данные из БД. Она имеет следующий формальный синтаксис:
DELETE FROM имя_таблицы [WHERE условие_удаления]
Например, возьмем следующую таблицу products, которая хранит некоторый набор товаров:
CREATE TABLE products ( id INTEGER PRIMARY KEY AUTOINCREMENT, name TEXT NOT NULL, company TEXT NOT NULL, product_count INTEGER DEFAULT 0, price INTEGER ); INSERT INTO products (name, company, product_count, price) VALUES ('iPhone 13', 'Apple', 3, 76000), ('iPhone 12', 'Apple', 2, 51000), ('Galaxy S21', 'Samsung', 2, 56000), ('Galaxy S20', 'Samsung', 1, 41000), ('P40 Pro', 'Huawei', 5, 36000);
Удалим из этой таблицы товары, у которых производитель - Huawei:
DELETE FROM products WHERE company='Huawei';
Или удалим все товары, производителем которых является Apple и которые имеют цену меньше 60000:
DELETE FROM products WHERE company='Apple' AND price < 60000;
Если необходимо вовсе удалить все строки вне зависимости от условия, то условие можно не указывать:
DELETE FROM products;
Как удалить таблицу SQlite?
Данные в БД берутся из JSON(которые меняется постоянно) c сервера. Т.е. БД всегда имеет новую версию при входе в приложение.
15 дек 2015 в 9:50
т.е условие лишнее?
15 дек 2015 в 10:02
3 ответа 3
Сортировка: Сброс на вариант по умолчанию
Уверены ли вы в том, что эта строчка выполняется?
db.execSQL("DROP TABLE IF EXISTS " + TABLE_CONTACT); После выполнения этого запроса таблица должна дропнуться. После у вас выполняется код:
onCreate(db); из которого выполняется
database.execSQL(DATABASE_CREATE); а значит таблица создаётся снова
в метод onUpgrade вы попадаете в случае если версия дб изменилась т.е. DATABASE_VERSION а условие
if (newVersion > oldVersion) Необходимо для уточнения версии (если вы например будете поддерживать приложение в котором будут часто меняться версии бд и информацию из них необходимо будет сохранять.
Попробуйте продебажить и поставьте бреакпоинт на строчке
db.execSQL("DROP TABLE IF EXISTS " + TABLE_CONTACT); Должно получится так - если изменить при том коде который вы предоставили DATABASE_VERSION на 3, то после включения приложения вы получите autowash.db с пустой таблицей autowash.