Работа с базой данных SQLite в Python – примеры
Разберем работу с SQLite в Python и пример создания базы данных . Сначала вам нужно установить Python и SQLite на свой компьютер.
Установка Python
Используйте следующий код:
sudo apt-get update sudo apt-get upgrade python
Нажмите y, и установка будет завершена в течение нескольких секунд.
Установка SQLite
- введите следующую команду:
sudo apt-get install sqlite3 libsqlite3-dev
- По завершению проверьте установку: терминал sqlite должен выдать вам подсказку и информацию о версии.
sqlite3
- Перейдите в нужную папку и создайте базу данных: sqlite3 database.db. Database.db будет создана в папке, которую вы дали команде.
Чтобы проверить, создана ли ваша база данных, используйте следующую команду в терминале sqlite3:
.databases
Примечание. Чтобы подключить SQLite к Python, вам не нужно устанавливать модуль подключения отдельно, поскольку он поставляется по умолчанию вместе с Python версии 2.5.x и выше.
Соединение SQLite с Python
Создайте файл python “connect.py” со следующим кодом:
#!/usr/bin/python import sqlite3 conn = sqlite3.connect('javatpoint.db') print "Opened database successfully";
Выполните следующий оператор в командной строке:
python connect.py
Соединение создается с базой данных javatpoint. Теперь вы можете создать таблицу.
Создание таблицы
Создайте таблицу «Сотрудники» в базе данных «javatpoint».
Создайте файл python createtable.py со следующим кодом:
#!/usr/bin/python import sqlite3 conn = sqlite3.connect('javatpoint.db') print "Opened database successfully"; conn.execute('''CREATE TABLE Employees (ID INT PRIMARY KEY NOT NULL, NAME TEXT NOT NULL, AGE INT NOT NULL, ADDRESS CHAR(50), SALARY REAL);''') print "Table created successfully"; conn.close()
Выполните следующий оператор в командной строке:
python createtable.py
В базе данных javatpoint создается таблица «Сотрудники».
Вставка записей
Вставьте несколько записей в таблицу «Сотрудники».
Создайте файл python “connection.py” со следующим кодом:
#!/usr/bin/python import sqlite3 conn = sqlite3.connect('javatpoint.db') print "Opened database successfully"; conn.execute("INSERT INTO Employees (ID,NAME,AGE,ADDRESS,SALARY) \ VALUES (1, 'Ajeet', 27, 'Delhi', 20000.00 )"); conn.execute("INSERT INTO Employees (ID,NAME,AGE,ADDRESS,SALARY) \ VALUES (2, 'Allen', 22, 'London', 25000.00 )"); conn.execute("INSERT INTO Employees (ID,NAME,AGE,ADDRESS,SALARY) \ VALUES (3, 'Mark', 29, 'CA', 200000.00 )"); conn.execute("INSERT INTO Employees (ID,NAME,AGE,ADDRESS,SALARY) \ VALUES (4, 'Kanchan', 22, 'Ghaziabad ', 65000.00 )"); conn.commit() print "Records inserted successfully"; conn.close()
Выполните следующий оператор в командной строке:
python connection.py
Записи успешно вставлены.
Выбор записи
Теперь вы можете получать и отображать свои записи из таблицы «Сотрудники» с помощью оператора SELECT.
Создайте файл python “select.py” со следующим кодом:
#!/usr/bin/python import sqlite3 conn = sqlite3.connect('javatpoint.db') data = conn.execute("select * from Employees"); for row in data: print "ID = ", row[0] print "NAME = ", row[1] print "ADDRESS = ", row[2] print "SALARY = ", row[3], "\n" conn.close();
Выполните следующий оператор в командной строке:
python select.py
Просмотрите все записи, которые вы вставили ранее. С помощью тех же процедур вы можете обновить и удалить таблицу в базе данных SQLite, используя Python.
Введение в SQLite Python
Цель этого руководства — продемонстрировать принципы разработки приложений на Python с использованием базы данных SQLite.
Подключение к SQLite в Python
В этом разделе разберем, как создавать базу данных SQLite и подключаться к ней в Python с помощью модуля sqlite3.
Для установки соединения нужно указать название базы данных, к которой требуется подключиться. Если указать название той, что уже есть на диске, то произойдет подключение. Если же указать другое, то SQLite создаст новую базу данных.
Для подключения к SQLite нужно выполнить следующие шаги
- Использовать метод connect() из модуля sqlite3 и передать в качестве аргумента название базы данных.
- Создать объект cursor с помощью объекта соединения, который вернул прошлый метод для выполнения SQLite-запросов из Python.
- Закрыть объект cursor после завершения работы.
- Перехватить исключение базы данных, если в процессе подключения произошла ошибка.
Следующая программа создает файл базы данных sqlite_python.db и выводит подробности о версии SQLite.
import sqlite3
try:
sqlite_connection = sqlite3.connect('sqlite_python.db')
cursor = sqlite_connection.cursor()
print("База данных создана и успешно подключена к SQLite")
sqlite_select_query = "select sqlite_version();"
cursor.execute(sqlite_select_query)
record = cursor.fetchall()
print("Версия базы данных SQLite: ", record)
cursor.close()
except sqlite3.Error as error:
print("Ошибка при подключении к sqlite", error)
finally:
if (sqlite_connection):
sqlite_connection.close()
print("Соединение с SQLite закрыто")После подключения должно появиться следующее сообщение.
База данных создана и успешно подключена к SQLite Версия базы данных SQLite: [('3.31.1',)] Соединение с SQLite закрытоПонимание SQLite-подключения в подробностях
- Эта строка импортирует в программу модуль sqlite3. С помощью классов и методов из этого модуля можно взаимодействовать с базой данных SQLite.
- С помощью метода connect() выполняется подключение к базе данных. Этот метод возвращает объект подключения SQLite.
- Объект connection не является потокобезопасным. Модуль sqlite3 не позволяет делиться подключением между потоками. Если попытаться сделать это, то можно получить исключение.
- Метод connect() принимает разные аргументы. В этом примере передается название базы данных.
- С помощью объекта соединения создается объект cursor , который позволяет выполнять SQLite-запросы из Python.
- Для одного соединения можно создать неограниченное количество cursor. Он также не является потокобезопасным. Модуль не позволяет делиться объектами cursor между потоками. Если это сделать, то будет ошибка.
После этого создается запрос для получения версии базы данных.
- С помощью метода execute объекта cursor можно выполнить запрос в базу данных из Python. Он принимает SQLite-запрос в качестве параметра и возвращает resultSet — то есть, строки базы данных
- Получить результат запроса из resultSet можно с помощью методов, например, fetchAll()
- В этом примере SELECT version(); выполняется для получения версии базы данных SQLite.
Блок try-except-finally: весь код расположен в блоке try-except, что позволит перехватить исключения и ошибки базы данных, которые могут появиться в процессе.
- С помощью класса sqlite3.Error можно обработать любую ошибку и исключение, которые могут появиться при работе с SQLite из Python.
- Это позволит сделать приложение более отказоустойчивым. Класс sqlite3.Error позволит понять суть ошибки. Он возвращает сообщение и код ошибки.
cursor.close() и connection.close()
- Хорошей практикой считается закрывать объекты connection и curosor после завершения работы, чтобы избежать проблем с базой данных.
Создание таблицы SQLite в Python
В этом разделе разберемся, как создавать таблицы в базе данных SQLite с помощью Python и модуля sqlite3. Создание таблицы — это DDL-запрос, выполняемый из Python.
В этом примере создадим базу sqlitedb_developers в базе данных sqlite_python.db .
Шаги для создания таблицы в SQLite с помощью Python:
- Соединиться с базой данных с помощью sqlite3.connect() . Речь об этом шла в первом разделе.
- Подготовить запрос создания таблицы.
- Выполнить запрос с помощью cursor.execute(query) .
- Закрыть соединение с базой и объектом cursor .
import sqlite3
try:
sqlite_connection = sqlite3.connect('sqlite_python.db')
sqlite_create_table_query = '''CREATE TABLE sqlitedb_developers (
id INTEGER PRIMARY KEY,
name TEXT NOT NULL,
email text NOT NULL UNIQUE,
joining_date datetime,
salary REAL NOT NULL);'''
cursor = sqlite_connection.cursor()
print("База данных подключена к SQLite")
cursor.execute(sqlite_create_table_query)
sqlite_connection.commit()
print("Таблица SQLite создана")
cursor.close()
except sqlite3.Error as error:
print("Ошибка при подключении к sqlite", error)
finally:
if (sqlite_connection):
sqlite_connection.close()
print("Соединение с SQLite закрыто")База данных подключена к SQLite Таблица SQLite создана Соединение с SQLite закрыто
Типы данных SQLite и соответствие типам Python
Перед переходом к выполнению CRUD-операций в SQLite из Python сначала нужно разобраться с типами данных SQLite и соответствующими им типами данных в Python, которые помогают хранить и считывать данные из таблицы.
У движка SQLite есть несколько классов для хранения значений. Каждое значение, хранящееся в базе данных, имеет один из следующих типов или классов.
Типы данных SQLite:
- NULL — значение NULL
- INTEGER — числовые значения. Целые числа хранятся в 1, 2, 3, 4, 6 и 8 байтах в зависимости от величины
- REAL — числа с плавающей точкой, например, 3.14, число Пи
- TEXT — текстовые значения. Могут храниться в кодировке UTF-8, UTF-16BE или UTF-16LE
- BLOB — бинарные данные. Для хранения изображений и файлов
Следующие типы данных из Python без проблем конвертируются в SQLite. Для конвертации достаточно лишь запомнить эту таблицу.
| Тип Python | Тип SQLite |
|---|---|
| None | NULL |
| int | INTEGER |
| float | REAL |
| str | TEXT |
| bytes | BLOB |
Выполнение SQL запросов с помощью функции executescript
Скрипты SQLite отлично справляются со стандартными задачами. Это набор SQL-команд, сохраненных в файле (в формате .sql). Один файл содержит одну или больше SQL-операций, которые затем выполняются из командной строки.
Дальше несколько распространенных сценариев использования SQL-скриптов
- Создание резервных копий сразу нескольких баз данных за раз.
- Сравнение количества строк двух разных баз с одной схемой.
- Создание всех таблиц в одном скрипте, что позволит создать нужную схему на любом сервере
Выполнить скрипт из командной строки SQLite можно с помощью команды .read :
sqlite> .read sqlitescript.sqlНапример, этот простой скрипт создает две таблицы.
CREATE TABLE fruits ( id INTEGER PRIMARY KEY, name TEXT NOT NULL, price REAL NOT NULL ); CREATE TABLE drinks ( id INTEGER PRIMARY KEY, name TEXT NOT NULL, price REAL NOT NULL );Теперь посмотрим, как выполнить его из Python.
import sqlite3
try:
sqlite_connection = sqlite3.connect('sqlite_python.db')
cursor = sqlite_connection.cursor()
print("База данных подключена к SQLite")
with open('sqlite_create_tables.sql', 'r') as sqlite_file:
sql_script = sqlite_file.read()
cursor.executescript(sql_script)
print("Скрипт SQLite успешно выполнен")
cursor.close()
except sqlite3.Error as error:
print("Ошибка при подключении к sqlite", error)
finally:
if (sqlite_connection):
sqlite_connection.close()
print("Соединение с SQLite закрыто")
Таблицы SQLite создаются за счет выполнения скрипта из Python. Вывод:
База данных подключена к SQLite Скрипт SQLite успешно выполнен Соединение с SQLite закрытоПримечание: после соединения с SQLite все содержимое файла сохраняется в переменной. Затем используется команда cursor.executescript(script) для выполнения всех инструкций за раз.
Исключения базы данных SQLite
- sqlite3.Warning . Подкласс Exception . Его можно игнорировать, если нужно, чтобы оно не останавливало выполнение.
- sqlite3.Error . Базовый класс для остальных исключений модуля sqlite3. Подкласс Exception .
- sqlite3.DatabaseError . Исключение, которое возвращается при ошибках базы данных. Например, если попытаться открыть файл как базу sqite3, хотя он ею не является, то вернется ошибка «sqlite3.DatabaseError: file is encrypted or is not a database».
- sqlite3.IntegrityError . Подкласс DatabaseError . Эта ошибка возвращается, когда затрагиваются отношения в базе, например, например, не проходит проверка внешнего ключа.
- sqlite3.ProgrammingError . Подкласс DatabaseError . Эта ошибка возникает из-за ошибок программиста: создание таблицы с именем, которое уже занято, синтаксическая ошибка в SQL-запросах.
- sqlite3.OperationalError . Подкласс DatabaseError . Эту ошибку невозможно контролировать. Она появляется в ситуациях, которые касаются работы базы данных, например, обрыв соединения, неработающий сервер, проблемы с источником данных и так далее.
- sqlite3.NotSupportedError . Это исключение появляется при попытке использовать неподдерживаемое базой данных API. Пример: вызов метода rollback() для соединения, которое не поддерживает транзакции. Вызов коммита после команды создания таблицы.
Таким образом рекомендуется всегда писать код управления базой данных в блоке try, чтобы была возможность перехватывать исключения и предпринимать действия, которые помогут справиться с ними.
Например, попробуем добавить данные в таблицу, которой не существует и выведем весь стек исключений из Python.
import sqlite3
import traceback
import sys
try:
sqlite_connection = sqlite3.connect('sqlite_python.db')
cursor = sqlite_connection.cursor()
print("База данных подключена к SQLite")
sqlite_insert_query = """INSERT INTO unknown_table_1
(id, text) VALUES (1, 'Демо текст')"""
count = cursor.execute(sqlite_insert_query)
sqlite_connection.commit()
print("Запись успешно вставлена в таблицу sqlitedb_developers ", cursor.rowcount)
cursor.close()
except sqlite3.Error as error:
print("Не удалось вставить данные в таблицу sqlite")
print("Класс исключения: ", error.__class__)
print("Исключение", error.args)
print("Печать подробноcтей исключения SQLite: ")
exc_type, exc_value, exc_tb = sys.exc_info()
print(traceback.format_exception(exc_type, exc_value, exc_tb))
finally:
if (sqlite_connection):
sqlite_connection.close()
print("Соединение с SQLite закрыто")База данных подключена к SQLite Не удалось вставить данные в таблицу sqlite Класс исключения: Исключение ('no such table: unknown_table_1',) Печать подробноcтей исключения SQLite: ['Traceback (most recent call last):\n', ' File "C:\\Users\\demo\\AppData\\Local\\Programs\\Python\\Python38\\sqlitet.py", line 13, in \n count = cursor.execute(sqlite_insert_query)\n', 'sqlite3.OperationalError: no such table: unknown_table_1\n'] Соединение с SQLite закрытоИзменения timeout при подключении из Python
Бывает такое, что есть несколько подключений к базе данных SQLite, и одно из них выполняет определенное изменение. Для этого соединению требуется выполнить блокировку — база данных блокируется до тех пор, пока транзакция не будет завершена.
Параметр timeout, который задается при подключении, определяет, как долго соединение будет ожидать отключения блокировки перед возвращением исключения.
По умолчанию значение этого параметра равно 5.0 (5 секунд). Его не нужно задавать, потому что это значение по умолчанию. Таким образом при подключении к базе данных из Python, если ответ не будет получен в течение 5 секунд, вернется исключение. Однако параметр все-таки можно задать в функции sqlite3.connect .
Посмотрим, как это сделать из Python.
import sqlite3
def read_sqlite_table():
try:
sqlite_connection= sqlite3.connect('sqlite_python.db', timeout=20)
cursor = sqlite_connection.cursor()
print("Подключен к SQLite")
sqlite_select_query = """SELECT count(*) from sqlitedb_developers"""
cursor.execute(sqlite_select_query)
total_rows = cursor.fetchone()
print("Всего строк: ", total_rows)
cursor.close()
except sqlite3.Error as error:
print("Ошибка при подключении к sqlite", error)
finally:
if (sqlite_connection):
sqlite_connection.close()
print("Соединение с SQLite закрыто")
read_sqlite_table()Подключен к SQLite Всего строк: (0,) Соединение с SQLite закрытоПолучение изменений с момента подключения к базе данных
Для статистики может потребоваться найти количество строк базы данных, которые были вставлены, удалены или изменены с момента открытия соединения. Для этого используется функция connection.total_changes модуля sqlite3.
Этот метод возвращается общее количество строк, которые были затронуты. Рассмотрим пример.
import sqlite3
try:
sqlite_connection = sqlite3.connect('sqlite_python.db')
cursor = sqlite_connection.cursor()
print("Подключен к SQLite")
sqlite_insert_query = """INSERT INTO sqlitedb_developers
(id, name, email, joining_date, salary)
VALUES (4, 'Alex', 'sale@gmail.com', '2020-11-20', 8600);"""
cursor.execute(sqlite_insert_query)
sql_update_query = """Update sqlitedb_developers set salary = 10000 where /> cursor.execute(sql_update_query)
sql_delete_query = """DELETE from sqlitedb_developers where /> cursor.execute(sql_delete_query)
sqlite_connection.commit()
cursor.close()
except sqlite3.Error as error:
print("Ошибка при работе с SQLite", error)
finally:
if (sqlite_connection):
print("Всего строк, измененных после подключения к базе данных: ", sqlite_connection.total_changes)
sqlite_connection.close()
print("Соединение с SQLite закрыто")Подключен к SQLite Всего строк, измененных после подключения к базе данных: 3 Соединение с SQLite закрытоСохранение резервной копии базы данных из Python
Модуль sqlite3 в Python предоставляет функцию для сохранения резервной копии базы данных SQLite. С помощью метода connection.backup() можно сделать резервную копию базы SQLite.
connection.backup(target, *, pages=0, progress=None, name="main", sleep=0.250)Эта функция делает полную резервную копию базы данных SQLite. Изменения записываются в аргумент target , который должен быть экземпляром другого соединения.
По умолчанию когда параметр pages равен 0 или отрицательному числу, вся база данных копируется в один шаг. В противном случае метод выполняет цикл, копируя заданное количество страниц за раз.
Аргумент name определяет базу данных, резервную копию которой нужно сделать. Аргумент sleep — количество секунд между последовательными попытками сохранить оставшиеся страницы. Аргумент sleep можно задать как в качестве целого числа, так и в виде числа с плавающей точкой.
Рассмотрим один пример копирования базы данных в другую.
import sqlite3
def progress(status, remaining, total):
print(f'Скопировано из . ')
try:
sqlite_con = sqlite3.connect('sqlite_python.db')
backup_con = sqlite3.connect('sqlite_backup.db')
with backup_con:
sqlite_con.backup(backup_con, pages=3, progress=progress)
print("Резервное копирование выполнено успешно")
except sqlite3.Error as error:
print("Ошибка при резервном копировании: ", error)
finally:
if(backup_con):
backup_con.close()
sqlite_con.close()Скопировано 3 из 5. Скопировано 5 из 5. Резервное копирование выполнено успешно
- После подключения к SQLite обе базы данных были открыты с помощью двух разных подключений
- Дальше выполняется метод connection.backup() с помощью экземпляра первого подключения. Также задано количество страниц, которые нужно скопировать за одну итерацию.
Модуль sqlite3 языка Python
Сами по себе СУБД редко используются для работы с базами данных. В том смысле, что в реальных проектах связки БД + СУБД бывает недостаточно. Обычно с СУБД работают через какой-либо язык программирования. Это позволяет более гибко принимать запросы, обрабатывать ответы перед передачей их куда-либо далее. Ведь у императивного, а не декларативного как SQL, языка программирования средств для работы с данными больше, да и логика богаче.
При этом неизбежна определенная специфика, накладываемая языком программирования и особенностями его работы с СУБД. Есть команды на подключение к БД, использование объекта "курсора", выполнение SQL-запросов, сохранение изменений в БД и др.
Инструменты для работы с конкретной СУБД не являются базовыми командами и объектами самого языка. Обычно они подключаются через импорт модуля или библиотеки. Так модуль sqlite3 входит в установочный пакет языка Python, является компонентом стандартной библиотеки и не требует отдельной загрузки и установки. Однако его все равно надо импортировать:
>>> import sqlite3
Библиотеку SQLite также не требуется устанавливать отдельно. Она есть в установочном пакете Python. Непосредственно модуль sqlite3 – это API к СУБД SQLite. Своего рода адаптер, который переводит команды, написанные на Питоне, в команды, которые понимает SQLite. Как и наоборот, доставляет ответы от SQLite в python-программу.
Модуль sqlite3 содержит много классов, функций и констант. Их перечень можно посмотреть с помощью функции dir().
Вызов функции connect() приводит к созданию объекта-экземпляра от класса Connection. Этот объект обеспечивает связь с файлом базы данных, представляет конкретную БД в программе:
>>> db = sqlite3.connect('site.sqlite') >>> type(db)
Почему объект создается с помощью функции, а не от самого класса? Видимо дело в том, что помимо имени-адреса базы данных может передаваться ряд других необязательных аргументов, первичной обработкой которых занимается функция.
После того как экземпляр Connection создан, чтобы выполнять SQL-команды, над создать еще один объект, но теперь уже от класса Cursor. Делается это с помощью метода cursor() объекта типа Connection:
SQL-команды выполняются с помощью метода execute() и некоторых других. Если запрос длинный, и его удобно разбить на несколько строк, используют тройные кавычки. Создадим таблицы:
>>> cur.execute(''' . CREATE TABLE sections ( . _id INTEGER PRIMARY KEY, . name TEXT)''') >>> cur.execute(''' . CREATE TABLE pages ( . _id INTEGER PRIMARY KEY AUTOINCREMENT, . title TEXT, . url TEXT NOT NULL, . theme INTEGER NOT NULL, . num INTEGER NOT NULL DEFAULT 100, . FOREIGN KEY (theme) . REFERENCES sections(_id)) . ''')
Если нужна поддержка внешнего ключа включим ее:
>>> cur.execute("PRAGMA foreign_keys = ON")
Обратим внимание, что в конце SQL-запросов здесь точка с запитой не ставятся. Также метод возвращает сам объект.
Заполнять таблицы можно тоже с помощью execute(). Однако, если требуется вставить несколько записей, лучше воспользоваться методом executemany():
>>> themes = [ . (1, 'Information'), . (2, 'Digital Systems'), . (3, 'Boolean Algebra')] >>> cur.executemany(''' . INSERT INTO sections . VALUES (?, ?)''', themes)
Мы создаем список из кортежей. Каждый кортеж – это отдельная запись таблицы. Метод executemany() выполняет SQL-команду по отношению к каждому элементу списка. При этом данные из кортежа подставляются вместо знаков вопроса. Такая подстановка работает и через execute():
>>> cur.execute(''' . INSERT INTO sections VALUES . (4, ?)''', ('Algorithm',))
То, что подставляется, должно быть кортежем. Через знаки вопроса кортеж как бы распаковывается.
Есть еще метод executescript(). В качестве аргумента передается скрипт на языке SQL, который может включать несколько запросов, каждый из которых заканчивается точкой с запятой.
У объекта-курсора есть методы fetchone(), fetchmany() и fetchall(), которые позволяют извлекать из него данные, если sql-запрос предполагал их передачу. По-сути они наделяют курсор свойствами объекта-итератора (такой имеет метод __next__()):
>>> cur.execute("SELECT * FROM sections") >>> cur.fetchone() (1, 'Information') >>> cur.fetchone() (2, 'Digital Systems') >>> cur.__next__() (3, 'Boolean Algebra')
Два других метода:
>>> cur.execute("SELECT * FROM sections") >>> cur.fetchall() [(1, 'Information'), (2, 'Digital Systems'), (3, 'Boolean Algebra'), (4, 'Algorithm')] >>> cur.fetchall() [] >>> cur.execute("SELECT * FROM sections") >>> cur.fetchmany(2) [(1, 'Information'), (2, 'Digital Systems')] >>> cur.fetchmany(2) [(3, 'Boolean Algebra'), (4, 'Algorithm')] >>> cur.fetchmany(2) []
Для того, чтобы корректно завершить работу с базой данных, надо применить изменения (выполнить транзакцию) и разорвать соединение. Обратите внимание, это делается по отношению к экземпляру Connection, а не Cursor:
>>> db.commit() >>> db.close()
Закрытие без commit() приведет к потере изменений, сделанных за сессию. Если нужно откатить, а не применить, изменения текущей сессии, используется метод rollback().
X Скрыть Наверх
Введение в реляционные базы данных. SQLite
Работа с SqLite3 в Python
Базы данных и работа с ними заслуживают отдельного раздела, здесь же мы рассмотрим в чем заключается работа с 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 ('', '', , '');" 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.