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](https://stackoverflow.com/questions/31796332/psycopg2-insert-python-dictionary-as-json) — Stefano Frazzetto, Jan 24 '21 at 19:31
In addition to @StefanoFrazzetto post, see this [Query parameters](https://www.psycopg.org/docs/usage.html#passing-parameters-to-sql-queries) for why you should not be using formatted strings. — Adrian Klaver, Jan 24 '21 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, Jan 24 '21 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, Jan 24 '21 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 cursor.execute(sql,cat_fact) TypeError: dict is not a sequence — Schwenk, Jan 24 '21 at 19:44

score 1 · Answer 1 · answered Jan 24 '21 at 20:17

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 '21 at 20:17

Adrian Klaver

15,886
2
17
28

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('https://cat-fact.herokuapp.com/facts').json() instead of requests.get('https://cat-fact.herokuapp.com/facts').json. Thanks already Adrian – Schwenk Jan 24 '21 at 20:37
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 Jan 24 '21 at 21:45
My answer assumes that `cat_fact` is a Python object as you would get from doing `json.loads(json_str)`. – Adrian Klaver Jan 24 '21 at 21:53

score 0 · Answer 2 · answered Jan 25 '21 at 14:35

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 '21 at 14:35

Schwenk

225
2
13

1

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. – Adrian Klaver Jan 25 '21 at 15:26

Insert JSON data from REST API into PostgreSQL table using Python

2 Answers2