如何为时间戳（日期时间）数组指定 psycopg2 参数
我想使用 psycopg2 在 Python 中运行 PostgreSQL 查询，该查询按 timestamp without timezone 类型的列进行过滤。我有一个很长的时间戳允许值列表（而不是一个范围），并且 psycopg2 可以方便地处理数组，所以我认为这应该可行：
SELECT somestuff
FROM mytable
WHERE thetimestamp = ANY (%(times)s)
times 参数是 datetime< /代码> 对象。我还尝试过 psycopg2.Timestamp() 。它们都翻译为 WHERE thetimestamp = ANY (ARRAY['2009-07-06T00:00:00', '2009-07-07T00:00:00', ...]) 不幸的是失败并出现以下错误：
operator does not exist: timestamp without time zone = text
LINE 3: WHERE thetimestamp = ANY (ARRAY['2009-07-06T00:00:00', '2009-07-07T00:00:00', ...]
HINT: No operator matches the given name and argument type(s). You might need to add explicit type casts.
我也在 pgAdmin 中确认了这一点，所以它不仅仅是 psycopg2。似乎正在发生的事情是 Postgres 不会隐式地将字符串数组转换为时间戳数组。如果我显式地将 ::timestamp 添加到 pgAdmin 中的每个元素，它将很好地转换单个字符串，并且数组可以正常工作，但我不知道如何在 psycopg2 中做到这一点。
除了忘记 DB-API 参数并手动构建一长串时间戳之外，最好的方法是什么？有什么方法可以让它转换为正确的类型吗？
I'd like to run a PostgreSQL query in Python using psycopg2, which filters by a column of type timestamp without timezone. I have a long list of allowed values for the timestamp (rather than a range) and psycopg2 conveniently handles arrays, so I thought that this should work:
SELECT somestuff
FROM mytable
WHERE thetimestamp = ANY (%(times)s)
The times parameter is a list of datetime objects. I've also tried psycopg2.Timestamp(). They both translates to WHERE thetimestamp = ANY (ARRAY['2009-07-06T00:00:00', '2009-07-07T00:00:00', ...]) and unfortunately that fails with the following error:
operator does not exist: timestamp without time zone = text
LINE 3: WHERE thetimestamp = ANY (ARRAY['2009-07-06T00:00:00', '2009-07-07T00:00:00', ...]
HINT: No operator matches the given name and argument type(s). You might need to add explicit type casts.
I've confirmed this in pgAdmin as well, so it's not just psycopg2. What seems to be happening is that Postgres will not implicitly convert an array of strings into an array of timestamps. It will convert a single string fine and the array works fine if I explicitly add ::timestamp to each element in pgAdmin, but I don't know how to do that in psycopg2.
What's the best way of doing this, other than forgetting DB-API parameters and just building the long string of timestamps manually? Is there any way I can get it to cast to the correct type?
如果你对这篇内容有疑问，欢迎到本站社区发帖提问 参与讨论，获取更多帮助，或者扫码二维码加入 Web 技术交流群。
绑定邮箱获取回复消息
由于您还没有绑定你的真实邮箱，如果其他用户或者作者回复了您的评论，将不能在第一时间通知您！
发布评论
评论（2）
试试这样：
Try it like this:
如果您使用 psycopg2 版本 2.2.0 或更高版本，并且按照您的建议将值包装在
Timestamp()构造函数中，则原始代码应该可以工作。之前它不起作用的原因是 psycopg2 实现中的一个错误。建议的解决方法是将显式转换插入 SQL，如另一个答案中所建议的。
If you use psycopg2 version 2.2.0 or newer, your original code should work, if you wrap the values in
Timestamp()constructors, as you suggested.The reason that it didn't work before was a bug in the psycopg2 implementation. The suggested workaround was to insert explicit casts into the SQL, as suggested in another answer.