SQLite Python AND 语句不起作用
我正在尝试从 SQLite 数据库中提取一周中给定日期和当天特定时间的行列表。 当我打开我的 sqlite 浏览器并输入 SQL 时：
SELECT * FROM points
WHERE strftime('%H', checkintime) == '12'
AND strftime('%w', checkintime) == '1'
它起作用了，我收到了我应该收到的所有行。 然而，当我尝试在 python 中执行此操作时：
_points.execute("""SELECT * FROM points WHERE strftime('%H', checkintime) == ? AND strftime('%w', checkintime) == ?""", (time, day))
其中时间为 12，日期为 1，我返回的结果为零。 更奇怪的是，当我将 execute 语句更改为使用硬编码值时，它起作用了。
我尝试将我的变量类型转换为字符串和整数，但仍然不行。 :(
有什么建议吗？
I'm attempting to pull a list of rows from an SQLite database that are on a given day of the week and a certain hour of that day.
When I open my sqlite browser and type the SQL:
SELECT * FROM points
WHERE strftime('%H', checkintime) == '12'
AND strftime('%w', checkintime) == '1'
It works and I receive all of the rows I should be receiving.
However when I attempt to do this in python:
_points.execute("""SELECT * FROM points WHERE strftime('%H', checkintime) == ? AND strftime('%w', checkintime) == ?""", (time, day))
Where time is 12 and day is 1 I get zero results returned.
What's even more strange is when I change the execute statement to use hardcoded values, it works.
I've attempted to typecast my vars to both strings and ints but its still a no go. :(
Any suggestions?
如果你对这篇内容有疑问，欢迎到本站社区发帖提问 参与讨论，获取更多帮助，或者扫码二维码加入 Web 技术交流群。
绑定邮箱获取回复消息
由于您还没有绑定你的真实邮箱，如果其他用户或者作者回复了您的评论，将不能在第一时间通知您！
发布评论
评论（4）
我认为你必须将数字参数转换为字符串，否则它们不会像你在 sqlite 语句中那样被引用。变量替换为 ?不引用数字。
您实际上是在比较字符串，因此必须将参数转换为字符串。
I reckon you have to convert your number parameters to strings, otherwise they won't get quoted as you do in your statement in sqlite. The variable substitution with ? does not quote numbers.
You are actually comparing strings, so you must convert the parameters to strings.
时间和日期值确实需要是字符串。另外，我想知道你的 '%' 是否需要转义（用 %% ）——有可能在 pysqlite 内部
?占位符被转换为 % 样式的占位符，然后你的 %H 和 %w 被误解。下面的方法有效吗？The time and day values do need to be strings. Also, I wonder if your '%' needs escaping (with %%) -- It's possible that inside pysqlite the
?placeholders get converted to %-style ones and then your %H and %w get misinterpreted. Does the following work?在执行调用之外处理字符串格式怎么样？例如，
这应该避免执行语句无法完成的字符串映射。
祝你好运！
How about you handle the string formatting outside of the execute call? For example,
This should avoid the string mapping that the execute statement is unable to accomplish.
Best of luck!
尝试这样：
_points.execute("""SELECT * FROM 点 WHERE strftime('%H', checkintime) == ? AND strftime('%w', checkintime) == ?"""%(time , day))
注意 %(time, day)
我不确定 %H 和 %w ，不知道时间和日期格式是什么
try like this:
_points.execute("""SELECT * FROM points WHERE strftime('%H', checkintime) == ? AND strftime('%w', checkintime) == ?"""%(time, day))
pay attention to %(time, day)
I'm not sure about the %H and %w , don't know what time and day format is