0

I am creating a web app using Flask with SQLAlchemy. My connection to the database is successful and can see the table names. But while saving references of tables gets key error. Here's my code. The database is fortune500_db and it has 2 tables, fortune500 and 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

Below is the output after running 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

Here's the schema for 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)
    );

The schema was created in PostgreSQL. But data was written using pd.to_sql command and my pandas dataframe doesn't have a column for primary key.

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

Can that be an issue?

snakecharmerb
  • 47,570
  • 11
  • 100
  • 153
ic2019
  • 1
  • 1
  • 2
  • 2
    You are calling `Base.prepare(engine, reflect=True)` twice, was this intentional? – Martijn Pieters Sep 19 '19 at 15:42
  • Sorry. I removed the extra Base.prepare line. But that didn't help in removing the error. – ic2019 Sep 19 '19 at 16:44
  • I also configured primary key for both tables removing 'Id' column now. 'Rank' is the PK for fortune500 table and 'Sector' PK for sector_industry table. But still getting same 'key' error. – ic2019 Sep 19 '19 at 18:12
  • Saw this post. So looks like that's the root cause. https://stackoverflow.com/questions/30867390/python-pandas-to-sql-how-to-create-a-table-with-a-primary-key Thanks – ic2019 Sep 19 '19 at 18:27

0 Answers0