Cannot remove SQLite row using 'where' and variable

Question

Got a problem, would appreciate any help on it.

I want to remove a row, which was transferred to my func from a variable. That's the code:

con = sql.connect('db.sqlite')
cur = con.cursor()
query = 'delete from media_tmp where media_id="%s"' % media_id.strip()
print(query)
cur.execute(query)
if con:
    con.close()

print(query) gives me the following:

delete from media_tmp where media_id="737589711821419460_184456611"

and when I execute that directly in sqlite, it works perfectly and removes the deserved row. but executed by cur.execute(query) doesn't work at all.

The func finishes good, I got no errors.

sorry, didn't mention that. No errors, finishes perfect.

oleskii
– oleskii

2014-06-07 17:45:46 +00:00
Commented Jun 7, 2014 at 17:45 — oleskii
– oleskii, Commented Jun 7, 2014 at 17:45

Martijn Pieters · Accepted Answer · 2014-06-07 17:46:18Z

You are probably forgetting to commit the change:

con.commit()

The sqlite3 command line tool auto-commits changes, the sqlite3 library does not.

You should really use SQL parameters instead of string interpolation:

query = 'delete from media_tmp where media_id=?'
cur.execute(query, (media_id.strip(),))

Quoting the sqlite3 documentation:

Usually your SQL operations will need to use values from Python variables. You shouldn’t assemble your query using Python’s string operations because doing so is insecure; it makes your program vulnerable to an SQL injection attack (see http://xkcd.com/327/ for humorous example of what can go wrong).

Instead, use the DB-API’s parameter substitution. Put ? as a placeholder wherever you want to use a value, and then provide a tuple of values as the second argument to the cursor’s execute() method. (Other database modules may use a different placeholder, such as %s or :1.)

Collectives™ on Stack Overflow

Cannot remove SQLite row using 'where' and variable

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