Migracja z SQLite do Microsoft SQL z mssql-python

SQLite to domyślna baza danych dla wielu projektów Python, FastAPI i aplikacji Flapsk. Pozwala to szybko zbudować aplikację, odkładając decyzję o platformie danych na później. Gdy przyjdzie czas na uruchomienie aplikacji produkcyjnej, potrzebujesz wsparcia dla jednoczesnych użytkowników, bezpieczeństwa opartego na rolach, wysokiej dostępności, odzyskiwania po awarii i innych funkcji korporacyjnych. Musisz przejść do Microsoft SQL za pomocą sterownika mssql-python.

Różnice w dialekcie SQL

Podczas migracji z SQLite do Microsoft SQL musisz zająć się dwoma rzeczami: przepisać swoje instrukcje SQL dla Transact-SQL (T-SQL) oraz przeprowadzić migrację danych.

Poniższa tabela mapuje typowe wzorce SQLite na ich odpowiedniki Microsoft SQL:

SQLite SQL Server (T-SQL) Notatki
INTEGER PRIMARY KEY AUTOINCREMENT int IDENTITY(1,1) PRIMARY KEY Microsoft SQL używa IDENTITY do automatycznego zwiększania.
TEXT nvarchar(255) lub nvarchar(max) Zawsze określaj długość. Zastosowanie nvarchar w Unicode.
REAL float lub decimal(18,2) Użyj decimal dla dokładnych wartości, takich jak pieniądze.
BLOB varbinary(max) To samo zachowanie, inne imię.
BOOLEAN (przechowywane jako INTEGER) bit Żadna z baz danych nie posiada natywnego booleana. Oba przechowują 0/1.
DATETIME('now') GETDATE() lub SYSDATETIME() SYSDATETIME() Daje większą precyzję.
LIMIT 10 OFFSET 20 OFFSET 20 ROWS FETCH NEXT 10 ROWS ONLY Wymaga klauzuli ORDER BY .
\|\| (struna konkata) + lub CONCAT() CONCAT() obsługuje NULL wartości.
IFNULL(a, b) ISNULL(a, b) lub COALESCE(a, b) COALESCE jest standardem ANSI.
GROUP_CONCAT(col) STRING_AGG(col, ',') Dostępny w SQL Server 2017+.
INSERT OR REPLACE INTO MERGE, oświadczenie SQLite usuwa i wstawia ponownie; MERGE aktualizuje na miejscu. Zobacz następujący przykład.
last_insert_rowid() OUTPUT INSERTED.id Użyj OUTPUT w instrukcji INSERT. SCOPE_IDENTITY() Też działa, ale wymaga osobnego SELECT.
typeof(x) SQL_VARIANT_PROPERTY(x, 'BaseType') Rzadko potrzebne przy ścisłym pisaniu na klawiaturze.

CREATE TABLE Przykład

-- 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
);

Przykłady zapytań

Paginacja:

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)
)

Kolejność parametrów jest odwrócona. Transact-SQL stawia OFFSET przed FETCH NEXT.

Upsert (wstaw lub zaktualizuj):

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))

Klauzula definiuje USINGsource.[key] i source.value jako aliasy kolumnowe. Klauzule WHEN odnoszą się do tych aliasów. Potrzebne są tylko dwa znaczniki ?.

Ostatni wprowadzony ID:

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()

Zaktualizuj kod połączenia

Zastąp ciąg sqlite3.connect() ciągiem 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;"
    )

Dostęp na poziomie wiersza działa podobnie. Fabryka Row SQLite zwraca wiersze podobne do dyktów z row["column"] składnią. Sterownik mssql-python zwraca obiekty Row, które obsługują ten sam dostęp przy użyciu kluczy tekstowych, a także dostęp przez atrybuty i indeksy:

SQLite (z row_factory):

row["name"]

mssql-python:

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

Styl aktualizacji parametrów

Zarówno SQLite, jak i mssql-python ? używają jako markera parametrów, więc większość zapytań działa bez zmian.

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

Różnica jest taka: SQLite pozwala na nazwane parametry z :name składnią. Sterownik mssql-python używa zamiast tego %(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})

Migracja istniejących danych

Ważne kwestie przy migracji danych z SQLite do Microsoft SQL:

  • Microsoft SQL przechowuje wartości nvarchar(max) i varbinary(max) jako duże obiekty (LOB), które są wolniejsze w odczytie i zapisie niż dane w wierszu. Utrzymuj kolumny ciągów na poziomie nvarchar(4000) lub mniej, kiedy tylko pozwalają na to twoje dane. SQL Server przechowuje te wartości bezpośrednio w wierszu danych, co pozwala uniknąć narzutu LOB.
  • Mapowanie typów opiera się na zasadach powinowactwa typów SQLite. Przejrzyj wygenerowane tabele po migracji, aby dopracować rozmiary kolumn (np. nvarchar(100) zamiast nvarchar(4000)) lub dodać ograniczenia, których SQLite nie egzekwował.
  • W tabelach SQLite, które używają TEXT-a do przechowywania dat, może być konieczne przetworzenie wartości na obiekty Python datetime przed wstawieniem. Microsoft SQL oczekuje prawidłowych wartości dat, a nie tekstów.

Użyj tego skryptu, aby odczytać schemat i dane z SQLite oraz tworzyć pasujące tabele w 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()

Różnice między funkcjami

Po migracji Twoja aplikacja zyskuje dostęp do funkcji Microsoft SQL, których SQLite nie obsługuje:

Funkcja SQLite SQL Server
Współbieżne zapisy Jeden pisarz na raz Pełna współbieżność z blokowaniem na poziomie wierszy
Authentication Tylko uprawnienia do plików uwierzytelnianie SQL, uwierzytelnianie systemu Windows, Microsoft Entra ID
Procedury przechowywane Niewspierane Pełna programowalność T-SQL
Encryption Nie wbudowane TLS w transporcie, TDE w spoczynku
Transakcje Punkty przywracania, podstawowe poziomy izolacji Poziomy pełnej izolacji, transakcje rozproszone
Obsługa formatu JSON json_extract() OPENJSON(), JSON_VALUE(), FOR JSON
Wyszukiwanie pełnotekstowe Rozszerzenie FTS5 Wbudowane indeksowanie pełnego tekstu
Maksymalny rozmiar bazy danych ~281 TB (praktyczny limit niższy) 524 PB
Buforowanie połączeń N/A (w trakcie realizacji) Wbudowane w mssql-python

Treści powiązane

  • Last updated on