TypeError: dict is not a sequence while inserting data to postgres db

Question

Need help with the error. Below is my function to insert data to postgres db

import psycopg2

def insert_records(data):

    conn = psycopg2.connect(
        database = "postgres",
        user = "some_user",
        password = "some_password",
        host = "***rds.amazonaws.com",
        port="5432"
        )
        
    conn.autocommit = True
    
    cursor = conn.cursor()
    
    column_names = ', '.join(data[0].keys()) -->output ID, FIRST_NAME, LAST_NAME
    placeholders = ', '.join(['%s' for _ in range(len(data[0]))]) --->output %s, %s, %s
    insert_query = f"INSERT INTO {table_name} ({column_names}) VALUES ({placeholders})"
    
    cursor.executemany(insert_query, data)

the data being used is below:

[{'ID':'ID1234', 'FIRST_NAME':'Thomas', 'LAST_NAME':'Edison'}]

Getting the type error

cursor.executemany(insert_query, data) "TypeError: dict is not a sequence"

Answer 1

According to the documentation the documentation data should be a tuple sequence, not a dict (Example: {name: harry}) ]. Like so:

nums = ((1,), (5,), (10,))
cur.executemany("INSERT INTO test (num) VALUES (%s)", nums)

Answer 2

There is a mismatch between your value placeholders (%s ) and your values container (a dict).

You use %s if your values are stored in a sequence like a list or, more conventionally, a tuple:

cursor.execute(
    """INSERT INTO tbl (c1, c2) VALUES (%s, %s)""",
    ('a', 1)
)

If your values are stored in a dict, the placeholder format is %(key)s:

cursor.execute(
    """INSERT INTO tbl (c1, c2) VALUES (%(k1)s, %(k2)s)""",
   ({'k1': 'a', 'k2': 1)
)

In either case, when using executemany the value containers must be stored inside a sequence.

The error message

TypeError: dict is not a sequence

is telling you that psycopg2 expects the values to be in a sequence, but they are in a dict.

---

Note: psycopg2's executemany method doesn't provide any performance improvement - for that you need to use fast execution helpers