Inserting values into a Access 2003 database from a Python application using pyodbc

Question

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

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

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