Groups | Search | Server Info | Keyboard shortcuts | Login | Register [http] [https] [nntp] [nntps]

Groups > comp.lang.python > #64555 > unrolled thread

problem with sqlite3: cannot use < in a SQL query with (?)

Back to article view | Back to comp.lang.python

Contents

  problem with sqlite3: cannot use < in a SQL query with (?) lgabiot <lgabiot@hotmail.com> - 2014-01-23 03:32 +0100
    Re: problem with sqlite3: cannot use < in a SQL query with (?) lgabiot <lgabiot@hotmail.com> - 2014-01-23 03:34 +0100
    Re: problem with sqlite3: cannot use < in a SQL query with (?) Chris Angelico <rosuav@gmail.com> - 2014-01-23 13:42 +1100
    Re: problem with sqlite3: cannot use < in a SQL query with (?) bob gailer <bgailer@gmail.com> - 2014-01-22 21:51 -0500
    Re: problem with sqlite3: cannot use < in a SQL query with (?) bob gailer <bgailer@gmail.com> - 2014-01-22 21:51 -0500
    Re: problem with sqlite3: cannot use < in a SQL query with (?) Tim Chase <python.list@tim.thechases.com> - 2014-01-22 21:05 -0600
      sqlite3 docbug (was problem with sqlite3) Rustom Mody <rustompmody@gmail.com> - 2014-01-22 20:33 -0800
        Re: sqlite3 docbug (was problem with sqlite3) Rustom Mody <rustompmody@gmail.com> - 2014-01-22 20:37 -0800
        Re: sqlite3 docbug (was problem with sqlite3) Chris Angelico <rosuav@gmail.com> - 2014-01-23 15:41 +1100
          Re: sqlite3 docbug (was problem with sqlite3) Rustom Mody <rustompmody@gmail.com> - 2014-01-22 21:35 -0800
            Re: sqlite3 docbug (was problem with sqlite3) Chris Angelico <rosuav@gmail.com> - 2014-01-23 16:42 +1100
              Re: sqlite3 docbug (was problem with sqlite3) lgabiot <lgabiot@hotmail.com> - 2014-01-23 08:37 +0100
                Re: sqlite3 docbug (was problem with sqlite3) Mark Lawrence <breamoreboy@yahoo.co.uk> - 2014-01-23 09:04 +0000
                  Re: sqlite3 docbug (was problem with sqlite3) lgabiot <lgabiot@hotmail.com> - 2014-01-23 10:45 +0100
                    Re: sqlite3 docbug (was problem with sqlite3) Rustom Mody <rustompmody@gmail.com> - 2014-01-23 02:23 -0800
                  Re: sqlite3 docbug (was problem with sqlite3) lgabiot <laurent.gabiot@gmail.com> - 2014-01-23 10:45 +0100
              Re: sqlite3 docbug (was problem with sqlite3) lgabiot <laurent.gabiot@gmail.com> - 2014-01-23 08:37 +0100
            Re: sqlite3 docbug (was problem with sqlite3) Terry Reedy <tjreedy@udel.edu> - 2014-01-23 05:43 -0500
              Re: sqlite3 docbug (was problem with sqlite3) Rustom Mody <rustompmody@gmail.com> - 2014-01-23 07:00 -0800
            Re: sqlite3 docbug (was problem with sqlite3) Tim Chase <python.list@tim.thechases.com> - 2014-01-23 06:36 -0600
            Re: sqlite3 docbug (was problem with sqlite3) Terry Reedy <tjreedy@udel.edu> - 2014-01-23 20:03 -0500
          Re: sqlite3 docbug (was problem with sqlite3) Asaf Las <roegltd@gmail.com> - 2014-01-22 23:18 -0800
            Re: sqlite3 docbug (was problem with sqlite3) Chris Angelico <rosuav@gmail.com> - 2014-01-23 18:47 +1100

Page 1 of 2  [1] 2  Next page →

#64555 — problem with sqlite3: cannot use < in a SQL query with (?)

Hello,

