Insert JSON data from REST API into PostgreSQL table using Python

Question

I tried to insert each element of the json api into my postgres table.

But I get the follwoing error:

Traceback (most recent call last):
  File "c:/Users/myname/Documents/repos/docker-playground/parse_json_to_postgres.py", line 20, in <module>
    cursor.execute(f"INSERT into catfacts(data) VALUES (  {cat_fact}  )")
psycopg2.errors.SyntaxError: syntax error at or near "{"
LINE 1: INSERT into catfacts(data) VALUES (  {'status': {'verified':...
                                             ^

My postgres table:

CREATE TABLE cat_facts (
    id serial NOT NULL PRIMARY KEY,
    data jsonb NOT NULL
);

My Python code to insert the data into the table:

import requests, json, psycopg2  


cat_facts_json = requests.get('https://cat-fact.herokuapp.com/facts').json


conn = psycopg2.connect(user="postgres",
                                password="password",
                                host="localhost",
                                port="5432",
                                database="postgres")

cursor = conn.cursor()

for cat_fact in cat_facts_json():
    cursor.execute(f"INSERT into catfacts(data) VALUES ( \' {cat_fact} \' )")

API = https://cat-fact.herokuapp.com/facts

What I am trying to achieve:

INSERT INTO cat_facts(data) VALUES ('{"status":{"verified":true,"sentCount":1},"type":"cat","deleted":false,"_id":"58e008800aac31001185ed07","user":"58e007480aac31001185ecef","text":"Wikipedia has a recording of a cat meowing, because why not?","__v":0,"source":"user","updatedAt":"2020-08-23T20:20:01.611Z","createdAt":"2018-03-06T21:20:03.505Z","used":false}');
INSERT INTO cat_facts(data) VALUES ('{"status":{"verified":true,"sentCount":1},"type":"cat","deleted":false,"_id":"58e008630aac31001185ed01","user":"58e007480aac31001185ecef","text":"When cats grimace, they are usually \"taste-scenting.\" They have an extra organ that, with some breathing control, allows the cats to taste-sense the air.","__v":0,"source":"user","updatedAt":"2020-08-23T20:20:01.611Z","createdAt":"2018-02-07T21:20:02.903Z","used":false},{"status":{"verified":true,"sentCount":1},"type":"cat","deleted":false,"_id":"58e00a090aac31001185ed16","user":"58e007480aac31001185ecef","text":"Cats make more than 100 different sounds whereas dogs make around 10.","__v":0,"source":"user","updatedAt":"2020-08-23T20:20:01.611Z","createdAt":"2018-02-11T21:20:03.745Z","used":false}');

....

Did you have a look at the answers here? psycopg2 insert python dictionary as json — Stefano Frazzetto
– Stefano Frazzetto, Commented Jan 24, 2021 at 19:31
In addition to @StefanoFrazzetto post, see this Query parameters for why you should not be using formatted strings. — Adrian Klaver
– Adrian Klaver, Commented Jan 24, 2021 at 19:35
@AdrianKlaver I like the documentation part where it says: "Never, never, NEVER use Python string concatenation (+) or string parameters interpolation (%) to pass variables to a SQL query string. Not even at gunpoint." — Stefano Frazzetto
– Stefano Frazzetto, Commented Jan 24, 2021 at 19:38
Yes, I tried that for cat_fact in cat_facts_json(): cursor.execute("INSERT into catfacts(data) VALUES ( %s )" % json.dumps(cat_fact) ) which didnt work still the same error. — Schwenk
– Schwenk, Commented Jan 24, 2021 at 19:42
And this for cat_fact in cat_facts_json(): sql = "INSERT into catfacts(data) VALUES ( %s )" cursor.execute(sql,cat_fact) retuns:Traceback (most recent call last): File "c:/Users/user/Documents/repos/docker-playground/parse_json_to_postgres.py", line 33, in <module> cursor.execute(sql,cat_fact) TypeError: dict is not a sequence — Schwenk
– Schwenk, Commented Jan 24, 2021 at 19:44

Adrian Klaver · Accepted Answer · 2021-01-24 20:17:20Z

1

See here JSON Adaption.

So something like:

from psycopg2.extras import Json

cursor.execute("INSERT into catfacts(data) VALUES (%s)", [Json(cat_fact)])

answered Jan 24, 2021 at 20:17

Adrian Klaver

20.3k3 gold badges24 silver badges40 bronze badges

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

3 Comments

Schwenk Over a year ago

Not quite there yet, but closer. The print works and it return each cat fact object. The insert is somehow not working. for cat_fact in cat_facts_json: print(cat_fact) cursor.execute("INSERT into cat_facts(data) VALUES (%s)", [Json(cat_fact)]) And missed () in my get query which is now requests.get('cat-fact.herokuapp.com/facts').json() instead of requests.get('cat-fact.herokuapp.com/facts').json. Thanks already Adrian

Adrian Klaver Over a year ago

Please add this to your question where it can be formatted correctly. When you do that also include the result of print(cat_fact). Also define 'not working'.

Adrian Klaver Over a year ago

My answer assumes that cat_fact is a Python object as you would get from doing json.loads(json_str).

Schwenk · Accepted Answer · 2021-01-25 14:35:38Z

0

I got it working now:

for cat_fact in cat_facts_json:

    data = json.dumps(cat_fact)
    insert_query = "insert into cat_facts (data) values (%s) returning data"
    cursor.execute(insert_query, (data,))
    conn.commit()
    conn.close()

I considered your comments @Stefano Frazzetto and @Adrian Klaver.

json.dumps works !
I didn't execute the parameters directly in the execute query

I still think, this is a pretty odd syntax with the comma after data:

cursor.execute(insert_query, (data,))

answered Jan 25, 2021 at 14:35

Schwenk

2242 silver badges18 bronze badges

1 Comment

Adrian Klaver Over a year ago

Which is the long way around of doing:

insert_query = "insert into cat_facts (data) values (%s) returning data"  cursor.execute(insert_query, (Json(cat_fact),))

. (data,) is a tuple and Python syntax requires it to have a comma to distinguish it from just a bracketed value so: t = (1,) t[0] 1, t = (1) t[0] 'int' object is not subscriptable. If you don't want to do that use a list.

Collectives™ on Stack Overflow

Insert JSON data from REST API into PostgreSQL table using Python

2 Answers 2

3 Comments

1 Comment

Your Answer

Linked

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

3 Comments

1 Comment

Your Answer

Sign up or log in

Post as a guest

Linked

Related