Как работать с sqlite3 python

Библиотека sqlite3 встроена в Python и позволяет создавать легковесные базы данных без установки дополнительных серверов. Для подключения достаточно вызвать sqlite3.connect(‘имя_файла.db’), где файл будет хранить все таблицы и данные в формате SQLite.

Создание таблиц выполняется через метод cursor.execute(), используя стандартный SQL-синтаксис. SQLite поддерживает типы INTEGER, TEXT, REAL, BLOB и NULL. Для начинающих важно понимать, что SQLite не требует явного указания размера строк, а уникальные и первичные ключи можно задать через PRIMARY KEY и UNIQUE.

Добавление данных происходит через INSERT INTO с использованием параметризованных запросов, чтобы избежать ошибок и SQL-инъекций. После внесения изменений необходимо вызвать connection.commit() для сохранения данных. Для чтения применяются SELECT и методы fetchone() или fetchall(), возвращающие строки в виде кортежей.

Для начинающих полезно работать с контекстным менеджером with sqlite3.connect(…) as conn, который автоматически закрывает соединение. Регулярное использование conn.close() и проверка исключений через try-except предотвращают потерю данных и блокировки базы.

Установка и подключение к базе SQLite в Python

SQLite встроена в Python начиная с версии 2.5, установка дополнительных пакетов не требуется. Проверка доступности модуля выполняется командой:

import sqlite3

Создание или подключение к базе данных осуществляется через sqlite3.connect(). Пример:

conn = sqlite3.connect('my_database.db')

Если файла my_database.db нет, SQLite создаст его автоматически. Для выполнения SQL-запросов создается курсор:

cursor = conn.cursor()

Пример создания таблицы для проверки подключения:


cursor.execute('CREATE TABLE IF NOT EXISTS products (id INTEGER PRIMARY KEY, name TEXT, price REAL)')


Изменения сохраняются с помощью метода commit():

conn.commit()

После работы соединение закрывается командой:

conn.close()

Для автоматического управления ресурсами рекомендуется использовать конструкцию with sqlite3.connect('my_database.db') as conn:. Она закрывает соединение при завершении блока, включая случаи возникновения ошибок.

Создание таблиц и определение типов данных

В SQLite таблица создается с помощью команды CREATE TABLE. Название таблицы должно быть уникальным в рамках базы данных и следовать правилам идентификаторов Python, но кавычки позволяют использовать пробелы и специальные символы.

Пример создания таблицы для хранения информации о книгах:

import sqlite3
conn = sqlite3.connect('library.db')
cursor = conn.cursor()
cursor.execute('''
CREATE TABLE books (
id INTEGER PRIMARY KEY,
title TEXT NOT NULL,
author TEXT,
published_year INTEGER,
price REAL
)
''')
conn.commit()
conn.close()

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

• INTEGER – целые числа. Часто используется для идентификаторов (PRIMARY KEY).
• REAL – числа с плавающей точкой. Подходит для хранения цены, рейтингов, измерений.
• TEXT – строки, ограничений на длину нет.
• BLOB – бинарные данные, например, изображения или файлы.
• NUMERIC – гибкий тип для чисел, поддерживает хранение даты и логических значений.

Рекомендации по созданию таблиц:

1. Использовать PRIMARY KEY для уникальной идентификации записей.
2. Применять NOT NULL к полям, обязательным для заполнения.
3. Выбирать тип данных исходя из планируемого использования и объема данных.
4. При необходимости хранить даты и время использовать TEXT или NUMERIC с форматом YYYY-MM-DD.
5. Стараться избегать избыточных столбцов: каждая колонка должна иметь смысл для анализа данных.

SQLite автоматически индексирует поле с PRIMARY KEY, что ускоряет поиск по этому столбцу.

Для изменения таблицы после создания используются команды ALTER TABLE, но они ограничены: добавление нового столбца возможно, удаление или изменение типа требует создания новой таблицы и переноса данных.

Добавление и обновление записей в базе

Для вставки данных в таблицу SQLite в Python используется метод execute() или executemany(). Например, чтобы добавить одну запись в таблицу users с полями id, name и age:

cursor.execute("INSERT INTO users (id, name, age) VALUES (?, ?, ?)", (1, "Иван", 25))

Для добавления нескольких записей сразу рекомендуется executemany():

