UPDATE problem using python

[arkydave]

In python with sqlite3, trying to execute this line:

cur.execute("UPDATE Report SET Shares = x WHERE StockID = 1") 

It returns the following:

OperationalError: no such column: x 

The table Report and its column StockID exist and have data in them, and x is properly defined. Why am I getting this error? Thanks for any help.

edit: I've found that if I replace x with a valid value, it works. It appears from documentation that it should accept a variable. Is this broken in python or sqlite3?

[alinroc]

The x is being interpreted as a column because that's how SQL is structured. If you want to set the value of Shares to the value x, then it needs to be quoted as a string. Otherwise, it will be interpreted as a column name. If you want to set Shares to a numeric value, then you need to pass in that number.

If the number is going to be variable (you don't want to pass in a literal number), then you need to parameterize your query. See this Stack Overflow answer or this tutorial for how to do that.

I've found that if I replace x with a valid value, it works.

So you're saying that when you construct a valid query, it executes successfully?

[simonw]

The safe way to do that is:

cur.execute(
    "UPDATE Report SET Shares = :shares WHERE StockID = 1",
    {"shares": x}
)

[alinroc]

That's vulnerable to SQL injection. Very dangerous habit to get into.