Работа с базами данных: Python Sqlite, примеры

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

Погрузимся в мир Python и SQLite

Python может работать со всеми распространенными СУБД (системами управления базами данных):

• Реляционными (SQL)
• Нереляционными (NoSQL)
• Объектно-ориентированными
• Объектно-реляционными
• Резидентными
• Столбцовыми
• Ключ-значение

В этой статье мы будем изучать приемы работы с реляционной СУБД SQLite, которая поставляется с Python. Еще мы рассмотримбазы типа «ключ-значение», которые отлично подходят для хранения данных в простых приложениях.

Что такое SQL, СУБД, SQLite и ORM

SQL(Structured Query Language) – это специальный язык запросов, который используется для создания, изменения и выборки данных в реляционных базах данных, управляемых определенной СУБД.

Система управления базами данных(СУБД) – это программное обеспечение, которое позволяет сохранять, организовывать, изменять, получать и анализировать данные. Обычно СУБД выполняют следующие функции:

• Создание и управление структурой данных (таблицы, индексы, ограничения и т.д.)
• Хранение данных на диске или в памяти.
• Поиск и выборка нужных данных с помощью запросов.
• Манипулирование данными (добавление, изменение, удаление).
• Обеспечение сохранности и целостности данных (транзакции, резервное копирование, восстановление).
• Обеспечение доступности и безопасности данных (права доступа, шифрование и т.д.)

Реляционные СУБД используют язык SQL для выполнения запросов к данным и управления ими. Данные в реляционных базах хранятся в виде таблиц: каждая строка представляет собой отдельную запись, а каждый столбец – отдельное поле данных.

Надо заметить, что язык SQL – не единственный способ создавать запросы к базе данных: при создании веб-приложений на базе фреймворков Django и Flask разработчики обычно используют ORM.

Установка и настройка

Для работы с базами данных SQLite в Python нужно установить стандартный модуль sqlite3. Он входит в состав библиотеки Python по умолчанию, поэтому отдельно устанавливать ничего не нужно.

Чтобы подключиться к базе данных SQLite, используем метод connect() модуля sqlite3:

 import sqlite3 connection = sqlite3.connect('database.db') 

Это создаст подключение к базе данных database.db. Если такая база не существует, она будет автоматически создана в текущем каталоге. Также можно указать :memory: для создания базы данных в оперативной памяти.

Для удобной работы с SQLite из командной строки можно использовать утилиту sqlite3, которая также входит в состав Python:

 $ sqlite3 test.db SQLite version 3.7.15.2 2014-08-15 11:53:05 Enter ".help" for instructions Enter SQL statements terminated with a ";" sqlite> 

Это откроет подключение к базе данных test.db и запустит интерактивный режим SQL.

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

Чтобы создать таблицу в базе данных SQLite, используем оператор CREATE TABLE. Укажем имена и типы столбцов:

 CREATE TABLE users ( id INTEGER PRIMARY KEY, name TEXT NOT NULL, email TEXT UNIQUE, age INTEGER ); 

Это создаст таблицу users со столбцами id, name, email и age. Для столбца id указано свойство PRIMARY KEY - первичный ключ, для name указано NOT NULL - значение не может быть пустым, а для email UNIQUE - значение должно быть уникальным.

Основные типы данных в SQLite:

• INTEGER - целые числа
• TEXT - строки
• BLOB - двоичные данные
• REAL - вещественные числа
• NUMERIC - числовые с определенной точностью

Чтобы изменить существующую таблицу, используем ALTER TABLE. Например, добавим столбец registration_date:

 ALTER TABLE users ADD COLUMN registration_date TEXT; 

Для удаления таблицы применяется оператор DROP TABLE:

 DROP TABLE users; 

Вставка, обновление и удаление данных

Для вставки данных в таблицу используется оператор INSERT INTO. Укажем значения для каждого столбца:

 INSERT INTO users (id, name, email, age) VALUES (1, 'John Doe', 'john@doe.com', 30); 

Это добавит новую запись в таблицу users.

Для обновления данных в таблице применяется оператор UPDATE. Например, изменим возраст пользователя:

 UPDATE users SET age = 35 WHERE id = 1; 

Здесь важно указать условие WHERE, чтобы изменить данные только для нужного пользователя.

Для удаления записей используется DELETE FROM. Удалим пользователя:

 DELETE FROM users WHERE id = 1; 

Опять же, важно указать условие WHERE, чтобы удалить только нужную запись.

Выполнение запросов SELECT

Для извлечения данных из таблиц применяются SQL-запросы с оператором SELECT. Например, получим все данные о пользователях:

 SELECT * FROM users; 

