使用pyodbc从Python应用程序将值插入Access 2003数据库

I've checked * a lot in the past and have always been able to find what I've been looking for, but I just can't seem to get this one to work so I'm asking my first question.

我过去经常检查*,并且总能找到我一直在寻找的东西,但我似乎无法让这个工作,所以我问我的第一个问题。

I'm not really a programmer, but I mentioned Python at work and now I have a Python project. I was actually getting everything figured out alright, but inserting values into a database is throwing me for a loop.

我不是真正的程序员,但我在工作中提到了Python,现在我有了一个Python项目。我实际上是把所有事情弄清楚了,但是将值插入数据库会让我陷入困境。

The Basic Problem:

基本问题:

I have a form built using Python and tkinter. When a button on the form is pressed I want values to be inserted into a database.

我有一个使用Python和tkinter构建的表单。当按下窗体上的按钮时,我希望将值插入到数据库中。

The Details:

I'm working with Python 3.4, pyodbc, and an Access 2003 database.

我正在使用Python 3.4,pyodbc和Access 2003数据库。

The database is just 1 table called file_info and it has the following fields, the fields data type is listed behind the pipe.

数据库只是一个名为file_info的表,它有以下字段,字段数据类型列在管道后面。

ID | AutoNumber

ID |自动编号

filename | Text

文件名|文本

date | Date/Time

日期|约会时间

batch_amount | Number

batch_amount |数

parcel_amount | Number

parcel_amount |数

sum_amount | Number

sum_amount |数

Eventually I'd like to insert some values calculated in other functions, but currently I'm just trying to insert some set values via a function and I can't get it to work.

最后我想插入一些在其他函数中计算的值,但是目前我只是试图通过函数插入一些设置值而我无法使其工作。

Connection string:

db_file = r'''C:\Users\amarquart\Documents\testlockboxdb.mdb'''
user = 'admin'
password = ''
odbc_conn_str = 'DRIVER={Microsoft Access Driver (*.mdb,   
*.accdb)};DBQ=%s;UID=%s;PWD=%s' % \
(db_file, user, password)

conn = pyodbc.connect(odbc_conn_str)
cur = conn.cursor()

The program compiles and runs fine with this so I'm assuming the error isn't with the connection string. So here's some examples of the code I've been using in my function to no avail.

程序编译并运行正常,所以我假设错误不是连接字符串。所以这里有一些我在我的函数中使用的代码的例子无济于事。

def insert_data():
    sql = '''INSERT INTO file_info
    (
      [ID],
      [date],
      [filename],
      [batches_amount],
      [parcels_amount],
      [sum_amount],
    )
    VALUES
    (
      '1',
      'test',
      '8/01/2014 1:00:00 PM',
      '1',
      '1',
      '1',
    );'''

    cur.execute(sql)

    conn.commit()
    cur.commit()
    conn.close()

That gives this error:

这给出了这个错误:

Exception in Tkinter callback Traceback (most recent call last): File "C:\Python34\lib\tkinter__init__.py", line 1487, in call return self.func(*args) File "C:/Users/amarquart/PycharmProjects/Grid testing/Source/Grid testing.py", line 170, in run insert_data() File "C:/Users/amarquart/PycharmProjects/Grid testing/Source/Grid testing.py", line 36, in insert_data cur.execute(sql) pyodbc.ProgrammingError: ('42000', '[42000] [Microsoft][ODBC Microsoft Access Driver] Syntax error in INSERT INTO statement. (-3502) (SQLExecDirectW)')

Tkinter回调中的异常回溯(最近一次调用最后一次):文件“C:\ Python34 \ lib \ tkinter__init __。py”,第1487行,在调用返回self.func(* args)文件“C:/ Users / amarquart / PycharmProjects /网格测试/ Source / Grid testing.py“,第170行,在运行insert_data()文件”C:/ Users / amarquart / PycharmProjects / Grid testing / Source / Grid testing.py“,第36行,在insert_data中执行cur.execute( sql)pyodbc.ProgrammingError:('42000','[42000] [Microsoft] [ODBC Microsoft Access驱动程序] INSERT INTO语句中的语法错误。( - 3502)(SQLExecDirectW)')

def insert_data():
    sql = ("""INSERT INTO [file_info] ([ID], [date], [filename], [batches_amount],     
         [parcels_amount], [sum_amount])
          VALUES (?, ?, ?, ?, ?, ?)""", [1, '8/01/2014 1:00:00 PM', 'test', 10, 4, 2])
    cur.execute(sql)

    conn.commit()
    cur.commit()
    conn.close()

