在Python ODBC中使用Microsoft Access SQL运算符 [英] Using Microsoft Access SQL operators in Python ODBC

问题描述

简短版本:当我尝试通过ODBC使用Access的DatePart函数时，无法解决它.

Short version: When I try to use Access's DatePart function via ODBC, it cannot be resolved.

长版:

我有一个Microsoft Access查询，该查询返回带有时间戳和分数的行.我想按 day 排序，然后按得分排序-实际上是当天的高分表.

I have a Microsoft Access query which returns rows with a timestamp and a score. I want to sort it by day and then by score - effectively a high-score table for the day.

由于需要更好的功能，我使用了DatePart函数从时间戳中提取年，月和日中的每一个，并在它们中分别添加ORDER BY和分数.

For want of a better function, I used the DatePart function to extract each of the Year, Month and Day from the timestamp, and ORDER BY them followed by Score.

在Microsoft Access中，查询工作正常.

In Microsoft Access, the query works beautifully.

但是，当我使用pyodbc访问同一查询时，DatePart函数将ODBC驱动程序绊倒，并认为它是缺少参数的名称.

However, when I use pyodbc to access the same query, the ODBC driver is stumped by the DatePart function, and thinks it is the name of a missing parameter.

令我惊讶的是，即使我隐藏了DatePart函数，通过创建一个新的HighScore查询，然后又SELECT * FROM HighScore，它 still 仍然抱怨找不到参数.显然，查询的SQL在该过程的后期被解决了.

What astonished me was that even if I hid the DatePart function, by creating a new HighScore query, and then SELECT * FROM HighScore, it still complained that it couldn't find the parameter. Apparently, the query's SQL is being resolved fairly late in the process.

我的问题是:

• 如何在SQL中解析DatePart函数以允许Access运行它，或者
• 通过ODBC在时间戳的一天中进行排序的正确方法是什么?

添加了其他信息:

似乎有些矫kill过正，但这是一些代码:

Seems like overkill, but here's some code:

import pyodbc
access_db_path = r"<ellided>"
connection_string = 'Driver={Microsoft Access Driver (*.mdb, *.accdb)};DBQ='+access_db_path
connection = pyodbc.connect(connection_string, autocommit = True)

print "First query"
connection.cursor().execute('SELECT ScoreTime FROM SplitExtendedP1')
print "Worked"

print "Second query"
print connection.cursor().execute('SELECT DatePart("yyyy",ScoreTime) FROM SplitExtendedP1')
print "Doesn't get here."

以下是结果:

First query
Worked
Second query
Traceback (most recent call last):
  File "<ellided>.py", line 16, in <module>
    print connection.cursor().execute('SELECT DatePart("yyyy", ScoreTime) FROM SplitExtendedP1')
pyodbc.Error: ('07002', '[07002] [Microsoft][ODBC Microsoft Access Driver] Too few parameters. Expected 1. (-3010) (SQLExecDirectW)')

推荐答案

您确定在SQL的方言中使用引号"yyyy"代替撇号'yyyy'是否有效?

Are you sure that using quotes "yyyy" instead of apostrophes 'yyyy' is valid in that dialect of SQL?

这篇关于在Python ODBC中使用Microsoft Access SQL运算符的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！