CODEWITHSUNDEEP
pythonjsonpostgresqlrest
Schwenk
Schwenk

Reputation: 224

Insert JSON data from REST API into PostgreSQL table using Python

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

Upvotes: 1

Views: 5909

Answers (2)

Schwenk
Schwenk

Reputation: 224

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,))

Upvotes: 0

Adrian Klaver
Adrian Klaver

Reputation: 19724

See here JSON Adaption.

So something like:

from psycopg2.extras import Json

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

Upvotes: 1

Related Questions