psql TypeError: not all arguments converted during string formatting

Question

I have a trouble when I try to automatically generate pid

    CREATE TABLE players(
      pID SERIAL primary key,
      pName VARCHAR(90) not null
    );

and here is my function

    def addPlayer(name):
       conn = connect()
       cur = conn.cursor()
       cur.execute("INSERT INTO players(pName) VALUES(%s)",name)
       conn.commit()

and I call the function with

    addPlayer('Vera') 

I keep getting the error that

    cur.execute("INSERT INTO players(pName) VALUES(%s)",name)
    TypeError: not all arguments converted during string formatting

I search for hours but still confused. Can anyone help me with this? Thanks a lot!

Answer 1

I think you're confusing string interpolation with query variables.

String interpolation:

name = "Ben"
print("Hello, my name is %s" % name)

names = ["Adam", "Ben", "Charlie"]
print("Hello, our names are %s, %s and %s" % names)

Query variables:

values = [name]
cur.execute("INSERT INTO players(pName) VALUES(?)", values)

So changing your comma to a % would pass the variable into the string and would work, but the other way sanitises the input, so you should use the second example as is.

Answer 2

you need to pass a tuple or list as the second parameter to execute.

When having more than one replacement in the query the tuple looks "normal" like this: (name, age).

In your case you need to add a tuple with just one part. The short but a bit unusual way to write that is to use (name,) as second parameter.

thus:

cur.execute("INSERT INTO players(pName) VALUES(%s)",(name,))

Although it's more common to use ? as the replacement char which would look like this:

cur.execute("INSERT INTO players(pName) VALUES(?)",(name,))