How do I set the schema in SQLAlchemy for MSSQL?
Solution 1
Reading this SO Question Possible to set default schema from connection string? it seems not possible to select schema using connection string.
try a full qualified name :
sql = ('SELECT foo FROM exampleschema.bar;')
Solution 2
You can also specify the schema name in the class definition (is not your specific case but I think it's a common situation).
For example, if you have a table "dog" into the "animal" schema:
from sqlalchemy import Column, Integer, String
from sqlalchemy.ext.declarative import declarative_base
Base = declarative_base()
class Notification(Base):
__tablename__ = "dog"
__table_args__ = {"schema": "animal"}
id = Column(Integer, primary_key=True)
name = Column(String)
Solution 3
If you load metadata from the database and load table from the metadata, you can set the schema during table load.
Then create a session and query the table
from sqlalchemy import MetaData, Table
from sqlalchemy.orm import sessionmaker
table = Table({Table Name}, {metadata}, autoload=True, autoload_with={engine}, schema={Schema name})
Session = sessionmaker()
Session.configure(bind={engine})
session = Session()
query = session.query(table)
Martin Thoma
I also have a blog about Code, the Web and Cyberculture (medium as well) and a career profile on Stackoverflow. My interests are mainly machine-learning, neural-networks, data-analysis, python, and in general backend development. I love building secure and maintainable systems.
Updated on June 04, 2022Comments
-
Martin Thoma almost 2 years
I currently do this:
#!/usr/bin/env python # 3rd party modules from sqlalchemy import create_engine # requires pymssql # local modules from config import cfg connection_string = 'mssql+pymssql://{user}:{password}@{host}:{port}/{db}' engine = create_engine(connection_string .format(host=cfg['db']['host'], db=cfg['db']['database'], user=cfg['db']['user'], password=cfg['db']['password'], port=cfg['db']['port'], schema=cfg['db']['schema'])) with engine.begin() as conn: sql = ('SELECT foo FROM bar;') rows = conn.execute(sql) print(rows)
But I get
File "/usr/local/lib/python3.5/dist-packages/sqlalchemy/engine/default.py", line 470, in do_execute cursor.execute(statement, parameters) File "pymssql.pyx", line 464, in pymssql.Cursor.execute (pymssql.c:7491) sqlalchemy.exc.ProgrammingError: (pymssql.ProgrammingError) (208, b"Invalid object name 'bar'.DB-Lib error message 20018, severity 16:\nGeneral SQL Server error: Check messages from the SQL Server\n") [SQL: 'SELECT foo FROM bar;']
I think the problem is that I have to use the schema
exampleschema
. I can access the tablebar
with the columnfoo
in the schemaexampleschema
with the same credentials via DBeaver.But when I add
/{schema}
to the connection string, I getsqlalchemy.exc.OperationalError: (pymssql.OperationalError) (18456, b"Login failed for user 'exampleuser'.DB-Lib error message 20018, severity 14:\nGeneral SQL Server error: Check messages from the SQL Server\nDB-Lib error message 20002, severity 9:\nAdaptive Server connection failed (192.168.123.456:1433)\n")
How do I set the schema?
-
matteogll almost 4 yearsYou can use raw query this way:
from sqlalchemy.orm import Session
session.execute("SELECT * FROM animal.dog")