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

I've checked stackoverflow 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.

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.

The Details:

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

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

ID | AutoNumber

filename | Text

date | Date/Time

batch_amount | Number

parcel_amount | Number

sum_amount | Number

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)')

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.

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')

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

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)')

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)')

解决方案

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:

...

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:

....

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)

...