PYODBC - 参数太少

Question

我有以下代码：

Late_Students = cursor.execute('''
    SELECT Student.Forename, Student.Surname, FORMAT(Event_Date_Time,"Long Time") AS Time_Of_Event
    FROM Events, Student
    WHERE FORMAT(Event_Date_Time,"Short Date") = Date()
    AND Events.RFID = Student.RFID AND
    Events.In_Or_Out = ?
    AND FORMAT(Event_Date_Time,"Long Time")>#08:40:00#''','In')
rows = cursor.fetchall()
print(rows)

这是一个非常简单的，我的程序中有很多类似的程序，但是当我运行该程序时，我收到以下错误：

Traceback (most recent call last):
  File "...Coursework System 1.8.py", line 104, in <module>
    AND FORMAT(Event_Date_Time,"Long Time")>#08:40:00#''','In')
pyodbc.Error: ('07002', '[07002] [Microsoft][ODBC Microsoft Access Driver]
                         Too few parameters. Expected 3. (-3010) (SQLExecDirectW)')

当我添加参数时，我收到以下错误，告诉我参数太多：

Traceback (most recent call last):
  File "...Coursework System 1.8.py", line 104, in <module>
    AND FORMAT(Event_Date_Time,"Long Time")>#08:40:00#''','In','','')
pyodbc.ProgrammingError: ('The SQL contains 1 parameter markers, but 3
                           parameters were supplied', 'HY000')

我做错了什么？

Answer 1

Long_Time = 'Long Time'
Short_Date = 'Short Date'
Todays_Date = time.strftime('%d/%m/%Y')
Reg_Time = str('#08:40:00#')
In = 'In'
Late_Students = '''
                SELECT Student.Forename, Student.Surname, FORMAT(Event_Date_Time,?) AS Time_Of_Event
                FROM Events, Student
                WHERE FORMAT(Event_Date_Time,?) =?
                AND Events.RFID = Student.RFID AND
                Events.In_Or_Out =?
                AND FORMAT(Event_Date_Time,?)>?'''
parameters = (Long_Time, Short_Date,Todays_Date, In, Long_Time, Reg_Time)
cursor.execute(Late_Students, parameters)