vendredi 6 novembre 2015

sqlite query gives different result in shell compared to python

I run a query in sqlite3 shell

(sorry for the image, but I cannot copy text from the windows command shell for some reason)

enter image description here

but the same query run from inside python (on the same database) returns no rows.

def strange_test(dbf=DBF):
    con = lite.connect(dbf)
    con.row_factory = lite.Row
    with con:
        inputs_sql = "SELECT id, linefill_end FROM T1 WHERE CAST(linefill_end as integer) != linefill_end"
        #inputs_sql = "SELECT id, linefill_end FROM T1 WHERE id = 2307"



        cur = con.cursor()
        cur.execute(inputs_sql)
        row = cur.fetchone()
        if row:
            print row[0], row[1]
        else:
            print "Nothing found"

    return row    

Gives no results:

%run "C:/Users/foo/Dropbox/parity_checking.py"
Nothing found

I know from the sql shell results that record number 2307 has a non-integer value "not found". If I query that record specifically from inside Python, it also shows the value as "not found". I think this proves they're looking at the same records. So why does the query make results in the shell, but not inside python??!?!? Does somehow cast() not work properly when called from python?

def strange_test(dbf=DBF):
    con = lite.connect(dbf)
    con.row_factory = lite.Row
    with con:
        #inputs_sql = "SELECT id, linefill_end FROM T1 WHERE CAST(linefill_end as integer) != linefill_end"

        """search for a record I know has a non-integer linefill_end value"""
        inputs_sql = "SELECT id, linefill_end FROM T1 WHERE id = 2307"



        cur = con.cursor()
        cur.execute(inputs_sql)
        row = cur.fetchone()
        if row:
            print row[0], row[1]
        else:
            print "Nothing found"

    return row



%run "C:/Users/foo/Dropbox/_python/parity_checking.py"
2307 not found

Aucun commentaire:

Enregistrer un commentaire