'SQLAlchemy (psycopg2.ProgrammingError) can't adapt type 'dict'

Couldn't find a solution on the web for my problem. I am trying to insert this pandas df to a Postgresql table using SQLAlchemy

  • Pandas 0.24.2
  • sqlalchemy 1.3.3
  • python 3.7

Relevant part of my code is below:

engine = create_engine('postgresql://user:pass@host:5432/db')

file = open('GameRoundMessageBlackjackSample.json', 'r', encoding='utf-8')
json_dict = json.load(file)
df = json_normalize(json_dict, record_path='cards', meta=['bet', 'dealerId', 'dealerName', 'gameOutcome', 'gameRoundDuration', 'gameRoundId', 'gameType', 'tableId', 'win'])
df = df[['win', 'betAmount', 'bets']]

df.to_sql('test_netent_data', engine, if_exists='append')

enter image description here

When I try to load this table to sql without the column 'bets' everyting works as expected. But when I include it i get the following error:

sqlalchemy.exc.ProgrammingError: (psycopg2.ProgrammingError) can't adapt 
type 'dict'
[SQL: INSERT INTO test_netent_data (index, win, "betAmount", bets) VALUES (%(index)s, %(win)s, %(betAmount)s, %(bets)s)]
[parameters: ({'index': 0, 'win': '2000.00', 'betAmount': '1212112', 'bets': [{'name': '1', 'amount': '1212112'}]}, {'index': 1, 'win': '2000.00', 'betAmount': '1212000', 'bets': [{'name': '1', 'amount': '1212000'}]}, {'index': 2, 'win': '2000.00', 'betAmount': '1212112', 'bets': [{'name': '1', 'amount': '1212112'}]}, {'index': 3, 'win': '2000.00', 'betAmount': '1212000', 'bets': [{'name': '1', 'amount': '1212000'}]}, {'index': 4, 'win': '2000.00', 'betAmount': '1212112', 'bets': [{'name': '1', 'amount': '1212112'}]}, {'index': 5, 'win': '2000.00', 'betAmount': '1212000', 'bets': [{'name': '1', 'amount': '1212000'}]}, {'index': 6, 'win': '2000.00', 'betAmount': '1212112', 'bets': [{'name': '1', 'amount': '1212112'}]}, {'index': 7, 'win': '2000.00', 'betAmount': '1212000', 'bets': [{'name': '1', 'amount': '1212000'}]})]
(Background on this error at: http://sqlalche.me/e/f405)

I have checked the type of this column but it is (type object) no different from other columns. Ive also tried to convert it to string and got a bunch of other errors. I believe there should be a simple solution which I can't get my head around.

jsonpython-3.xpandaspostgresqlsqlalchemy


Solution 1:[1]

For me, the better way will be parse this list dict into separated columns. However if you want add column bets into SQL table you need to convert it. You wrote that this is object, but it is list with dicts. Below is code how to convert it into string:

df['bets'] = list(map(lambda x: json.dumps(x), df['bets']))

Solution 2:[2]

Just use dataframe apply

df['bets'] = df['bets'].apply(json.dumps)

Solution 3:[3]

Since you're using pandas.DataFrame.to_sql, a better alternative is to leverage the native JSON type:

df.to_sql(
  'test_netent_data',
  engine,
  if_exists='append',
  type={"bets": sqlalchemy.types.JSON},
)

(inspired by https://stackoverflow.com/a/41469431/554319)

Sources

This article follows the attribution requirements of Stack Overflow and is licensed under CC BY-SA 3.0.

Source: Stack Overflow

Solution Source
Solution 1 Nikaido
Solution 2 buddemat
Solution 3 astrojuanlu

Related Questions