В прошлой статье мы рассказали про SQLite — простую базу данных, которая может работать почти на любой платформе. Теперь проверим теорию на практике: напишем простой код на Python, который сделает нам простую базу и наполнит её данными и связями.
Предыстория
Если это первая статья про базы данных, которую вы читаете, то лучше сделать так, а потом вернуться сюда:
- Почитать про виды баз данных и посмотреть на схему связей в реляционной базе данных. Там простая схема про магазин — в ней связаны товары, клиенты и покупки.
- Посмотреть, как работают SQL-запросы: что это такое, как база на них реагирует и что получается в итоге. В статье мы с помощью SQL-запросов сделали базу данных по магазинной схеме.
Что будем делать
Сегодня мы сделаем то же самое, что и в SQL-запросах, но на Python, используя стандартную библиотеку sqlite3:
- создадим базу и таблицы в ней;
- наполним их данными;
- создадим связи;
- проверим, как это работает.
После этого мы сможем использовать такой же подход в других проектах и хранить все данные не в текстовых файлах, а в полноценной базе данных.
Подключаем и создаём базу данных
За работу с SQLite в Python отвечает стандартная библиотека sqlite3:
# подключаем SQLite
import sqlite3 as sl
Теперь нам нужно указать файл базы данных, с которым мы будем дальше работать. Удобство библиотеки в том, что нам достаточно указать имя файла, а дальше будет такое:
- если этого файла нет, то программа создаст пустую базу данных с таким именем;
- если указанный файл есть, то программа подключится к нему и будет с ним работать.
Получается, нам неважно, есть файл с базой или нет — мы в любом случае после запуска получим то, что нам нужно. Для этого пишем команду:
# открываем файл с базой данных
con = sl.connect('thecode.db')
Мы указали, что файл называется thecode.db, без указания папок и дисков. Это значит, что файл с базой появится в той же папке, что и наш скрипт — можно в этом убедиться после запуска программы.
Создаём таблицу с товарами
У нас есть база, в которой можно создавать таблицы для хранения данных. Создадим первую таблицу для товаров:
with con:
con.execute("""
CREATE TABLE goods (
product VARCHAR(20) PRIMARY KEY,
count INTEGER,
price INTEGER
);
""")Если посмотреть внимательно на код, можно заметить, что текст внутри кавычек полностью повторяет обычный SQL-запрос, который мы уже использовали в прошлой статье. Единственное отличие — в SQLite используется INTEGER вместо INT:
CREATE TABLE goods (
product VARCHAR(20) PRIMARY KEY,
count INT,
price INT
);Теперь соберём код вместе и запустим его ещё раз:
# подключаем SQLite
import sqlite3 as sl
# открываем файл с базой данных
con = sl.connect('thecode.db')
# создаём таблицу для товаров
with con:
con.execute("""
CREATE TABLE goods (
product VARCHAR(20) PRIMARY KEY,
count INTEGER,
price INTEGER
);
""")Но после второго запуска компьютер почему-то выдаёт ошибку:
❌ sqlite3.OperationalError: table goods already exists
Дело в том, что при повторном запуске программа пытается создать таблицу с товарами, которая уже есть в базе. Так как имена таблиц совпадают, а двух одинаковых имён быть не может, отсюда и возникает ошибка.
Чтобы не попадать в такую ситуацию, добавим проверку: посмотрим, есть ли в базе нужная нам таблица или нет. Если нет — создаём, если есть — двигаемся дальше:
# открываем базу
with con:
# получаем количество таблиц с нужным нам именем
data = con.execute("select count(*) from sqlite_master where type='table' and name='goods'")
for row in data:
# если таких таблиц нет
if row[0] == 0:
# создаём таблицу для товаров
with con:
con.execute("""
CREATE TABLE goods (
product VARCHAR(20) PRIMARY KEY,
count INTEGER,
price INTEGER
);
""")Точно так же мы потом сделаем и с остальными таблицами — сразу встроим проверку, и если нужных таблиц не будет, то программа создаст их автоматически.
Теперь наполняем нашу таблицу товарами, используя стандартный SQL-запрос. Например, можно добавить два стола, которые стоят по 3000 ₽:
INSERT INTO goods SET
product = 'стол',
count = 2,
price = 3000;
Но добавлять записи по одному товару за раз — это долго и неэффективно. Проще сразу в одном запросе добавить все нужные товары: стол, стул и табурет:
# подготавливаем множественный запрос
sql = 'INSERT INTO goods (product, count, price) values(?, ?, ?)'
# указываем данные для запроса
data = [
('стол', 2, 3000),
('стул', 5, 1000),
('табурет', 1, 500)
]
# добавляем с помощью множественного запроса все данные сразу
with con:
con.executemany(sql, data)
# выводим содержимое таблицы на экран
with con:
data = con.execute("SELECT * FROM goods")
for row in data:
print(row)В конце мы добавили вывод таблицы — так можно убедиться, что запрос сработал и данные отправились в базу в нужное место.
Создаём и заполняем таблицу с товарами
Заведём таблицу clients для клиентов и заполним её точно так же, как мы это сделали с клиентской таблицей. Для этого просто копируем предыдущий код, меняем название таблицы и указываем правильные названия полей.Ещё посмотрите на отличие от обычного SQL в последней строке объявления полей таблицы: вместо id INT AUTO_INCREMENT PRIMARY KEY надо указать id INTEGER PRIMARY KEY. Без этого не будет работать автоувеличение счётчика.
# --- создаём таблицу с клиентами ---
# открываем базу
with con:
# получаем количество таблиц с нужным нам именем — clients
data = con.execute("select count(*) from sqlite_master where type='table' and name='clients'")
for row in data:
# если таких таблиц нет
if row[0] == 0:
# создаём таблицу для клиентов
with con:
con.execute("""
CREATE TABLE clients (
name VARCHAR(40),
phone VARCHAR(10) UNIQUE,
id INTEGER PRIMARY KEY
);
""")
# подготавливаем множественный запрос
sql = 'INSERT INTO clients (name, phone) values(?, ?)'
# указываем данные для запроса
data = [
('Миша', 9208381096),
('Наташа', 9307265198),
('Саша', 9307281096)
]
# добавляем с помощью множественного запроса все данные сразу
with con:
con.executemany(sql, data)
# выводим содержимое таблицы с клиентами на экран
with con:
data = con.execute("SELECT * FROM clients")
for row in data:
print(row)Cоздаём таблицу с покупками и связываем всё вместе
У нас всё готово для того, чтобы на основе первых двух таблиц создать третью — в ней будут данные сразу и о покупках, и о том, кто это купил. Если интересно, как это работает в деталях, — почитайте статью про связи в базе данных.
# --- создаём таблицу с покупками ---
# открываем базу
with con:
# получаем количество таблиц с нужным нам именем — orders
data = con.execute("select count(*) from sqlite_master where type='table' and name='orders'")
for row in data:
# если таких таблиц нет
if row[0] == 0:
# создаём таблицу для покупок
with con:
con.execute("""
CREATE TABLE orders (
order_id INTEGER PRIMARY KEY,
product VARCHAR,
amount INTEGER,
client_id INTEGER,
FOREIGN KEY (product) REFERENCES goods(product),
FOREIGN KEY (client_id) REFERENCES clients(id)
);
""")Проверим, что связь работает: добавим в таблицу с заказами запись о том, что Миша купил 2 табурета:
# подготавливаем запрос
sql = 'INSERT INTO orders (product, amount, client_id) values(?, ?, ?)'
# указываем данные для запроса
data = [
('табурет', 2, 1)
]
# добавляем запись в таблицу
with con:
con.executemany(sql, data)
# выводим содержимое таблицы с покупками на экран
with con:
data = con.execute("SELECT * FROM orders")
for row in data:
print(row)Компьютер выдал строку (1, 'табурет', 2, 1), значит, таблицы связались правильно.
Что дальше
Теперь, когда мы знаем, как работать с SQLite в Python, можно использовать эту базу данных в более серьёзных проектах:
- хранить результаты парсинга;
- запоминать отсортированные датасеты;
- вести учёт пользователей и их действий в системе.
Подпишитесь, чтобы не пропустить продолжение про SQLite. А если вам интересна аналитика и работа с данными, приходите на курс «SQL для работы с данными и аналитики».
