Базы данных и работа с ними заслуживают отдельного раздела, здесь же мы рассмотрим в чем заключается работа с SqLite3 в Python.
Установка SqLite3
Итак, SqLite3 в Python не является стандартным модулем и требует дополнительной установки. Что бы проверить, установлен ли у вас модуль SqLite3 пишем в терминале:
sqlite3
Исли вы получите сообщение, что эта команда не определена, то ее надо установить, на Macbook она предустановлена, для Windows – устанавливаем!
Для этого:
- Переходим на официальный сайт sqlite.org
- Находим раздел Precompiled Binaries for Windows
- Если у вас 32-х битная система, то скачиваем 1й и 3й архив
- Если у вас 64-х битная система, то скачиваем 1й и 2й арсив
- После чего оба архива нужно распаковать, не имеет значения на какой диск, предварительно создав дирректорию для помещения в нее файлов архива. Для примера, я это сделаю в корневую дирректори диска С и назову sqlite
- Нужно добавить пути: переходим Этот компьютер – Свойства – Дополнительные параметры системы – Переменные среды – Path – Изменить – Создать и прописываем c:\sqlite, нажимаем ОК (закрываем ранее открытые окна)
Теперь, когда вы откроете командную строку и пропишите команду для проверки, у вас будет выведено сообщение с версией SqLite.
Создание таблицы и полей в таблице в SqLite3
Объявим объект подключения, для чего воспользуемся функцией connect(), в которую передадим строку с путем к БД. Не менее важен и другой параметр, timeout, отвечающий за длительность подключения к базе данных. Если за отведенное время оно не установится, будет сгенерировано соответствующее исключение, а программа продолжит свою работу или разобьется о консоль. Сделаем этот параметр равным пяти секундам, передав вызову соответствующее значение:
connection = sqlite3.connect("Student_DB.db", 5)
Теперь создадим курсор – общепринятое название для объекта, который непосредственно работает с подключением, передавая и обрабатывая SQL-запросы и сохраняя в себе результат их выполнения. Сделаем это с помощью метода cursor() объекта подключения:
cur = connection.cursor()
Добавим строки с выводом на экран объявленных элементов. Последней запишем команду для закрытия подключения с помощью метода close(). Если этого не сделать, подключение останется открытым даже после завершения выполнения программы, что впоследствии может привести к невозможности нового соединения, поскольку все возможные линии связи будут заняты:
print(connection) print(cur) connection.close()
Подключение выполнено, но наша база пока пуста. Начнем ее наполнение, создав первую таблицу. Для этого в метод execute() курсора передадим строку с SQL-запросом “CREATE TABLE first_table (name TEXT);”. Теперь вызовем метод commit() объекта подключения. Он нужен для закрытия транзакции – специального способа одновременной передачи большого количества изменений, что позволяет избежать проблем с целостностью данных:
import sqlite3
connection = sqlite3.connect("Student_DB.db", 5)
cur = connection.cursor()
cur.execute("CREATE TABLE first_table (name TEXT);")
connection.commit()
connection.close()
Если же вам необходимо создать несколько полей, не только name, а допустим еще и lastName, age, phone, то срока будет выглядеть вот так:
cur.execute("CREATE TABLE first_table (name TEXT, lastName TEXT, age INTEGER, phone TEXT);")
На самом деле, вам может показать что работа с SqLite3 в Python сложная из-за того, что мало визуализации, но на самом деле это можно исправить с помощью плагина Database Navigator, и мы это сделаем позже, а сейчас давайте заполним таблицу данными, для этого пропишем вот такую команду:
cur.execute("INSERT INTO first_table (name) VALUES ('Nick');")
Т.е. в таблицу first_table, поле name установим значение “Nick”
Ну, и если вам необходимо вносить данные не в одно поле, как в моем примере выше, а сразу в несколько полей, то делается это вот так:
cur.execute("INSERT INTO first_table (name, lastName, age, phone) VALUES ('Nick', 'Petrov', 28, '+380987856321');")
INSERT INTO – команда о внесении данных;
first_table – в какую именно таблицу вносим;
(name, lastName, age, phone) – в какие поля таблицы вносим данные;
(‘Nick’, ‘Petrov’, 28, ‘+380987856321’) – сами данные.
Очень интересный момент, что эту строку внесения данных можно записать в переменную и ее передать в метод execut:
data_user = "INSERT INTO first_table (name, lastName, age, phone) VALUES ('Nick', 'Petrov', 28, '+380987856321');"
cur.execute(data_user)
Но здесь мы прописываем данные прямо в запросе, что не очень удобно! Очень часто, почти всегда, мы данные получаем откуда-то, либо нам их передают по сети, и здесь очень важно и правильно было бы данные в запрос подставлять в виде переменных! И здесь нам могут помочь так называемы f-строки, но сразу оговоримся, это не очень безопасный способ:
name = 'Nick'
lastName = 'Petrov'
age = 28
phone = '+380987856321'
request_data = f"INSERT INTO first_table (name, lastName, age, phone) VALUES ('{name}', '{lastName}', {age}, '{phone}');"
cur.execute(request_data)
Но, как уже сказано было выше, это не безопасный способ, приведен в качестве объяснения, что вместо прямой передачи переменных можно использовать подстановочные символы, а именно “?”.
Для этого в методе execute необходимо передать вторым параметром кортеж с нашими переменными, а в запросе просто поставить подстановочные символы, т.е. “?”:
name = 'Nick' lastName = 'Petrov' age = 28 phone = '+380987856321' request_data = "INSERT INTO first_table (name, lastName, age, phone) VALUES (?, ?, ?, ?);" cur.execute(request_data, (name, lastName, age, phone))
Или же мы заранее создадим кортеж с нашими данными, а потом передадим просто его:
name = 'Nick' lastName = 'Petrov' age = 28 phone = '+380987856321' data = (name, lastName, age, phone) request_data = f"INSERT INTO first_table (name, lastName, age, phone) VALUES (?, ?, ?, ?);" cur.execute(request_data, data)
И вот здесь и начинается самое интересное, и появляется вся работа с SqLite3 в Python. Понятное дело, что кортежи не изменяемые типы данных, их и не только их мы описали в статье Типы данных языка программирования Python, и их мы не можем динамечески создавать, но мы можем создавать списки, добавлять и корректировать в них данные, или словари, или даже получать по API данные в виде json, извлекать с него ту информацию, которая нам нужна, преобразовывать в тип данных tuple и уже его передавать в метод execute для записи в базу данных.
Простой пример: мы получаем данные с сервера, создаем из них кортежи, далее эти кортежи добавляем в список как отдельные элементы, т.е. будем иметь список кортежей, и потом в цикле перебираем все элементы этого списка и отправляем запросы на добавление в БД. Код будет иметь примерно такой вид:
data_lst = [('Nick', 'Petrov', 28, '+380987856321'),
('Ivan', 'Ivanov', 25, '+380669632541'),
('Anna', 'Urkova', 18, '+380875632147')]
request_data = f"INSERT INTO first_table (name, lastName, age, phone) VALUES (?, ?, ?, ?);"
for data in data_lst:
cur.execute(request_data, data)
Но есть и второй вариант, это использовать метод executemany, и кроме запроса передать в него список кортежей, таким образом цикл нам не нужен, этот метод их сам перебирает:
data_lst = [('Nick', 'Petrov', 28, '+380987856321'),
('Ivan', 'Ivanov', 25, '+380669632541'),
('Anna', 'Urkova', 18, '+380875632147')]
request_data = f"INSERT INTO first_table (name, lastName, age, phone) VALUES (?, ?, ?, ?);"
cur.executemany(request_data, data_lst)
Чтение данных с БД при помощи Python
Для получения данных с БД необходимо использовать запрос SELECT, помещая данные в объект курсора, и в дальнейшем обращаться с ним как с итерируемым объектом, т.е. перебирать в цикле:
cur.execute("SELECT * FROM first_table;")
for row in cur:
print(row)
Там образом мы распечатаем каждую строку из нашей БД. Но в большинстве случаев нам не надо вся таблица, а данные выборочные! Для этого пропишем условие с помощью ключевого слова WHERE:
cur.execute("SELECT * FROM first_table WHERE name IS 'Nick';")
for row in cur:
print(row)
И получаем данные только с тех строк, у которых в поле name есть значение “Nick”. Если же нам нужны какие-то конкретные поля, то мы можем написать запрос так:
cur.execute("SELECT name, age FROM first_table;")
И получим только имена и возраст с нашей таблицы.
Для получения отдельных строк необходимо использовать метод fetchone()
cur.execute("SELECT * FROM first_table WHERE name IS 'Nick';")
print(cur.fetchone())
И выводим на консоль первую строку с таблицы, где name = “Nick”
Что бы получить все строки можно использовать метод fetchall()
cur.execute("SELECT * FROM first_table WHERE name IS 'Nick';")
print(cur.fetchall())
И получим все строки, где name = “Nick”.
Обновление данных с БД при помощи Python
Для обновления данных используется ключевое слово UPDATE, и далее указываем что именно обновить с помощью ключевого слова SET и условие, т.е. в какой именно строке:
cur.execute("UPDATE first_table SET age = 26 WHERE name IS 'Nick';")
Ну, и что бы посмотреть данные в таблице, необходимо сделать выборку с помощь SELECT, и либо циклом пробежаться по курсору, либо применить метод fetchall()
cur.execute("SELECT * FROM first_table")
date = cur.fetchall() #Возвращает список кортежей
[print(row) for row in data] # перебираем список и выводим в консоль
И после UPDATE мы увидим, что значение age изменилось на 26.
Если нам необходимо, допустим, изменить номер телефона поля с name = ‘Anna’, то для этого пишем:
cur.execute("UPDATE first_table SET phone = '+78070000000' WHERE name IS 'Anna';")
Удаление данных с БД SqLite3
Для удаления используется ключевое слово DELETE, и если нам надо, допустим удалить запись с name = “Ivan”, то запрос будет выглядеть вот так:
cur.execute("DELETE FROM first_table WHERE name IS 'Ivan';")
Если выполнить запрос на просмотр, то это поле будет уже пустым.
В этой стать мы пытались показать, что работа с SqLite3 в Python это просто и легко, не смотря на отсутствие визуализации сохранения данных, но о плагине DB Navigator мы все таки вспоминали. Если вам интересно, как работать с ним, посмотрите статью DB Navigator Pycharm.
