Bermigrasi dari SQLite ke Microsoft SQL dengan mssql-python

SQLite adalah database default untuk banyak proyek Python, FastAPI, dan aplikasi Flask. Ini memungkinkan Anda membangun aplikasi dengan cepat dengan menunda keputusan platform data hingga nanti. Saat tiba waktunya untuk membawa aplikasi Anda ke produksi, Anda memerlukan dukungan untuk pengguna bersamaan, keamanan berbasis peran, ketersediaan tinggi, pemulihan bencana, dan fitur perusahaan lainnya. Anda perlu bermigrasi ke Microsoft SQL menggunakan driver mssql-python.

Perbedaan dialek SQL

Saat Anda bermigrasi dari SQLite ke SQL Microsoft, Anda perlu menangani dua hal: menulis ulang pernyataan SQL untuk Transact-SQL (T-SQL) dan memigrasikan data Anda.

Tabel berikut memetakan pola SQLite umum ke padanan Microsoft SQL-nya:

SQLite SQL Server (T-SQL) Catatan
INTEGER PRIMARY KEY AUTOINCREMENT int IDENTITY(1,1) PRIMARY KEY Microsoft SQL menggunakan IDENTITY untuk kenaikan otomatis.
TEXT nvarchar(255) atau nvarchar(max) Selalu tentukan panjangnya. Gunakan nvarchar untuk Unicode.
REAL float atau decimal(18,2) Gunakan decimal untuk nilai yang tepat seperti uang.
BLOB varbinary(max) Perilaku yang sama, nama yang berbeda.
BOOLEAN (disimpan sebagai INTEGER) bit Tidak ada database yang memiliki boolean asli. Keduanya menyimpan 0/1.
DATETIME('now') GETDATE() atau SYSDATETIME() SYSDATETIME() memberikan presisi yang lebih tinggi.
LIMIT 10 OFFSET 20 OFFSET 20 ROWS FETCH NEXT 10 ROWS ONLY Membutuhkan ORDER BY klausa.
\|\| (koncat string) + atau CONCAT() CONCAT() menangani nilai NULL.
IFNULL(a, b) ISNULL(a, b) atau COALESCE(a, b) COALESCE adalah standar ANSI.
GROUP_CONCAT(col) STRING_AGG(col, ',') Tersedia di SQL Server 2017+.
INSERT OR REPLACE INTO MERGE pernyataan SQLite menghapus dan menyisipkan ulang; MERGE memperbarui langsung di tempat. Lihat contoh berikut.
last_insert_rowid() OUTPUT INSERTED.id Gunakan OUTPUT dalam INSERT pernyataan. SCOPE_IDENTITY() juga berfungsi tetapi memerlukan SELECT terpisah.
typeof(x) SQL_VARIANT_PROPERTY(x, 'BaseType') Jarang dibutuhkan dengan pengetikan yang ketat.

CREATE TABLE contoh

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

Contoh kueri pencarian

Penomoran halaman:

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

Urutan parameter dibalik. Transact-SQL menempatkan OFFSET sebelum FETCH NEXT.

Upsert (sisipkan atau perbarui):

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

Klausa USING mendefinisikan source.[key] dan source.value sebagai alias kolom. Klausul tersebut WHEN merujuk pada alias tersebut. Hanya diperlukan dua penanda ?.

ID terakhir dimasukkan:

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

Perbarui kode koneksi

Ganti sqlite3.connect() dengan 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;"
    )

Akses baris berfungsi dengan cara yang serupa. Fungsi factory SQLite Row mengembalikan baris mirip kamus dengan sintaks row["column"]. Driver mssql-python mengembalikan Row objek yang mendukung akses kunci string yang sama, ditambah akses atribut dan indeks:

SQLite (dengan row_factory):

row["name"]

mssql-python:

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

Perbarui gaya parameter

Baik SQLite dan mssql-python menggunakan ? sebagai penanda parameter, sehingga sebagian besar kueri bekerja tanpa perubahan.

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

Satu perbedaannya: SQLite memungkinkan parameter bernama dengan sintaks :name. Driver mssql-python menggunakan %(name)s sebagai gantinya.

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

Memigrasikan data yang ada

Pertimbangan penting saat memigrasikan data dari SQLite ke Microsoft SQL:

  • Microsoft SQL menyimpan nilai nvarchar(max) dan varbinary(max) sebagai objek besar (LOB), yang lebih lambat untuk membaca dan menulis daripada data dalam baris. Pertahankan kolom string di nvarchar(4000) atau kurang kapan pun data Anda memungkinkan. SQL Server menyimpan nilai-nilai ini langsung di baris data, menghindari overhead LOB.
  • Pemetaan jenis mengikuti aturan afinitas jenis SQLite. Tinjau tabel yang dihasilkan setelah migrasi untuk memperketat ukuran kolom (misalnya, nvarchar(100) alih-alih nvarchar(4000)) atau menambahkan batasan yang tidak diterapkan SQLite.
  • Untuk tabel SQLite yang menggunakan TEXT untuk menyimpan tanggal, Anda mungkin perlu mengurai nilai ke dalam objek Python datetime sebelum menyisipkan. Microsoft SQL mengharapkan nilai tanggalwaktu yang tepat, bukan string teks.

Gunakan skrip ini untuk membaca skema dan data dari SQLite dan membuat tabel yang cocok di 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()

Perbedaan fitur

Setelah migrasi, aplikasi Anda mendapatkan akses ke fitur Microsoft SQL yang tidak didukung SQLite:

Feature SQLite SQL Server
Penulisan serentak Hanya satu penulis pada satu waktu Konkurensi penuh dengan penguncian tingkat baris
Authentication Hanya izin file Autentikasi SQL, Autentikasi Windows, Microsoft Entra ID
Prosedur yang disimpan Tidak didukung Kemampuan pemrograman T-SQL penuh
Encryption Tidak terpasang TLS dalam perjalanan, TDE tidak aktif
Transactions Titik simpan, tingkat isolasi dasar Tingkat isolasi penuh, transaksi terdistribusi
Dukungan JSON json_extract() OPENJSON(), JSON_VALUE(), FOR JSON
Pencarian teks lengkap Ekstensi FTS5 Pengindeksan teks lengkap bawaan
Ukuran database maksimum ~281 TB (batas praktis lebih rendah) 524 PB
Pemanfaatan koneksi N/A (dalam proses) Terintegrasi dengan mssql-python

Konten terkait

  • Last updated on