TypeError: not all arguments converted during string formatting - psycopg2 - mogrify - python 3.9.5

Question

I want to save a list of strings to a postgresDB with psycopg2 in python 3.9.5. When I store the data as a python list and send it to the database with execute I get the following error.

TypeError: not all arguments converted during string formatting My code looks like this

import psycopg2

dbName = "testDB"

with psycopg2.connect(user="user", password="password") as conn:
    cur = conn.cursor()

    alon = (1,2,3)

    cur.execute("Insert into " + dbName + "(url,arrayInp) VALUES ('test_url',%s)",alon)

This error does not occur when I "create" the list inside the execute statement like this

import psycopg2

dbName = "testDB"

with psycopg2.connect(user="user", password="password") as conn:
    cur = conn.cursor()
    
    cur.mogrify("Insert into " + dbName + "(url,arrayInp) VALUES ('test_url','{{1},{2},{3}}')")

Whenever I try to use mogrify to send commands to the DB it works without errors, but nothing is inserted into the DB

Answer 1

You get the error because cursor.execute() expects its second argument to be a sequence of values, and the number of values should match the number of "%s" placeholders in the SQL statement. alon has three elements, but there is only one placeholder, so the exception is raised.

If you want to insert an array type, pass a list. For example:

with psycopg2.connect(dbname='test') as conn:
    cur = conn.cursor()
    alon = [1, 2, 3]
    data = ('test_url', alon)
    # Double-quote table and column names if they contain upper-case characters
    stmt = f"""INSERT INTO "{table_name}" (url, "testInp") VALUES (%s, %s)"""
    cur.execute(stmt, data)

If you want to insert only the array, wrap the list in a tuple:

cur.execute("""INSERT INTO tbl (array_column) VALUES (%s)""", ([1, 2, 3],))

cursor.mogrify builds a statement from placeholders and values, it doesn't send the statement to the database.