i cannot insert rows into my psycopg2 script in python

Question

I am trying to build a money spending monitor and am starting with db. i have a script for building the table:

def create_table(name):
    conn = db.connect(dbname='Money_Tracker' ,user="",password="",host="",port="")
    cur = conn.cursor()
    cur.execute(f"CREATE TABLE IF NOT EXISTS {name}(ID SERIAL PRIMARY KEY ,F_Name VARCHAR, L_Name VARCHAR, Expense INT, Category VARCHAR  )")
    conn.commit()
    conn.close()

and created a table

create_table("money_spent")

secondly i built a function in order to insert data into the table:

def add_money_spent(Firstname, Lastname, Amount, Cat):
    conn = db.connect(dbname='Money_Tracker' ,user="",password="",host="",port="")
    cur = conn.cursor()
    cur.execute("INSERT INTO money_spent VALUES(%s, %s, %s, %s)",(Firstname,Lastname,Amount,Cat))
    conn.close()

and am tring to use it like this:

add_money_spent("Michael","Ben-Haym",15,"Cofee")

when i run the code i get the error sycopg2.errors.InvalidTextRepresentation: invalid input syntax for type integer: "Cofee" LINE 1: ... INTO money_spent VALUES(15, 'Michael', 'Ben-Haym', 'Cofee')
it seems the code thinks cofee should be an integer even though i specified in the table that categories should be varchar
can someone please explain to me how i can fix this?
thanks :)

Answer 1

It appears that the column order is random. So you must specify it:

cur.execute("""INSERT INTO money_spent (F_name, L_Name, Expense, Category) 
               VALUES(%s, %s, %s, %s);""",(Firstname,Lastname,Amount,Cat))

Then please make sure to commit your changes with:

conn.commit()

Before you close the connection.