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")

Have you tried just doing engine.execute("USE databasename") immediately after calling engine_create using the normal DSN syntax? — Gord Thompson
– Gord Thompson, Commented Aug 2, 2017 at 22:04
Oh wow this is great! I did not think of using SQL for this. Would you please turn this as an "answer" not "comment" so that I can choose this as a solution? — E.K.
– E.K., Commented Aug 3, 2017 at 15:46

user787267 · Accepted Answer · 2017-11-14 08:16:26Z

6

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.

edited Nov 14, 2017 at 8:16

answered Nov 13, 2017 at 16:10

user787267

3,0803 gold badges27 silver badges37 bronze badges

Sign up to request clarification or add additional context in comments.

Comments

Gord Thompson · Accepted Answer · 2021-04-05 13:26:53Z

3

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.

edited Apr 5, 2021 at 13:26

answered Aug 3, 2017 at 17:37

Gord Thompson

125k38 gold badges251 silver badges458 bronze badges

Collectives™ on Stack Overflow

sqlalchemy, specify database name with DSN

2 Answers 2

Comments

Comments

Your Answer

Linked

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

Comments

Comments

Your Answer

Sign up or log in

Post as a guest

Linked

Related