Проектирование баз данных с помощью Python

Раздел: Базы данных -> Проектирование БД

Создание базы данных в Python: обзор подходов

Как создать базу данных SQLite встроенными средствами Python?

Наиболее эффективное решение для локальных проектов, прототипов и тестирования - использование модуля sqlite3, который входит в стандартную библиотеку Python. Он не требует установки дополнительных серверов и обеспечивает полнофункциональную реляционную БД на основе файла.

import sqlite3

# Создание или подключение к файлу базы данных
conn = sqlite3.connect('example.db')
cursor = conn.cursor()

# Создание таблицы
cursor.execute('''
  CREATE TABLE IF NOT EXISTS users (
    id INTEGER PRIMARY KEY AUTOINCREMENT,
    name TEXT NOT NULL,
    email TEXT UNIQUE NOT NULL,
    created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP
  )
''')

# Вставка данных
cursor.execute('''
  INSERT INTO users (name, email) VALUES (?, ?)
''', ('Alice', 'alice@example.com'))
conn.commit()

# Запрос
cursor.execute('SELECT * FROM users')
print(cursor.fetchall())

conn.close()

Python создание базы (создание базы данных в python)

Пояснение: connect создаёт файл базы, если он отсутствует. Курсор выполняет SQL-команды. Важно закрывать соединение после работы.

Возможные проблемы:

  • Ошибка OperationalError: table already exists - решается использованием IF NOT EXISTS.
  • Проблемы с конкурентым доступом - SQLite допускает только одного пишущего одновременно. Для многопользовательских приложений лучше перейти на клиент-серверные СУБД.
  • Отсутствие прав на запись в директорию - проверьте пути и права файловой системы.

Как использовать SQLAlchemy ORM для создания базы данных?

SQLAlchemy предлагает декларативный способ определения схемы и автоматическое создание таблиц. Это решение подходит для проектов со сложной бизнес-логикой и необходимостью смены СУБД.

from sqlalchemy import create_engine, Column, Integer, String, DateTime
from sqlalchemy.ext.declarative import declarative_base
from sqlalchemy.orm import sessionmaker
from datetime import datetime

Base = declarative_base()

class User(Base):
    __tablename__ = 'users'
    id = Column(Integer, primary_key=True, autoincrement=True)
    name = Column(String, nullable=False)
    email = Column(String, unique=True, nullable=False)
    created_at = Column(DateTime, default=datetime.utcnow)

# Создание двигателя для SQLite
engine = create_engine('sqlite:///orm_example.db', echo=True)

# Генерация таблиц
Base.metadata.create_all(engine)

# Работа с сессией
Session = sessionmaker(bind=engine)
session = Session()
new_user = User(name='Bob', email='bob@example.com')
session.add(new_user)
session.commit()

users = session.query(User).all()
for u in users:
    print(f'{u.id}: {u.name}')
session.close()

Типичные ошибки:

  • Импорт declarative_base из устаревшего модуля - используйте from sqlalchemy.orm import declarative_base (SQLAlchemy 1.4+).
  • Проблемы с изменением схемы после создания таблиц - для миграций применяется Alembic.

Как подключиться к PostgreSQL из Python и создать базу?

Для работы с PostgreSQL используется библиотека psycopg2. Требуется установка: pip install psycopg2-binary.

import psycopg2

conn = psycopg2.connect(
    dbname='mydb',
    user='myuser',
    password='mypassword',
    host='localhost',
    port='5432'
)
cursor = conn.cursor()
cursor.execute('''
  CREATE TABLE IF NOT EXISTS projects (
    id SERIAL PRIMARY KEY,
    name VARCHAR(100) NOT NULL,
    description TEXT
  )
''')
conn.commit()
cursor.close()
conn.close()

Для создания самой базы данных (если она не существует) необходимо сначала подключиться к служебной базе postgres и выполнить CREATE DATABASE.

conn = psycopg2.connect(dbname='postgres', user='myuser', password='mypassword', host='localhost')
conn.autocommit = True
cursor = conn.cursor()
cursor.execute('CREATE DATABASE mydb')
cursor.close()
conn.close()

Распространённые сложности:

  • Ошибка аутентификации - проверьте пароль и права пользователя.
  • Отсутствие сервера PostgreSQL или неправильный хост/порт.
  • Несовместимость версий psycopg2 и PostgreSQL - используйте psycopg2-binary.

Как создать базу данных MySQL через mysql-connector-python?

Для MySQL используется официальный драйвер mysql-connector-python. Установка: pip install mysql-connector-python.

import mysql.connector

conn = mysql.connector.connect(
    host='localhost',
    user='root',
    password='rootpass',
    database='mydb'  # если база уже существует
)
cursor = conn.cursor()
cursor.execute('''
  CREATE TABLE IF NOT EXISTS employees (
    id INT AUTO_INCREMENT PRIMARY KEY,
    name VARCHAR(100) NOT NULL,
    position VARCHAR(100)
  )
''')
conn.commit()
cursor.close()
conn.close()

Если база данных не создана, подключитесь без указания database и выполните CREATE DATABASE mydb.

