0

First I tried:

sqlite3.cursor.execute("select * from mytable where mycol=?", (myval,))

when I execute it in the shell it works. In my class method it tells me: ProgrammingError: Incorrect number of bindings supplied. The current statement uses 0, and there are 12 supplied.

Then I tried:

sqlite3.cursor.execute("select * from mytable where mycol='%s'" % myval)

when I execute it in the shell it works. In my class method it tells me: ProgrammingError: Incorrect number of bindings supplied. The current statement uses 0, and there are 12 supplied.

So I decided to just format the string first and then pass that in. Like this:

sqlcmd = "select * from mytable where mycol='%s'" % myval

when I execute it in the shell it works. In my class method it tells me the same error: ProgrammingError: Incorrect number of bindings supplied. The current statement uses 0, and there are 12 supplied.

My method is defined like this:

def mymethod(self, myval):
    cur = self.conn.cursor()
    cur.execute( "..." ... )
    ....

What am I missing?

dviljoen
  • 1,612
  • 1
  • 16
  • 28
  • 1
    Show the exact code you use in your class. The first code snippet should work, and you should never use string formatting on SQL queries. – Colonel Thirty Two Oct 27 '15 at 14:52
  • 2
    I can't see a class method in your examples, could you give a [mcve]? Your first example is the correct way to create a query, you shouldn't interpolate values with `%`/`str.format`. – jonrsharpe Oct 27 '15 at 14:52
  • 1
    Perhaps `myval` isn't what you think it is? – Brent C Oct 27 '15 at 14:53
  • 1
    Possible duplicate of [sqlite3.ProgrammingError: Incorrect number of bindings supplied. The current statement uses 1, and there are 74 supplied](http://stackoverflow.com/questions/16856647/sqlite3-programmingerror-incorrect-number-of-bindings-supplied-the-current-sta) – Reto Aebersold Oct 27 '15 at 14:54
  • I closed and reopened the python console in pyCharm and my code magically started working. It was giving me errors with my current code in the error, but I'm guessing that was not the code it was executing. Anyone know? – dviljoen Oct 27 '15 at 14:57
  • This is not a dup of that question. If you read my code example I was quite clearly supplying the trailing comma in my parameter. – dviljoen Oct 27 '15 at 15:00
  • @ColonelThirtyTwo Why not use string formatting on SQL queries? Not disputing that advice, just looking for more clarification. – chishaku Oct 27 '15 at 15:09
  • I believe string formatting in SQL can be exploited and so is deemed unsafe. – dviljoen Oct 27 '15 at 15:12
  • 2
    @chishaku `"select * from mytable where mycol='%s'" % ("nothing' UNION ALL SELECT * FROM private_information; --")` Try to imagine what this does. – Colonel Thirty Two Oct 27 '15 at 15:47
  • @ColonelThirtyTwo Understood, thank you. – chishaku Oct 27 '15 at 16:42

1 Answers1

0

This was a "know your tools" problem. As I mentioned above in my comment, my issue was not understanding when/how pyCharm reloads modified code. I was testing my class in a python console in pyCharm. But after I changed my code it does not automatically reload the python code, but runs the cached code.

But the part that really threw me off was when it reports an error, it pulls the source code line from the current source code, not from the cached code. So my first time through I forgot the trailing command ...(myval,). When I got the error I realized my mistake and added it. I reran it and it displayed the same error, but pulled the source code in the error from my modified code. But this was not the cached code that was being executed.

I ran it on the command line and it worked (as expected). It was just not realizing that I needed to reload the python console when I made a code change.

dviljoen
  • 1,612
  • 1
  • 16
  • 28