如何在MSSQL的SQLAlChemy中设置架构？ [英] How do I set the schema in SQLAlchemy for MSSQL?

问题描述

我目前这样做：

#!/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。我可以使用相同的凭据通过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中设置架构？的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！