SQLAlchemy does not connect to SQL Server with ODBC Driver 18 for SQL Server but pyodbc does

Question

I am working on a windows pc and cannot connect to my Microsoft SQL server with SQLAlchemy.

I have the login credentials as follows: IP, Server name, user name, database, password.

I can't use windows authorization since I want to run the finished script off an r, pi or cloud service periodically throughout the day.

password = 'xxx'
server = os.environ['xxx']
database = os.environ['xxx']
username = os.environ['xxx']
driver='{ODBC Driver 18 for SQL Server}' #does not work with pyodbc if + used instead of {}

connection_string = f"mssql+pyodbc://{username}:{password}@{server}/{database}?driver={driver};Encrypt=False"

try:
    # Create the SQLAlchemy engine
    engine = create_engine(connection_string)
    
    # Test the connection
    with engine.connect() as connection:
        # Use the inspector to get table names
        inspector = inspect(connection)
        tables = inspector.get_table_names()
        
        # Print the list of tables
        print("Tables in the database:")
        for table in tables:
            print(table)

except Exception as e:
    print(f"Error connecting to the database: {e}")

I am able to connect to the server without issue using pyodbc only.

connection_string = f'DRIVER={driver};SERVER={server};DATABASE={database};UID={username};PWD={password};TrustServerCertificate=Yes;'

try:
    connection = pyodbc.connect(connection_string)
    print("Connection successful!")
    connection.close()
except Exception as e:
    print(f"Error connecting to the database: {e}")

When trying to use SQLAlchemy I either get an 08001, IM0002 or IM0012 Error depending on what I do to the connect string.

In driver='{ODBC Driver 18 for SQL Server}' I have also tried + instead of spaces with and without {}

I have also tried with and without TrustedServerCertificate=Yes/True and Encrypt=False/No

I expected that SQLAlchemy would have connected as easily pyodbc but I can't get it to work.

I also tried the method suggested here.

Full error:

Error connecting to the database: (pyodbc.Error) ('IM012', '[IM012] [Microsoft][ODBC Driver Manager] DRIVER keyword syntax error (0) (SQLDriverConnect)')

Any insight would be greatly appreciated.

Answer 1

For a SQLAlchemy connection URL, the querystring separator is &, not ;. Also, Encrypt=False is not valid, use Encrypt=no.

I usually recommend that people use URL.create() to build their connection URL:

import sqlalchemy as sa

connection_url = sa.engine.URL.create(
    "mssql+pyodbc",
    username="scott",
    password="tiger^5HHH",
    host="192.168.0.199",
    database="test",
    query={"driver": "ODBC Driver 18 for SQL Server", "Encrypt": "no"}
)

Then, if you want to see the stringified URL with the password hidden, just print it.

print(connection_url)
"""
mssql+pyodbc://scott:***@192.168.0.199/test?Encrypt=no&driver=ODBC+Driver+18+for+SQL+Server
"""