如何使用 pyodbc 从 ms access 数据库中选择最后 5 分钟的数据？

发布于 2024-11-17 12:32:26 字数 466 浏览 3 评论 0原文

我有以下查询（Windows 7 上的 python pyodbc 访问访问数据库 .mdb）：

SQL = 'SELECT acq_spill_3_1_sec.time_stamp, acq_spill_3_1_sec.float_value 
    FROM acq_spill_3_1_sec WHERE acq_spill_3_1_sec.time_stamp > DateADD(n,-5,Now())'

DateADD 似乎不起作用。我收到以下错误：

pyodbc.Error: ('07002', '[070021] [Microsoft][ODBC Microsoft Access Driver] Too few
parameters. Expected 1.

我尝试过在 n 周围使用单引号、双引号和无引号，但这没有什么区别。

原文

I have the following query (python pyodbc on windows 7 accessing an access db .mdb):

SQL = 'SELECT acq_spill_3_1_sec.time_stamp, acq_spill_3_1_sec.float_value 
    FROM acq_spill_3_1_sec WHERE acq_spill_3_1_sec.time_stamp > DateADD(n,-5,Now())'

The DateADD doesn't seem to work. I get the following error:

pyodbc.Error: ('07002', '[070021] [Microsoft][ODBC Microsoft Access Driver] Too few
parameters. Expected 1.

I've tried single, double and no quotes around the n, but that makes no difference.

分享到QQ

分享到微博

如果你对这篇内容有疑问，欢迎到本站社区发帖提问参与讨论，获取更多帮助，或者扫码二维码加入 Web 技术交流群。

发布评论

需要登录才能够评论，你可以免费注册一个本站的账号。

潇烟暮雨 2024-11-24 12:32:26

两个问题： 1) 在 n 参数两边加上双引号。 2) 将 time_stamp 放在方括号内。（在某些情况下，Access 会将 time_stamp 字段中的下划线解释为特殊字符。查询解析器会尽力解释特殊字符，但有时会出错。方括号可帮助解析器消除歧义字段名称，因此它可以正确解析。）

只是我的意见，我会使用 DateDiff 相反，因为当您阅读它时它更有意义：

SQL = 'SELECT time_stamp, float_value FROM acq_spill_3_1_sec WHERE DateDiff("n",[time_stamp],Now()) <= 5'

我发现在 Access 中自己构建查询要容易得多并先在那里尝试一下，确保它们可以工作，然后再将它们写入代码。祝你好运！

Two problems: 1) Put double quotes around the n parameter. 2) Put square brackets around the time_stamp. (Access interprets your underscore in the time_stamp field as a special character under certain circumstances. The query parser does its best to interpret special characters, but sometimes it gets it wrong. The square brackets help the parser disambiguate the field name so it does the parsing correctly.)

Just my opinion, I would use DateDiff instead, since it makes more sense when you read it:

SQL = 'SELECT time_stamp, float_value FROM acq_spill_3_1_sec WHERE DateDiff("n",[time_stamp],Now()) <= 5'

I find it's a lot easier to build the queries yourself inside Access and try them out there first to make sure they work before writing them in code. Good luck!

回复收藏 0 原文