conn = mysql.connector.connect(host='localhost', user='root', password='rootpass')
cursor = conn.cursor()
cursor.execute('CREATE DATABASE IF NOT EXISTS mydb')
cursor.close()
conn.close()

Типичные ошибки:

  • Ошибка mysql.connector.errors.DatabaseError: 1049 (42000): Unknown database - создайте базу заранее.
  • Проблемы с кодировкой - укажите charset='utf8mb4' при подключении.
  • Необходимость явного коммита для DDL - в mysql-connector DDL не требует commit, но для безопасности можно вызвать.

Расширенные примеры создания и проектирования базы данных

Пример 1: Создание связанных таблиц с внешними ключами (SQLite)

Пример 
import sqlite3

conn = sqlite3.connect('store.db')
cursor = conn.cursor()

# Включаем поддержку внешних ключей
cursor.execute('PRAGMA foreign_keys = ON')

cursor.execute('''
  CREATE TABLE IF NOT EXISTS categories (
    id INTEGER PRIMARY KEY AUTOINCREMENT,
    name TEXT NOT NULL UNIQUE
  )
''')

cursor.execute('''
  CREATE TABLE IF NOT EXISTS products (
    id INTEGER PRIMARY KEY AUTOINCREMENT,
    name TEXT NOT NULL,
    price REAL NOT NULL,
    category_id INTEGER,
    FOREIGN KEY (category_id) REFERENCES categories(id) ON DELETE SET NULL
  )
''')

conn.commit()

# Вставка данных с проверкой целостности
cursor.execute('INSERT INTO categories (name) VALUES (?)', ('Electronics',))
cursor.execute('INSERT INTO products (name, price, category_id) VALUES (?, ?, ?)',
               ('Smartphone', 599.99, 1))
conn.commit()

# Попытка удалить категорию, на которую ссылаются продукты
cursor.execute('DELETE FROM categories WHERE id = 1')
# Внешний ключ сработает: category_id в products станет NULL
conn.commit()

cursor.execute('SELECT * FROM products')
print(cursor.fetchall())
conn.close()
[(1, 'Smartphone', 599.99, None)]

Пояснение: PRAGMA foreign_keys = ON включает каскадные действия. При удалении категории связанные товары не удаляются, а ссылка становится NULL.

Пример 2: Использование SQLAlchemy с миграциями через Alembic

После настройки Alembic (alembic init alembic) можно создать миграцию для добавления таблицы orders:

Пример 
# alembic/versions/0001_create_orders.py
from alembic import op
import sqlalchemy as sa

# revision identifiers, used by Alembic.
revision = '0001'
down_revision = None
branch_labels = None
depends_on = None

def upgrade():
    op.create_table(
        'orders',
        sa.Column('id', sa.Integer(), primary_key=True, autoincrement=True),
        sa.Column('user_id', sa.Integer(), sa.ForeignKey('users.id'), nullable=False),
        sa.Column('total', sa.Float(), nullable=False),
        sa.Column('created_at', sa.DateTime(), server_default=sa.func.now())
    )

def downgrade():
    op.drop_table('orders')

Затем запуск: alembic upgrade head - таблица создаётся. Миграции позволяют версионировать схему.

Пример 3: Транзакции в PostgreSQL с psycopg2

Пример 
import psycopg2

conn = psycopg2.connect(dbname='mydb', user='myuser', password='mypassword', host='localhost')
cursor = conn.cursor()

try:
    cursor.execute('BEGIN')
    cursor.execute('''
        CREATE TABLE IF NOT EXISTS accounts (
            id SERIAL PRIMARY KEY,
            holder VARCHAR(100),
            balance NUMERIC(10,2) DEFAULT 0
        )
    ''')
    cursor.execute('INSERT INTO accounts (holder, balance) VALUES (%s, %s)', ('Alice', 1000))
    cursor.execute('UPDATE accounts SET balance = balance - 100 WHERE holder = %s', ('Alice',))
    conn.commit()
    print('Транзакция успешна')
except Exception as e:
    conn.rollback()
    print(f'Ошибка, откат: {e}')
finally:
    cursor.close()
    conn.close()

Результат: при любой ошибке изменения отменяются. Это гарантирует согласованность данных.

Пример 4: Подключение к MySQL с пулом соединений (mysql.connector.pooling)

Пример 
import mysql.connector
from mysql.connector import pooling

config = {
    'host': 'localhost',
    'user': 'root',
    'password': 'rootpass',
    'database': 'mydb',
    'pool_name': 'mypool',
    'pool_size': 5
}
pool = mysql.connector.pooling.MySQLConnectionPool(**config)
conn = pool.get_connection()
cursor = conn.cursor()
cursor.execute('SELECT COUNT(*) FROM employees')
count = cursor.fetchone()[0]
print(f'Всего записей: {count}')
cursor.close()
conn.close()  # возвращается в пул

Пул соединений эффективен для веб-приложений, избегая частого создания новых подключений.

Создание базы данных в Python - comments

En
Python создание базы (python)