parametrizing a sqlite query

[Sebastian Bassi]

c.execute("SELECT bin FROM bins WHERE qtl LIKE '%:keys%'",{'keys':keywords})

This query returns empty. When it is executed, keywords = 'harvest'.
To check it, I do it on the command line and it works as expected:

sqlite> SELECT bin FROM bins WHERE qtl LIKE '%harvest%';
11C
11D
12F

I guess there is a problem with the "%".

 

[Jon Clements]

c.execute("SELECT bin FROM bins WHERE qtl LIKE '%:keys%'",{'keys':keywords})

This query returns empty. When it is executed, keywords = 'harvest'.
To check it, I do it on the command line and it works as expected:

sqlite> SELECT bin FROM bins WHERE qtl LIKE '%harvest%';
11C
11D
12F

I guess there is a problem with the "%".

You might want:
c.execute("SELECT bin FROM bins where qtl like $keys", {'keys':
keywords} )

Cheers,

Jon.

 

[Jon Clements]

You might want:
c.execute("SELECT bin FROM bins where qtl like $keys", {'keys':
keywords} )

Cheers,

Jon.

As soon as I posted that, the $ didn't look right; the docs use :keys
syntax.

Cheers,

Jon.

 

[Diez B. Roggisch]

Am 24.02.10 18:07, schrieb Sebastian Bassi:

c.execute("SELECT bin FROM bins WHERE qtl LIKE '%:keys%'",{'keys':keywords})

This query returns empty. When it is executed, keywords = 'harvest'.
To check it, I do it on the command line and it works as expected:

sqlite> SELECT bin FROM bins WHERE qtl LIKE '%harvest%';
11C
11D
12F

I guess there is a problem with the "%".

You aren't supposed to put ' into the query. The thing you pass needs to
be the full literal.

Use

c.execute("select ... qtl like :keys", dict(keys="%%%s%%" % keywords))


Diez