1

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

....

enter image description here

Improve this question
5
  • Did you have a look at the answers here? psycopg2 insert python dictionary as json Commented Jan 24, 2021 at 19:31
  • 2
    In addition to @StefanoFrazzetto post, see this Query parameters for why you should not be using formatted strings. 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." 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. 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 Commented Jan 24, 2021 at 19:44

2 Answers 2

Reset to default
1

See here JSON Adaption.

So something like:

from psycopg2.extras import Json

cursor.execute("INSERT into catfacts(data) VALUES (%s)", [Json(cat_fact)])
Improve this answer
Sign up to request clarification or add additional context in comments.

3 Comments

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
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'.
My answer assumes that cat_fact is a Python object as you would get from doing json.loads(json_str).
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,))
Improve this answer

1 Comment

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.

Start asking to get answers

Find the answer to your question by asking.

Ask question

Explore related questions

See similar questions with these tags.