2

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 2

2

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.

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

Comments

0

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

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

1 Comment

the username and password are correct, I can connect to the database just fine, the error is when I try to reflect the database in MetaData

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge you have read our privacy policy.

Start asking to get answers

Find the answer to your question by asking.

Ask question

Explore related questions

See similar questions with these tags.