gives this error:

给出了这个错误:

Exception in Tkinter callback Traceback (most recent call last): File "C:\Python34\lib\tkinter__init__.py", line 1487, in call return self.func(*args) File "C:/Users/amarquart/PycharmProjects/Grid testing/Source/Grid testing.py", line 154, in run insert_data() File "C:/Users/amarquart/PycharmProjects/Grid testing/Source/Grid testing.py", line 20, in insert_data cur.execute(sql) TypeError: The first argument to execute must be a string or unicode query.

Tkinter回调中的异常回溯(最近一次调用最后一次):文件“C:\ Python34 \ lib \ tkinter__init __。py”,第1487行,在调用返回self.func(* args)文件“C:/ Users / amarquart / PycharmProjects /网格测试/源/网格testing.py“,第154行,在运行insert_data()文件”C:/ Users / amarquart / PycharmProjects / Grid testing / Source / Grid testing.py“,第20行,在insert_data中执行cur.execute( SQL)TypeError:要执行的第一个参数必须是字符串或unicode查询。

def insert_data():
    sql = """
    INSERT INTO file_info (ID, date, filename, batches_amount, parcels_amount, sum_amount)
    VALUES (1, '8/01/2014 1:00:00 PM', 'test', 2, 2, 2)
    """ 
    cur.execute(sql)

    conn.commit()
    cur.commit()
    conn.close()

Gave the same error as the last code

给出与上一个代码相同的错误

def insert_data():
   cur.execute("INSERT INTO file_info VALUES (AutoNumber, Text, Date/Time, Number,
               Number, Number)",
(1, 'test', '8/01/2014 1:00:00 PM', 2, 2, 2))
   conn.commit()
   cur.commit()
   conn.close()

Gave this error:

给出了这个错误:

Exception in Tkinter callback Traceback (most recent call last): File "C:\Python34\lib\tkinter__init__.py", line 1487, in call return self.func(*args) File "C:/Users/amarquart/PycharmProjects/Grid testing/Source/Grid testing.py", line 153, in run insert_data() File "C:/Users/amarquart/PycharmProjects/Grid testing/Source/Grid testing.py", line 19, in insert_data (1, 'test', '8/01/2014 1:00:00 PM', 2, 2, 2)) pyodbc.ProgrammingError: ('The SQL contains 0 parameter markers, but 6 parameters were supplied', 'HY000')

Tkinter回调中的异常回溯(最近一次调用最后一次):文件“C:\ Python34 \ lib \ tkinter__init __。py”,第1487行,在调用返回self.func(* args)文件“C:/ Users / amarquart / PycharmProjects /网格测试/ Source / Grid testing.py“,第153行,在运行insert_data()文件”C:/ Users / amarquart / PycharmProjects / Grid testing / Source / Grid testing.py“,第19行,在insert_data(1,' test','8/01/2014 1:00:00 PM',2,2,2))pyodbc.ProgrammingError:('SQL包含0个参数标记,但提供了6个参数','HY000')

I'm guessing everything I've tried is extremely incorrect so any help would be greatly appreciated.

我猜我所尝试的一切都是非常不正确的,所以任何帮助都将非常感激。

Thanks everyone.

EDIT:

new attempts based on first response, the only difference is that I use triple quotes because the code spans 2 lines

基于第一个响应的新尝试,唯一的区别是我使用三重引号,因为代码跨越2行

cur.execute("""INSERT INTO file_info (ID, date, filename, batches_amount,   
parcels_amount, sum_amount) 
         VALUES (1, 'test', '8/01/2014 1:00:00 PM', 2, 2, 2)""")
conn.commit()

gives this error

给出了这个错误

Exception in Tkinter callback Traceback (most recent call last): File "C:\Python34\lib\tkinter__init__.py", line 1487, in call return self.func(*args) File "C:/Users/amarquart/PycharmProjects/Grid testing/Source/Grid testing.py", line 19, in insert_data VALUES (1, 'test', '8/01/2014 1:00:00 PM', 2, 2, 2)''') pyodbc.ProgrammingError: ('42000', '[42000] [Microsoft][ODBC Microsoft Access Driver] Syntax error in INSERT INTO statement. (-3502) (SQLExecDirectW)')

Tkinter回调中的异常回溯(最近一次调用最后一次):文件“C:\ Python34 \ lib \ tkinter__init __。py”,第1487行,在调用返回self.func(* args)文件“C:/ Users / amarquart / PycharmProjects /网格测试/ Source / Grid testing.py“,第19行,在insert_data VALUES(1,'test','8/01/2014 1:00:00 PM',2,2,2)''')pyodbc。 ProgrammingError:('42000','[42000] [Microsoft] [ODBC Microsoft Access驱动程序] INSERT INTO语句中的语法错误。( - 3502)(SQLExecDirectW)')

