Migra da SQLite a Microsoft SQL con mssql-python

SQLite è il database predefinito per molti progetti Python, FastAPI e app Flask. Ti permette di costruire la tua applicazione rapidamente ritardando la decisione sulla piattaforma dati a un futuro successivo. Quando arriva il momento di portare la tua applicazione in produzione, hai bisogno di supporto per utenti concorrenti, sicurezza basata sui ruoli, alta disponibilità, disaster recovery e altre funzionalità aziendali. Devi migrare a Microsoft SQL usando il driver mssql-python.

Differenze tra dialetti SQL

Quando migri da SQLite a Microsoft SQL, devi affrontare due cose: riscrivere le tue istruzioni SQL per Transact-SQL (T-SQL) e migrare i tuoi dati.

La seguente tabella mappa i modelli SQLite comuni ai loro equivalenti Microsoft SQL:

CREATE TABLE Esempio

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

Esempi di query

Paginazione:

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

L'ordine dei parametri è invertito. Transact-SQL inserisce OFFSET prima di FETCH NEXT.

Upsert (inserire o aggiornare):

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

La USING clausola definisce source.[key] e source.value come alias di colonna. Le WHEN clausole fanno riferimento a quegli alias. Sono necessari solo due indicatori ?.

Ultimo ID inserito:

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

Aggiorna il codice di connessione

Sostituire sqlite3.connect() con 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;"
    )

L'accesso alle righe funziona in modo simile. La factory di Row di SQLite restituisce righe simili a dizionari con sintassi row["column"]. Il driver mssql-python restituisce Row oggetti che supportano lo stesso accesso a string-key, più accesso ad attributi e indici:

SQLite (con row_factory):

row["name"]

MSSQL-Python:

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

Aggiornamento dello stile dei parametri

Sia SQLite che mssql-python usano ? come indicatore di parametro, quindi la maggior parte delle query funziona senza modifiche.

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

L'unica differenza: SQLite permette parametri nominati con :name sintassi. Il driver mssql-python utilizza invece %(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})

Migrazione dei dati esistenti

Considerazioni importanti durante la migrazione dei dati da SQLite a Microsoft SQL:

• Microsoft SQL memorizza i valori nvarchar(max) e varbinary(max) come oggetti grandi (LOB), che sono più lenti da leggere e scrivere rispetto ai dati in riga. Mantieni le colonne di tipo stringa a nvarchar(4000) o inferiori quando i dati lo consentono. SQL Server memorizza questi valori direttamente nella riga dati, evitando l'overhead LOB.
• La mappatura dei tipi segue le regole di affinità dei tipi di SQLite. Rivedi le tabelle generate dopo la migrazione per stringere le dimensioni delle colonne (ad esempio, nvarchar(100) invece di nvarchar(4000)) o aggiungere vincoli che SQLite non ha applicato.
• Per le tabelle SQLite che usano TEXT per memorizzare le date, potresti dover analizzare i valori in oggetti Python datetime prima di inserirli. Microsoft SQL richiede valori datetime corretti, non stringhe.

Usa questo script per leggere lo schema e i dati da SQLite e creare tabelle corrispondenti in 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()

Differenze di funzionalità

Dopo la migrazione, la tua applicazione ottiene accesso a funzionalità di Microsoft SQL che SQLite non supporta:

Contenuti correlati

• Installa mssql-python
• Stringhe di connessione
• Copia in blocco
• Query con parametri
• Paginazione
• Usa mssql-python con Flask