本文介紹了如何在MSSQL的SQLAlChemy中設置架構？的處理方法，對大家解決問題具有一定的參考價值，需要的朋友們下面隨著小編來一起學習吧！
問題描述
我目前這樣做：
#!/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:
General SQL Server error: Check messages from the SQL Server
") [SQL: 'SELECT foo FROM bar;']
我認為問題在于我必須使用架構exampleschema。我可以使用相同的憑據(jù)通過DBeaver訪問架構exampleschema中列foo的表bar。
但當我將/{schema}添加到連接字符串時，我得到
sqlalchemy.exc.OperationalError: (pymssql.OperationalError)
(18456,
b"Login failed for user 'exampleuser'.DB-Lib error message 20018,
severity 14:
General SQL Server error: Check messages from the SQL Server
DB-Lib error message 20002,
severity 9:
Adaptive Server connection failed (192.168.123.456:1433)
")
如何設置架構？
推薦答案
閱讀此SO問題Possible to set default schema from connection string?似乎無法使用連接字符串選擇架構。
嘗試使用完全限定名稱：
sql = ('SELECT foo FROM exampleschema.bar;')
這篇關于如何在MSSQL的SQLAlChemy中設置架構？的文章就介紹到這了，希望我們推薦的答案對大家有所幫助，
