Inserting Values Into A Access 2003 Database From A Python Application Using Pyodbc

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 ques

Solution 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:

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

Solution 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.

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

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.