sqlite, python, apsw: trouble parameterizing nested select

Question

I am trying to query some data of an sqlite database with a nested select statement. The final result of the query needs to be restricted by a WHERE clause, so I put brackets around the "inner" select statement, named it s1, and put another select around it.

When using a parameter in the last WHERE in the code (last but one line in the sql) I get an empty result as if no data was in the table. Without the parameter, just providing the string '2018-12-31' instead, it works just fine and I get 4 tuples of data (my expected result). But the parameter I use in the WHERE clause (:jahresEnde) I use in other places of the select as well. It is exactly the same. What am I doing wrong?

Here comes my code:

sqlCommands = '''
SELECT Name, Einheit, Whg, von, bis FROM 
( 
SELECT B.Name, H.Einheit, H.ID as Whg, FkEinheit,
    CASE WHEN julianday(B.Einzug) < julianday(:jahresAnfang)
            THEN :jahresAnfang 
            ELSE B.Einzug 
        END von, 
    CASE WHEN julianday(B.Auszug) > julianday(:jahresEnde) 
            THEN :jahresEnde 
        WHEN B.Auszug == '' AND julianday(:jahresEnde) < julianday(DATE())
            THEN :jahresEnde 
        WHEN B.Auszug == '' AND julianday(:jahresEnde) > julianday(DATE())
            THEN DATE() 
        ELSE B.Auszug 
        END bis
FROM bewohner AS B INNER JOIN haus AS H 
    ON B.FkEinheit = H.ID 
WHERE julianday(bis) IS NULL 
              OR julianday(bis) >= julianday(:jahresAnfang)
) s1 
WHERE julianday(von) <= julianday(:jahresEnde)
ORDER BY FkEinheit DESC 
'''
args={
        ":jahresAnfang":'2018-01-01',
        ":jahresEnde":'2018-12-31'
}

cursor = db.cursor()
ok = cursor.execute(sqlCommands,args)

print (list(ok))

I also tried another sqlite module before: PyQt6.QtSql. With that module it worked just fine, but I got other trouble at another position in the code. So I wanted to switch to apsw.

Answer 1

The error is in your dictionary bindings (args variable). Leave out the colons! The colons in the SQL are for SQLite to know that it is a binding.

args={
        "jahresAnfang":'2018-01-01',
        "jahresEnde":'2018-12-31'
}

It would be far more helpful if APSW treated missing bindings as an error instead of NULL. That will be changed.

(Disclosure: I am the APSW author)