Вступление

В этом руководстве будет рассмотрено использование 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, я буду исходить из предпосылки, что база данных должна быть создана для фиктивного книжного магазина, в котором уже собраны указанные ниже данные о продажах книг.

клиент Дата продукт цена

Алан Тьюринг 22.02.1944 Введение в комбинаторику 7,99 Дональд Кнут 03.07.1967 Руководство по написанию рассказов 17,99 Дональд Кнут 03.07.1967 Структуры данных и алгоритмы 11,99 Эдгар Кодд 12.01.1969 Расширенная теория множеств 16,99

Изучив эти данные, становится очевидным, что они содержат информацию о клиентах, товарах и заказах. Распространенным шаблоном проектирования баз данных для транзакционных систем этого типа является разделение заказов на две дополнительные таблицы, заказы и позиции (иногда называемые деталями заказа ) для достижения большей нормализации.

В интерпретаторе Python в том же каталоге, что и модуль db_utils.py, определенный ранее, введите следующий код SQL для создания таблиц клиентов и продуктов:

 >>> 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) 

Приведенный выше код создает объект подключения, а затем использует его для создания экземпляра объекта курсора. Объект курсора используется для выполнения операторов SQL в базе данных SQLite.

Создав курсор, я написал SQL для создания таблицы клиентов, присвоив ей первичный ключ вместе с текстовым полем имени и фамилии и назначив его переменной с именем customers_sql . Затем я вызываю метод execute(...) объекта курсора, передавая ему переменную customers_sql Затем я аналогичным образом создаю таблицу продуктов.

Вы можете запросить sqlite_master , встроенную таблицу метаданных SQLite, чтобы убедиться, что вышеуказанные команды были выполнены успешно.

Чтобы увидеть все таблицы в подключенном в данный момент запроса к базе данных с name столбец sqlite_master таблицы , где type равно «столом».

 >>> cur.execute("SELECT name FROM sqlite_master WHERE type='table'") 
 <sqlite3.Cursor object at 0x104ff7ce0> 
 >>> print(cur.fetchall()) 
 [('customers',), ('products',)] 

Чтобы взглянуть на схему таблиц, запросите sql той же таблицы, где type по-прежнему является «таблица», а name равно «клиентам» и / или «продуктам».

 >>> cur.execute("""SELECT sql FROM sqlite_master WHERE type='table' 
 … AND name='customers'""") 
 <sqlite3.Cursor object at 0x104ff7ce0> 
 >>> print(cur.fetchone()[0]) 
 CREATE TABLE customers ( 
 id integer PRIMARY KEY, 
 first_name text NOT NULL, 
 last_name text NOT NULL) 

Следующая таблица, которую необходимо определить, будет таблицей заказов, которая связывает клиентов с заказами через внешний ключ и дату их покупки. Поскольку SQLite не поддерживает фактический тип данных даты / времени (или класс данных, чтобы соответствовать общему языку 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) 

Загрузка данных

В этом разделе я продемонстрирую, как ВСТАВИТЬ наши образцы данных в только что созданные таблицы. Естественным начальным местом было бы заполнение таблицы продуктов в первую очередь, потому что без продуктов у нас не может быть продажи и, следовательно, не будет внешних ключей для связи с позициями и заказами. Глядя на образцы данных, я вижу, что существует четыре продукта:

• Введение в комбинаторику (7,99 $)
• Руководство по написанию рассказов (17,99 $)
• Структуры данных и алгоритмы (11,99 долл. США)
• Расширенная теория множеств (16,99 $)

Рабочий процесс для выполнения операторов INSERT прост:

1. Подключиться к базе данных
2. Создать объект курсора
3. Напишите параметризованный SQL-оператор вставки и сохраните его как переменную
4. Вызвать метод execute для объекта курсора, передав ему переменную 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)) 

Приведенный выше код, вероятно, кажется довольно очевидным, но позвольте мне немного его обсудить, поскольку здесь происходят некоторые важные вещи. Оператор вставки следует стандартному синтаксису SQL, за исключением символа ? немного. ? фактически являются заполнителями в так называемом "параметризованном запросе".

Параметризованные запросы - важная особенность практически всех интерфейсов баз данных для современных языков программирования высокого уровня, таких как модуль sqlite3 в Python. Этот тип запросов служит для повышения эффективности запросов, которые повторяются несколько раз. Возможно, что более важно, они также дезинфицируют входные данные, которые заменяют ? заполнители, которые передаются во время вызова метода execute объекта курсора для предотвращения нечестных вводов, ведущих к SQL-инъекции . Ниже приводится комикс из популярного блога xkcd.com, в котором описываются опасности внедрения SQL.

{.ezlazyload}

Чтобы заполнить оставшиеся таблицы, мы будем следовать немного другому шаблону, чтобы немного изменить ситуацию. Рабочий процесс для каждого заказа, определяемый комбинацией имени и фамилии клиента и даты покупки, будет следующим:

1. Вставьте нового клиента в таблицу клиентов и получите его идентификатор первичного ключа.
2. Создайте запись заказа на основе идентификатора клиента и даты покупки, затем получите его идентификатор первичного ключа.
3. Для каждого продукта в заказе определите его идентификатор первичного ключа и создайте запись позиции, связывающую заказ и продукт.

Чтобы упростить себе жизнь, позвольте нам быстро просмотреть все наши продукты. На данный момент не стоит слишком беспокоиться о механизме SQL-оператора SELECT, поскольку мы вскоре посвятим ему один раздел.

 >>> 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 полю 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) 

Я чувствую себя обязанным дать еще один совет, как студент, изучающий программное обеспечение. Когда вы обнаружите, что выполняете несколько манипуляций с базой данных (в данном случае INSERT), чтобы выполнить то, что на самом деле является одной кумулятивной задачей (т. Е. Создание заказа), лучше всего обернуть подзадачи (создание клиента, заказа, затем позиций) в одна транзакция базы данных, поэтому вы можете либо зафиксировать в случае успеха, либо выполнить откат, если в процессе возникнет ошибка.

Это выглядело бы примерно так:

 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.

Чтобы выполнить базовый многострочный запрос таблицы клиентов, вы передаете инструкцию SELECT execute(...) объекта курсора. После этого вы можете перебирать результаты запроса, вызывая метод fetchall() того же объекта курсора.

 >>> 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') 

Допустим, вы хотите вместо этого просто получить одну запись из базы данных. Вы можете сделать это, написав более конкретный запрос, скажем, для идентификатора Дональда Кнута, fetchone() объекта курсора.

 >>> cur.execute("SELECT id, first_name, last_name FROM customers WHERE id = 2") 
 >>> result = cur.fetchone() 
 >>> print(result) 
 (2, 'Donald', 'Knuth') 

Посмотрите, как отдельная строка каждого результата имеет форму кортежа? Ну, хотя кортежи являются очень полезной структурой данных Pythonic для некоторых случаев использования программирования, многие люди находят их немного мешающими, когда дело доходит до задачи извлечения данных. Так уж получилось, что есть способ представить данные таким образом, который, возможно, будет более гибким для некоторых. Все, что вам нужно сделать, это установить для row_factory объекта подключения нечто более подходящее, например 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. Я также попытался дать несколько советов относительно передовых методов программирования баз данных, но предупреждаю новичков, что сложности программирования баз данных, как правило, являются одними из наиболее подверженных дырам в безопасности на уровне предприятия и в других областях. знания необходимы перед таким начинанием.

Как всегда, я благодарю вас за чтение и приветствую комментарии и критику ниже.