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 table bar
with the column foo
in the schema exampleschema
with the same credentials via DBeaver.
But when I add /{schema}
to the connection string, I get
sqlalchemy.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?
MSSQL has added support for LIMIT / OFFSET as of SQL Server 2012, via the “OFFSET n ROWS” and “FETCH NEXT n ROWS” clauses. SQLAlchemy supports these syntaxes automatically if SQL Server 2012 or greater is detected.
In order to set the default schema for a Windows Group, open SQL Server Management Studio, navigate to Security > Logins, right click on the Windows Group that you want to change and choose Properties. The below window will then open.
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)
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)
If you love us? You can donate to us via Paypal or buy me a coffee so we can maintain and grow! Thank you!
Donate Us With