时间戳

首页 > 解决方案 > SQLAlchemy 抛出表名的键错误。不知道为什么？请指导

python - SQLAlchemy 抛出表名的键错误。不知道为什么？请指导

问题描述

我正在使用带有 SQLAlchemy 的 Flask 创建一个 Web 应用程序。我的数据库连接成功，可以看到表名。但是在保存表的引用时会出现关键错误。这是我的代码。数据库是fortune500_db，它有2 个表，fortune500 和sector_industry。感谢任何帮助。谢谢你

My code

# reflect an existing database into a new 
engine = create_engine("postgresql://<userid>:<passwd>@localhost:5432/fortune500_db")
print(engine.table_names())
inspector = inspect(engine)
columns = inspector.get_columns('sector_industry')
for column in columns:
    print(column["name"], column["type"])
Base = automap_base()

# reflect the tables
Base.prepare(engine, reflect=True)
Base.prepare(engine, reflect=True)

# Save references to each table
Fortune500 = Base.classes.fortune500
Sector_Industry = Base.classes.sector_industry

下面是运行 Flask app.py 后的输出

(pythonenv37) C:\Users\indum\OneDrive\Documents\group_project2>python fortune500\app.py
['fortune500', 'sector_industry']
Sector TEXT
Industry TEXT
Revenues DOUBLE PRECISION
Profits DOUBLE PRECISION
Profit_Margin DOUBLE PRECISION
Revenue_Percent DOUBLE PRECISION
Profit_Percent DOUBLE PRECISION
Traceback (most recent call last):
  File "C:\Users\indum\Anaconda3\envs\pythonenv37\lib\site-packages\sqlalchemy\util\_collections.py", line 210, in __getattr__
    return self._data[key]
KeyError: 'fortune500'

During handling of the above exception, another exception occurred:

Traceback (most recent call last):
  File "fortune500\app.py", line 37, in <module>
    Fortune500 = Base.classes.fortune500
  File "C:\Users\indum\Anaconda3\envs\pythonenv37\lib\site-packages\sqlalchemy\util\_collections.py", line 212, in __getattr__
    raise AttributeError(key)
AttributeError: fortune500

这是fortune500 的架构。

------Create table fortune500 data------
create table fortune500 (
    "Id" serial primary key,
    "Rank" int not null,
    "Title" varchar not null,
    "Employees" int not null,
    "CEO" varchar not null,
    "CEO Title" varchar not null,
    "Sector" varchar not null,
    "Industry" varchar not null,
    "Years_on_Fortune_500_List" varchar not null,
    "City" varchar not null,
    "State" varchar not null,
    "Latitude" numeric not null,
    "Longitude" numeric not null,
    "Revenues" numeric,
    "Revenue_Change" numeric,
    "Profits" numeric,
    "Profit_Change" numeric,
    "Assets" numeric,
    "Mkt_Value_as_of_3/29/18" numeric,
    "Symbol" char(10)
    );

该模式是在 PostgreSQL 中创建的。但是数据是使用 pd.to_sql 命令写入的，而我的 pandas 数据框没有主键列。

# Loading fortune500 data into postgreSQL table
fortune500_data.to_sql(name='fortune500', if_exists='replace', con=engine, index=False)

这会是个问题吗？

标签： pythonsqlalchemy

解决方案

推荐阅读