How to retrieve data from an api and insert it to my JSON column in postgresql?

Question

I have created a table in PostgreSQL with a JSON column and when I try to Insert data into the column It returns an error. I try to get those data from an api using python: here is the what I have done so far:

defaulturl = 'https://pokeapi.co/api/v2/pokemon/1/'
sql = 'CREATE TABLE IF NOT EXISTS pokeapi (id INTEGER, body JSONB);'
print(sql)
cur.execute(sql)

sql = 'SELECT COUNT(body) FROM pokeapi;'
count = myutils.queryValue(cur, sql)
if count < 1:
    objects = ['films', 'species', 'people']
    for obj in objects:
        sql = f"INSERT INTO pokeapi (body) VALUES ( 'https://pokeapi.co/api/v2/{obj}/1/' )";
        print(sql)
        cur.execute(sql, (defaulturl))
    conn.commit()

I try to loop through and retrieve the JSON data for urls ending in 1 to 100 and store it in the table in the body column. Note that the url is in the API and API have the following structure:

{
ability: {
name: "overgrow",
url: "https://pokeapi.co/api/v2/ability/65/"
},
is_hidden: false,
slot: 1
}

pletnes · Accepted Answer · 2022-02-02 18:07:01Z

2

You're trying to put the URL into the body column. This has 2 issues:

You didn't really mean to put it there
The URL doesn't validate as JSON so the database complains about the URL not being a valid JSON string.

Fix it by replacing (body) with (url, body), in the INSERT.

answered Feb 2, 2022 at 18:07

pletnes

4795 silver badges18 bronze badges

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

3 Comments

fyardlest Over a year ago

hello, @pletnes and thank you for the answer I really appreciate it. Now I change it with (url), in the INSERT it sent back it without error

Arson 0 Over a year ago

Hi @fyardlest, if you have found pletnes answer useful and it solves your problem, consider choosing his answer as the selected one for this question :)

fyardlest Over a year ago

Thank you @Arson0

Collectives™ on Stack Overflow

How to retrieve data from an api and insert it to my JSON column in postgresql?

1 Answer 1

3 Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

3 Comments

Your Answer

Sign up or log in

Post as a guest

Related