Migrera från SQLite till Microsoft SQL med mssql-python

SQLite är standarddatabasen för många Python-projekt, FastAPI och Flask-appar. Det låter dig snabbt bygga din applikation genom att skjuta upp beslutet om dataplattformen till senare. När det är dags att ta din applikation i produktion behöver du stöd för samtidiga användare, rollbaserad säkerhet, hög tillgänglighet, katastrofåterställning och andra företagsfunktioner. Du behöver migrera till Microsoft SQL med mssql-python-drivrutinen.

Skillnader i SQL-dialekter

När du migrerar från SQLite till Microsoft SQL behöver du ta itu med två saker: skriva om dina SQL-satser för Transact-SQL (T-SQL) och migrera dina data.

Följande tabell mappar vanliga SQLite-mönster till deras Microsoft SQL-motsvarigheter:

SQLite SQL Server (T-SQL) Noteringar
INTEGER PRIMARY KEY AUTOINCREMENT int IDENTITY(1,1) PRIMARY KEY Microsoft SQL använder IDENTITY för autoincrement.
TEXT nvarchar(255) eller nvarchar(max) Ange alltid en längd. Använd nvarchar för Unicode.
REAL float eller decimal(18,2) Använd decimal för exakta värden som pengar.
BLOB varbinary(max) Samma beteende, annat namn.
BOOLEAN (lagrad som INTEGER) bit Ingen av databaserna har en inbyggd boolean. Båda lagrar 0/1.
DATETIME('now') GETDATE() eller SYSDATETIME() SYSDATETIME() ger högre precision.
LIMIT 10 OFFSET 20 OFFSET 20 ROWS FETCH NEXT 10 ROWS ONLY Kräver en ORDER BY klausul.
\|\| (strängsammanfogning) + eller CONCAT() CONCAT() hanterar NULL värden.
IFNULL(a, b) ISNULL(a, b) eller COALESCE(a, b) COALESCE är ANSI-standard.
GROUP_CONCAT(col) STRING_AGG(col, ',') Tillgänglig i SQL Server 2017+.
INSERT OR REPLACE INTO MERGE uttalande SQLite raderar och infogar igen; MERGE uppdaterar på plats. Se exemplet som följer.
last_insert_rowid() OUTPUT INSERTED.id Använd OUTPUT i uttalandet INSERT . SCOPE_IDENTITY() Fungerar också men kräver en separat SELECT.
typeof(x) SQL_VARIANT_PROPERTY(x, 'BaseType') Sällan nödvändigt med strikt typning.

CREATE TABLE Exempel

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

Exempelförfrågningar

Paginering:

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

Parameterordningen är omvänd. Transact-SQL sätter OFFSET före FETCH NEXT.

Upsert (infoga eller uppdatera):

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

Satsen USING definierar source.[key] och source.value som kolumnalias. Klausulerna WHEN refererar till dessa alias. Endast två ? markörer behövs.

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

Uppdatera anslutningskod

Ersätt sqlite3.connect() med 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;"
    )

Radåtkomst fungerar på liknande sätt. SQLites Row radfabrik returnerar ordboksliknande rader med syntaxen row["column"]. mssql-python-drivrutinen returnerar Row objekt som stödjer samma strängnyckelåtkomst, plus attribut- och indexåtkomst:

SQLite (med row_factory):

row["name"]

mssql-python:

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

Uppdatera parameterstil

Både SQLite och mssql-python används ? som parametermarkör, så de flesta frågor fungerar utan ändringar.

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

Den enda skillnaden: SQLite tillåter namngivna parametrar med :name syntax. Drivrutinen mssql-python använder %(name)s istället.

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

Migrera befintlig data

Viktiga överväganden vid migrering av data från SQLite till Microsoft SQL:

  • Microsoft SQL lagrar nvarchar(max) och varbinär(max)-värden som stora objekt (LOBs), vilka är långsammare att läsa och skriva än raddata. Håll strängkolumnerna på nvarchar(4000) eller lägre när din data tillåter. SQL Server lagrar dessa värden direkt i dataraden och undviker LOB-överhead.
  • Typmappningen följer SQLites typaffinitetsregler. Gå igenom de genererade tabellerna efter migrering för att skärpa kolumnstorlekarna (till exempel nvarchar(100) istället för nvarchar(4000)) eller lägg till begränsningar som SQLite inte upprätthöll.
  • För SQLite-tabeller som använder TEXT för att lagra datum kan du behöva tolka värdena till Python-objekt datetime innan du infogar dem. Microsoft SQL förväntar sig korrekta datumtidsvärden, inte textsträngar.

Använd detta skript för att läsa schemat och data från SQLite och skapa matchande tabeller i 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()

Skillnader i funktioner

Efter migreringen får din applikation tillgång till Microsoft SQL-funktioner som SQLite inte stödjer:

Feature SQLite SQL Server
Samtidiga skrivoperationer Ensam författare åt gången Full samtidighet med låsning på radnivå
Authentication Endast filbehörigheter SQL-autentisering, Windows-autentisering, Microsoft Entra ID
Lagrade procedurer Stöds ej Fullständigt T-SQL-stöd för programmering
Encryption Inte inbyggt TLS under överföring, TDE för lagrade data
Transaktioner Sparpunkter, grundläggande isoleringsnivåer Fulla isoleringsnivåer, distribuerade transaktioner
JSON-stöd json_extract() OPENJSON(), JSON_VALUE(), FOR JSON
Fulltextsökning FTS5-förlängning Inbyggd fulltextindexering
Största databasstorlek ~281 TB (praktisk gräns lägre) 524 PB
Anslutningspoolning Ej tillgängligt (under arbete) Inbyggt med mssql-python

Relaterat innehåll

  • Last updated on