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

This question is similar to: Insert JSON data from REST API into PostgreSQL table using Python. If you believe it’s different, please edit the question, make it clear how it’s different and/or how the answers on that question are not helpful for your problem. — Adrian Klaver
– Adrian Klaver, Commented Sep 17, 2024 at 14:45
1) DO NOT use f strings, use Parameter Passing. 2) Use the built in JSON adaptation that comes with psycopg2. 3) Read my answer to this question Insert JSON data from REST API into PostgreSQL table using Python — Adrian Klaver
– Adrian Klaver, Commented Sep 17, 2024 at 14:49
Thank you for your suggestions Adrian Klaver. I have tried your sollutions but I could't make it work. The sollution from Kirill Ilichev worked for me. There is the possibility that a more experienced programmer than me would find your solutions better. — user27341959
– user27341959, Commented Sep 21, 2024 at 10:27
All you need to do is: from psycopg2.extras import Json and then: cur.execute(sql, (1, Json(text_json))). — Adrian Klaver
– Adrian Klaver, Commented Sep 21, 2024 at 16:30
Thanks Adrian Klaver for your engagement. I wasn't able to pull that out of your link. I think I will stay with the solution which was more self-explanatory. — user27341959
– user27341959, Commented Sep 23, 2024 at 11:44

Kirill Ilichev · Accepted Answer · 2024-09-17 10:36:19Z

2

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

answered Sep 17, 2024 at 10:36

Kirill Ilichev

1,3381 gold badge7 silver badges20 bronze badges

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

5 Comments

user27341959 Over a year ago

Thank you very much. I am not sure if it worked. There ist no Error anymore but if I look at my body column, it seems empty?

Kirill Ilichev Over a year ago

Could you please provide output of this code: cur.execute('SELECT * FROM pokeapi;'); rows = cur.fetchall(); for row in rows: print(row)

user27341959 Over a year ago

Thank you very much! Here is the output: (1, {'id': 1, 'name': 'bulbasaur', 'cries': {'latest': 'raw.githubusercontent.com/PokeAPI/cries/main/cries/pokemon/…). Everything works.

Adrian Klaver Over a year ago

1) Why not use the JSON adapter that comes with psycopg2? 2) Why not use json_resp = response.json()?

user27341959 Over a year ago

I don't know unfortunately. I think I am not experienced enough to answer that. Why is it bad to stay with the solution from Ilichev?

Collectives™ on Stack Overflow

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

1 Answer 1

5 Comments

Your Answer

Linked

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

5 Comments

Your Answer

Sign up or log in

Post as a guest

Linked

Related