CODEWITHSUNDEEP
pythonsqlitesyntax
Tom Ray
Tom Ray

Reputation: 131

Sqlite syntax error even though there's no syntax error. Help?

Here's the code I use to update information in my sqlite database:

self.c.execute("UPDATE proxydata (proxy, description) VALUES ('" + proxy + "', '" + description + "') WHERE proxy='" + proxy + "'")

But I get this error:

sqlite3.OperationalError: near "(": syntax error

For the life of mine I can't find an error. Both variables upon execution are correctly formated strings.

EDIT:

This works fine:

self.c.execute("UPDATE proxydata SET description='" + description + "' WHERE proxy='" + proxy + "'")

You can close the thread.

Upvotes: 1

Views: 1490

Answers (2)

unutbu
unutbu

Reputation: 879103

Use a parametrized sql:

sql='UPDATE proxydata SET description = ? WHERE proxy = ?'
args=[decription,proxy]
self.c.execute(sql,args)

This is clearly easier, since you don't have to quote the arguments yourself, and thus less error-prone. It is also safer, since parametrizing the sql allows sqlite3 to protect against sql injection.

Note if proxy or description itself contains a single quotation mark, then it would need to be escaped. Your manual construction of the SQL statement does not properly escape that type of quotation mark. It might be the cause of the syntax error you are seeing.

Edit: As others have noted, the real source of the syntax error is simply that UPDATE ... VALUES ... WHERE is not valid (sqlite) SQL. The proper UPDATE syntax is UPDATE ... SET ... WHERE.

Upvotes: 6

Dave Rager
Dave Rager

Reputation: 8150

There's no such thing as a syntax error without a syntax error. Try this:

self.c.execute("UPDATE proxydata SET proxy='" + proxy + "', description='" + description + "' WHERE proxy='" + proxy + "'")

This syntax is described here: http://www.sqlite.org/lang_update.html

Upvotes: 3

Related Questions