Unable to insert data into Postgres: psycopg2.ProgrammingError: can't adapt type 'dict'

Question

I have tried any solution I have found here so far, however not a single one solved my issues of not being able to insert data into azure Postgres using psycopg2.

This is the sample dict I have and want to insert:

response_data = {
            "product_id" : "Test0",
            "ean": product_data["ean"],
            "sku": product_data["sku"],
            "product_title": product_data["product_name"],
            "category": product_data["category"],
            "seller": product_data["seller"],
            "current_price": product_data["current_price"],
            "uvp": product_data["original_price"],
            "source" : "Google",
            "availability" : product_data["availability"],
            "discount": product_data["discount"],
            "product_details": product_data["details"],
            "description": product_data["description"],
            "avg_stars": float(product_data["avg_stars"]),
            "recommendation_rate": int(product_data["recommendation_rate"]),
            "image_url": product_data["image_url"],
            "url": 'url',
            "product_url": "product_url"
        }

Some of the values are dicts again. This is how I insert the values:

    insert_sql = """
INSERT INTO products.product_data(
    product_id, image_url, product_url, url, source,
    ean, sku, product_title,
    uvp, current_price, discount,
    seller, availability, product_details,
    description, avg_stars, recommendation_rate, reviews,
    crawling_timestamp
)
VALUES (
    %(product_id)s, %(image_url)s, %(product_url)s, %(url)s, %(source)s,
    %(ean)s, %(sku)s, %(product_title)s,
    %(uvp)s, %(current_price)s, %(discount)s,
    %(seller)s, %(availability)s, %(product_details)s,
    %(description)s, %(avg_stars)s, %(recommendation_rate)s, %(reviews)s,
    NOW()
);
    """
cursor.execute(insert_sql, response_data)

With this code I am getting the error:

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

Answer 1

I have the sample response data as shown below:

response_data = {
    "product_id": "Test1",
    "image_url": {
        "url1": "image_url1",
        "url2": "image_url2"
    },
    "product_url": {
        "url1": "product_url1",
        "url2": "product_url2"
    },
    "url": {
        "url1": "url1",
        "url2": "url2"
    },
    "source": "Google",
    "ean": {
        "ean1": "ean_value1",
        "ean2": "ean_value2"
    },
    "sku": {
        "sku1": "sku_value1",
        "sku2": "sku_value2"
    },
    "product_title": "Product Title",
    "uvp": {
        "original": 950.00,
        "discounted": 800.00
    },
    "current_price": {
        "price1": 900.00,
        "price2": 850.00
    },
    "discount": {
        "discount1": "10%",
        "discount2": "5%"
    },
    "seller": {
        "seller1": "Seller 1",
        "seller2": "Seller 2"
    },
    "availability": {
        "availability1": "In Stock",
        "availability2": "Out of Stock"
    },
    "product_details": {
        "detail1": "Product Detail 1",
        "detail2": "Product Detail 2"
    },
    "description": {
        "description1": "Product Description 1",
        "description2": "Product Description 2"
    },
    "avg_stars": 4.5,  # Numeric value
    "recommendation_rate": 90,  # Numeric value
    "reviews": {
        "review1": "Positive",
        "review2": "Negative"
    }
}

I tried to insert data into the product_data table with the columns below:

product_id TEXT PRIMARY KEY,
    image_url JSONB,
    product_url JSONB,
    url JSONB,
    source TEXT,
    ean JSONB,
    sku JSONB,
    product_title JSONB,
    uvp JSONB,
    current_price JSONB,
    discount JSONB,
    seller JSONB,
    availability JSONB,
    product_details JSONB,
    description JSONB,
    avg_stars FLOAT,
    recommendation_rate INTEGER,
    reviews JSONB,
    crawling_timestamp TIMESTAMPTZ

With the code below:

insert_sql = """
INSERT INTO product_data(
    product_id, image_url, product_url, url, source,
    ean, sku, product_title,
    uvp, current_price, discount,
    seller, availability, product_details,
    description, avg_stars, recommendation_rate, reviews,
    crawling_timestamp
)
VALUES (
    %(product_id)s, %(image_url)s, %(product_url)s, %(url)s, %(source)s,
    %(ean)s, %(sku)s, %(product_title)s,
    %(uvp)s, %(current_price)s, %(discount)s,
    %(seller)s, %(availability)s, %(product_details)s,
    %(description)s, %(avg_stars)s, %(recommendation_rate)s, %(reviews)s,
    NOW()
);

I got the same error as shown below:

I added the code below to resolve the error:

image_url = Json(response_data['image_url'])
product_url = Json(response_data['product_url'])
url = Json(response_data['url'])
ean = Json(response_data['ean'])
sku = Json(response_data['sku'])
product_title = Json(response_data['product_title'])
uvp = Json(response_data['uvp'])
current_price = Json(response_data['current_price'])
discount = Json(response_data['discount'])
seller = Json(response_data['seller'])
availability = Json(response_data['availability'])
product_details = Json(response_data['product_details'])
description = Json(response_data['description'])
reviews = Json(response_data['reviews'])

When I ran the code, it executed successfully without any error, as shown below:

The rows were inserted into the product_data table successfully, as shown below:

Complete code:

import psycopg2
import json
from psycopg2.extras import Json
response_data = {

}
connection = psycopg2.connect(
    host="<serverName>.postgres.database.azure.com",
    database="<databaseNmae>",
    user="server",
    password="<password>"
)

cursor = connection.cursor()

insert_sql = """
INSERT INTO product_data (
    product_id, image_url, product_url, url, source,
    ean, sku, product_title,
    uvp, current_price, discount,
    seller, availability, product_details,
    description, avg_stars, recommendation_rate, reviews,
    crawling_timestamp
)
VALUES (
    %(product_id)s, %(image_url)s, %(product_url)s, %(url)s, %(source)s,
    %(ean)s, %(sku)s, %(product_title)s,
    %(uvp)s, %(current_price)s, %(discount)s,
    %(seller)s, %(availability)s, %(product_details)s,
    %(description)s, %(avg_stars)s, %(recommendation_rate)s, %(reviews)s,
    NOW()
);
"""
image_url = Json(response_data['image_url'])
product_url = Json(response_data['product_url'])
url = Json(response_data['url'])
ean = Json(response_data['ean'])
sku = Json(response_data['sku'])
product_title = Json(response_data['product_title'])
uvp = Json(response_data['uvp'])
current_price = Json(response_data['current_price'])
discount = Json(response_data['discount'])
seller = Json(response_data['seller'])
availability = Json(response_data['availability'])
product_details = Json(response_data['product_details'])
description = Json(response_data['description'])
reviews = Json(response_data['reviews'])
cursor.execute(insert_sql, {
    "product_id": response_data['product_id'],
    "image_url": image_url,
    "product_url": product_url,
    "url": url,
    "source": response_data['source'],
    "ean": ean,
    "sku": sku,
    "product_title": product_title,
    "uvp": uvp,
    "current_price": current_price,
    "discount": discount,
    "seller": seller,
    "availability": availability,
    "product_details": product_details,
    "description": description,
    "avg_stars": response_data['avg_stars'],
    "recommendation_rate": response_data['recommendation_rate'],
    "reviews": reviews
})
connection.commit()
cursor.close()
connection.close()