Psycopg2.programmingerror: can’t adapt type ‘dict’

psycopg2.ProgrammingError: can’t adapt type ‘dict’

The error “psycopg2.ProgrammingError: can’t adapt type ‘dict'” occurs when you try to insert a Python dictionary object into a PostgreSQL database using the psycopg2 library. The psycopg2 library is a popular PostgreSQL adapter for Python that provides a way to interact with the database.

The error message indicates that the dictionary object you are trying to insert is not compatible with the PostgreSQL database. PostgreSQL is a relational database system, and it expects structured data to be inserted into its tables. However, a dictionary object in Python represents an unordered collection of key-value pairs, which cannot be directly adapted into a structured format for a database table.

To resolve this error, you need to convert the dictionary object into a format that PostgreSQL can handle. There are a few approaches you can take to achieve this:

Convert the dictionary to a JSON string: PostgreSQL has built-in support for JSON data type. You can convert the dictionary object to a JSON string using the json module in Python, and then insert this string into a JSON column in the database. Here’s an example:

import json
import psycopg2

# Create a Python dictionary
data = {
    'name': 'John',
    'age': 30,
    'city': 'New York'
}

# Convert the dictionary to a JSON string
json_data = json.dumps(data)

# Insert the JSON string into the database
conn = psycopg2.connect(database="your_database", user="your_user", password="your_password", host="your_host", port="your_port")
cursor = conn.cursor()
cursor.execute("INSERT INTO your_table (json_column) VALUES (%s)", (json_data,))
conn.commit()
conn.close()

In this example, we convert the dictionary object “data” into a JSON string using the json.dumps() method. Then, we insert the JSON string into the “json_column” of the “your_table” table.

Convert the dictionary to a PostgreSQL array: If your dictionary contains a fixed set of keys and values, you can convert it to a PostgreSQL array. PostgreSQL arrays are similar to Python lists and can be directly inserted into an array column in the database. Here’s an example:

import psycopg2

# Create a Python dictionary
data = {
    'name': 'John',
    'age': 30,
    'city': 'New York'
}

# Convert the dictionary to a PostgreSQL array
array_data = list(data.values())

# Insert the array into the database
conn = psycopg2.connect(database="your_database", user="your_user", password="your_password", host="your_host", port="your_port")
cursor = conn.cursor()
cursor.execute("INSERT INTO your_table (array_column) VALUES (%s)", (array_data,))
conn.commit()
conn.close()

In this example, we create a Python dictionary “data” and convert it to a PostgreSQL array using the list() function and the values from the dictionary. Then, we insert the array into the “array_column” of the “your_table” table.

By converting the dictionary object into a JSON string or a PostgreSQL array, you can adapt it to a format compatible with PostgreSQL and avoid the “psycopg2.ProgrammingError: can’t adapt type ‘dict'” error.

Leave a comment Cancel reply