Migrálás SQLite-ről Microsoft SQL-re az mssql-python használatával

Az SQLite az alapértelmezett adatbázis sok Python projekthez, FastAPI-hoz és Flask alkalmazáshoz. Lehetővé teszi, hogy gyorsan felépítsd az alkalmazásodat azáltal, hogy az adatplatformmal kapcsolatos döntést későbbre halasztod. Amikor eljön az idő, hogy az alkalmazást gyártásba állítsd, szükség van párhuzamos felhasználók, szerepalapú biztonság, magas rendelkezésre állás, katasztrófa-helyreállítás és egyéb vállalati funkciók támogatására. Az mssql-python driverrel kell migrálnod Microsoft SQL-re.

SQL dialektusi különbségek

Amikor SQLite-ról Microsoft SQL-re migrálsz, két dolgot kell kezelned: újraírnod az SQL utasításokat Transact-SQL (T-SQL) és migrálni az adataidat.

Az alábbi táblázat a gyakori SQLite-mintákat a Microsoft SQL megfelelőinek felelteti meg:

SQLite SQL Server (T-SQL) Jegyzetek
INTEGER PRIMARY KEY AUTOINCREMENT int IDENTITY(1,1) PRIMARY KEY A Microsoft SQL automatikus növeléshez használjaIDENTITY.
TEXT nvarchar(255) vagy nvarchar(max) Mindig határozz meg egy hosszt. Unicode-hoz használom nvarchar .
REAL float vagy decimal(18,2) Használd decimal pontos értékekhez, mint például a pénz.
BLOB varbinary(max) Ugyanaz a viselkedés, más név.
BOOLEAN (egész számként tárolva) bit Egyik adatbázisban nincs natív boolean. Mindkettő tárol 0/1.
DATETIME('now') GETDATE() vagy SYSDATETIME() SYSDATETIME() nagyobb pontosságot ad.
LIMIT 10 OFFSET 20 OFFSET 20 ROWS FETCH NEXT 10 ROWS ONLY ORDER BY záradék szükséges.
\|\| (karakterláncok összefűzése) + vagy CONCAT() CONCAT() kezeli a(z) NULL értékeket.
IFNULL(a, b) ISNULL(a, b) vagy COALESCE(a, b) COALESCE ANSI szabvány.
GROUP_CONCAT(col) STRING_AGG(col, ',') Elérhető SQL Server 2017+ verzióban.
INSERT OR REPLACE INTO MERGE nyilatkozat Az SQLite töröl és újra beszúr; MERGE helyben frissít. Lásd az alábbi példát.
last_insert_rowid() OUTPUT INSERTED.id Használd OUTPUT a INSERT nyilatkozatban. SCOPE_IDENTITY() szintén működik, de külön SELECT-t igényel.
typeof(x) SQL_VARIANT_PROPERTY(x, 'BaseType') Ritkán kell szigorú gépeléssel.

CREATE TABLE Példa

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

Lekérdezési példák

Lapozás:

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

A paraméter sorrendje megfordul. A Transact-SQL a(z) OFFSET elemet FETCH NEXT elé helyezi.

Upsert (beadás vagy frissítés):

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

A USING záradék a source.[key] és source.value elemeket oszlopálnevekként határozza meg. A WHEN záradékok ezekre az álnevekre hivatkoznak. Csak két ? jelölő kell.

Utoljára beillesztett azonosító:

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

Kapcsolódási kód frissítése

Cserélje le sqlite3.connect() a következőre 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;"
    )

A sorhozzáférés is hasonlóan működik. Az SQLite gyára Row dikt-szerű sorokat ad, amelyek row["column"] szintaxissal rendelkeznek. Az mssql-python illesztőprogram olyan objektumokat ad vissza Row , amelyek ugyanazt a string-kulcs-hozzáférést, valamint attribútum- és index-hozzáférést támogatnak:

SQLite (ezzel: row_factory):

row["name"]

MSSQL-Python:

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

Paraméter stílusának frissítése

Az SQLite és az mssql-python ? is használja a paraméterjelölőt, így a legtöbb lekérdezés változtatás nélkül működik.

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

Az egyetlen különbség: az SQLite lehetővé teszi a névnevű paramétereket szintaxissal :name . Az mssql-python illesztőprogram helyette a(z) %(name)s elemet használja.

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

Meglévő adatok migrálása

Fontos szempontok az adatok áthelyezése során SQLite-ról Microsoft SQL-re:

  • A Microsoft SQL a nvarchar(max) és varbinary(max) értékeket nagy objektumként (LOB-ként) tárolja, amelyek lassabban olvashatók és írhatók, mint a soron belüli adatok. Tartsd a stringoszlopokat nvarchar (4000) vagy annál alacsonyabb állapoton, amikor az adataid engedik. Az SQL Server ezeket az értékeket közvetlenül az adatsorban tárolja, elkerülve a LOB túlterhelést.
  • A típusleképezés az SQLite típusaffinitási szabályait követi. Nézd át a generált táblákat a migráció után, hogy szigorítsd az oszlopméreteket (például nvarchar(100)nvarchar(4000) helyett), vagy olyan korlátozásokat adj hozzá, amelyeket az SQLite nem érvényesített.
  • Az SQLite táblák esetében, amelyek TEXT segítségével tárolják a dátumokat, előfordulhat, hogy az értékeket Python datetime objektumokba kell parzálni a beillesztés előtt. A Microsoft SQL megfelelő datetime értékeket vár, nem szöveges sorokat.

Ezt a szkriptet használja az SQLite sémájának és adatainak olvasására, valamint a Microsoft SQL-ben megfelelő táblázatok létrehozására:

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

Szolgáltatások eltérései

Migráció után az alkalmazásod hozzáfér a Microsoft SQL funkciókhoz, amelyeket az SQLite nem támogat:

Feature SQLite SQL Server
Egyidejű írások Egyszerre csak egy író Teljes párhuzamosság sorszintű zárolással
Authentication Csak fájlengedélyek SQL-hitelesítés, Windows-hitelesítés, Microsoft Entra ID
Tárolt eljárások Nem támogatott Teljes T-SQL programozhatóság
Encryption Nincs beépítve A TLS úton van, TDE nyugalmi állapotban
Tranzakciók Mentési pontok, alapvető izolációs szintek Teljes izolációs szintek, elosztott tranzakciók
JSON-támogatás json_extract() \, \, \
Teljes szöveges keresés FTS5 kiterjesztés Beépített teljes szöveges indexelés
Adatbázis maximális mérete ~281 TB (gyakorlati korlát alacsonyabb) 524 petabájt
Kapcsolatmegosztás N/A (folyamatban van) MSSQL-python beépített

Kapcsolódó tartalom

  • Last updated on