sqlalchemy, specify database name with DSN

Question

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 there a way to specify a database name using DSN? I am aware that we can specify a database name either in odbc.ini or a SQL query but I would like to know if we can also do something like this.

engine = create_engine("mssql+pyodbc://scott:tiger@some_dsn/databasename")

Answer 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' % mydsn
    cargs = {'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.

Answer 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.