Connect to Microsoft SQL Server with Python

Python can connect to Microsoft SQL Server from a Raspberry Pi through the ODBC interface. FreeTDS provides the SQL Server protocol implementation, while pyodbc exposes ODBC to Python.

Install FreeTDS and pyodbc

On Raspberry Pi OS or another Debian-based distribution, install the ODBC headers, the FreeTDS driver, and an isolated Python environment:

sudo apt update
sudo apt install python3 python3-venv unixodbc unixodbc-dev freetds-bin freetds-dev tdsodbc

python3 -m venv .venv
. .venv/bin/activate
python -m pip install --upgrade pip
python -m pip install pyodbc

Confirm that unixODBC can see the driver before debugging Python:

odbcinst -q -d

The output should include FreeTDS. If it does not, inspect /etc/odbcinst.ini and verify that the tdsodbc package registered its driver library.

Connect from Python

Keep credentials outside the source file. The example expects MSSQL_SERVER, MSSQL_DATABASE, MSSQL_USER, and MSSQL_PASSWORD in the process environment:

import os

import pyodbc


connection_string = (
    "DRIVER={FreeTDS};"
    f"SERVER={os.environ['MSSQL_SERVER']};"
    "PORT=1433;"
    f"DATABASE={os.environ['MSSQL_DATABASE']};"
    f"UID={os.environ['MSSQL_USER']};"
    f"PWD={os.environ['MSSQL_PASSWORD']};"
    "TDS_Version=8.0;"
)

connection = pyodbc.connect(connection_string, timeout=10)

try:
    cursor = connection.cursor()
    cursor.execute(
        "SELECT id, name FROM dbo.t1 WHERE active = ?",
        1,
    )

    for row in cursor:
        print(row.id, row.name)
finally:
    connection.close()

The question-mark placeholder is an ODBC parameter marker. Passing values separately prevents data from being interpreted as SQL syntax. Identifiers such as table and column names cannot be parameterized; choose them from trusted application code instead of accepting arbitrary input.

Troubleshooting

• Driver not found: use the exact driver name reported by odbcinst -q -d. Braces are required around names containing spaces.
• Connection refused: verify the server address, TCP port 1433, firewall rules, and whether SQL Server accepts remote TCP connections.
• Login failed: confirm that SQL Server authentication is enabled and that the account can access the selected database.
• TLS errors: configure encryption and certificate validation for the installed FreeTDS version rather than disabling verification in application code.

For writes, call connection.commit() after a successful transaction or connection.rollback() after an error. Avoid enabling autocommit unless each statement is intentionally independent.