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.
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’sexecute()method. (Other database modules may use a different placeholder, such as%sor:1.)