CODEWITHSUNDEEP
sql-serversqlalchemyconnectionpyodbc
AGc
AGc

Reputation: 41

Creating a SQLAlchemy engine from a SQL Server ODBC connection string

I tried to connect using pyodbc like this and it worked:

 self.connection = pyodbc.connect('Driver={ODBC Driver 18 for SQL Server};Server=' + server_full_name + ';Database=' + database + ';ENCRYPT=yes;UID=' + full_user+';PWD=' + self.db_password)

While I am trying to do the same thing using SQL Alchemy:

connection_string = 'Driver={ODBC Driver 18 for SQL Server};Server=' + server_full_name + ';Database=' + database + ';ENCRYPT=yes;UID=' + full_user+';PWD=' + self.db_password
self.connection = create_engine(f'mssql+pyodbc:///?odbc_connect={connection_string}').connect()

This doesn't work and I am getting the following error

sqlalchemy.exc.OperationalError: (pyodbc.OperationalError) ('08001', '[08001] [Microsoft][ODBC Driver 18 for SQL Server]Neither DSN nor SERVER keyword supplied (0) (SQLDriverConnect)'

EDIT: The problem was that I was using python 3.8. Installed 3.10 and it works fine. If you want to make it work without installing a new version of Python use urllib.

Upvotes: 0

Views: 1218

Answers (1)

Gord Thompson
Gord Thompson

Reputation: 123654

When using a pass-through ODBC connection string to create a SQLAlchemy Engine object, the recommended approach is to use SQLAlchemy's URL object:

from sqlalchemy import create_engine
from sqlalchemy.engine import URL

connection_string = "Driver=…"
connection_url = URL.create(
    "mssql+pyodbc",
    query={"odbc_connect": connection_string}
)
engine = create_engine(connection_url)

Upvotes: 1

Related Questions