users_data = [(2, "Анна", 30), (3, "Петр", 28)]
cursor.executemany("INSERT INTO users (id, name, age) VALUES (?, ?, ?)", users_data)

После выполнения вставки необходимо вызвать connection.commit(), чтобы изменения сохранились в базе.

Для обновления данных используется команда UPDATE. Например, чтобы изменить возраст пользователя с именем «Иван»:

cursor.execute("UPDATE users SET age = ? WHERE name = ?", (26, "Иван"))
connection.commit()

Обновление нескольких записей по условию выполняется также через WHERE с логическими операторами:

cursor.execute("UPDATE users SET age = age + 1 WHERE age < 30")
connection.commit()

Для контроля изменений удобно использовать таблицу с демонстрацией до и после обновления:

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

Чтение данных с помощью SELECT и фильтров

Для извлечения данных из таблицы SQLite используется команда SELECT. Базовый синтаксис: SELECT столбец1, столбец2 FROM имя_таблицы;. Если нужны все столбцы, используется SELECT * FROM имя_таблицы;.

Для фильтрации записей применяют ключевое слово WHERE. Пример: SELECT * FROM пользователи WHERE возраст > 18; вернет только пользователей старше 18 лет. Можно комбинировать условия с AND и OR: SELECT имя, email FROM пользователи WHERE возраст > 18 AND город='Москва';.

Для поиска по шаблону применяется оператор LIKE с символами % (любой набор символов) и _ (один символ): SELECT имя FROM пользователи WHERE email LIKE '%@gmail.com'; извлечет все Gmail-адреса.

Для ограничения количества возвращаемых строк используется LIMIT: SELECT * FROM пользователи LIMIT 10; покажет только первые 10 записей.

В Python подключение к базе и чтение данных осуществляется через sqlite3: conn = sqlite3.connect('db.sqlite3'), cursor = conn.cursor(), cursor.execute("SELECT * FROM пользователи WHERE возраст>18"). Результаты извлекаются методами fetchone() для одной строки и fetchall() для всех строк: rows = cursor.fetchall().

Используйте именованные параметры для безопасной подстановки значений и предотвращения SQL-инъекций: cursor.execute("SELECT * FROM пользователи WHERE город=:city", {"city": "Москва"}).

Удаление и модификация данных в таблицах

Для удаления записей в SQLite используется оператор DELETE FROM. Важно всегда указывать условие WHERE, иначе будут удалены все строки таблицы. Например, чтобы удалить пользователя с id = 5:

cursor.execute("DELETE FROM users WHERE id = ?", (5,))

После удаления необходимо зафиксировать изменения с помощью connection.commit(), иначе они не сохранятся.

Модификация данных выполняется через оператор UPDATE. Синтаксис: UPDATE имя_таблицы SET колонка1 = значение1, колонка2 = значение2 WHERE условие. Пример: изменить email пользователя с id = 3:

cursor.execute("UPDATE users SET email = ? WHERE id = ?", ("new_email@example.com", 3))

Для повышения безопасности рекомендуется использовать параметризованные запросы, чтобы исключить SQL-инъекции. При массовом обновлении стоит проверять условие WHERE на корректность, чтобы случайно не изменить лишние строки.

После операций удаления или обновления можно проверить результат через SELECT. Например:

cursor.execute("SELECT * FROM users WHERE id = ?", (3,))
print(cursor.fetchone())

При работе с большими таблицами полезно использовать транзакции: объединение нескольких операций DELETE или UPDATE в одну транзакцию уменьшает нагрузку на диск и ускоряет выполнение.

Для безопасного удаления всех строк без удаления структуры таблицы применяется DELETE FROM table_name без WHERE, или для сброса автоинкремента можно использовать DELETE FROM table_name; VACUUM;.

Использование параметризованных запросов для безопасности

В Python с sqlite3 параметризованные запросы предотвращают SQL-инъекции, отделяя код запроса от данных. Вместо конкатенации строк используйте placeholders – символы вопроса `?` или именованные параметры `:имя`.

Пример безопасного вставления данных:

cursor.execute(«INSERT INTO users (username, password) VALUES (?, ?)», (username, password))

Здесь значения `username` и `password` автоматически экранируются, что исключает выполнение вредоносного SQL-кода.

