19

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.

Alexander Ka
  • 259
  • 1
  • 2
  • 11

4 Answers4

16

Just use dataframe apply

df['bets'] = df['bets'].apply(json.dumps)
buddemat
  • 4,552
  • 14
  • 29
  • 49
Dima Berehovets
  • 220
  • 2
  • 5
11

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']))
Nikaido
  • 4,443
  • 5
  • 30
  • 47
Przemek Karwowski
  • 126
  • 1
  • 4
11

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)

astrojuanlu
  • 6,744
  • 8
  • 45
  • 105
0

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)
Sergiy Iurenko
  • 39
  • 4