Создаём и наполняем базу данных SQLite в Python
В прошлой статье мы рассказали про 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 для работы с данными и аналитики».
А аналитики данных — новая элита ИТ. Эти люди помогают понять огромные массивы данных и принять правильные решения. Изучите профессию аналитика и начните карьеру в ИТ: старт — бесплатно, а после обучения — помощь с трудоустройством.
В «Яндекс Практикуме» можно стать разработчиком, тестировщиком, аналитиком и менеджером цифровых продуктов. Первая часть обучения всегда бесплатная, чтобы попробовать и найти то, что вам по душе. Дальше — программы трудоустройства.