1

I have a pyodbc connection object using the keyword functionality of pyodbc.connect() (https://github.com/mkleehammer/pyodbc/wiki/Module#connect).

The keywords include a driver parameter:

conn = pyodbc.connect(driver="SQL Server", server="myserver")

I am using this connection to pass to the SQLAlchemy create_engine() function through the creator parameter (docs) as per the suggestion at this answer:

engine = create_engine("mssql+pyodbc://", creator=lambda: conn)

However, the creator parameter now ignores the connection parameters specified in the URL parameter:

Usage of this function causes connection parameters specified in the URL argument to be bypassed.

This means I'm getting this warning message when creating the engine:

SAWarning: No driver name specified; this is expected by PyODBC when using DSN-less connections "No driver name specified; "

However, running a sql query as a test returns correct data. How can I supply the driver information (I'm assuming its mssql+pyodbc) to the create_engine function to remove this warning?

Tom Malkin
  • 2,134
  • 2
  • 19
  • 35

1 Answers1

0

It seems you are passing the DBAPI connection and not the creator. Try declaring your creator function:

def creator():
    return pyodbc.connect(driver="SQL Server", server="myserver")

And pass the function (not evaluation of the function) as the value for creator:

engine = create_engine("mssql+pyodbc://", creator=creator)
mvaldes
  • 11
  • 4
  • wouldn't the lambda function in the function call do the same thing?: creator=lambda: conn – Tom Malkin Mar 13 '19 at 05:21
  • Well the lambda will return always the same connection. I'm assuming there are more than one thread and the conn is not been shared. If its not been shared, you could try to do it global to each thread and in that case the lambda will be fine, and you have to be careful using the connection. But most commonly you want one connection for each thread, which is why the function that returns new connections is convenient. After the querys are done each thread closes its connection and returns it to de pool. – mvaldes Mar 14 '19 at 15:28
  • Uhmm.. reading pyodbc docs more carefully, it seems connections can't be shared between threads: https://github.com/mkleehammer/pyodbc/wiki/Module#threadsafety (this is a restriction on current pyodbc implementation and not odbc in general) – mvaldes Mar 14 '19 at 15:58