Migrer de SQLite vers Microsoft SQL avec mssql-python

SQLite est la base de données par défaut pour de nombreux projets Python, FastAPI et les applications Flask. Cela vous permet de construire rapidement votre application en retardant la décision de la plateforme de données à plus tard. Au moment de mettre votre application en production, vous avez besoin d’un support pour les utilisateurs concurrents, la sécurité basée sur les rôles, la haute disponibilité, la reprise après sinistre et d’autres fonctionnalités d’entreprise. Vous devez migrer vers Microsoft SQL en utilisant le pilote mssql-python.

Différences entre les dialectes SQL

Lorsque vous migrez de SQLite vers Microsoft SQL, vous devez aborder deux choses : réécrire vos instructions SQL pour Transact-SQL (T-SQL) et migrer vos données.

Le tableau suivant associe les modèles SQLite courants à leurs équivalents Microsoft SQL :

CREATE TABLE Exemple

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

Exemples de requêtes

Pagination :

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’ordre des paramètres est inversé. Transact-SQL met OFFSET avant FETCH NEXT.

Upsert (insérer ou mettre à jour) :

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 clause définit source.[key] et source.value comme des alias de colonne. Les WHEN clauses font référence à ces alias. Seuls deux ? repères sont nécessaires.

Dernier identifiant inséré :

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

Mettre à jour le code de connexion

Remplacez sqlite3.connect() par 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’accès aux lignes fonctionne de manière similaire. La fabrique Row de SQLite renvoie des lignes de type dictionnaire avec la syntaxe row["column"]. Le pilote mssql-python retourne Row des objets qui supportent le même accès à la clé de chaîne, ainsi que l’accès aux attributs et à l’index :

SQLite (avec row_factory) :

row["name"]

MSSQL-python :

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

Mise à jour du style des paramètres

SQLite et mssql-python servent ? tous deux de marqueur de paramètres, donc la plupart des requêtes fonctionnent sans modifications.

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

La seule différence : SQLite permet des paramètres nommés avec la syntaxe :name. Le pilote mssql-python utilise %(name)s à la place.

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

Migration des données existantes

Considérations importantes lors de la migration de données de SQLite vers Microsoft SQL :

• Microsoft SQL stocke les valeurs nvarchar(max) et varbinary(max) sous forme de gros objets (LOB), qui sont plus lents à lire et à écrire que les données en ligne. Gardez les colonnes de chaînes à nvarchar(4000) ou moins quand vos données le permettent. SQL Server stocke ces valeurs directement dans la ligne de données, évitant ainsi la surcharge de LOB.
• Le mappage de types suit les règles d’affinité de type de SQLite. Révisez les tables générées après migration pour resserrer la taille des colonnes (par exemple, nvarchar(100) au lieu de nvarchar(4000)) ou ajouter des contraintes que SQLite n’a pas appliquées.
• Pour les tables SQLite qui utilisent du TEXTE pour stocker les dates, il se peut que vous deviez analyser les valeurs en objets Python datetime avant d’y insérer. Microsoft SQL attend des valeurs de date-heure correctes, pas des chaînes de texte.

Utilisez ce script pour lire le schéma et les données de SQLite et créer des tables correspondantes dans 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()

Différences de fonctionnalités

Après la migration, votre application accéde à des fonctionnalités Microsoft SQL que SQLite ne prend pas en charge :

Contenu connexe

• Installer mssql-python
• Chaînes de connexion
• Copie en bloc
• Requêtes paramétrables
• Pagination
• Utilisez mssql-python avec Flask