使用pyodbc在Access中处理日期，并给出“参数太少"错误 [英] Working with dates in Access using pyodbc giving "Too few parameters" error

问题描述

我正在将Python与pyodbc导入配合使用.

我正在使用Microsoft Office 2013 64位.

我正在尝试查询accdb数据库以选择范围内的不同日期，并将它们分配给游标，以便随后将它们附加到列表中.

我的Access数据库有一个名为Closing_prices的表和一个名为Date_的列，其数据类型为日期/时间".

我的代码如下:

cursor=conx.cursor()
query="select distinct Date_ FROM Closing_prices where Date_ >= '10/8/2011' and Date_ < '30/04/2014'"
cursor.execute(query)
dates=list()
for date in cursor:
   dates.append(date[0])

但是我收到错误消息:

Traceback (most recent call last):
  File "C:/Users/Stuart/PycharmProjects/untitled/Apache - Copy.py", line 20, in <module>
cursor.execute(query)
pyodbc.Error: ('07002', '[07002] [Microsoft][ODBC Microsoft Access Driver] Too few parameters. Expected 1. (-3010) (SQLExecDirectW)')

由于Date_是一个日期时间，因此我也尝试过:

query="select distinct Date_ FROM Closing_prices where Date_ >= '10/8/2011 00:00:00' and Date_ < '30/04/2014 00:00:00'"

当我跑步时:

cursor = conx.cursor()
query="select Date_ FROM Closing_prices"
cursor.execute(query)

for row in cursor:
    print row

print type(row[0])

我得到以下输出作为示例:

(datetime.datetime(2014, 3, 24, 0, 0), )
(datetime.datetime(2014, 3, 25, 0, 0), )
(datetime.datetime(2014, 3, 26, 0, 0), )
(datetime.datetime(2014, 3, 27, 0, 0), )

我对Python还是相对较新，对SQL查询甚至更新，所以有人可以指出我要去哪里了，也许我可以如何更改代码以帮助我根据需要将不同的日期附加到列表中. /p>

非常感谢.

解决方案

收件人

1. 免去了查找适用日期分隔符的麻烦，并且
2. 促进良好的编码习惯

您应该像这样简单地使用参数化查询:

 db = pyodbc.connect(connStr)
crsr = db.cursor()
sql = """
SELECT DISTINCT Date_ FROM Closing_prices WHERE Date_ >= ? AND Date_ < ?
"""
params = (datetime.date(2011, 8, 10), datetime.date(2014, 4, 30))
crsr.execute(sql, params)
 

I am using Python with a pyodbc import.

I am using Microsoft Office 2013 64bit.

I am attempting to query an accdb database to select distinct dates within a range and assign them to a cursor so I can then append them to a list.

My Access database has a table named Closing_prices, and a column named Date_, which has the data type "Date/Time".

My code is as follows:

cursor=conx.cursor()
query="select distinct Date_ FROM Closing_prices where Date_ >= '10/8/2011' and Date_ < '30/04/2014'"
cursor.execute(query)
dates=list()
for date in cursor:
   dates.append(date[0])

However I am receiving the error message:

Traceback (most recent call last):
  File "C:/Users/Stuart/PycharmProjects/untitled/Apache - Copy.py", line 20, in <module>
cursor.execute(query)
pyodbc.Error: ('07002', '[07002] [Microsoft][ODBC Microsoft Access Driver] Too few parameters. Expected 1. (-3010) (SQLExecDirectW)')

As Date_ is a datetime, I have also tried:

query="select distinct Date_ FROM Closing_prices where Date_ >= '10/8/2011 00:00:00' and Date_ < '30/04/2014 00:00:00'"

When I run:

cursor = conx.cursor()
query="select Date_ FROM Closing_prices"
cursor.execute(query)

for row in cursor:
    print row

print type(row[0])

I get the following output as an example:

(datetime.datetime(2014, 3, 24, 0, 0), )
(datetime.datetime(2014, 3, 25, 0, 0), )
(datetime.datetime(2014, 3, 26, 0, 0), )
(datetime.datetime(2014, 3, 27, 0, 0), )

I am relatively new to Python and even newer to SQL queries, so could someone please point out where I am going wrong, and perhaps how I can change my code to help me append the distinct dates into a list as desired.

Many thanks.

解决方案

To

1. save yourself the hassle of finding the applicable date delimiter, and
2. promote good coding practice

you should simply use a parameterized query like this:

db = pyodbc.connect(connStr)
crsr = db.cursor()
sql = """
SELECT DISTINCT Date_ FROM Closing_prices WHERE Date_ >= ? AND Date_ < ?
"""
params = (datetime.date(2011, 8, 10), datetime.date(2014, 4, 30))
crsr.execute(sql, params)

这篇关于使用pyodbc在Access中处理日期，并给出“参数太少"错误的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！