Здесь звездочка означает "все столбцы". Также можно указать конкретные столбцы:

 SELECT name, email FROM users; 

Для фильтрации результатов используется условие WHERE. Например, отберем пользователей старше 30 лет:

 SELECT * FROM users WHERE age > 30; 

Для сортировки данных применяется ORDER BY. Сортируем по имени:

 SELECT * FROM users ORDER BY name; 

Можно объединять таблицы с помощью JOIN. Например, объединим пользователей и заказы:

 SELECT * FROM users JOIN orders ON users.id = orders.user_id; 

Это основы запросов SELECT. Их можно комбинировать и создавать очень сложные запросы к данным.

Обработка результатов запросов

Результаты запросов SELECT возвращаются в виде набора кортежей. Например:

 import sqlite3 conn = sqlite3.connect('database.db') cursor = conn.cursor() cursor.execute('SELECT * FROM users') results = cursor.fetchall() print(results) # [(1, 'John Doe', 'john@doe.com', 35), # (2, 'Jane Doe', 'jane@doe.com', 28)] 

Метод fetchall() возвращает список кортежей. Также есть fetchone() для получения одной строки и fetchmany() для нескольких.

Часто удобнее преобразовать результаты в словари:

 results = [] for row in cursor: results.append({ 'id': row[0], 'name': row[1], 'email': row[2], 'age': row[3] }) print(results) # [{'id': 1, 'name': 'John Doe', 'email': 'john@doe.com', 'age': 35}, # {'id': 2, 'name': 'Jane Doe', 'email': 'jane@doe.com', 'age': 28}] 

Так результаты можно легко обрабатывать в коде на Python.

При работе с запросами важно правильно обрабатывать значение NULL, которое означает отсутствие данных. Для проверки на NULL используются операторы IS NULL и IS NOT NULL.

Транзакции и управление данными

Транзакции позволяют группировать несколько операций с данными, чтобы гарантировать их атомарное выполнение. Либо транзакция выполняется полностью - либо не выполняется вовсе.

В SQLite транзакции можно управлять вручную с помощью операторов BEGIN TRANSACTION, COMMIT и ROLLBACK:

 BEGIN TRANSACTION; UPDATE users SET age = 36 WHERE id = 1; UPDATE users SET age = 29 WHERE id = 2; COMMIT; 

Но лучше использовать контекстные менеджеры в Python, чтобы не забывать завершать транзакции:

 import sqlite3 conn = sqlite3.connect('db.sqlite') with conn: conn.execute('UPDATE users SET age = 36 WHERE id = 1') conn.execute('UPDATE users SET age = 29 WHERE id = 2') 

При выходе из блока with транзакция будет зафиксирована автоматически.

Асинхронная работа с SQLite

Модуль async sqlite python позволяет выполнять операции с базой данных SQLite асинхронно, без блокировки основного потока.

Например:

 import aiosqlite async def execute_queries(db): async with db.execute('SELECT * FROM users') as cursor: async for row in cursor: print(row) await db.execute('INSERT INTO users VALUES (?, ?)', (3, 'Bob')) async def main(): async with aiosqlite.connect('database.db') as db: await execute_queries(db) asyncio.run(main()) 

Это позволяет выполнять операции с БД параллельно с другими задачами.

SQLite в Django

Фреймворк python django sqlite windows использует SQLite в качестве базы данных по умолчанию. Это удобно для разработки.

Чтобы подключить SQLite к проекту Django, нужно указать:

 DATABASES = { 'default': { 'ENGINE': 'django.db.backends.sqlite3', 'NAME': BASE_DIR / 'db.sqlite3', } } 

После этого Django автоматически будет использовать эту базу данных и создавать миграции.

Оптимизация запросов

Для оптимизации запросов к SQLite можно использовать:

• Индексы для ускорения поиска данных
• Представления для упрощения сложных запросов
• Подготовленные операторы для многократного выполнения
• Анализ запросов с EXPLAIN

Например, индекс позволит быстрее находить пользователей по email:

 CREATE INDEX idx_email ON users(email); 

А представления скроют сложность запросов с JOIN:

 CREATE VIEW v_users_orders AS SELECT * FROM users JOIN orders ON users.id = orders.user_id; 

Что нам дало изучение работы с базами данных

Теперь, когда мы знаем, как работать с SQLite в Python, можно использовать эту базу данных в более серьезных проектах:

• хранить результаты парсинга;
• запоминать отсортированные датасеты;
• вести учет пользователей и их действий в системе.

Близкое взаимодействие с базами данных неразрывно связано с SQL (Structured Query Language). С помощью него можно не только создать таблицу и заполнить ее уникальной информацией, но и «вытащить» из базы практически любую информацию, используя специальные запросы.