Для выборки с фильтром применяйте аналогичный подход:

cursor.execute(«SELECT * FROM users WHERE email = ?», (email,))

Важно передавать параметры как кортеж или словарь, не форматируя строки вручную через `+` или `f»»`. Даже если данные кажутся безопасными, ручная вставка открывает путь SQL-инъекциям.

При множественных вставках используйте executemany():

cursor.executemany(«INSERT INTO products (name, price) VALUES (?, ?)», products_list)

Это снижает нагрузку и сохраняет защиту данных. В sqlite3 нет необходимости отдельно экранировать строки – параметризованные запросы делают это автоматически.

Резюме: всегда разделяйте код и данные, используйте `?` или `:имя`, передавайте параметры как кортеж/словарь, применяйте executemany для массовых операций. Это минимизирует риск SQL-инъекций и упрощает поддержку кода.

Сохранение изменений и закрытие соединения с базой

После выполнения операций INSERT, UPDATE или DELETE изменения не сохраняются автоматически. Для фиксации изменений используйте метод commit() объекта соединения: conn.commit(). Это гарантирует, что все изменения будут записаны в файл базы данных и не потеряются при закрытии программы.

Если требуется отменить все изменения, сделанные с последнего коммита, применяйте rollback(): conn.rollback(). Это полезно при обнаружении ошибок в данных перед сохранением.

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

Для безопасной работы рекомендуется оборачивать код взаимодействия с базой в конструкцию try…finally или использовать менеджер контекста with sqlite3.connect('example.db') as conn:, который автоматически выполнит коммит и закроет соединение при выходе из блока.

Пример стандартного подхода:

import sqlite3
conn = sqlite3.connect('example.db')
cursor = conn.cursor()
cursor.execute("INSERT INTO users (name, age) VALUES (?, ?)", ("Иван", 30))
conn.commit()
conn.close()

Такой порядок действий обеспечивает целостность данных и корректное завершение работы с базой SQLite.

Вопрос-ответ:

Что такое sqlite3 и зачем он нужен в Python?

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

Как создать новую базу данных и таблицу в sqlite3?

Для создания базы данных в sqlite3 достаточно подключиться к файлу с помощью функции sqlite3.connect(‘имя_файла.db’). Если файла с таким именем нет, Python создаст его автоматически. После подключения можно использовать объект курсора, созданный методом .cursor(), для выполнения SQL-запросов. Например, чтобы создать таблицу «users» с полями id, name и age, используется команда cursor.execute(»’CREATE TABLE users (id INTEGER PRIMARY KEY, name TEXT, age INTEGER)»’). После выполнения запроса нужно сохранить изменения вызовом connection.commit().

Как вставлять и извлекать данные из таблицы?

Для добавления данных используется метод cursor.execute() с SQL-запросом INSERT INTO. Например: cursor.execute(«INSERT INTO users (name, age) VALUES (?, ?)», («Анна», 25)). Обратите внимание на использование знаков вопроса и кортежа значений — это предотвращает ошибки и повышает безопасность. Для получения данных применяется SELECT: cursor.execute(«SELECT * FROM users») вернет все записи. Чтобы пройтись по результатам, можно использовать метод fetchall() или fetchone(), который возвращает список кортежей с данными.

Как обновлять и удалять записи в sqlite3?

Для изменения данных применяется запрос UPDATE, например: cursor.execute(«UPDATE users SET age = ? WHERE name = ?», (26, «Анна»)). Это изменит возраст конкретного пользователя. Для удаления записей используется DELETE: cursor.execute(«DELETE FROM users WHERE name = ?», («Анна»,)). После выполнения этих запросов нужно вызвать connection.commit(), чтобы изменения сохранились в файле базы данных. Без commit изменения не сохранятся.

Как безопасно работать с параметрами запросов?

Важно не вставлять значения напрямую в строки SQL, чтобы избежать ошибок и атак типа SQL-инъекции. В sqlite3 для этого используются плейсхолдеры ?. Например, cursor.execute(«INSERT INTO users (name, age) VALUES (?, ?)», («Иван», 30)). Такой подход автоматически экранирует специальные символы и делает код безопаснее. Также можно использовать метод executemany() для вставки нескольких записей за один запрос, что упрощает работу с большими объемами данных.