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 :)

Can you try instead: cur.execute("INSERT INTO money_spent (F_name, L_Name, Expense, Category) VALUES(%s, %s, %s, %s)",(Firstname,Lastname,Amount,Cat))? — mechanical_meat
– mechanical_meat, Commented Jan 12, 2020 at 16:10
now i don't get an error but it also doesn't get into the db, it stays empty — michox2
– michox2, Commented Jan 12, 2020 at 16:18
omg i cant belive i forgot the commit i love you thanks it works now — michox2
– michox2, Commented Jan 12, 2020 at 16:21

mechanical_meat · Accepted Answer · 2020-01-12 16:21:17Z

2

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.

answered Jan 12, 2020 at 16:21

mechanical_meat

170k25 gold badges238 silver badges231 bronze badges

Sign up to request clarification or add additional context in comments.

Collectives™ on Stack Overflow

i cannot insert rows into my psycopg2 script in python

1 Answer 1

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

Comments

Your Answer

Sign up or log in

Post as a guest

Related