CODEWITHSUNDEEP
jsonpython-3.xpandaspostgresqlsqlalchemy
Alexander Ka
Alexander Ka

Reputation: 299

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

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.

Upvotes: 23

Views: 66973

Answers (4)

Sergiy Iurenko
Sergiy Iurenko

Reputation: 87

Just register an adapter for type dict with psycopg2:

from psycopg2.extensions import register_adapter, AsIs
import json

def adapt_dict(dict_var):
    return AsIs("'" + json.dumps(dict_var) + "'")

register_adapter(dict, adapt_dict)

Upvotes: 4

user554319
user554319

Reputation:

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',
  dtype={"bets": sqlalchemy.types.JSON},
)

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

Upvotes: 18

Dima Berehovets
Dima Berehovets

Reputation: 250

Just use dataframe apply

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

Upvotes: 19

Przemek Karwowski
Przemek Karwowski

Reputation: 146

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']))

Upvotes: 13

Related Questions