Как создать таблицу в базе данных при помощи Python

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

Пример 1. Создание таблицы с типом JSON в PostgreSQL

PostgreSQL поддерживает нативный JSON. Это удобно для хранения гибких данных:

import psycopg2

conn = psycopg2.connect('dbname=test')
cursor = conn.cursor()
cursor.execute('''
    CREATE TABLE logs (
        id SERIAL PRIMARY KEY,
        event_time TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
        payload JSONB NOT NULL
    )
''')
conn.commit()
cursor.execute("INSERT INTO logs (payload) VALUES ('{"action": "login", "user": 42}'::jsonb)")
cursor.execute('SELECT * FROM logs')
print(cursor.fetchall())
conn.close()

[(1, datetime.datetime(2025, 3, 24, 12, 0, 0), {'action': 'login', 'user': 42})]

Пояснение:

Тип JSONB позволяет индексировать и эффективно искать внутри JSON. В SQLite тоже есть JSON-функции, но отдельного типа нет, используется TEXT.

Пример 2. Таблица с наследованием в PostgreSQL

Наследование таблиц удобно при реализации общих полей:

cursor.execute('''
    CREATE TABLE person (
        id SERIAL PRIMARY KEY,
        name TEXT NOT NULL,
        age INTEGER
    )
''')
cursor.execute('''
    CREATE TABLE employee (
        salary NUMERIC,
        department TEXT
    ) INHERITS (person)
''')
cursor.execute("INSERT INTO employee (name, age, salary, department) VALUES ('Alice', 30, 70000, 'IT')")
cursor.execute('SELECT * FROM only person')  -- только person
cursor.execute('SELECT * FROM employee')     -- все поля person + employee
conn.commit()

# Строк из employee, но не из person
(1, 'Alice', 30, 70000.0, 'IT')

Пояснение:

Наследование создаёт неявные связи. Таблица-потомок содержит все столбцы родителя. Запрос к родителю не видит строки потомка (если не использовать ONLY). Наследование редко применяется, так как усложняет работу с внешними ключами.

Пример 3. Создание таблицы с автоматическим обновлением временной метки в PostgreSQL

Можно использовать триггер для автоматического обновления updated_at:

cursor.execute('''
    CREATE TABLE articles (
        id SERIAL PRIMARY KEY,
        title TEXT NOT NULL,
        content TEXT,
        created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
        updated_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP
    )
''')
cursor.execute('''
    CREATE OR REPLACE FUNCTION update_updated_at()
    RETURNS TRIGGER AS $$
    BEGIN
        NEW.updated_at = CURRENT_TIMESTAMP;
        RETURN NEW;
    END;
    $$ LANGUAGE plpgsql
''')
cursor.execute('''
    CREATE TRIGGER trg_articles_updated
    BEFORE UPDATE ON articles
    FOR EACH ROW EXECUTE FUNCTION update_updated_at()
''')
conn.commit()

Пояснение:

В SQLite можно использовать DEFAULT с datetime('now'), но автоматическое обновление при изменении строки требует триггера.

Пример 4. Создание таблицы из Pandas DataFrame (SQLite)

Pandas упрощает перенос данных, но не всегда создаёт оптимальные типы:

import pandas as pd
import sqlite3

df = pd.DataFrame({
    'product': ['A', 'B'],
    'price': [100, 200]
})
conn = sqlite3.connect('pandas_example.db')
df.to_sql('products', conn, if_exists='replace', index=False)
cursor = conn.cursor()
cursor.execute('SELECT * FROM products')
print(cursor.fetchall())
conn.close()

[('A', 100.0), ('B', 200.0)]

Пояснение:

Метод to_sql автоматически создаёт таблицу и определяет типы столбцов на основе типов Pandas. Для тонкой настройки типов лучше использовать sqlalchemy или сырой SQL.

Пример 5. Создание таблицы с полнотекстовым поиском (FTS) в SQLite

SQLite поддерживает виртуальные таблицы FTS5 для быстрого поиска:

import sqlite3

conn = sqlite3.connect('fts_example.db')
cursor = conn.cursor()
cursor.execute('''
    CREATE VIRTUAL TABLE documents USING fts5(title, content)
''')
cursor.execute("INSERT INTO documents VALUES ('Python SQL', 'Learning SQL with Python')")
cursor.execute("SELECT * FROM documents WHERE documents MATCH 'SQL'")
print(cursor.fetchall())
conn.close()

[(1, 'Python SQL', 'Learning SQL with Python')]

Пояснение:

FTS5 создаёт инвертированный индекс. Можно использовать оператор MATCH. В PostgreSQL для полнотекстового поиска применяются типы tsvector и tsquery.