Python: pyodbc (SQL Server / TDS)

Connects over TDS to a MachSQL database created with the T-SQL dialect; uses OBJECT_ID, IDENTITY and OUTPUT INSERTED. The code is the same against the original database and against MachSQL; only the address changes.

The only change

DB_CONNECTION="DRIVER={ODBC Driver 18 for SQL Server};SERVER=old-sqlserver,1433;DATABASE=app;UID=app;PWD=...;Encrypt=yes"
DB_CONNECTION="DRIVER={ODBC Driver 18 for SQL Server};SERVER=machsql-host,1433;DATABASE=app;UID=app;PWD=...;Encrypt=yes"

Nothing else changes: not the code, not the driver, not its version, not the SQL.

Versions

• Python 3.15
• pyodbc 5.3.0
• Microsoft ODBC Driver 18 for SQL Server (or newer)

Run

python3 -m venv .venv && . .venv/bin/activate
pip install -r requirements.txt
export DB_CONNECTION="DRIVER={ODBC Driver 18 for SQL Server};SERVER=machsql-host,1433;DATABASE=app;UID=app;PWD=...;Encrypt=yes"
python main.py

Expected output: inserted todo <id>, <n> open.

Source

The program as it is kept and tested in the Mach Database for PostgreSQL repository; highlighted lines read the connection setting.

main.py

"""pyodbc with the Microsoft ODBC driver over TDS (the SQL Server protocol)."""

import os

import pyodbc

with pyodbc.connect(os.environ["DB_CONNECTION"]) as conn:
    cur = conn.cursor()
    cur.execute("IF OBJECT_ID(N'dbo.todo', N'U') IS NULL CREATE TABLE dbo.todo (id BIGINT IDENTITY PRIMARY KEY, title NVARCHAR(200) NOT NULL, done BIT NOT NULL DEFAULT 0)")
    (todo_id,) = cur.execute(
        "INSERT INTO dbo.todo (title) OUTPUT INSERTED.id VALUES (?)", "write the examples"
    ).fetchone()
    (open_count,) = cur.execute("SELECT count(*) FROM dbo.todo WHERE done = 0").fetchone()
    conn.commit()
    print(f"inserted todo {todo_id}, {open_count} open")

requirements.txt

pyodbc==5.3.0