Migreren van SQLite naar Microsoft SQL met mssql-python

SQLite is de standaarddatabase voor veel Python-projecten, FastAPI en Flask-apps. Het stelt je in staat je applicatie snel te bouwen door de beslissing over het dataplatform uit te stellen tot later. Wanneer het tijd is om je applicatie in productie te nemen, heb je ondersteuning nodig voor gelijktijdige gebruikers, rolgebaseerde beveiliging, hoge beschikbaarheid, disaster recovery en andere enterprise-functies. Je moet migreren naar Microsoft SQL met de mssql-python driver.

Verschillen in SQL-dialect

Wanneer je van SQLite naar Microsoft SQL migreert, moet je twee dingen aanpakken: je SQL-statements herschrijven voor Transact-SQL (T-SQL) en je data migreren.

De volgende tabel koppelt veelvoorkomende SQLite-patronen aan hun Microsoft SQL-equivalenten:

SQLite SQL Server (T-SQL) Aantekeningen
INTEGER PRIMARY KEY AUTOINCREMENT int IDENTITY(1,1) PRIMARY KEY Microsoft SQL gebruikt IDENTITY voor autoincrement.
TEXT nvarchar(255) of nvarchar(max) Geef altijd een lengte aan. Gebruik nvarchar voor Unicode.
REAL float of decimal(18,2) Gebruik decimal het voor exacte waarden zoals geld.
BLOB varbinary(max) Zelfde gedrag, andere naam.
BOOLEAN (opgeslagen als INTEGER) bit Geen van beide databases heeft een native boolean. Beide winkelen 0/1.
DATETIME('now') GETDATE() of SYSDATETIME() SYSDATETIME() geeft een hogere precisie.
LIMIT 10 OFFSET 20 OFFSET 20 ROWS FETCH NEXT 10 ROWS ONLY Vereist een ORDER BY clausule.
\|\| (tekenreeksconcatenatie) + of CONCAT() CONCAT() Behandelt NULL waarden.
IFNULL(a, b) ISNULL(a, b) of COALESCE(a, b) COALESCE is ANSI-standaard.
GROUP_CONCAT(col) STRING_AGG(col, ',') Beschikbaar in SQL Server 2017+.
INSERT OR REPLACE INTO MERGE verklaring SQLite verwijdert en voegt opnieuw in; MERGE werkt ter plekke bij. Zie het volgende voorbeeld.
last_insert_rowid() OUTPUT INSERTED.id Gebruik OUTPUT in de INSERT verklaring. SCOPE_IDENTITY() Werkt ook, maar vereist een aparte SELECT.
typeof(x) SQL_VARIANT_PROPERTY(x, 'BaseType') Zelden nodig bij strikte typering.

CREATE TABLE Voorbeeld

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

Voorbeelden van queries

Paginatiek:

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

De parametervolgorde is omgekeerd. Transact-SQL zet OFFSET vóór FETCH NEXT.

Upsert (invoegen of bijwerken):

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

De USING clausule definieert source.[key] en source.value als kolomaliasen. De WHEN clausules verwijzen naar die aliassen. Er zijn slechts twee ? markers nodig.

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

Update verbindingscode

Vervangen sqlite3.connect() door 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;"
    )

Toegang op rijniveau werkt op dezelfde manier. De fabriek van Row SQLite geeft dict-achtige rijen met row["column"] syntaxis terug. De mssql-python-driver levert objecten terug Row die dezelfde string-key access ondersteunen, plus attribuut- en index-access:

SQLite (met row_factory):

row["name"]

mssql-python:

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

Stijl van updateparameter

Zowel SQLite als mssql-python worden gebruikt ? als parametermarker, dus de meeste queries werken zonder wijzigingen.

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

Het enige verschil: SQLite staat benoemde parameters met :name syntaxis toe. De mssql-python driver gebruikt %(name)s in plaats daarvan.

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

Migrer bestaande data

Belangrijke overwegingen bij het migreren van data van SQLite naar Microsoft SQL:

  • Microsoft SQL slaat nvarchar(max) en varbinary(max) waarden op als grote objecten (LOB's), die langzamer te lezen en schrijven zijn dan in-row data. Houd stringkolommen op nvarchar(4000) of lager wanneer je data het toelaat. SQL Server slaat deze waarden direct op in de datarij, waardoor de LOB-overhead wordt vermeden.
  • De typemapping volgt de typeaffiniteitsregels van SQLite. Bekijk de gegenereerde tabellen na migratie om kolomgroottes te verkleinen (bijvoorbeeld nvarchar(100) in plaats van nvarchar(4000)) of voeg beperkingen toe die SQLite niet afdwingde.
  • Voor SQLite-tabellen die TEXT gebruiken om data op te slaan, moet je de waarden mogelijk parsen in Python-objecten datetime voordat je ze invoegt. Microsoft SQL verwacht correcte datum-tijdwaarden, geen tekststrings.

Gebruik dit script om het schema en de data uit SQLite te lezen en bijpassende tabellen te maken 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()

Verschillen in functies

Na migratie krijgt je applicatie toegang tot Microsoft SQL-functies die SQLite niet ondersteunt:

Feature SQLite SQL Server
Gelijktijdige schrijfbewerkingen Enkele schrijver tegelijk Volledige gelijktijdigheid met vergrendeling op rijniveau
Authentication Alleen bestandsrechten SQL-authenticatie, Windows-authenticatie, Microsoft Entra ID
opgeslagen procedures Niet ondersteund Volledige T-SQL programmeerbaarheid
Encryption Niet ingebouwd TLS onderweg, TDE in rust
Transactions Savepoints, basisisolatieniveaus Volledige isolatieniveaus, gedistribueerde transacties
JSON-ondersteuning json_extract() OPENJSON(), JSON_VALUE(), FOR JSON
Zoeken in volledige tekst FTS5-uitbreiding Ingebouwde volledige-tekstindexering
Maximale databasegrootte ~281 TB (praktische limiet ligt lager) 524 PB (petabyte)
Groepsgewijze verbindingen n.v.t. (in behandeling) Ingebouwd met mssql-python

Verwante onderwerpen

  • Last updated on