Menu
I have a python script that reads raw movie text files into an sqlite database.
I use re.escape(title) to add escape chars into the strings to make them db safe before executing the inserts.
Why does this not work:
In [16]: c.execute("UPDATE movies SET rating = '8.7' WHERE name='\'Allo\ \'Allo\!\"\ \(1982\)'") --------------------------------------------------------------------------- OperationalError Traceback (most recent call last) /home/rajat/Dropbox/amdb/<ipython console> in <module>() OperationalError: near "Allo": syntax error Yet this works (removed \' in two places) :
In [17]: c.execute("UPDATE movies SET rating = '8.7' WHERE name='Allo\ Allo\!\"\ \(1982\)'") Out[17]: <sqlite3.Cursor object at 0x9666e90> I can't figure it out. I also can't ditch those leading quotes because they're actually part of the movie title. Thank you.
924
Double-quotes in SQLite identifiers are escaped as two double quotes. SQLite identifiers preserve case, but they are case-insensitive towards ASCII letters. It is possible to enable unicode-aware case-insensitivity.
Inserting data using pythonImport sqlite3 package. Create a connection object using the connect() method by passing the name of the database as a parameter to it. The cursor() method returns a cursor object using which you can communicate with SQLite3.
Introduction. SQLite is a self-contained, file-based SQL database. SQLite comes bundled with Python and can be used in any of your Python applications without having to install any additional software.
You're doing it wrong. Literally. You should be using parameters, like this:
c.execute("UPDATE movies SET rating = ? WHERE name = ?", (8.7, "'Allo 'Allo! (1982)")) Like that, you won't need to do any quoting at all and (if those values are coming from anyone untrusted) you'll be 100% safe (here) from SQL injection attacks too.
53
If you love us? You can donate to us via Paypal or buy me a coffee so we can maintain and grow! Thank you!
Donate Us With
