CODEWITHSUNDEEP
pythonjsonpostgresqlpsycopg2
Davide Dunne Sánchez
Davide Dunne Sánchez

Reputation: 13

Error psycopg2.ProgrammingError: can't adapt type 'dict' from PokeJSON

Can't insert into SQL table the first 100 Pokemon from PokeApi

import psycopg2
import json
import myutils
import requests
conn=psycopg2.connect(                                                                                                                          
    host='host',database='database',
    user='user',password='password',
    connect_timeout=3)
cur=conn.cursor()
sql='DROP TABLE IF EXISTS pokeapi;'
cur.execute(sql)
conn.commit()
print(sql)
sql = 'CREATE TABLE IF NOT EXISTS pokeapi (id SERIAL,body JSONB);'
cur.execute(sql)
conn.commit()
print(sql)   
for i in range(1,101):
    url='https://pokeapi.co/api/v2/pokemon/%s' % (str(i))                                                                                       
    response = requests.get(url)
    text = response.text
    js = json.loads(text)
    stuff = js.get('forms', None)
    sql='INSERT INTO pokeapi VALUES (%s)'
    cur.execute(sql,(stuff,))
    conn.commit()
    print(sql)

This is the error I'm getting

psycopg2.ProgrammingError: can't adapt type 'dict'

Notice the JSON structure

"species":{"name":"bulbasaur","url":"https://pokeapi.co/api/v2/pokemon-species/1/"}

Upvotes: 0

Views: 5445

Answers (1)

Oluwafemi Sule
Oluwafemi Sule

Reputation: 38982

You must adapt the python dict to Json when passing it as a query parameter.

from psycopg2.extras import Json

#...
cur.execute(sql,(Json(stuff),))

Upvotes: 1

Related Questions