В этой статье мы разберемся с SQLite. Мы узнаем, как использовать SQLite через библиотеку Python под названием sqlite3. В самом конце мы рассмотрим некоторые более продвинутые функции, предоставляемые, sqlite3чтобы облегчить нашу работу.
Что такое SQLite?
Девиз SQLite: «Маленький. Быстро. Надежный. Выбери любые три».
SQLite — это встроенная библиотека баз данных, написанная на C. Возможно, вы знакомы с другими технологиями баз данных, такими как MySQL или PostgreSQL. В них используется клиент-серверный подход: база данных устанавливается как сервер, а затем для подключения к ней используется клиент. SQLite отличается: он известен как встроенная база данных, потому что он включен в программу как библиотека. Все данные хранятся в файле — обычно с.dbрасширением — и у вас есть функции, которые позволяют запускать операторы SQL или выполнять любые другие операции с базой данных.
Решение для хранения на основе файлов также обеспечивает одновременный доступ, что означает, что несколько процессов или потоков могут обращаться к одной и той же базе данных. Итак, каково использование SQLite? Подходит ли он для любого приложения?
Ну, есть несколько случаев, когда SQLite выделяется:
- Будучи включенным в большинство мобильных операционных систем, таких как Android и iOS, SQLite может стать идеальным выбором, если вам нужно автономное и бессерверное решение для хранения данных.
- Вместо использования огромных CSV-файлов вы можете использовать возможности SQL и поместить все свои данные в единую базу данных SQLite.
- SQLite можно использовать для хранения данных конфигурации ваших приложений. Фактически, SQLite на 35% быстрее, чем файловая система, такая как файл конфигурации.
С другой стороны, по каким причинам не стоит выбирать SQLite?
- В отличие от MySQL или PostgreSQL, в SQLite отсутствуют многопользовательские функции.
- SQLite по-прежнему является решением для хранения данных на основе файлов, а не услугой. Вы не можете управлять им как процессом, вы не можете запустить или остановить его или управлять использованием ресурсов.
Интерфейс Python для SQLite
Как я сказал во введении, SQLite — это библиотека C. Однако существуют интерфейсы, написанные на многих языках, включая Python. Модуль sqlite3предоставляет интерфейс SQL и требует как минимум SQLite 3.7.15.
Удивительно то, что он sqlite3поставляется с Python, поэтому вам не нужно ничего устанавливать.
Начало работы с sqlite3
Пришло время кодировать! В этой первой части мы создадим базовую базу данных. Первое, что нужно сделать, это создать базу данных и подключиться к ней:
import sqlite3 dbName = 'database.db' try: conn = sqlite3.connect(dbName) cursor = conn.cursor() print("Database created!") except Exception as e: print("Something bad happened: ", e) if conn: conn.close()
В строке 1 мы импортируем sqlite3библиотеку. Затем внутри try/exceptблока кода мы вызываем sqlite3.connect()для инициализации соединения с базой данных. Если все пойдет правильно, connбудет экземпляр Connectionобъекта. Если tryне удается, мы печатаем полученное исключение, и соединение с базой данных закрывается. Как указано в официальной документации, каждая открытая база данных SQLite представлена Connectionобъектом. Каждый раз, когда нам нужно выполнить команду SQL, у Connectionобъекта есть метод с именем cursor(). В технологиях баз данных курсор — это управляющая структура, позволяющая перемещаться по записям в базе данных.
Теперь, если мы выполним этот код, мы должны получить следующий вывод:
> Database created!
Если мы посмотрим на папку, в которой находится наш скрипт Python, мы должны увидеть новый файл с именем database.db. Этот файл был создан автоматически sqlite3.
Создание, чтение и изменение записей
На данный момент мы готовы создать новую таблицу, добавить первые записи и выполнить команды SQL, такие как SELECT, UPDATEили DROP.
Чтобы создать таблицу, нам просто нужно выполнить простую инструкцию SQL. В этом примере мы создадим таблицу студентов, которая будет содержать следующие данные:
| id | name | surname |
|---|---|---|
| 1 | John | Smith |
| 2 | Lucy | Jacobs |
| 3 | Stephan | Taylor |
После print(«Database created!»)строки добавьте это:
# Create operation create_query = '''CREATE TABLE IF NOT EXISTS student( id INTEGER PRIMARY KEY, name TEXT NOT NULL, surname TEXT NOT NULL); ''' cursor.execute(create_query) print("Table created!") # Insert and Read operation cursor.execute("INSERT INTO student VALUES (1, 'John', 'Smith')") print("Insert #1 done!") cursor.execute("INSERT INTO student VALUES (2, 'Lucy', 'Jacobs')") print("Insert #2 done!") cursor.execute("INSERT INTO student VALUES (3, 'Stephan', 'Taylor')") print("Insert #3 done!") conn.commit() conn.close()
Мы создаем таблицу и вызываем cursor.execute()метод, который используется, когда мы хотим выполнить один оператор SQL.
Затем мы делаем INSERTдля каждой строки, которую хотим добавить. После того, как все наши изменения были сделаны, мы вызываем, conn.commit()чтобы зафиксировать ожидающую транзакцию в базе данных. Без вызова commit()метода любые ожидающие изменения в базе данных будут потеряны. Наконец, мы закрываем соединение с базой данных, вызывая conn.close()метод.
Хорошо, теперь давайте запросим нашу базу данных! Нам понадобится переменная для сохранения результатов нашего запроса, поэтому давайте сохраним результат cursor.execute()в переменную с именем records:
records = cursor.execute("SELECT * FROM student") for row in findrecords: print(row)
После выполнения этого мы увидим все записи в stdout:
(1, 'John', 'Smith') (2, 'Lucy', 'Jacobs') (3, 'Stephan', 'Taylor')
К этому моменту вы могли заметить, что внутри cursor.execute()метода мы помещаем SQL-команду, которую необходимо выполнить. В синтаксисе Python ничего не меняется, если мы хотим выполнить другую команду SQL, например UPDATEили DROP.
The Placeholders
Методу cursor.execute()нужна строка в качестве аргумента. В предыдущем разделе мы видели, как вставлять данные в нашу базу данных, но все было жестко запрограммировано. Что, если нам нужно сохранить в базе данных что-то, что находится в переменной? По этой причине в sqlite3нем есть некоторые причудливые вещи, называемые заполнителями. Заполнители позволяют нам использовать подстановку параметров, что значительно упростит вставку переменной в запрос.
Давайте посмотрим на этот пример:
def insert_command(conn, student_id, name, surname): command = 'INSERT INTO student VALUES (?, ?, ?)' cur = conn.cursor() cur.execute(command, (student_id, name, surname, )) conn.commit()
Мы создаем метод с именем insert_command(). Этот метод принимает четыре аргумента: первый является Connectionэкземпляром, а остальные три будут использоваться в нашей команде SQL.
Каждая переменная ?внутри commandпредставляет собой заполнитель. Это означает, что если вы вызовете insert_commandфункцию с помощью student_id=1, name=’Jason’и surname=’Green’, INSERTоператор станет INSERT INTO student VALUES(1, ’Jason’, ’Green’).
Когда мы вызываем execute()функцию, мы передаем нашу команду и все переменные, которые будут заменены на заполнители. С этого момента каждый раз, когда нам нужно вставить строку в таблицу учеников, мы вызываем insert_command()метод с требуемыми параметрами.
Транзакции
Даже если вы не новичок в определении транзакции, позвольте мне кратко рассказать о ее важности. Транзакция — это последовательность операций, выполняемых в базе данных, которая логически рассматривается как единое целое.
Наиболее важным преимуществом транзакции является обеспечение целостности данных. Это может быть бесполезно в примере, который мы представили выше, но когда мы имеем дело с большим количеством данных, хранящихся в нескольких таблицах, транзакции действительно имеют значение.
Модуль Python sqlite3запускает транзакцию до того, как execute() и executemany() выполнит операторы INSERT, UPDATE, DELETEили REPLACE. Это подразумевает две вещи:
- Мы должны позаботиться о вызове commit() метода. Если мы вызовем Connection.close() без выполнения commit(), все изменения, сделанные нами во время транзакции, будут потеряны.
- Мы не можем открыть транзакцию в том же процессе, используя BEGIN.
Решение? Явная обработка транзакций.
Как? Используя вызов функции sqlite3.connect(dbName, isolation_level=None)вместо sqlite3.connect(dbName). Установив значение isolation_level, Noneмы заставляем sqlite3никогда не открывать транзакции неявно.
Следующий код представляет собой переписывание предыдущего кода, но с явным использованием транзакций:
import sqlite3 dbName = 'database.db' def insert_command(conn, student_id, name, surname): command = 'INSERT INTO student VALUES (?, ?, ?)' cur = conn.cursor() cur.execute("BEGIN") try: cur.execute(command, (student_id, name, surname, )) cur.execute("COMMIT") except conn.Error as e: print("Got an error: ", e) print("Aborting...") cur.execute("ROLLBACK") conn = sqlite3.connect(dbName, isolation_level=None) cursor = conn.cursor() print("Database created!") # Create operation create_query = '''CREATE TABLE IF NOT EXISTS student( id INTEGER PRIMARY KEY, name TEXT NOT NULL, surname TEXT NOT NULL); ''' cursor.execute(create_query) print("Table created!") # Insert and Read operation insert_command(conn , 1, 'John', 'Smith') insert_command(conn , 2, 'Lucy', 'Jacobs') insert_command(conn , 3, 'Stephan', 'Taylor') insert_command(conn , 4, 'Joseph', 'Random') findRecords = cursor.execute("SELECT * FROM student") for row in findRecords: print(row) conn.close()
Заключение
Я надеюсь, что теперь вы хорошо понимаете, что такое SQLite, как вы можете использовать его для своих проектов Python и как работают некоторые из его расширенных функций. Поначалу явное управление транзакциями может показаться немного сложным, но оно, безусловно, поможет вам максимально эффективно использовать возможности sqlite3.
