CODEWITHSUNDEEP
python-3.xpandaspostgresqlsqlalchemy
Jack
Jack

Reputation: 115

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

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.

Upvotes: 2

Views: 1417

Answers (1)

Bob
Bob

Reputation: 14654

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)

Upvotes: 2

Related Questions