Переход с SQLite на Microsoft SQL с помощью mssql-python

SQLite является базой данных по умолчанию для многих проектов на Python, FastAPI и приложений Flask. Это позволяет быстро создать приложение, откладывая решение по платформе данных на более поздний срок. Когда приходит время запускать приложение в продакшн, вам нужна поддержка для одновременных пользователей, ролевая безопасность, высокая доступность, восстановление после аварий и другие корпоративные функции. Вам нужно перейти на Microsoft SQL с помощью драйвера mssql-python.

Различия в диалектах SQL

При переходе с SQLite на Microsoft SQL необходимо учесть два аспекта: переписать SQL-выражения для Transact-SQL (T-SQL) и перенести данные.

Следующая таблица сопоставляет распространённые шаблоны SQLite с их аналогами Microsoft SQL:

SQLite SQL Server (T-SQL) Примечания.
INTEGER PRIMARY KEY AUTOINCREMENT int IDENTITY(1,1) PRIMARY KEY Microsoft SQL использует IDENTITY для автоинкремента.
TEXT nvarchar(255) или nvarchar(max) Всегда указывайте длину. Используйте nvarchar для Unicode.
REAL float или decimal(18,2) Используйте decimal для точных значений, например, денежных сумм.
BLOB varbinary(max) То же самое поведение, но другое имя.
BOOLEAN (хранится как целое число) bit Ни одна из баз данных не имеет родного булевого значения. Оба хранят 0/1.
DATETIME('now') GETDATE() или SYSDATETIME() SYSDATETIME() даёт большую точность.
LIMIT 10 OFFSET 20 OFFSET 20 ROWS FETCH NEXT 10 ROWS ONLY Требуется условие ORDER BY.
\|\| (конкатенация строк) + или CONCAT() CONCAT() Обрабатывает NULL значения.
IFNULL(a, b) ISNULL(a, b) или COALESCE(a, b) COALESCE является стандартом ANSI.
GROUP_CONCAT(col) STRING_AGG(col, ',') Доступно в SQL Server 2017+.
INSERT OR REPLACE INTO заявление MERGE SQLite удаляет и повторно вставляет; MERGE обновляет на месте. См. следующий пример.
last_insert_rowid() OUTPUT INSERTED.id Используйте OUTPUT в заявлении INSERT . SCOPE_IDENTITY() Тоже работает, но требует отдельного SELECT.
typeof(x) SQL_VARIANT_PROPERTY(x, 'BaseType') Редко нужна при строгом наборе текста.

CREATE TABLE пример

-- SQLite
CREATE TABLE IF NOT EXISTS products (
    id INTEGER PRIMARY KEY AUTOINCREMENT,
    name TEXT NOT NULL,
    price REAL DEFAULT 0.0,
    created_at TEXT DEFAULT (datetime('now')),
    is_active BOOLEAN DEFAULT 1
);

-- SQL Server
IF NOT EXISTS (SELECT * FROM sys.tables WHERE name = 'products')
CREATE TABLE products (
    id int IDENTITY(1,1) PRIMARY KEY,
    name nvarchar(100) NOT NULL,
    price decimal(10,2) DEFAULT 0.0,
    created_at datetime2 DEFAULT SYSDATETIME(),
    is_active bit DEFAULT 1
);

Примеры запросов

Пагинация:

SQLite:

cursor.execute("SELECT * FROM products ORDER BY name LIMIT ? OFFSET ?", (10, 20))

mssql-python:

cursor.execute(
    "SELECT * FROM Production.Product ORDER BY Name OFFSET ? ROWS FETCH NEXT ? ROWS ONLY",
    (20, 10)
)

Порядок параметров обратный. Transact-SQL ставит OFFSET перед FETCH NEXT.

Upsert (вставка или обновление):

SQLite:

cursor.execute("""
    INSERT OR REPLACE INTO settings (key, value)
    VALUES (?, ?)
""", (key, value))

mssql-python:

cursor.execute("""
    MERGE #Settings AS target
    USING (SELECT ? AS [key], ? AS value) AS source
    ON target.[key] = source.[key]
    WHEN MATCHED THEN UPDATE SET value = source.value
    WHEN NOT MATCHED THEN INSERT ([key], value) VALUES (source.[key], source.value);
""", (key, value))

Предложение USING определяет source.[key] и source.value как псевдонимы столбцов. Пункты WHEN ссылаются на эти псевдонимы. Нужны только два маркера ?.

Последний вставленный идентификатор:

SQLite:

cursor.execute("INSERT INTO products (name) VALUES (?)", ("Widget",))
product_id = cursor.lastrowid

mssql-python:

cursor.execute(
    "INSERT INTO #Products (Name) OUTPUT INSERTED.ProductID VALUES (?)",
    ("Widget",)
)
product_id = cursor.fetchval()

Обновить код соединения

Замените sqlite3.connect() на mssql_python.connect():

SQLite:

import sqlite3

def get_connection():
    conn = sqlite3.connect("myapp.db")
    conn.row_factory = sqlite3.Row
    return conn

mssql-python:

import mssql_python

def get_connection():
    return mssql_python.connect(
        "Server=<server>.database.windows.net;"
        "Database=<database>;"
        "Authentication=ActiveDirectoryDefault;"
        "Encrypt=yes;"
    )

Доступ к строкам осуществляется аналогично. Фабрика Row в SQLite возвращает строки, подобные словарям, с синтаксисом row["column"]. Драйвер mssql-python возвращает Row объекты, поддерживающие тот же доступ к строковому ключу, а также доступ к атрибутам и индексам:

SQLite (с row_factory):

row["name"]

