时间戳

首页 > 解决方案 > 用中文列名访问sqlalchemy中的表

python - 用中文列名访问sqlalchemy中的表

问题描述

我正在按照 SQLalchemy 文档示例尝试修改现有数据库中的一些数据，但Base.classes.information_tbl会引发属性错误。我昨天刚开始玩数据库和 SQLalchemy，所以如果我没有提供正确的信息，我很抱歉。

def modify_db():
    Base = automap_base()
    engine = create_engine('mysql://root:123456@localhost/nutrition?charset=utf8mb4', echo=True) 
    Base.prepare(engine, reflect=True)    # reflect the tables
    nutrition_tbl = Base.classes.information_tbl
    s = Session(engine)

但是，此代码可以很好地查询数据库：

def query_db(password=123456, database="nutrition", table="information_tbl"):
    engine = create_engine(f'mysql://root:{password}@localhost/{database}?charset=utf8mb4')
    connection = engine.connect()
    metadata = sqlalchemy.MetaData()
    information_tbl = sqlalchemy.Table(table, metadata, autoload=True, autoload_with=engine)

    query = sqlalchemy.select([information_tbl]).where(information_tbl.columns.食品中文名 == "红茶")
    ResultProxy = connection.execute(query)
    ResultSet = ResultProxy.fetchall()
    return ResultSet

这是 AttributeError 的完整堆栈跟踪：

  File "D:\Anaconda3\envs\pytorch\lib\site-packages\sqlalchemy\util\_collections.py", line 186, in __getattr__
    return self._data[key]
KeyError: 'information_tbl'

During handling of the above exception, another exception occurred:

Traceback (most recent call last):
  File "D:\Anaconda3\envs\pytorch\lib\runpy.py", line 197, in _run_module_as_main
    return _run_code(code, main_globals, None,
  File "D:\Anaconda3\envs\pytorch\lib\runpy.py", line 87, in _run_code
    exec(code, run_globals)
  File "c:\Users\MyPC\.vscode\extensions\ms-python.python-2021.5.926500501\pythonFiles\lib\python\debugpy\__main__.py", line 45, in <module>
    cli.main()
  File "c:\Users\MyPC\.vscode\extensions\ms-python.python-2021.5.926500501\pythonFiles\lib\python\debugpy/..\debugpy\server\cli.py", line 444, in main   
    run()
  File "c:\Users\MyPC\.vscode\extensions\ms-python.python-2021.5.926500501\pythonFiles\lib\python\debugpy/..\debugpy\server\cli.py", line 285, in run_file
    runpy.run_path(target_as_str, run_name=compat.force_str("__main__"))
  File "D:\Anaconda3\envs\pytorch\lib\runpy.py", line 268, in run_path
    return _run_module_code(code, init_globals, run_name,
  File "D:\Anaconda3\envs\pytorch\lib\runpy.py", line 97, in _run_module_code
    _run_code(code, mod_globals, init_globals,
  File "D:\Anaconda3\envs\pytorch\lib\runpy.py", line 87, in _run_code
    exec(code, run_globals)
  File "f:\code\snippets\port_csv_to_db.py", line 91, in <module>
    modify_db()
  File "f:\code\snippets\port_csv_to_db.py", line 71, in modify_db
    nutrition_tbl = Base.classes.information_tbl
  File "D:\Anaconda3\envs\pytorch\lib\site-packages\sqlalchemy\util\_collections.py", line 188, in __getattr__
    raise AttributeError(key)
AttributeError: information_tbl

标签： pythonsqlalchemy

解决方案

推荐阅读