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

Question

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

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.

Answer 1

Just use dataframe apply

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

Answer 2

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

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

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