Error in Sqlite while Updating the table using Python

Question

I am using a python to update entries in Sqlite table. The command I am using is:

handle.execute("UPDATE RECORD set NAME=%s DEVICE=%s PROJECT=%s IP=%s COMMENT=%s where ID = %s"%(arg[2],arg[3],arg[4],arg[5],arg[6],arg[1]))

To this I get am getting an error as:

sqlite3.OperationalError: near "DEVICE": syntax error

I cannot understand what is specifically wrong with Device. Also I have checked the variables are as expected. The data base has a column named device and the database can be opened / accessed and edited using this python file.

falsetru · Accepted Answer · 2017-07-02 02:54:28Z

1

There are commas missing between set items.

In addition to that, instead of string formatting, pass parameters to prevent SQL injection:

handle.execute(
    """UPDATE RECORD
    SET NAME=%s, DEVICE=%s, PROJECT=%s, IP=%s, COMMENT=%s
    WHERE ID = %s""",
    (arg[2], arg[3], arg[4], arg[5], arg[6], arg[1]))

UPDATE If you insist to use string formatting, you should quote %s: '%s'

edited Jul 2, 2017 at 2:54

answered Jul 2, 2017 at 2:37

falsetru

371k69 gold badges769 silver badges659 bronze badges

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

3 Comments

john Over a year ago

Adding commas between columns worked. Now it is giving me an error as "sqlite3.OperationalError: no such column: john". Here John is the variable I am trying to update in column:- 'name'. Instead why is it searching for a column named john ?

falsetru Over a year ago

@john, You need to quote values %s -> '%s'. BTW , as I mentioned in the answer, you'd better to use parameter passing instead of string formatting yourself.

falsetru Over a year ago

SET NAME='%s', DEVICE='%s', PROJECT='%s', IP='%s', COMMENT='%s' WHERE ID = '%s'

Collectives™ on Stack Overflow

Error in Sqlite while Updating the table using Python

1 Answer 1

3 Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

3 Comments

Your Answer

Sign up or log in

Post as a guest