I am trying to use psycopg2 to insert a row into a table from a python list, but having trouble with the string formatting.

The table has 4 columns of types (1043-varchar, 1114-timestamp, 1043-varchar, 23-int4). I have also made attempts with 1082-date instead of timestamp, and 21-int2 instead of int4.

The following statement works fine in pgAdmin or through a psycopg2 cursor execution without string formatting:

INSERT INTO ssurgo.distmd VALUES ('5', '2015-01-01', 'Successful', 4891);

However, if I do:

sql_text = "INSERT INTO ssurgo.distmd VALUES %s ;"
data  = ['5', '2015-01-01', 'Successful', 4891]
data[1] = date.today() # ensure psycopg2 recognizes a date using datetime
print(curs.mogrify(sql_text, data))

I get:

TypeError: not all arguments converted during string formatting

I get the same error if I keep the date as a '2015-01-01' string instead of a datetime.date object, and if I use curs.execute(sql_text, data) rather than mogrify.

I am passing a list, so I don't think this is related to the more common single tuple error I found in other questions that is necessary to create a non-string sequence, and explicitly converting to a tuple did not fix the error.

Does anyone know why the psycopg2 string formatting is giving an error?

You can keep your original code but pass a tuple in instead of a list:

sql_text = "INSERT INTO ssurgo.distmd VALUES %s ;"
data  = ('5', date.today(), 'Successful', 4891)
print(curs.mogrify(sql_text, [data]))

Notice that you are passing a single value so it is necessary to wrap it in an iterable which is what Psycopg expects. The tuple will be adapted by Psycopg to the correct values syntax: a record