gpt4 book ai didi

python - 如何在 SQLAlchemy 中为 MSSQL 设置架构?

转载 作者:太空狗 更新时间:2023-10-30 01:26:00 24 4
gpt4 key购买 nike

我目前这样做:

#!/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 Server\n") [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 Server\nDB-Lib error message 20002,
severity 9:\nAdaptive Server connection failed (192.168.123.456:1433)\n")

如何设置模式?

最佳答案

您还可以在类定义中指定架构名称(这不是您的具体情况,但我认为这是常见情况)。

例如,如果您将表“dog”放入“animal”模式中:

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)

关于python - 如何在 SQLAlchemy 中为 MSSQL 设置架构?,我们在Stack Overflow上找到一个类似的问题: https://stackoverflow.com/questions/47077649/

24 4 0
Copyright 2021 - 2024 cfsdn All Rights Reserved 蜀ICP备2022000587号
广告合作:1813099741@qq.com 6ren.com