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

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
}

CodePudding user response：

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.