CODEWITHSUNDEEP
sql-serverpython-3.xsqlalchemypyinstaller
Brad Cooley
Brad Cooley

Reputation: 373

Which exact driver is sqlalchemy using?

I am having trouble with a MS SQL connection when using pyinstaller. When run in interactive mode, everything works as expected. After compiling to an exe, the MS SQL database connection times out on the first query with the following error:

(pyodbc.OperationalError) ('08001', '[08001] [Microsoft][ODBC Driver 17 for SQL Server]Named Pipes Provider: Could not open a connection to SQL Server [53]. (53) (SQLDriverConnect); [08001] [Microsoft][ODBC Driver 17 for SQL Server]Login timeout expire (0); ...

My connection string is similar to the following:

create_engine(
"mssql+pyodbc://USER:PASSWORD@SERVERIP/DB_NAME?driver=ODBC+Driver+17+for+SQL+Server"
)

In attempting to diagnose the issue, I am printing out the drivers available to pyodbc with pyodbc.drivers() (which shows a large disparity between available drivers in compiled vs interactive) as well as the driver in use using

print(session.bind.dialect.name)
> pyodbc
print(session.bind.dialect.driver)
> mssql

It returns the upper level python modules which are being used but not the .dll that is handling it at a lower level. Is there any way to find which exact driver is being used? Any tips on what could be causing the error in the compiled version in the firstplace would be appreciated as well.

Upvotes: 2

Views: 2598

Answers (2)

Jose Lora
Jose Lora

Reputation: 1390

The issue may be in your connection string.

To create a proper connection string to connect to MSSQL Server ODBC driver with sqlAlchemy use the following:

import urllib
from sqlalchemy import create_engine

server = 'serverName\instanceName,port' # to specify an alternate port
database = 'mydb' 
username = 'myusername' 
password = 'mypassword'

params = urllib.parse.quote_plus('DRIVER={ODBC Driver 17 for SQL Server};SERVER='+server+';DATABASE='+database+';UID='+username+';PWD='+password)

engine = create_engine("mssql+pyodbc:///?odbc_connect=%s" % params)

Also, you can check the following article Connecting to Microsoft SQL Server using SQLAlchemy and PyODBC

Upvotes: 3

Gord Thompson
Gord Thompson

Reputation: 123689

Is there any way to find which exact driver [.dll] is being used?

import pyodbc

cnxn = engine.raw_connection()
print(cnxn.getinfo(pyodbc.SQL_DRIVER_NAME))  # msodbcsql17.dll
print(cnxn.getinfo(pyodbc.SQL_DRIVER_VER))  # 17.08.0001

Upvotes: 2

Related Questions