Search code examples
pythonsqlgoogle-cloud-platformsqlalchemypyodbc

GCP SQL Server Python connection (sqlalchemy) Error

I have the following code:

import sqlalchemy as sa

db_connect_string = 'driver://username:password@server/db'

engine = sa.create_engine(db_connect_string)

connection = engine.connect()

In the connection string driver is mssql, username is sqlserver and server is the GCP public server IP

In the GCP console, under connections, public IP connectivity is enabled and my current machine IP is whitelisted, I tested the connection is SSMS and I am able to successfully connect to the server via SSMS

I get the following error:

sqlalchemy.exc.InterfaceError: (pyodbc.InterfaceError) ('IM002', '[IM002] [Microsoft][ODBC Driver Manager] Data source name not found and no default driver specified (0) (SQLDriverConnect)')
(Background on this error at: https://sqlalche.me/e/20/rvf5)

I have tried multiple other drivers (such as mssql+pyodbc and SQL Server) and checked my system drivers, But it still does not work with other drivers

The error is unfortunately non descript and I cannot figure out why the connection is failing but works just fine via SSMS.

Solution

  • Referring to the following sqlalchemy documentation https://docs.sqlalchemy.org/en/20/dialects/mssql.html#hostname-connections

    When using a hostname connection, the driver name must also be specified in the query parameters of the URL. As these names usually have spaces in them, the name must be URL encoded which means using plus signs for spaces

    This means you should alter your connection string to contain the driver name like so:

    engine = create_engine("mssql+pyodbc://username:password@server/db?driver=ODBC+Driver+17+for+SQL+Server")

    You will need to add the string for the appropriate driver installed on your machine.