Автор оригинала: Adam McQuistan.
Вступление
Этот учебник будет посвящен использованию SQLite в сочетании с интерфейсом Python sqlite3 . SQLite-это однофайльная реляционная база данных в комплекте с большинством стандартных установок Python. SQLite часто является предпочтительной технологией для небольших приложений, особенно для встроенных систем и устройств, таких как телефоны и планшеты, интеллектуальные приборы и инструменты. Однако нередко можно услышать, что он используется для небольших и средних веб-приложений и настольных компьютеров.
Создание базы данных и установление соединения
Создать новую базу данных SQLite так же просто, как создать соединение с помощью модуля sqlite3 в стандартной библиотеке Python. Чтобы установить соединение, все, что вам нужно сделать, это передать путь к файлу методу connect(...) в модуле sqlite3, и если база данных, представленная файлом, не существует, она будет создана по этому пути.
import sqlite3
con = sqlite3.connect('/path/to/file/db.sqlite3')
Вы обнаружите, что в повседневном программировании баз данных вы будете постоянно создавать соединения с вашей базой данных, поэтому неплохо обернуть этот простой оператор соединения в многократно используемую обобщенную функцию.
# db_utils.py
import os
import sqlite3
# create a default path to connect to and create (if necessary) a database
# called 'database.sqlite3' in the same directory as this script
DEFAULT_PATH = os.path.join(os.path.dirname(__file__), 'database.sqlite3')
def db_connect(db_path=DEFAULT_PATH):
con = sqlite3.connect(db_path)
return con
Создание таблиц
Чтобы создать таблицы базы данных, вам нужно иметь представление о структуре данных, которые вы хотите сохранить. Существует много конструктивных соображений, которые входят в определение таблиц реляционной базы данных, о которых были написаны целые книги. Я не буду вдаваться в подробности этой практики и вместо этого оставлю ее читателю для дальнейшего исследования.
Однако, чтобы помочь в нашем обсуждении программирования баз данных SQLite с Python, я буду исходить из предпосылки, что база данных должна быть создана для фиктивного книжного магазина, в котором уже собраны нижеприведенные данные о продажах книг.
| 2/22/1944 | 7.99 | Введение в комбинаторику | Алан Тьюринг |
| 7/3/1967 | 17.99 | Руководство по написанию коротких рассказов | Дональд Кнут |
| 7/3/1967 | 11.99 | Структуры данных и алгоритмы | Дональд Кнут |
| 1/12/1969 | 16.99 | Продвинутая теория множеств | Эдгар Кодд |
При проверке этих данных становится очевидным, что они содержат информацию о клиентах, продуктах и заказах. Общим шаблоном при проектировании баз данных для транзакционных систем такого типа является разбиение ордеров на две дополнительные таблицы, orders и line items (иногда называемые order details ) для достижения большей нормализации.
В интерпретаторе Python, в том же каталоге, что и db_utils.модуль py, определенный ранее, введите SQL для создания таблиц customers и products следующим образом:
>>> from db_utils import db_connect >>> con = db_connect() # connect to the database >>> cur = con.cursor() # instantiate a cursor obj >>> customers_sql = """ ... CREATE TABLE customers ( ... id integer PRIMARY KEY, ... first_name text NOT NULL, ... last_name text NOT NULL)""" >>> cur.execute(customers_sql) >>> products_sql = """ ... CREATE TABLE products ( ... id integer PRIMARY KEY, ... name text NOT NULL, ... price real NOT NULL)""" >>> cur.execute(products_sql)
Приведенный выше код создает объект соединения, а затем использует его для создания экземпляра объекта курсора. Объект cursor используется для выполнения инструкций SQL в базе данных SQLite.
С помощью созданного курсора я затем написал SQL для создания таблицы customers, дав ей первичный ключ вместе с текстовым полем имени и фамилии и назначив его переменной с именем customers_sql . Затем я вызываю метод execute(...) объекта cursor, передавая ему переменную customers_sql . Затем я создаю таблицу products аналогичным образом.
Вы можете запросить таблицу sqlite_master , встроенную таблицу метаданных SQLite, чтобы убедиться, что вышеперечисленные команды были успешными.
Чтобы увидеть все таблицы в текущей подключенной базе данных, запросите столбец name таблицы sqlite_master , где type равен “table”.
>>> cur.execute("SELECT name FROM sqlite_master WHERE type='table'")
>>> print(cur.fetchall())
[('customers',), ('products',)]
Чтобы получить представление о схеме таблиц, запросите столбец sql той же таблицы, где type по-прежнему является “таблицей”, а name равно “клиентам” и/или “продуктам”.
>>> cur.execute("""SELECT sql FROM sqlite_master WHERE type='table'
… AND name='customers'""")
>>> print(cur.fetchone()[0])
CREATE TABLE customers (
id integer PRIMARY KEY,
first_name text NOT NULL,
last_name text NOT NULL)
Следующей таблицей для определения будет таблица orders , которая связывает клиентов с заказами через внешний ключ и дату их покупки. Поскольку SQLite не поддерживает фактический тип данных date/time (или класс данных, совместимый с жаргоном SQLite), все даты будут представлены в виде текстовых значений.
>>> orders_sql = """ ... CREATE TABLE orders ( ... id integer PRIMARY KEY, ... date text NOT NULL, ... customer_id integer, ... FOREIGN KEY (customer_id) REFERENCES customers (id))""" >>> cur.execute(orders_sql)
Конечной таблицей для определения будет таблица позиции , которая дает подробный учет продуктов в каждом заказе.
lineitems_sql = """ ... CREATE TABLE lineitems ( ... id integer PRIMARY KEY, ... quantity integer NOT NULL, ... total real NOT NULL, ... product_id integer, ... order_id integer, ... FOREIGN KEY (product_id) REFERENCES products (id), ... FOREIGN KEY (order_id) REFERENCES orders (id))""" >>> cur.execute(lineitems_sql)
Загрузка данных
В этом разделе я покажу, как ВСТАВИТЬ наши примеры данных в только что созданные таблицы. Естественным начальным местом было бы сначала заполнить таблицу products , потому что без продуктов мы не можем иметь продажи и, следовательно, не имели бы внешних ключей для связи с позициями и заказами. Глядя на примерные данные я вижу что есть четыре продукта:
- Введение в комбинаторику ($7.99)
- Руководство по написанию коротких рассказов ($17.99)
- Структуры данных и алгоритмы ($11.99)
- Продвинутая теория множеств ($16.99)
Рабочий процесс для выполнения инструкций INSERT прост:
- Подключение к базе данных
- Создание объекта курсора
- Напишите параметризованную инструкцию insert SQL и сохраните ее как переменную
- Вызовите метод execute для объекта cursor, передав ему переменную sql и значения в виде кортежа, которые будут вставлены в таблицу
Учитывая этот общий план, давайте напишем еще немного кода.
>>> con = db_connect()
>>> cur = con.cursor()
>>> product_sql = "INSERT INTO products (name, price) VALUES (?, ?)"
>>> cur.execute(product_sql, ('Introduction to Combinatorics', 7.99))
>>> cur.execute(product_sql, ('A Guide to Writing Short Stories', 17.99))
>>> cur.execute(product_sql, ('Data Structures and Algorithms', 11.99))
>>> cur.execute(product_sql, ('Advanced Set Theory', 16.99))
Приведенный выше код, вероятно, кажется довольно очевидным, но позвольте мне немного обсудить его, поскольку здесь происходят некоторые важные вещи. Оператор insert следует стандартному синтаксису SQL, за исключением ? бит. В ? ‘s на самом деле являются заполнителями в так называемом “параметризованном запросе”.
Параметризованные запросы являются важной особенностью практически всех интерфейсов баз данных к современным языкам программирования высокого уровня, таким как модуль sqlite3 в Python. Этот тип запросов служит для повышения эффективности запросов, которые повторяются несколько раз. Возможно, что более важно, они также дезинфицируют входы, которые занимают место ? заполнители, которые передаются во время вызова метода execute объекта cursor для предотвращения нечестивых входов, ведущих к SQL-инъекции . Ниже приводится комикс из популярного xkcd.com блог, описывающий опасности SQL-инъекций.
Чтобы заполнить оставшиеся таблицы, мы будем следовать немного другой схеме, чтобы немного изменить ситуацию. Рабочий процесс для каждого заказа, идентифицируемого комбинацией имени и фамилии клиента и даты покупки, будет следующим::
- Вставьте нового клиента в таблицу customers и получите его идентификатор первичного ключа
- Создайте запись заказа на основе идентификатора клиента и даты покупки, а затем извлеките ее идентификатор первичного ключа
- Для каждого продукта в заказе определите его идентификатор первичного ключа и создайте запись строки, связывающую заказ и продукт
Чтобы упростить себе задачу, давайте сделаем быстрый поиск всех наших продуктов. Пока что не стоит слишком беспокоиться о механике оператора SELECT SQL, так как мы вскоре посвятим ему отдельный раздел.
>>> cur.execute("SELECT id, name, price FROM products")
>>> formatted_result = [f"{id:<5}{name:<35}{price:>5}" for id, name, price in cur.fetchall()]
>>> id, product, price = "Id", "Product", "Price"
>>> print('\n'.join([f"{id:<5}{product:<35}{price:>5}"] + formatted_result))
Id Product Price
1 Introduction to Combinatorics 7.99
2 A Guide to Writing Short Stories 17.99
3 Data Structures and Algorithms 11.99
4 Advanced Set Theory 16.99
Первый заказ был сделан 22 февраля 1944 года Аланом Тьюрингом, который приобрел Введение в комбинаторику за $7,99.
Начните с создания новой записи клиента для мистера Тьюринга, а затем определите его идентификатор первичного ключа, обратившись к полю lastrowid объекта курсора.
>>> customer_sql = "INSERT INTO customers (first_name, last_name) VALUES (?, ?)"
>>> cur.execute(customer_sql, ('Alan', 'Turing'))
>>> customer_id = cur.lastrowid
>>> print(customer_id)
1
Теперь мы можем создать запись заказа, собрать новое значение идентификатора заказа и связать его с записью позиции вместе с продуктом, заказанным мистером Тьюрингом.
>>> order_sql = "INSERT INTO orders (date, customer_id) VALUES (?, ?)" >>> date = "1944-02-22" # ISO formatted date >>> cur.execute(order_sql, (date, customer_id)) >>> order_id = cur.lastrowid >>> print(order_id) 1 >>> li_sql = """INSERT INTO lineitems ... (order_id, product_id, quantity, total) ... VALUES (?, ?, ?, ?)""" >>> product_id = 1 >>> cur.execute(li_sql, (order_id, 1, 1, 7.99))
Остальные записи загружаются точно так же, за исключением заказа, сделанного Дональду Кнуту, который получит две записи позиции. Однако повторяющийся характер такой задачи кричит о необходимости обернуть эти функции в многоразовые функции. В db_utils.модуль py добавьте следующий код:
def create_customer(con, first_name, last_name):
sql = """
INSERT INTO customers (first_name, last_name)
VALUES (?, ?)"""
cur = con.cursor()
cur.execute(sql, (first_name, last_name))
return cur.lastrowid
def create_order(con, customer_id, date):
sql = """
INSERT INTO orders (customer_id, date)
VALUES (?, ?)"""
cur = con.cursor()
cur.execute(sql, (customer_id, date))
return cur.lastrowid
def create_lineitem(con, order_id, product_id, qty, total):
sql = """
INSERT INTO lineitems
(order_id, product_id, quantity, total)
VALUES (?, ?, ?, ?)"""
cur = con.cursor()
cur.execute(sql, (order_id, product_id, qty, total))
return cur.lastrowid
Ого, теперь мы можем работать с некоторой эффективностью!
Вам нужно будет exit() ваш интерпретатор Python и перезагрузить его, чтобы ваши новые функции стали доступны в интерпретаторе.
>>> from db_utils import db_connect, create_customer, create_order, create_lineitem >>> con = db_connect() >>> knuth_id = create_customer(con, 'Donald', 'Knuth') >>> knuth_order = create_order(con, knuth_id, '1967-07-03') >>> knuth_li1 = create_lineitem(con, knuth_order, 2, 1, 17.99) >>> knuth_li2 = create_lineitem(con, knuth_order, 3, 1, 11.99) >>> codd_id = create_customer(con, 'Edgar', 'Codd') >>> codd_order = create_order(con, codd_id, '1969-01-12') >>> codd_li = create_lineitem(con, codd_order, 4, 1, 16.99)
Я чувствую себя обязанным дать еще один совет как студент, изучающий мастерство программного обеспечения. Когда вы обнаруживаете, что выполняете несколько манипуляций с базой данных (в данном случае вставки), чтобы выполнить то, что на самом деле является одной совокупной задачей (например, создание заказа), лучше всего обернуть подзадачи (создание клиента, заказа, затем позиций) в одну транзакцию базы данных, чтобы вы могли либо зафиксировать успех, либо откатить, если по пути возникнет ошибка.
Это будет выглядеть примерно так:
try:
codd_id = create_customer(con, 'Edgar', 'Codd')
codd_order = create_order(con, codd_id, '1969-01-12')
codd_li = create_lineitem(con, codd_order, 4, 1, 16.99)
# commit the statements
con.commit()
except:
# rollback all database actions since last commit
con.rollback()
raise RuntimeError("Uh oh, an error occurred ...")
Я хочу закончить этот раздел быстрой демонстрацией того, как обновить существующую запись в базе данных. Давайте обновим Руководство по написанию коротких рассказов до 10,99 (поступит в продажу).
>>> update_sql = "UPDATE products SET price = ? WHERE id = ?" >>> cur.execute(update_sql, (10.99, 2))
Запрос к базе данных
Как правило, наиболее распространенным действием, выполняемым с базой данных, является извлечение некоторых данных, хранящихся в ней, с помощью оператора SELECT. В этом разделе я продемонстрирую, как использовать интерфейс sqlite3 для выполнения простых запросов SELECT.
Для выполнения базового многорядного запроса таблицы customers вы передаете оператор SELECT методу execute(...) объекта cursor. После этого вы можете повторить результаты запроса, вызвав метод fetchall() того же объекта cursor.
>>> cur.execute("SELECT id, first_name, last_name FROM customers")
>>> results = cur.fetchall()
>>> for row in results:
... print(row)
(1, 'Alan', 'Turing')
(2, 'Donald', 'Knuth')
(3, 'Edgar', 'Codd')
Допустим, вы хотели бы вместо этого просто получить одну запись из базы данных. Вы можете сделать это, написав более конкретный запрос, скажем, для идентификатора Дональда Кнута 2, а затем вызвав метод fetchone() объекта cursor.
>>> cur.execute("SELECT id, first_name, last_name FROM customers WHERE id = 2")
>>> result = cur.fetchone()
>>> print(result)
(2, 'Donald', 'Knuth')
Видите, как отдельная строка каждого результата выглядит в виде кортежа? Ну, хотя кортежи являются очень полезной структурой данных Pythonic для некоторых случаев использования программирования, многие люди находят их немного мешающими, когда дело доходит до задачи поиска данных. Просто так получилось, что есть способ представить данные таким образом, который, возможно, является более гибким для некоторых. Все, что вам нужно сделать, это установить метод row_factory объекта connection на что-то более подходящее, например sqlite3.Row . Это даст вам возможность получить доступ к отдельным элементам строки по позиции или значению ключевого слова.
>>> import sqlite3
>>> con.row_factory = sqlite3.Row
>>> cur = con.cursor()
>>> cur.execute("SELECT id, first_name, last_name FROM customers WHERE id = 2")
>>> result = cur.fetchone()
>>> id, first_name, last_name = result['id'], result['first_name'], result['last_name']
>>> print(f"Customer: {first_name} {last_name}'s id is {id}")
Customer: Donald Knuth's id is 2
Вывод
В этой статье я дал краткую демонстрацию того, что я считаю наиболее важными функциями и функциональными возможностями интерфейса sqlite3 Python для легкой однофайловой базы данных SQLite, которая поставляется в комплекте с большинством установок Python. Я также попытался дать несколько советов относительно лучших практик, когда дело доходит до программирования баз данных, но я предупреждаю новичка, что тонкости программирования баз данных, как правило, являются одними из наиболее подверженных дырам в безопасности на уровне предприятия, и дополнительные знания необходимы перед таким предприятием.
Как всегда, я благодарю вас за чтение и приветствую комментарии и критику ниже.