psycopg2 not all arguments converted during string formatting
<p>I am trying to use psycopg2 to insert a row into a table from a python list, but having trouble with the string formatting.</p>

<p>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.</p>

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

<pre><code>INSERT INTO ssurgo.distmd VALUES ('5', '2015-01-01', 'Successful', 4891);
</code></pre>

<p>However, if I do:</p>

<pre><code>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))
</code></pre>

<p>I get:</p>

<pre><code>TypeError: not all arguments converted during string formatting
</code></pre>

<p>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.</p>

<p>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.</p>

<p>Does anyone know why the psycopg2 string formatting is giving an error?</p>

<p>You can keep your original code but pass a tuple in instead of a list:</p>

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

<p>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 <code>values</code> syntax: a record</p>

python postgresql python-2.7 psycopg2