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

[lgabiot]

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:
that's OK
max_level<(?)", threshold)
that doesn't work (throw an exception)

if I do: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.

 

[lgabiot]

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 ?

 

[Chris Angelico]

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

 

[bob gailer]

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:

that's OK

max_level<(?)", threshold)
that doesn't work (throw an exception)

PLEASE POST THE TRACEBACK!

Also get rid of the() around the ?.

 

[bob gailer]

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:

that's OK

max_level<(?)", threshold)
that doesn't work (throw an exception)

if I do:
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.

 

[Tim Chase]

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

 

[Rustom Mody]

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

 

[Rustom Mody]

The builtin connection.execute is even less helpful

I meant
help(conn.execute)

 

[Chris Angelico]

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

 

[Rustom Mody]

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!

 

[Chris Angelico]

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

 

[Asaf Las]

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

It looks like tuple, but i could be wrong:

from python-3.3.3.tar.bz2\Python-3.3.3\Modules\_sqlite\cursor.c

PyObject* _pysqlite_query_execute(pysqlite_Cursor* self, int multiple, PyObject* args)
{
....
if (multiple) {
/* executemany() */
if (!PyArg_ParseTuple(args, "OO", &operation, &second_argument)) {
goto error;
}

/Asaf

 

[lgabiot]

Thanks to all,

that was indeed the tuple issue!
the correct code is: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.

 

[lgabiot]

Thanks to all,

that was indeed the tuple issue!
the correct code is: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.

 

[Chris Angelico]

if (!PyArg_ParseTuple(args, "OO", &operation, &second_argument)) {
goto error;
}

That part just asks for "any object" as the second argument. Also,
that part is handling executemany(). Later on, the execute() handler
looks for an optional second arg, and then looks for an iterator from
it.

But as a general rule, I'd advise reading the docs rather than the
source, unless you're trying to figure out whether some other iterable
will work. For the most part, just follow the examples and use a
tuple.

ChrisA

 

[Mark Lawrence]

Thanks to all,

that was indeed the tuple issue!
the correct code is:
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".

 

[lgabiot]

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...

 

[lgabiot]

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...

 

[Rustom Mody]

Le 23/01/14 10:04, Mark Lawrence a écrit :

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

 

[Terry Reedy]

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