pysqlite user types in select statement

Question

Using pysqlite how can a user-defined-type be used as a value in a comparison, e. g: “... WHERE columnName > userType”?

For example, I've defined a bool type with the requisite registration, converter, etc. Pysqlite/Sqlite responds as expected for INSERT and SELECT operations (bool 'True' stored as an integer 1 and returned as True).

But it fails when the bool is used in either “SELECT * from tasks WHERE display = True” or “... WHERE display = 'True.' “ In the first case Sqlite reports an error that there is not a column named True. And in the second case no records are returned. The select works if a 1 is used in place of True. I seem to have the same problem when using pysqlite's own date and timestamp adaptors.

I can work around this behavior for this and other user-types but that's not as fun. I'd like to know if using a user-defined type in a query is or is not possible so that I don't keep banging my head on this particular wall.

Thank you.

Answer 1

You probably have to cast it to the correct type. Try "SELECT * FROM tasks WHERE (display = CAST ('True' AS bool))".

Answer 2

Use the correct way of passing variables to queries: Don't build the query, use question marks and pass the parameters as a tuple to execute().

myvar = True
cur.execute('SELECT * FROM tasks WHERE display = ?', (myvar,))

That way the sqlite driver will use the value directly. No escapeing, quoting, conversion is needed anymore.

Use this technique for every parameter, even string, integer, etc. That way you avoid SQL injection automatically.