Insert a single variable into a table in PostgreSQL using python

Question

I am trying to insert a single value into a Postgres table with two columns. The first column is an auto-increasing primary key while the second column should be a variable. I have done some research and found the following resources TypeError: not all arguments converted during string formatting python, How to set auto increment primary key in PostgreSQL? this Psycopg2 string formatting with variable names for type creation.

import psycopg2

try:
    conn = psycopg2.connect("dbname=postgres user=postgres password=actuarial")
    print("database created successfully.")
except psycopg2.OperationalError as ex:
    print("Connection failed: {0}".format(ex))
cur = conn.cursor()

def create_client_table():
    cur.execute("CREATE TABLE ClientTable (ClientID SERIAL PRIMARY KEY, Name varchar);")
    print('student table created successfully')

create_client_table()

def client_add():
    name = input("Enter the names of the student:")
    cur.execute("INSERT INTO CleintTable VALUES (%s)", (name));
    cur.execute("SELECT * FROM StudentTable")
    rows = cur.fetchall()
    print(rows)
client_add()

conn.close()

Upon running the code above I get the following error. Kindly help me identify where I am going wrong.

cur.execute("INSERT INTO CleintTable VALUES (%s)", (name));

TypeError: not all arguments converted during string formatting

Answer 1

A comma is necessary after name to turn that parenthesized expression into a tuple:

cur.execute("INSERT INTO CleintTable VALUES (%s)", (name,));

Otherwise it is a string sequence over which the execute method will iterate.

Answer 2

"CleintTable" might need to be changed to ClientTable, unless you intentionally named the table this way. Just something I noticed when reading your code, apart from what you were looking for.