问题描述:

I have the following code which is attempting to pull several SQL queries from an Access database

import pyodbc

import datetime

conx = pyodbc.connect("Driver={Microsoft Access Driver (*.mdb, *.accdb)};DBQ=C:\\Users\\Stuart\\PycharmProjects\\untitled\\Databases\\SandP.accdb;")

cursor=conx.cursor()

query=""" SELECT DISTINCT Date_ FROM Closing_prices

WHERE Date_ >= ? AND Date_ < ?"""

params1 = (datetime.date(2011, 8, 10), datetime.date(2014, 4, 30))

cursor.execute(query, params1)

dates = list()

for date in cursor:

dates.append(date[0])

for date in dates:

params2 = date

cursor = conx.cursor()

query= '''SELECT Volatility,Last_price FROM Volatility v,Closing_prices c WHERE c.Date_= ? and v.Date_=c.Date_'''

cursor.execute(query,params2)

for (vol,price) in cursor:

volatility=float(vol)

closing_price=float(price)

cursor.close()

cursor = conx.cursor()

if (date.weekday()==4):

nextDay=(date + datetime.timedelta(days=3))

else:

nextDay=(date + datetime.timedelta(days=1))

query= '''SELECT Date_,Time_, Close_ FROM Intraday_values WHERE (date = ? and time >= ?) or (date = ? and time <= ?)'''

params3 = (date,datetime.time(15, 30),nextDay,datetime.time(15, 14))

cursor.execute(query,params3)

This last bit is throwing up the following error:

Traceback (most recent call last):

File "C:/Users/Stuart/PycharmProjects/untitled/Apache - Copy.py", line 67, in <module>

cursor.execute(query,params3)

pyodbc.Error: ('07002', '[07002] [Microsoft][ODBC Microsoft Access Driver] Too few parameters. Expected 6. (-3010) (SQLExecDirectW)')

The request is attempting to pull out the Date_, Time_ and Close_ items from the table, for the dates passed to the request as it iterates through the list of dates created previously, along with cut off times of "after 15:30 for "date" and "before 15:14" for "date+1".

Firstly, why would this be expecting 6 parameters when there are only 4 question marks (?) in the SQL request - have I not formed this properly?

Also, I took a stab at the parameter creation for a datetime.time. Is this incorrectly formed also?

I'm a bit out of my depth!

网友答案:

Works after changing the query to

query= '''SELECT Date_,Time_, Close_
          FROM Intraday_values
          WHERE (Date_ = ? and Time_ >= ?)
                OR (Date_ = ? and Time_ <= ?)'''
相关阅读:
Top