pysqlite 用户在 select 语句中键入
使用 pysqlite 如何将用户定义类型用作比较中的值,例如。 g: “... WHERE 列名 > 用户类型”?
例如,我定义了一个带有必需的注册、转换器等的 bool 类型。Pysqlite/Sqlite 按预期响应 INSERT 和 SELECT 操作(bool 'True' 存储为整数 1 并返回为 True)。
但当 bool 用于“SELECT * fromtask WHERE display = True”或“... WHERE display = 'True'”时,它会失败。 “ 在第一种情况下,Sqlite 报告错误,指出不存在名为 True 的列。 在第二种情况下,不会返回任何记录。 如果使用 1 代替 True,则该选择有效。 使用 pysqlite 自己的日期和时间戳适配器时,我似乎遇到了同样的问题。
我可以解决此用户类型和其他用户类型的这种行为,但这并不那么有趣。 我想知道在查询中是否可以使用用户定义的类型,这样我就不会一直把头撞在这堵特定的墙上。
谢谢。
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.
如果你对这篇内容有疑问,欢迎到本站社区发帖提问 参与讨论,获取更多帮助,或者扫码二维码加入 Web 技术交流群。
绑定邮箱获取回复消息
由于您还没有绑定你的真实邮箱,如果其他用户或者作者回复了您的评论,将不能在第一时间通知您!
发布评论
评论(2)
使用将变量传递给查询的正确方法:不要构建查询,使用问号并将参数作为元组传递给
execute()
。这样sqlite驱动程序将直接使用该值。 不再需要转义、引用、转换。
对每个参数(甚至字符串、整数等)都使用此技术。这样您就可以自动避免 SQL 注入。
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()
.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.
您可能必须将其转换为正确的类型。 尝试“SELECT * FROM jobs WHERE (display = CAST ('True' AS bool))”。
You probably have to cast it to the correct type. Try "SELECT * FROM tasks WHERE (display = CAST ('True' AS bool))".