Migrar de SQLite para Microsoft SQL com mssql-python

O SQLite é a base de dados padrão para muitos projetos Python, FastAPI e aplicações Flask. Permite-lhe construir a sua aplicação rapidamente, adiando a decisão da plataforma de dados para mais tarde. Quando chegar a altura de levar a sua aplicação para produção, precisa de suporte para utilizadores concorrentes, segurança baseada em funções, alta disponibilidade, recuperação de desastres e outras funcionalidades empresariais. Precisas de migrar para Microsoft SQL usando o driver mssql-python.

Diferenças nos dialetos SQL

Quando migra de SQLite para Microsoft SQL, precisa de abordar duas coisas: reescrever as suas instruções SQL para Transact-SQL (T-SQL) e migrar os seus dados.

A tabela seguinte mapeia padrões SQLite comuns para os seus equivalentes Microsoft SQL:

CREATE TABLE exemplo

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

Exemplos de consultas

Paginação:

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 ordem dos parâmetros é invertida. Transact-SQL põe OFFSET antes FETCH NEXTde .

Upsert (inserir ou atualizar):

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 cláusula define source.[key] e source.value como alias de coluna. As WHEN cláusulas referem-se a esses pseudónimos. São necessários apenas dois ? marcadores.

Último ID inserido:

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

Atualizar código de ligação

Substituir sqlite3.connect() por 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;"
    )

O acesso por linha funciona de modo semelhante. A função de criação do SQLite Row retorna linhas semelhantes a dicionários com a sintaxe row["column"]. O driver mssql-python retorna objetos Row que suportam o mesmo acesso por chave de string, bem como acesso por atributo e por índice:

SQLite (com row_factory):

row["name"]

mssql-python:

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

Estilo de atualização do parâmetro

Tanto o SQLite como o mssql-python usam ? como marcador de parâmetro, pelo que a maioria das consultas funciona sem alterações.

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

A única diferença: o SQLite permite parâmetros nomeados com :name sintaxe. O driver mssql-python usa %(name)s em vez disso.

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

Migrar dados existentes

Considerações importantes ao migrar dados do SQLite para o Microsoft SQL:

• O Microsoft SQL armazena os valores nvarchar(max) e varbinary(max) como objetos grandes (LOBs), que são mais lentos a ler e escrever do que os dados em linha. Mantenha as colunas de cadeias de caracteres com nvarchar(4000) ou menos sempre que os seus dados o permitam. O SQL Server armazena estes valores diretamente na linha de dados, evitando a sobrecarga do LOB.
• O mapeamento de tipos segue as regras de afinidade de tipos do SQLite. Revê as tabelas geradas após a migração para restringir o tamanho das colunas (por exemplo, nvarchar(100) em vez de nvarchar(4000)) ou adicionar restrições que o SQLite não aplicou.
• Para tabelas SQLite que usam TEXT para armazenar datas, pode ser necessário analisar os valores em objetos Python datetime antes de inserir. O Microsoft SQL espera valores de data-hora corretos, não cadeias de texto.

Use este script para ler o esquema e os dados do SQLite e criar tabelas correspondentes no 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()

Diferenças entre caraterísticas

Após a migração, a sua aplicação ganha acesso a funcionalidades do Microsoft SQL que o SQLite não suporta:

Conteúdo relacionado

• Instalar mssql-python
• Cadeias de ligação
• Cópia em massa
• Consultas parametrizadas
• Paginação
• Utilizar mssql-python com Flask