给出“参数太少"的Python SQL数据库查询错误 [英] Python SQL database query giving "Too few parameters" error
问题描述
我有以下代码,试图从Access数据库中提取几个SQL查询
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)')
该请求正尝试从表中提取Date_,Time_和Close_项目,以获取传递给该请求的日期,因为它遍历先前创建的日期列表,并且截取时间为"15:30之后"表示日期",表示15:14之前"表示日期+1".
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".
首先,当SQL请求中只有4个问号(?)时,为什么会期望有6个参数?我不能正确地构成它吗?
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?
此外,我在创建参数datetime.time的过程中遇到了麻烦.这也是不正确的形式吗?
Also, I took a stab at the parameter creation for a datetime.time. Is this incorrectly formed also?
我有点不懂事!
推荐答案
更改查询后有效
query= '''SELECT Date_,Time_, Close_
FROM Intraday_values
WHERE (Date_ = ? and Time_ >= ?)
OR (Date_ = ? and Time_ <= ?)'''
这篇关于给出“参数太少"的Python SQL数据库查询错误的文章就介绍到这了,希望我们推荐的答案对大家有所帮助,也希望大家多多支持IT屋!