Inserting JSONB of a variable into table with Psycopg2 (Python to Postgres)

Question

My task is to get JSON-Data from pokeapi and put it in a table on a pg4e-server (postgres for everybody) with psycopg2.

It works despite I am not able to put the JSON-data from python into the JSONB column of the table on the server. I've tried several approaches, but I need your support.

How can I take the JSON saved in a variable (text) and store it into the table?

Thank you very much in advance

My code:

Imports...

conn...
cur...



sql = '''
CREATE TABLE IF NOT EXISTS pokeapi (id INTEGER, body JSONB);
'''
print(sql)
cur.execute(sql)

url = 'https://pokeapi.co/api/v2/pokemon/1'

print('=== Url is', url)

response = requests.get(url)
text = response.text
print('=== Text is', text) 
text = json.loads(text)

sql = f'Insert into pokeapi (id, body) Values (1, 'text'::JSONB);'
cur.execute(sql, (text, url))

conn.commit()

cur.close()

Response:

CREATE TABLE IF NOT EXISTS pokeapi (id INTEGER, body JSONB);

=== Url is https://pokeapi.co/api/v2/pokemon/1
=== Text is {"abilities":[{"ability":{"name":"overgrow","url":"https://pokeapi.co/api/v2/ability/65/"}....

 line 43
    sql = f'Insert into pokeapi (id, body) Values (1, 'text'::JSONB);'
                                                       ^^^^
SyntaxError: invalid syntax

Answer 1

You are getting syntax error due improper string formatting, also you trying to insert JSON using variable. Here is correct code that should work:

sql = '''
CREATE TABLE IF NOT EXISTS pokeapi (id INTEGER, body JSONB);
'''
cur.execute(sql)
url = 'https://pokeapi.co/api/v2/pokemon/1'
response = requests.get(url)
text = response.text
text_json = json.loads(text)
sql = 'INSERT INTO pokeapi (id, body) VALUES (%s, %s::JSONB);'
cur.execute(sql, (1, json.dumps(text_json)))

conn.commit()

cur.close()
conn.close()

As you can see I used %s as placeholder for params, and passed it values as tuple in cur.execute()

Also I converted text_json back to json string by json.dumps()