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

Question

I looked into many stack overflow posts and most of them told that I need to use a tuple or a list when inserting into VALUES (%s). I tried both lists and tuples, but I am still getting the same error: not all arguments converted during string formatting. Here is the code of the function used to insert some data into PostgreSQL database:

sql = '''
    INSERT INTO immediate_info (
    bytes_sent,
    bytes_recv,
    packets_sent,
    packets_recv,
    errin,
    errout,
    dropin,
    dropout)
    VALUES (%s);
'''

conn = None

bytes_recv, bytes_sent, packets_sent, packets_recv, errin, errout, dropin, dropout = data

try:
    conn = psycopg2.connect('all the connect stuff')
    # create a new cursor
    cur = conn.cursor()
    # execute the INSERT statement
    cur.execute(sql, (bytes_recv, bytes_sent, packets_sent, packets_recv, errin, errout, dropin, dropout,))
    # commit the changes to the database
    conn.commit()
    # close communication with the database
    cur.close()

except (Exception, psycopg2.DatabaseError) as error:

    print(error)

finally:
    if conn is not None:
        conn.close()

As I mentioned above, I have tried using lists as well. This time I decided to first unpack the data list first, though simply going through the data list using indexes (data[0],data[1], and so on) will, in my opinion, lead to the same results.

The data contains some network info used to measure the bandwidth of my computer. All of its contents is in int format.

Also, if you noticed, the string formatting here is the old one (referring to VALUES (%s)). How can the f-formatting be used in this case? And how do I get rid of this error?

question from:https://stackoverflow.com/questions/65847289/psycopg2-typeerror-not-all-arguments-converted-during-string-formatting

Answer 1

When executing INSERT statements using cursor.execute

• the number of columns being inserted must match the number of placeholders in the VALUES clause
• the number of elements in the second argument to cursor.execute must match the number of placeholders in the VALUES clause

So

cursor.execute("""INSERT INTO foo (bar, baz, quux) VALUES (%s, %s)""", args)

is wrong because there are three columns being inserted but only two values placeholders

and

cursor.execute("""INSERT INTO foo (bar, baz, quux) VALUES (%s, %s, %s)""",
               ('a', 'b', 'c', 'd'))

is wrong because the number of values in the second argument does not match the number of placeholders in the VALUES clause.