params = [(1, '8/01/2014 1:00:00 PM', 'test', 2, 2, 2)]
cur.executemany("""insert into file_info(ID, date, filename, batch_amount,   
parcel_amount, sum_amount)
                values (?, ?, ?, ?, ?, ?)""", params)
conn.commit()

gives this error

给出了这个错误

Exception in Tkinter callback Traceback (most recent call last): File "C:\Python34\lib\tkinter__init__.py", line 1487, in call return self.func(*args) File "C:/Users/amarquart/PycharmProjects/Grid testing/Source/Grid testing.py", line 20, in insert_data values (?, ?, ?, ?, ?, ?)""", params) pyodbc.Error: ('HYC00', '[HYC00] [Microsoft][ODBC Microsoft Access Driver]Optional feature not implemented (106) (SQLBindParameter)')

Tkinter回调中的异常回溯(最近一次调用最后一次):文件“C:\ Python34 \ lib \ tkinter__init __。py”,第1487行,在调用返回self.func(* args)文件“C:/ Users / amarquart / PycharmProjects /网格测试/ Source / Grid testing.py“,第20行,在insert_data值中(?,?,?,?,?,?)”“”,params)pyodbc.Error:('HYC00','[HYC00] [ Microsoft] [ODBC Microsoft Access驱动程序]未实现的可选功能(106)(SQLBindParameter)')

2 个解决方案

#1

You were close on a couple attempts. Date is a reserved word in Access, surround the column name with brackets and ensure the order of columns matches the order of values:

你几次尝试都很接近。 Date是Access中的保留字,用括号括住列名,并确保列的顺序与值的顺序匹配:

...
sql = """
INSERT INTO file_info (ID, [date], filename, batches_amount, parcels_amount, sum_amount)
VALUES (1, '8/01/2014 1:00:00 PM', 'test', 2, 2, 2)
""" 
cur.execute(sql)
....

According to Gord's comments below, parameterized queries are supported against Access, so the ideal code would be:

根据Gord的下面的评论,Access支持参数化查询,因此理想的代码是:

....
params = (1, '8/01/2014 1:00:00 PM', 'test', 2, 2, 2)
sql = """
INSERT INTO file_info (ID, [date], filename, batches_amount, parcels_amount, sum_amount)
VALUES (?, ?, ?, ?, ?, ?)
""" 
cur.execute(sql, params)
...

#2

You have the order slightly wrong: Values should come after the column definitions, and before the values being inserted, eg, in your case:

您的订单略有错误:值应位于列定义之后,并且在插入值之前,例如,在您的情况下:

cur.execute("INSERT INTO file_info (ID, filename, [date], batches_amount, parcels_amount, sum_amount) 
             VALUES (1, 'test', '8/01/2014 1:00:00 PM', 2, 2, 2)")
conn.commit()

This is standard SQL insert syntax and is not peculiar to pyodbc or Access. Note that you can also use placeholders, (?), for the values, and then provide an array of values, see the docs, particularly executemany.

这是标准的SQL插入语法,并不是pyodbc或Access特有的。请注意,您还可以使用占位符(?)作为值,然后提供值数组,请参阅文档,尤其是executemany。

Also note that you only have to call conn.commit(), not cur.commit(), see insert in these other docs

另请注意,您只需要调用conn.commit(),而不是cur.commit(),请参阅这些其他文档中的插入

EDIT: based on beargle's comments, you do need to put date in [], as it is a reserved word, which you had in your original attempt, but again, you had date and filename values backwards. Try and avoid using reserved words as fieldnames in general, and this applies to other databases than Access.

编辑:根据beargle的评论,您需要在[]中输入日期,因为它是您在原始尝试中保留的保留字,但同样,您还有向后的日期和文件名值。尝试并避免一般使用保留字作为字段名,这适用于除Access之外的其他数据库。

#1