[lnkForumImage]
TotalShareware - Download Free Software
Confronta i prezzi di migliaia di prodotti.
Asp Forum
 Home | Login | Register | Search 


 

Forums >

comp.lang.python

parametrizing a sqlite query

Sebastian Bassi

2/24/2010 5:07:00 PM

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 "%".
3 Answers

Jon Clements

2/24/2010 5:21:00 PM

0

On Feb 24, 5:07 pm, Sebastian Bassi <sba...@clubdelarazon.org> wrote:
> 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

2/24/2010 5:29:00 PM

0

On Feb 24, 5:21 pm, Jon Clements <jon...@googlemail.com> wrote:
> On Feb 24, 5:07 pm, Sebastian Bassi <sba...@clubdelarazon.org> wrote:
>
> > 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.

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

Cheers,

Jon.

Diez B. Roggisch

2/24/2010 6:13:00 PM

0

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