Создаём и наполняем базу данных SQLite в Python

Создаём и наполняем базу данных SQLite в Python

Ничего сложного, нужен только SQL

В прошлой статье мы рассказали про SQLite — простую базу данных, которая может работать почти на любой платформе. Теперь проверим теорию на практике: напишем простой код на Python, который сделает нам простую базу и наполнит её данными и связями.

Предыстория

Если это первая статья про базы данных, которую вы читаете, то лучше сделать так, а потом вернуться сюда:

  1. Почитать про виды баз данных и посмотреть на схему связей в реляционной базе данных. Там простая схема про магазин — в ней связаны товары, клиенты и покупки.
  2. Посмотреть, как работают SQL-запросы: что это такое, как база на них реагирует и что получается в итоге. В статье мы с помощью SQL-запросов сделали базу данных по магазинной схеме.

Создаём и наполняем базу данных SQLite в Python
Та самая схема базы данных простого магазина

Что будем делать

Сегодня мы сделаем то же самое, что и в 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)

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

Создаём и наполняем базу данных SQLite в Python

Создаём и заполняем таблицу с товарами

Заведём таблицу 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 для работы с данными и аналитики».

Текст:

Михаил Полянин

Редактор:

Максим Ильяхов

Художник:

Алексей Сухов

Корректор:

Ирина Михеева

Вёрстка:

Кирилл Климентьев

Соцсети:

Виталий Вебер

Данные — это новая нефть
А аналитики данных — новая элита ИТ. Эти люди помогают понять огромные массивы данных и принять правильные решения. Изучите профессию аналитика и начните карьеру в ИТ: старт — бесплатно, а после обучения — помощь с трудоустройством.
Начать качать
Данные — это новая нефть Данные — это новая нефть Данные — это новая нефть Данные — это новая нефть
Получите ИТ-профессию
В «Яндекс Практикуме» можно стать разработчиком, тестировщиком, аналитиком и менеджером цифровых продуктов. Первая часть обучения всегда бесплатная, чтобы попробовать и найти то, что вам по душе. Дальше — программы трудоустройства.
Начать карьеру в ИТ
Получите ИТ-профессию Получите ИТ-профессию Получите ИТ-профессию Получите ИТ-профессию
Еще по теме
Что такое обратная матрица
Что такое обратная матрица

Сложная тема из линейной алгебры.

hard
Что такое порт в программировании
Что такое порт в программировании

С ними компьютер не путает, что и куда отправлять

easy
Как работают алгоритмы кодирования в радиосвязи
Как работают алгоритмы кодирования в радиосвязи

Как вообще это возможно — что по воздуху к нам прилетает видео?

medium
Синхронизация: как это делают крутые ребята
Синхронизация: как это делают крутые ребята

Какая бывает синхронизация данных и от чего это зависит

medium
Новые чудеса оптимизации: как делали игру «Принц Персии»
Новые чудеса оптимизации: как делали игру «Принц Персии»

Как сделать плавную анимацию, если доступно всего несколько кадров

easy
Что такое экстремальное программирование
Что такое экстремальное программирование

Практика для сильных

easy
Устанавливаем Вордпресс в Docker
Устанавливаем Вордпресс в Docker

Это быстрее и проще, чем кажется.

medium
Flipper Zero: как устроен хакерский тамагочи
Flipper Zero: как устроен хакерский тамагочи

И сколько на нём можно заработать.

easy
SSL-сертификаты и безопасный интернет

Ваше соединение надёжно защищено. Или нет.

easy
«Я нашла работу в Альфа-Банке за месяц до окончания учёбы»
«Я нашла работу в Альфа-Банке за месяц до окончания учёбы»

Выпускница Практикума Ника Малинина о профессии инженера по тестированию

easy
easy