SQLAlchemy: Saving Dataframe to SQL (can't adapt type 'dict')

Question

I'm getting an error when trying to store data in my pgSQL database, the following code is the minimum example required to generate the error:

import pandas as pd
from sqlalchemy import create_engine

info_teamdata_df = pd.DataFrame(columns=['Team_1_bans'])
my_dict = {'Team_1_bans': [{'championId': 99, 'pickTurn': 1}, {'championId': 17, 'pickTurn': 2}, {'championId': 360, 'pickTurn': 3}, {'championId': 63, 'pickTurn': 4}, {'championId': 238, 'pickTurn': 5}]}
info_teamdata_df = info_teamdata_df.append(my_dict, ignore_index=True)


db = create_engine('postgresql://postgres:pass@host/MatchData_DB')
connection_alchemy = db.connect()

info_teamdata_df.to_sql('Tester', connection_alchemy, if_exists='append', index=False)

The error being:

sqlalchemy.exc.ProgrammingError: (psycopg2.ProgrammingError) can't adapt type 'dict'
[SQL: INSERT INTO "Tester" ("Team_1_bans") VALUES (%(Team_1_bans)s)]
[parameters: {'Team_1_bans': [{'championId': 99, 'pickTurn': 1}, {'championId': 17, 'pickTurn': 2}, {'championId': 360, 'pickTurn': 3}, {'championId': 63, 'pickTurn': 4}, {'championId': 238, 'pickTurn': 5}]}]
(Background on this error at: https://sqlalche.me/e/14/f405)

I'm not really sure on what the issue is, was hoping someone could correct the above code and explain what the issue is, I'm likely to encounter this in the future. Id like to input the data as a single column, but if there is a better way to do so happy to try.

Double check your dataframe looks right. Looks like you're trying to upload a dict to the database (most databases don't accept dicts) — rayad
– rayad, Commented Jun 16, 2022 at 21:35
maybe a regular table format like for instance pd.DataFrame(my_dict['Team_1_bans']).assign(type='Team_1_bans')? — Stef
– Stef, Commented Jun 17, 2022 at 8:00
Is there a way to change the format once its inside the DataFrame? — Jack
– Jack, Commented Jun 17, 2022 at 10:52
yes: df = info_teamdata_df.explode('Team_1_bans') followed by df = df['Team_1_bans'].apply(pd.Series) — Stef
– Stef, Commented Jun 17, 2022 at 13:32

Bob · Accepted Answer · 2022-06-19 11:34:37Z

2

+100

It seems you are trying to upload dictionaries to the database column Team_1_bans of the table tester. If every element has championId and pickTurn, that could be expressed in a single dataframe.

info_team_data = pd.DataFrame(
 [{'championId': 99, 'pickTurn': 1}, 
  {'championId': 17, 'pickTurn': 2}, 
  {'championId': 360, 'pickTurn': 3}, 
  {'championId': 63, 'pickTurn': 4}, 
  {'championId': 238, 'pickTurn': 5}])

info_teamdata_df.to_sql(
  'Tester', 
  connection_alchemy, 
  if_exists='append', 
  index=False)

answered Jun 19, 2022 at 11:34

Bob

14.8k1 gold badge11 silver badges39 bronze badges

Sign up to request clarification or add additional context in comments.

Collectives™ on Stack Overflow

SQLAlchemy: Saving Dataframe to SQL (can't adapt type 'dict')

1 Answer 1

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

Comments

Your Answer

Sign up or log in

Post as a guest

Related