Sqlalchemy, Specify Database Name With Dsn

I am trying to connect to a SQL Server from Linux using sqlalchemy. This page shows DSN-based connection as below. engine = create_engine('mssql+pyodbc://scott:tiger@some_dsn') Is

Solution 1:

You can pass arguments directly to the pyodbc.connect method through the connect_args parameter in create_engine:

def my_create_engine(mydsn, mydatabase, **kwargs):
    connection_string = 'mssql+pyodbc://@%s' % mydsncargs= {'database': mydatabase}
    cargs.update(**kwargs)
    e = sqla.create_engine(connection_string, connect_args=cargs)
    return e

This will also enable the database to be persisted through several transactions / sessions.

Solution 2:

I just tried something like this and it seemed to work fine

engine = create_engine("mssql+pyodbc://scott:tiger@some_dsn")
with engine.begin() as conn:
    conn.execute("USE databasename")

As a general rule we should be careful about changing the current catalog (a.k.a. "database") after establishing a connection because some technologies (e.g., JDBC Connection objects) keep track of the current catalog and can get confused if we directly call USE ... in T-SQL to change the current catalog. However, I'm not aware that pyodbc's Connection object does any such caching so this approach is probably okay.