Sqlalchemy is using the schema name instead of db name in USE command

Viewed 755

I'm trying to use SQLAlchemy to connect to a SQL Server database over the network. I was having som troubles using pyodbc as driver so a switched to pymssql and finally managed to create the engine and connect to the server in the 'fit_alunos' database using my username 'salas\guilherme.santo':

from sqlalchemy import create_engine, inspect

eng = create_engine('mssql+pymssql://salas\guilherme.santo:pass@server/fit_alunos?charset=utf8')

then if I inspect the engine, everything seems ok:

insp = inspect(engine)

insp.default_schema_name  # 'SALAS\\Guilherme.Santo'
insp.get_schema_names()  # a list of schemas with the pattern SALAS\\'something'
insp.get_table_names()  # all the tables in my schema, with no problem

but if I try to create a MetaData object and reflect the engine:

from sqlachemy import MetaData

meta = MetaData()
meta.reflect(bind=eng)

I got this OperationalError:

OperationalError: (pymssql.OperationalError) (911, b"Database 'SALAS\\Guilherme' does not exist. Make sure that the name is entered correctly.DB-Lib error message 20018, severity 16:\nGeneral SQL Server error: Check messages from the SQL Server\n")
[SQL: use [SALAS\Guilherme]]
(Background on this error at: http://sqlalche.me/e/e3q8)

I guess that SQLAlchemy is interpreting that the database is "SALAS\Guilherme" and the schema is "Santo", instead of the the database "fit_alunos" and the schema "SALAS\Guilherme.Santo".

is there a way to configure the db and schema so it can load it correctly?

[edit]

I ran the reflect method wit an engine with echo=True and find that it gets the db name using a SQL function:

2019-10-17 16:27:16,330 INFO sqlalchemy.engine.base.Engine select db_name()
2019-10-17 16:27:16,330 INFO sqlalchemy.engine.base.Engine {}
2019-10-17 16:27:16,350 INFO sqlalchemy.engine.base.Engine use [SALAS\Guilherme]
2019-10-17 16:27:16,350 INFO sqlalchemy.engine.base.Engine {}
2019-10-17 16:27:16,389 INFO sqlalchemy.engine.base.Engine ROLLBACK
---------------------------------------------------------------------------
MSSQLDatabaseException                    Traceback (most recent call last)

It seems that SELECT db_name() is returning the schema name instead of the db name.

Then, I tested the return value from the SQL functions that get the DB name and schema name, and it seems to be right

with eng.connect() as con: 
     rs = con.execute("select schema_name();") 
     print(rs.fetchall())  # [('SALAS\\Guilherme.Santo',)]
     rs = con.execute("select db_name();") 
     print(rs.fetchall())  # [('fit_alunos',)]

2 Answers

It appears to be a bug in SQLAlchemy. I've opened an issue on GitHub and there is a patch to be included in the next version (1.4) that will fix it.

It seems you need to usename and pass with a colon.

pymssql
engine = create_engine('mssql+pymssql://scott:tiger@hostname:port/dbname')
Related