0

I have a similar question than the one that has been answered in Python pysqlite not accepting my qmark parameterization

My problem is the following: I want a parameterized search for a string that is like something, not for the string itself.

This is my statement:

command = "select id, l from testDT where l like '%, ?]'"
cur.command(command, (123,))

pysqlite returns the following error:

pysqlite2.dbapi2.ProgrammingError: Incorrect number of bindings supplied. The current     statement uses 0, and there are 1 supplied.

I do understand that this is due to the qmark being interpreted as a literal. However, I do not know how to specify such a "like" search wit qmarks without the qmarks being interpreted as literals.

The following search succeeds:

command = "select id, l from testDT where l like '%, {x}]' "
command = command.format(x=123)
cur.execute(command)

But, as far as I understand it, that is exactly the way one should not use the format() function.

Community
  • 1
  • 1
user142295
  • 83
  • 4

1 Answers1

1

You use the whole lot as the parameter, eg:

command = "select id, l from testDT where l like ? "
cur.command(command, ('%, 123]',))
Jon Clements
  • 138,671
  • 33
  • 247
  • 280
  • Maybe one more question: Is it safe to use the method this way? x = 123 command = "select id, l from testDT where l like ? " cur.command(command, ('%, {}]'.format(x),)) – user142295 Mar 07 '14 at 16:40
  • @user142295 yes... as long as you're not applying formatting to the SQL query itself, you can apply formatting to build the parameter, and that parameter will be properly escaped for the actual query. – Jon Clements Mar 07 '14 at 16:41