我目前这样做:
#!/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)
但我得到
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 Servern") [SQL: 'SELECT foo FROM bar;']
我认为问题是我必须使用架构exampleschema
。我可以通过 DBeaver 使用相同的凭据访问具有架构exampleschema
foo
列的表bar
。
但是当我将/{schema}
添加到连接字符串时,我得到
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 ServernDB-Lib error message 20002,
severity 9:nAdaptive Server connection failed (192.168.123.456:1433)n")
如何设置架构?
您还可以在类定义中指定架构名称(不是您的具体情况,但我认为这是一种常见情况)。
例如,如果你在"动物"架构中有一个表"狗":
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)
如果从数据库加载元数据并从元数据加载表,则可以在表加载期间设置架构。
然后创建会话并查询表
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)
阅读这个 SO 问题 可以从连接字符串设置默认架构吗? 似乎无法使用连接字符串选择架构。
尝试使用完整的限定名称:
sql = ('SELECT foo FROM exampleschema.bar;')