1

I need to rewrite the php script that handles a POST request and queries a SQLite3 database into python. The Query now looks like this

cursor.execute("SELECT * from "+requestedProgram+" WHERE start LIKE ? ORDER BY start", (requestedDate,))

the first part until the WHERE condition works (it is not elegant but unfortunately it seems that table names cannot be parametrized).

However, I need to put quotes around the requestedDate string in order to make LIKE work. How do I get quotes around the parameter/variable?

On another note: does the ORDER BY statement even do anything considering the nature of the cursor?

g.rand
  • 25
  • 1
  • 6

1 Answers1

0

Use single quotes, don't use the trailing comma behind requestedDate (it tuple-izes it), and add a trailing wildcard to the string.

cursor.execute("SELECT * from "+requestedProgram+" WHERE start LIKE '?' ORDER BY start", requestedDate + '%')

Edit: Updated per conversation thread with OP below. Details added for those who stumble across this.

Phil Walton
  • 963
  • 6
  • 11
  • I tried that, however I get the error `sqlite3.ProgrammingError: Incorrect number of bindings supplied. The current statement uses 0, and there are 1 supplied.` when doing that, the same as when using regular quotes implying the _?_ isn't recognized as a parameter. – g.rand Apr 24 '15 at 04:44
  • Use `%s` instead of `?`. – Phil Walton Apr 24 '15 at 04:48
  • `sqlite3.OperationalError: near "%": syntax error` Is the new error I get. – g.rand Apr 24 '15 at 04:53
  • My bad. I was looking at MySQLCursor. Here's the documentation on sqlite cursor: https://docs.python.org/2/library/sqlite3.html – Phil Walton Apr 24 '15 at 04:55
  • Yeah I had found that doc. I was using the python3 version https://docs.python.org/3.5/library/sqlite3.html using _?_ since that is what I originally used. I tried running it with %s and python2 yielding the above syntax error – g.rand Apr 24 '15 at 04:57
  • According to their example `c.execute('SELECT * FROM stocks WHERE symbol=?', t)` the above should work. You just need to remove the parens and comma from around your variable. – Phil Walton Apr 24 '15 at 05:00
  • The example apparently is a single element. If I omit the parens and comma, it interprets every character of the string as an individual element of the parameter tuple and throws this error `sqlite3.ProgrammingError: Incorrect number of bindings supplied. The current statement uses 1, and there are 7 supplied. ` – g.rand Apr 24 '15 at 05:04
  • Ah, try using `str(requestedDate)` – Phil Walton Apr 24 '15 at 05:10
  • I am not familiar with str(), when I use the function either in the tuple or when setting the variable I get `TypeError: 'str' object is not callable` / EDIT: Now I am thoroughly confused. I still get this error even after taking out str() – g.rand Apr 24 '15 at 05:20
  • Have you declared a variable called `str`? http://stackoverflow.com/questions/6039605/typeerror-str-object-is-not-callable-python – Phil Walton Apr 24 '15 at 05:25
  • No, I hadn't before and now "str" is completely gone from my code, still getting this error https://i.imgur.com/VP7QBP9.png Pretty fascinating ;) Is something about my setting variables wrong? I am a python newbie so I don't know if I need a dict or something there. – g.rand Apr 24 '15 at 05:29
  • :O type `vi programlookup.py` and see if the contents match what's in your editor. – Phil Walton Apr 24 '15 at 05:33
  • They do. I am going to restart this machine real quick, that should get rid of it. EDIT: It didn't, still getting the same error – g.rand Apr 24 '15 at 05:35
  • 1
    It's cool - I think I figured it out! When I used `cursor.execute("SELECT ... " (requestedDate,))` I got the same error, then I changed it to `cursor.execute("SELECT ...", str(requestedDate))` and it worked. – Phil Walton Apr 24 '15 at 05:42
  • the error as you have just shown me was that I had forgotten the comma between query and tuple :D I am a bit stupid. now both `(requestedDate,)` and `(str(requestedDate),)` "work" in that they at least return an empty query result. Something about the like condition is still a problem but that's another issue. Thanks so far! EDIT: wait, no, that's pretty much what I had in the beginning... :D – g.rand Apr 24 '15 at 05:51
  • Nice! Try `LIKE '\%?\%'` for your where clause. Also, what type and format is the `start` field? – Phil Walton Apr 24 '15 at 05:55
  • Hm, that causes it to not recognize the _?_ binding at all anymore. Start is a string in the form of "20140706071500 +0200" for example and with `LIKE '20140701'` I want to check for entries from july 1st 2014. This works in the PHP solution `SELECT * from $Program WHERE start LIKE '$Date' ORDER BY start; ` – g.rand Apr 24 '15 at 05:58
  • Oh God nevermind I just forgot to get a % behind the string used in like which was my only mistake from the beginning -.- – g.rand Apr 24 '15 at 06:02
  • 1
    Gotcha... try `"...LIKE '?' ORDER BY start", str(requestedDate) + '%')` per http://stackoverflow.com/questions/3105249/python-sqlite-parameter-substitution-with-wildcards-in-like If the value of `start` will always begin with the datestamp, you should use something that results in `LIKE '20140701%'`. – Phil Walton Apr 24 '15 at 06:03
  • 1
    Yeah I did something like that. I'll leave the str() away though since it is a proper string and it works thanks through the tupelization by the comma. Thanks again ;) – g.rand Apr 24 '15 at 06:05