Python variables in SQL Update query

Question

I'm very new to Python and I'm facing a problem with Python-SQLite connection.

con=connection()
cur=con.cursor()
cur.execute("""UPDATE table1 SET column1=%s WHERE column2=%d""",(var1,var2))
if(cur.rowcount > 1):
    cur.commit()
else:
    return["Error"]

Where var1 stores a string, and var2 stores an integer.

The error is the following:

SQLError('SQLError: near "%": syntax error',)

I've read plenty of tutorials and threads in here about how to write my query with the variables and I'm in a dead end.

CodeLikeBeaker · Accepted Answer · 2023-05-10 14:36:11Z

6

According to the sqlite documentation you want to use ? for your variable. Example:

cur.execute("""update table1 set column1=? where column2=?""", (var1, var2))

edited May 10, 2023 at 14:36

answered May 17, 2017 at 20:28

CodeLikeBeaker

21.4k14 gold badges83 silver badges114 bronze badges

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

Comments

Herker · Accepted Answer · 2021-03-15 16:21:04Z

0

This worked for me

mycursor.execute("UPDATE table SET column WHERE name = '%s' AND age = %s" % ("Alex", 30)

When passing in a string.

Use quotes

And at the end of the execute statement, use

%

Keep in mind that if you will pass multiple variables you have to put them inside a tuple

Took me a long time just to understand that you have to have the % at the end when writing an UPDATE query

answered Mar 15, 2021 at 16:21

Herker

6029 silver badges20 bronze badges

Collectives™ on Stack Overflow

Python variables in SQL Update query

2 Answers 2

Comments

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

Comments

Comments

Your Answer

Sign up or log in

Post as a guest

Related