Searching values in SQLite, with apsw, on python

Question

Why this code returns the row 'p',8,9 when i asked it to returns only the rows where the first element is > then 3 ? The first element of this row is 'p'. I noticed that this metod work fine with int, but if the element compared is a str, this occours. Why ? And how to fix it (How can i get only the rows where some element is > than 3, for exemple) ? I'm more interested to know why this happens.

Code :

import apsw


connection=apsw.Connection("database01")
cursor=connection.cursor()
cursor.execute("create table foo(a,b,c)")
cursor.execute("insert into foo values(1,2,3);insert into foo values(4,5,6);insert into foo values(7,8,9);insert into foo values('p',8,9)")


for x,y,z in cursor.execute("select a,b,c from foo"):
    print (cursor.getdescription())  # shows column names and declared types
    print (x,y,z)


def rowtrace(*results):
    """Called with each row of results before they are handed off.  You can return None to
    cause the row to be skipped or a different set of values to return"""
    print ("Row:",results)
    return results

cursor.setrowtrace(rowtrace)
for row in cursor.execute("select a,b from foo where a>3"):
     pass

Output :

(('a', None), ('b', None), ('c', None))
1 2 3
(('a', None), ('b', None), ('c', None))
4 5 6
(('a', None), ('b', None), ('c', None))
7 8 9
(('a', None), ('b', None), ('c', None))
p 8 9
Row: (<apsw.Cursor object at 0x7fab057f92b0>, (4, 5))
Row: (<apsw.Cursor object at 0x7fab057f92b0>, (7, 8))
Row: (<apsw.Cursor object at 0x7fab057f92b0>, ('p', 8))

Source : http://www.cesarkallas.net/arquivos/apostilas/python/exemplos/APSW%20-%20Another%20Python%20SQLite%20Wrapper.htm

Answer 1

The "why" can be found in the sqlite3 doc on Comparison Expressions. It matches the Comparison Example for column a.

-- Because column "a" has text affinity, numeric values on the
-- right-hand side of the comparisons are converted to text before
-- the comparison occurs.
SELECT a < 40, a < 60, a < 600 FROM t1;
0|1|1

On option would be to CAST a to an integer before comparison, ie WHERE cast(a as int) > 3. It's only an option, because it would not be a perfect solution, depending on the use case. Another option would be to limit a at the high end eg WHERE a between 3 and 99999999; again not a perfect solution.