SQLAlchemy Setup for Microsoft SQL Server 18 ODBC

Warwick Matthews 0
Viewed 24

I'm actually asking this question as I have recently struggled to get SQLAlchemy working with SQL Server ODBC Driver 18 on a Mac and have not seen an up-to-date description that worked.

NB: 'TrustServerCertificate': 'yes' is very key for a lot of setups and knowing where and how to express it in the connection string is not immediately obvious.

This works (as @ 7 Sep 2022) with the MSSQL ODBC driver instructions at: Install the Microsoft ODBC driver for SQL Server (macOS)

sqlalchemy sql-server sql python
1 Answers
constring = f"mssql+pyodbc://{username}:{password}@{server}/{database}?driver={driver}"
print (f"Connection String: {constring}")
dbEngine = sqlalchemy.create_engine(constring, fast_executemany=True, connect_args={'connect_timeout': 10, 'TrustServerCertificate': 'yes'}, echo=False) 
try:
    with dbEngine.connect() as con:
        con.execute("SELECT 1")
    print('SQLAlchemy engine is valid')
except Exception as e:
    print(f'Engine invalid: {str(e)}')```
Warwick Matthews0
Related