How to iterate trough JSON list in python and insert into PostgreSQL?

Question

I am a bit stuck and the solutions I can think of will end in spaghetti code.

I am having the following JSON, I create an object of it in python 3 and I have to insert the values into PostgreSQL.

{
    "name": "test",
    "country": "DE",
    "time": "21-Oct-2019 (09:58:01.694763)",
    "toCurrency": ["EUR", "USD", "GBP"],
    "fromCurrency": ["DKK", "DKK", "DKK"],
    "buyMargin": ["1.2800", "1.2800", "2.0000"],
    "sellMargin": ["1.2800", "1.2800", "2.0000"],
    "unit": "M100" 
}

I am getting the exception: can only concatenate str (not "list") to str

How can I iterate through those lists in Python and insert one value at a time?

At the same time the other values of 'name', 'country', 'time' should be inserted every time.

Currently I can insert this JSON:

{
"name": "test",
"country": "DE",
"time": "21-Oct-2019 (09:58:01.694763)",
"toCurrency": "EUR",
"fromCurrency": "DKK",
"buyMargin": "1.2800",
"sellMargin": "1.2800",
"unit": "M100" 
}

are the values going to be inserted in a json column or they have to be parsed and inserted in 8 different columns? — Jim Jones
– Jim Jones, Commented Oct 23, 2019 at 12:42

Trollsors · Accepted Answer · 2019-10-23 12:45:49Z

1

Convert your data to a dataframe using pandas. Then using sqlalchemy, store this dataframe as a table in PostGre.

import pandas as pd
from sqlalchemy import create_engine
d1 = {
    "name": "test",
    "country": "DE",
    "time": "21-Oct-2019 (09:58:01.694763)",
    "toCurrency": ["EUR", "USD", "GBP"],
    "fromCurrency": ["DKK", "DKK", "DKK"],
    "buyMargin": ["1.2800", "1.2800", "2.0000"],
    "sellMargin": ["1.2800", "1.2800", "2.0000"],
    "unit": "M100" 
}
df1 = pd.DataFrame(d1)
engine = create_engine(
        'postgresql://username:password@host:port/database')
df1.to_sql(tablename, engine, if_exists='append', index=False)

Your Dataframe will look as follows. And will similarly be stored as a table in PostGre

answered Oct 23, 2019 at 12:45

Trollsors

4921 gold badge4 silver badges17 bronze badges

Sign up to request clarification or add additional context in comments.

2 Comments

Demi Dimitrova Over a year ago

Thank you for this solution! It is really clean and structured. Can I specify primary keys for the table in this way?

Trollsors Over a year ago

You can do that from postgres itself, after you create the table.

Kostas Charitidis · Accepted Answer · 2019-10-23 12:43:34Z

1

If I understand correct and your toCurrency, fromCurrency, buyMargin and sellMargin have equal number of elements which they should, the following should work gor you:

j = {
    "name": "test",
    "country": "DE",
    "time": "21-Oct-2019 (09:58:01.694763)",
    "toCurrency": ["EUR", "USD", "GBP"],
    "fromCurrency": ["DKK", "DKK", "DKK"],
    "buyMargin": ["1.2800", "1.2800", "2.0000"],
    "sellMargin": ["1.2800", "1.2800", "2.0000"],
    "unit": "M100"
}

for idx, val in enumerate(j['toCurrency']):
    print(j['toCurrency'][idx], j['fromCurrency'][idx], j['buyMargin'][idx], j['sellMargin'][idx])

So your insert statement should be in the position of the print and your columns should be accordingly.

answered Oct 23, 2019 at 12:43

Kostas Charitidis

3,1231 gold badge15 silver badges23 bronze badges

Collectives™ on Stack Overflow

How to iterate trough JSON list in python and insert into PostgreSQL?

2 Answers 2

2 Comments

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

2 Comments

Comments

Your Answer

Sign up or log in

Post as a guest

Related