I'm building an application using a simple sqlite3 database.
At some point, I need to select rows (more precisely some fields in 
rows) that have the following property: their field max_level (an INT),
should not exceed a value stored in a variable called threshold, where 
an int is stored (value 20000).
(threshold needs to be set by the user, so I cannot hard code a value 
there).

My database already exist on my drive (and of course the sqlite3 module 
is imported)

I do the following:

 >>>conn = sqlite3.connect(mydb) # open the database
that's OK

 >>>cursor = conn.execute("SELECT filename, filepath  FROM files WHERE 
max_level<(?)", threshold)
that doesn't work (throw an exception)

if I do:
 >>>cursor = conn.execute("SELECT filename, filepath  FROM files WHERE 
max_level<20000)")
it works...

I did similar operations on UPDATE instead of SELECT, and it works there.
Maybe my mind is fried right now, but I can't figure out the solution...

best regards.

[toc] | [next] | [standalone]

#64557

> I did similar operations on UPDATE instead of SELECT, and it works there.
> Maybe my mind is fried right now, but I can't figure out the solution...

so maybe I should rename my post:
cannot use =, < with (?) in SELECT WHERE query ?

[toc] | [prev] | [next] | [standalone]

#64558

On Thu, Jan 23, 2014 at 1:32 PM, lgabiot <lgabiot@hotmail.com> wrote:
>>>>cursor = conn.execute("SELECT filename, filepath  FROM files WHERE
>>>> max_level<(?)", threshold)
> that doesn't work (throw an exception)

What exception, exactly? Was it telling you that an integer is not
iterable, perhaps? If so, check your docs for conn.execute(). If not,
can you post the exact exception, please?

ChrisA

[toc] | [prev] | [next] | [standalone]

#64559

On 1/22/2014 9:32 PM, lgabiot wrote:
> Hello,
>
> I'm building an application using a simple sqlite3 database.
> At some point, I need to select rows (more precisely some fields in 
> rows) that have the following property: their field max_level (an INT),
> should not exceed a value stored in a variable called threshold, where 
> an int is stored (value 20000).
> (threshold needs to be set by the user, so I cannot hard code a value 
> there).
>
> My database already exist on my drive (and of course the sqlite3 
> module is imported)
>
> I do the following:
>
> >>>conn = sqlite3.connect(mydb) # open the database
> that's OK
>
> >>>cursor = conn.execute("SELECT filename, filepath  FROM files WHERE 
> max_level<(?)", threshold)
> that doesn't work (throw an exception)
PLEASE POST THE TRACEBACK!

Also get rid of the() around the ?.
>
> if I do:
> >>>cursor = conn.execute("SELECT filename, filepath  FROM files WHERE 
> max_level<20000)")
> it works...
>
> I did similar operations on UPDATE instead of SELECT, and it works there.
> Maybe my mind is fried right now, but I can't figure out the solution...
>
> best regards.

[toc] | [prev] | [next] | [standalone]

#64560

On 1/22/2014 9:32 PM, lgabiot wrote:
> Hello,
>
> I'm building an application using a simple sqlite3 database.
> At some point, I need to select rows (more precisely some fields in 
> rows) that have the following property: their field max_level (an INT),
> should not exceed a value stored in a variable called threshold, where 
> an int is stored (value 20000).
> (threshold needs to be set by the user, so I cannot hard code a value 
> there).
>
> My database already exist on my drive (and of course the sqlite3 
> module is imported)
>
> I do the following:
>
> >>>conn = sqlite3.connect(mydb) # open the database
> that's OK
>
> >>>cursor = conn.execute("SELECT filename, filepath  FROM files WHERE 
> max_level<(?)", threshold)
> that doesn't work (throw an exception)
>
> if I do:
> >>>cursor = conn.execute("SELECT filename, filepath  FROM files WHERE 
> max_level<20000)")
> it works...
>
> I did similar operations on UPDATE instead of SELECT, and it works there.
SO SHOW US THE UPDATE. "Similar" does not help.
> Maybe my mind is fried right now, but I can't figure out the solution...
>
> best regards.

[toc] | [prev] | [next] | [standalone]

#64561

On 2014-01-23 03:32, lgabiot wrote:
>  >>>cursor = conn.execute("SELECT filename, filepath  FROM files
>  >>>WHERE   
> max_level<(?)", threshold)
> that doesn't work (throw an exception)

That last argument should be a tuple, so unless "threshold"
 is a tuple, you would want to make it

  sql = "SELECT ... WHERE max_level < ?"
  cursor = conn.execute(sql, (threshold,))

-tkc

[toc] | [prev] | [next] | [standalone]

#64567 — sqlite3 docbug (was problem with sqlite3)

On Thursday, January 23, 2014 8:35:58 AM UTC+5:30, Tim Chase wrote:
> On 2014-01-23 03:32, lgabiot wrote:
> >  >>>cursor = conn.execute("SELECT filename, filepath  FROM files
> >  >>>WHERE   
> > max_level<(?)", threshold)
> > that doesn't work (throw an exception)

> That last argument should be a tuple, so unless "threshold"
>  is a tuple, you would want to make it

>   sql = "SELECT ... WHERE max_level < ?"
>   cursor = conn.execute(sql, (threshold,))

Seeing this is becoming a faq I looked at the docs to see if the tuple second
argument could do with some more emphasis

I think it sure could; see
http://docs.python.org/2/library/sqlite3.html#sqlite3.Cursor

The builtin connection.execute is even less helpful

[toc] | [prev] | [next] | [standalone]

#64568 — Re: sqlite3 docbug (was problem with sqlite3)

On Thursday, January 23, 2014 10:03:43 AM UTC+5:30, Rustom Mody wrote:
 
> The builtin connection.execute is even less helpful

I meant
help(conn.execute)

[toc] | [prev] | [next] | [standalone]

#64569 — Re: sqlite3 docbug (was problem with sqlite3)

On Thu, Jan 23, 2014 at 3:33 PM, Rustom Mody <rustompmody@gmail.com> wrote:
> On Thursday, January 23, 2014 8:35:58 AM UTC+5:30, Tim Chase wrote:
>> On 2014-01-23 03:32, lgabiot wrote:
>> >  >>>cursor = conn.execute("SELECT filename, filepath  FROM files
>> >  >>>WHERE
>> > max_level<(?)", threshold)
>> > that doesn't work (throw an exception)
>
>> That last argument should be a tuple, so unless "threshold"
>>  is a tuple, you would want to make it
>
>>   sql = "SELECT ... WHERE max_level < ?"
>>   cursor = conn.execute(sql, (threshold,))
>
> Seeing this is becoming a faq I looked at the docs to see if the tuple second
> argument could do with some more emphasis
>
> I think it sure could; see
> http://docs.python.org/2/library/sqlite3.html#sqlite3.Cursor
>
> The builtin connection.execute is even less helpful

I think it's fairly clear from the example that it has to be either a
tuple or a dict. Looks fine to me. But I'm sure that, if you come up
with better wording, a tracker issue would get the attention it
deserves.

ChrisA

[toc] | [prev] | [next] | [standalone]

#64571 — Re: sqlite3 docbug (was problem with sqlite3)

On Thursday, January 23, 2014 10:11:42 AM UTC+5:30, Chris Angelico wrote:
> I think it's fairly clear from the example that it has to be either a
> tuple or a dict. Looks fine to me.

yes 'from the example' and only from there!

[toc] | [prev] | [next] | [standalone]

#64573 — Re: sqlite3 docbug (was problem with sqlite3)

On Thu, Jan 23, 2014 at 4:35 PM, Rustom Mody <rustompmody@gmail.com> wrote:
> On Thursday, January 23, 2014 10:11:42 AM UTC+5:30, Chris Angelico wrote:
>> I think it's fairly clear from the example that it has to be either a
>> tuple or a dict. Looks fine to me.
>
> yes 'from the example' and only from there!

The fact that there's only one parameter that's supposed to handle all
of that also strongly suggests that it's a tuple.

ChrisA

[toc] | [prev] | [next] | [standalone]

#64576 — Re: sqlite3 docbug (was problem with sqlite3)

Thanks to all,

that was indeed the tuple issue!
the correct code is:
 >>>cursor = conn.execute("SELECT filename, filepath  FROM files WHERE 
max_level<?", (threshold,))

as was pointed out by many.

Sorry for missing such a silly point (well, a comma in fact). I'll learn 
to read more seriously the doc, but I was really confused (I spent more 
than one hour trying so many combinations, reading the doc, books I 
have, etc... before posting, and I was stuck)

but the basis for my blindness was more a lack of grasp of the 
fundamentals: how to declare a one element tuple.
Because I tried to write (threshold) being convinced it was a tuple...

I need to remember at all times: https://wiki.python.org/moin/TupleSyntax

best regards.

[toc] | [prev] | [next] | [standalone]

#64584 — Re: sqlite3 docbug (was problem with sqlite3)

On 23/01/2014 07:37, lgabiot wrote:
> Thanks to all,
>
> that was indeed the tuple issue!
> the correct code is:
>  >>>cursor = conn.execute("SELECT filename, filepath  FROM files WHERE
> max_level<?", (threshold,))
>
> as was pointed out by many.
>
> Sorry for missing such a silly point (well, a comma in fact). I'll learn
> to read more seriously the doc, but I was really confused (I spent more
> than one hour trying so many combinations, reading the doc, books I
> have, etc... before posting, and I was stuck)
>
> but the basis for my blindness was more a lack of grasp of the
> fundamentals: how to declare a one element tuple.
> Because I tried to write (threshold) being convinced it was a tuple...
>
> I need to remember at all times: https://wiki.python.org/moin/TupleSyntax
>
> best regards.
>

No, you need to remember how to type xyz into your favourite search 
engine.  For this case xyz would be something like "python single 
element tuple".

-- 
My fellow Pythonistas, ask not what our language can do for you, ask 
what you can do for our language.

Mark Lawrence

[toc] | [prev] | [next] | [standalone]

#64588 — Re: sqlite3 docbug (was problem with sqlite3)

Le 23/01/14 10:04, Mark Lawrence a écrit :
> No, you need to remember how to type xyz into your favourite search
> engine.  For this case xyz would be something like "python single
> element tuple".
>

No big deal, but I don't think you are correct.

Problem was that for me I "knew" (it was erroneous of course) that 
(element) was a python single element tuple... so there was no need for 
me to look for something I "knew".

Once I understood that what I "knew" was wrong (that is after reading 
the answers to my first post), I did type xyz in my favourite search 
engine, which led me to the link I posted in my answer...

[toc] | [prev] | [next] | [standalone]

#64592 — Re: sqlite3 docbug (was problem with sqlite3)

On Thursday, January 23, 2014 3:15:07 PM UTC+5:30, lgabiot wrote:
> Le 23/01/14 10:04, Mark Lawrence a écrit :
> > No, you need to remember how to type xyz into your favourite search
> > engine.  For this case xyz would be something like "python single
> > element tuple".

> No big deal, but I don't think you are correct.

> Problem was that for me I "knew" (it was erroneous of course) that 
> (element) was a python single element tuple... so there was no need for 
> me to look for something I "knew".

> Once I understood that what I "knew" was wrong (that is after reading 
> the answers to my first post), I did type xyz in my favourite search 
> engine, which led me to the link I posted in my answer...

Singleton tuples are a common gotcha in python
Follows from the world-wide shortage in parenthesis

[toc] | [prev] | [next] | [standalone]

#64589 — Re: sqlite3 docbug (was problem with sqlite3)

Le 23/01/14 10:04, Mark Lawrence a écrit :
> No, you need to remember how to type xyz into your favourite search
> engine.  For this case xyz would be something like "python single
> element tuple".
>

No big deal, but I don't think you are correct.

Problem was that for me I "knew" (it was erroneous of course) that 
(element) was a python single element tuple... so there was no need for 
me to look for something I "knew".

Once I understood that what I "knew" was wrong (that is after reading 
the answers to my first post), I did type xyz in my favourite search 
engine, which led me to the link I posted in my answer...

[toc] | [prev] | [next] | [standalone]

#64577 — Re: sqlite3 docbug (was problem with sqlite3)

Thanks to all,

that was indeed the tuple issue!
the correct code is:
 >>>cursor = conn.execute("SELECT filename, filepath  FROM files WHERE 
max_level<?", (threshold,))

as was pointed out by many.

Sorry for missing such a silly point (well, a comma in fact). I'll learn 
to read more seriously the doc, but I was really confused (I spent more 
than one hour trying so many combinations, reading the doc, books I 
have, etc... before posting, and I was stuck)

but the basis for my blindness was more a lack of grasp of the 
fundamentals: how to declare a one element tuple.
Because I tried to write (threshold) being convinced it was a tuple...

I need to remember at all times: https://wiki.python.org/moin/TupleSyntax

best regards.

[toc] | [prev] | [next] | [standalone]

#64594 — Re: sqlite3 docbug (was problem with sqlite3)

On 1/23/2014 12:35 AM, Rustom Mody wrote:
> On Thursday, January 23, 2014 10:11:42 AM UTC+5:30, Chris Angelico wrote:
>> I think it's fairly clear from the example that it has to be either a
>> tuple or a dict. Looks fine to me.
>
> yes 'from the example' and only from there!

'parameters' is a single parameter, which could be called 'seq_dict'. 
Let(seq_dict) must equal the number of replacements. A dict with extra 
pairs raises.

A list instead of a tuple does work, but not an iterable, so 'sequence'.

A dict subclass works, but a UserDict is treated as a sequence.
-----------
import sqlite3

con = sqlite3.connect(":memory:")
cur = con.cursor()
cur.execute("create table people (name_last, age)")

who = "Yeltsin"
age = 72
s = (who, age)

d = {'who':who, 'age':age}

class D(dict): pass
dD = D(d)

from collections import UserDict
dU = UserDict(d)

# This is the qmark style:
cur.execute("insert into people values (?, ?)", s)

# And this is the named style:
cur.execute("select * from people where name_last=:who and age=:age", dU)

print(cur.fetchone())
--------------
 >>>
Traceback (most recent call last):
   File "C:\Programs\Python34\tem.py", line 23, in <module>
     cur.execute("select * from people where name_last=:who and 
age=:age", dU)
   File "C:\Programs\Python34\lib\collections\__init__.py", line 883, in 
__getitem__
     raise KeyError(key)
KeyError: 0

Replacing dU in the last call with s works!

http://bugs.python.org/issue20364

-- 
Terry Jan Reedy

[toc] | [prev] | [next] | [standalone]

#64614 — Re: sqlite3 docbug (was problem with sqlite3)

On Thursday, January 23, 2014 4:13:26 PM UTC+5:30, Terry Reedy wrote:
> http://bugs.python.org/issue20364

Thanks for that!

I would have preferred a slightly louder warning with respect to singleton 
tuples given that:

1. Singleton tuple syntax is not consistent with other-length tuples
2. This inconsistency is somewhat alleviated by the fact that in a classic 
format ('%') expression, the second argument can be a single element when the 
format string has only one %-spec -- so one inconsistency to correct another
3. conn.execute's parameters, apparently very analogous to the '%'
-- even more so given the standard sql-injection advisory -- is not consistent 
with 2 above

Still the suggested doc-fix is much better than the current almost
undocumented situation -- so thanks again!

[toc] | [prev] | [next] | [standalone]

#64600 — Re: sqlite3 docbug (was problem with sqlite3)

On 2014-01-23 05:43, Terry Reedy wrote:
> A list instead of a tuple does work, but not an iterable, so
> 'sequence'.

In the OP's case using sqlite drivers, this is true.  However, I
maintain some old 2.4 code that uses a correspondingly ancient version
of mx.ODBC which requires a tuple and raises an exception on any other
iterable.  So I always use a tuple out of habit, even if it would be
easier to just use some other iterable.

-tkc

[toc] | [prev] | [next] | [standalone]

Page 1 of 2  [1] 2  Next page →

Back to top | Article view | comp.lang.python

csiph-web