mssql-python:

row["name"]   # String-key access, like SQLite
row.name      # Attribute access
row[0]        # Index access

Стиль обновления параметров

И SQLite, и mssql-python ? используются в качестве маркера параметров, поэтому большинство запросов работают без изменений.

# Works with both sqlite3 and mssql-python
cursor.execute("SELECT * FROM Production.Product WHERE Name = ?", ("Adjustable Race",))

Единственное отличие: SQLite позволяет использовать именованные параметры с :name синтаксисом. Вместо этого драйвер mssql-python использует %(name)s.

SQLite:

cursor.execute("SELECT * FROM products WHERE id = :id", {"id": 42})

mssql-python:

cursor.execute("SELECT * FROM Production.Product WHERE ProductID = %(id)s", {"id": 42})

Миграция существующих данных

Важные моменты при миграции данных из SQLite в Microsoft SQL:

  • Microsoft SQL хранит значения nvarchar(max) и varbinary(max) как крупные объекты (LOB), которые читаются и записывают медленнее, чем данные в строке. Держите столбцы строк на уровне nvarchar(4000) или меньше, когда позволяют ваши данные. SQL Server хранит эти значения непосредственно в строке данных, что позволяет избежать издержек, связанных с LOB.
  • Отображение типов соответствует правилам аффинности типов SQLite. Просмотрите сгенерированные таблицы после миграции, чтобы уменьшить размер столбцов (например, nvarchar(100) вместо nvarchar(4000)) или добавить ограничения, которые SQLite не обеспечил.
  • Для таблиц SQLite, которые используют TEXT для хранения дат, возможно, придётся парсировать значения в объекты Python datetime перед вставкой. Microsoft SQL ожидает правильных значений datetime, а не текстовых строк.

Используйте этот скрипт для чтения схемы и данных из SQLite и создания таблиц соответствия в Microsoft SQL:

import sqlite3
import mssql_python

# SQLite type affinity -> Microsoft SQL type
# Keywords follow SQLite's type affinity rules
TYPE_MAP = {
    "INT": "bigint",
    "CHAR": "nvarchar(4000)",
    "CLOB": "nvarchar(max)",
    "TEXT": "nvarchar(4000)",
    "BLOB": "varbinary(max)",
    "REAL": "float",
    "FLOA": "float",
    "DOUB": "float",
}


def map_type(sqlite_type: str) -> str:
    """Map a SQLite column type to a Microsoft SQL type."""
    upper = (sqlite_type or "TEXT").upper()
    for prefix, sql_type in TYPE_MAP.items():
        if prefix in upper:
            return sql_type
    return "decimal(18,6)"  # NUMERIC affinity (default)


# Connect to both databases
sqlite_conn = sqlite3.connect("myapp.db")
sql_conn = mssql_python.connect(
    "Server=<server>.database.windows.net;"
    "Database=<database>;"
    "Authentication=ActiveDirectoryDefault;"
    "Encrypt=yes;"
)

# Get list of tables from SQLite
sqlite_cur = sqlite_conn.cursor()
sqlite_cur.execute(
    "SELECT name FROM sqlite_master "
    "WHERE type='table' AND name NOT LIKE 'sqlite_%'"
)
tables = [row[0] for row in sqlite_cur.fetchall()]

sql_cursor = sql_conn.cursor()

for table in tables:
    # Read column info from SQLite
    sqlite_cur.execute(f"PRAGMA table_info([{table}])")
    columns = sqlite_cur.fetchall()
    # columns: (cid, name, type, notnull, default_value, pk)

    # Build CREATE TABLE statement
    col_defs = []
    for col in columns:
        name, col_type, notnull, pk = col[1], col[2], col[3], col[5]
        sql_type = map_type(col_type)
        parts = [f"[{name}] {sql_type}"]
        if notnull:
            parts.append("NOT NULL")
        if pk:
            parts.append("PRIMARY KEY")
        col_defs.append(" ".join(parts))

    create_sql = f"CREATE TABLE [{table}] ({', '.join(col_defs)})"
    sql_cursor.execute(
        f"IF OBJECT_ID('{table}','U') IS NULL " + create_sql
    )
    sql_conn.commit()

    # Read all rows from SQLite
    sqlite_cur.execute(f"SELECT * FROM [{table}]")
    rows = sqlite_cur.fetchall()

    if not rows:
        print(f"  {table}: created (empty)")
        continue

    # Use bulkcopy for fast insert
    result = sql_cursor.bulkcopy(table, rows)
    print(f"  {table}: {result['rows_copied']} rows copied")

sql_conn.commit()
sqlite_conn.close()
sql_conn.close()

Отличия функций

После миграции ваше приложение получает доступ к функциям Microsoft SQL, которые SQLite не поддерживает:

Функция SQLite SQL Server
Одновременные операции записи Один писатель за раз Полный параллелизм с блокировкой на уровне строк
Authentication Только права на файлы проверка подлинности SQL, проверка подлинности Windows, Microsoft Entra ID
Хранимые процедуры Не поддерживаются Полная программируемость T-SQL
Encryption Не встроен TLS в транзите, TDE в состоянии покоя
Транзакции Точки сохранения, базовые уровни изоляции Полные уровни изоляции, распределённые транзакции
Поддержка JSON json_extract() OPENJSON(), JSON_VALUE(), FOR JSON
Полнотекстовый поиск Расширение FTS5 Встроенная полнотекстовая индексация
Максимальный размер базы данных ~281 ТБ (на практике предел ниже) 524 ПB
Пулинг соединений N/A (в процессе) Встроено в mssql-python

Связанные материалы

  • Last updated on