Создание таблиц в SQLite в Python

Создание таблицы в SQLite с помощью Python достигается с помощью стандартного SQL-запроса CREATE TABLE. Но прежде чем мы перейдем к этому, давайте рассмотрим базовые шаги по подключению к базе данных и созданию курсора для выполнения запросов.

Подключение к базе данных

import sqlite3

# Подключаемся к базе данных (если файла нет, он будет создан)
conn = sqlite3.connect('my_database.db')

# Создаем курсор для выполнения SQL-запросов
cursor = conn.cursor()

Создание таблицы

Рассмотрим пример создания таблицы users:

cursor.execute('''
CREATE TABLE users (
    id INTEGER PRIMARY KEY,
    name TEXT NOT NULL,
    age INTEGER,
    email TEXT UNIQUE
)
''')

В данном примере:

id — это уникальный идентификатор для каждого пользователя, который автоматически инкрементируется.
name — это текстовое поле, которое не может быть NULL.
age — числовое поле для возраста пользователя.
email — текстовое поле для электронной почты, которое должно быть уникальным для каждого пользователя.

Дополнительные параметры и типы данных

SQLite — это уникальная реляционная база данных с упрощенной структурой типов данных по сравнению с большинством других баз данных, таких как MySQL или PostgreSQL. Рассмотрим основные типы данных и некоторые дополнительные параметры.

Типы данных

NULL - Значение NULL.
INTEGER - Целое число. Занимает от 1 до 8 байтов в зависимости от значения.
REAL - Число с плавающей запятой. Занимает 8 байтов.
TEXT - Строка текста. Хранится используя кодировку UTF-8, UTF-16BE или UTF-16LE.
BLOB - Двоичные данные. Хранится в том же формате, что и введен.

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

Дополнительные параметры столбцов

PRIMARY KEY - Этот столбец становится уникальным идентификатором для строки. В SQLite столбец с типом INTEGER PRIMARY KEY автоматически становится автоинкрементным.
NOT NULL - Столбец не может содержать значения NULL.
UNIQUE - Все значения в этом столбце должны быть уникальными.
DEFAULT - Значение по умолчанию для столбца.
CHECK (expression) - Ограничение, которое обеспечивает выполнение определенного условия для значений столбца.
FOREIGN KEY - Ссылка на другую таблицу. Используется для создания отношений между таблицами.

Некоторые дополнительные возможности

AUTOINCREMENT: Хотя в SQLite нет явного ключевого слова AUTOINCREMENT как в некоторых других базах данных, столбец, определенный как INTEGER PRIMARY KEY, автоматически будет автоинкрементным.
WITHOUT ROWID: По умолчанию каждая таблица в SQLite имеет специальный столбец ROWID, который уникален для каждой строки. Однако, если вы хотите оптимизировать свою таблицу и избавиться от этого ROWID (например, если у вас уже есть другой столбец, который действует как уникальный идентификатор), вы можете создать таблицу с ключевым словом WITHOUT ROWID.

Важно отметить, что из-за динамической типизации SQLite автоматически преобразует типы данных при необходимости, исходя из контекста операции. Например, если вы попытаетесь вставить текстовую строку в столбец, определенный как INTEGER, SQLite попытается преобразовать эту строку в число. Если преобразование невозможно, значение сохраняется как текст.

Создание таблицы с внешним ключом

Рассмотрим создание двух связанных таблиц - authors и books:

# Создание таблицы authors
cursor.execute('''
CREATE TABLE authors (
    author_id INTEGER PRIMARY KEY,
    name TEXT NOT NULL
)
''')

# Создание таблицы books
cursor.execute('''
CREATE TABLE books (
    book_id INTEGER PRIMARY KEY,
    title TEXT NOT NULL,
    author_id INTEGER,
    FOREIGN KEY (author_id) REFERENCES authors(author_id)
)
''')

Здесь author_id в таблице books является внешним ключом, который ссылается на author_id в таблице authors.

После создания таблицы или выполнения любых других операций изменения, рекомендуется сохранить изменения, вызвав commit():

conn.commit()

И, конечно, не забудьте закрыть соединение:

conn.close()

Дополнительно

В дополнение к уже представленной информации, стоит упомянуть несколько моментов:

CHECK-ограничение

Вы можете добавить ограничение CHECK в таблицу для обеспечения выполнения определенного условия. Например, если вы хотите, чтобы возраст пользователя был больше 0 и меньше 150:

cursor.execute('''
CREATE TABLE users (
    id INTEGER PRIMARY KEY,
    name TEXT NOT NULL,
    age INTEGER CHECK(age > 0 AND age < 150)
)
''')

DEFAULT-значение

Можно задать значение по умолчанию для столбца:

cursor.execute('''
CREATE TABLE users (
    id INTEGER PRIMARY KEY,
    name TEXT NOT NULL,
    age INTEGER DEFAULT 18
)
''')

Теперь, если при вставке записи не указан возраст, он автоматически установится в 18.

Индексы

Для ускорения операций поиска вы можете создавать индексы. Например, если вы часто ищете пользователей по их электронной почте:

cursor.execute("CREATE INDEX idx_email ON users(email)")

Безопасность и SQL-инъекции

Всегда используйте параметризованные запросы (как показано в предыдущих примерах с "?"), чтобы избежать SQL-инъекций. Не формируйте запросы, вставляя переменные напрямую в строку SQL.

Схема БД

Вы можете проверить структуру вашей базы данных, используя следующий запрос:

cursor.execute("PRAGMA table_info(users)")
print(cursor.fetchall())

Изменение структуры таблиц

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

Оптимизация и VACUUM

После удаления большого количества данных, база данных SQLite может занимать больше места на диске, чем требуется. Чтобы освободить это место, используйте команду VACUUM:

cursor.execute("VACUUM")

Заключение

Создание таблиц — это основа работы с реляционной базой данных, и понимание основных типов данных и параметров SQLite позволит создавать структурированные и оптимизированные таблицы для различных нужд. Важно помнить о безопасности и избегать потенциальных SQL-инъекций, используя параметризованные запросы.

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

Подпишись на наш telegram-канал FullStacker и получай свежие статьи, мануалы и шпаргалки по Python первым

Содержание:

Читайте в Telegram