All geek questions in one place

Insert values ​​into an Access 2003 database from a Python application using pyodbc

I checked stackoverflow a lot in the past and could always find what I was looking for, but I just can't get it to work, so I ask my first question.

I am not a programmer, but I mentioned Python at work, and now I have a Python project. I actually understood everything, but inserting values ​​into the database throws me into a loop.

Main problem:

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

Details:

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

The database is just one table named file_info and has the following fields, the data type of the fields is indicated behind the pipe.

ID | AutoNumber

file_name | Text

date | Date Time

batch_amount |

parcel_amount |

sum_amount |

, , , , .

:

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

, , . , , , .

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

:

Tkinter Traceback ( ): "C:\Python34\lib\tkinter__init __. Py" , 1487,   return 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 Driver] 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()

:

Tkinter Traceback ( ): "C:\Python34\lib\tkinter__init __. Py" , 1487,   return self.func(* args) "C:/Users/amarquart/PycharmProjects/Grid testing/Source/Grid testing.py" , 154,   insert_data() "C:/Users/amarquart/PycharmProjects/Grid testing/Source/Grid testing.py" , 20, insert_data   cur.execute(SQL) TypeError: .

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

,

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

:

Tkinter Traceback ( ): "C:\Python34\lib\tkinter__init __. Py" , 1487,   return self.func(* args) "C:/Users/amarquart/PycharmProjects/Grid testing/Source/Grid testing.py" , 153,   insert_data() "C:/Users/amarquart/PycharmProjects/Grid testing/Source/Grid testing.py" , 19, insert_data   (1, 'test', '08/01/2014 1:00:00 PM', 2, 2, 2)) pyodbc.ProgrammingError: ('SQL 0 , 6 ', 'HY000')

, , , , .

.

EDIT:

, , , , 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()

Tkinter Traceback ( ): "C:\Python34\lib\tkinter__init __. Py" , 1487,   return self.func(* args) "C:/Users/amarquart/PycharmProjects/Grid testing/Source/Grid testing.py" , 19, insert_data   VALUES (1, 'test', '08/01/2014 1:00:00 PM', 2, 2, 2) '' ') pyodbc.ProgrammingError: ('42000', '[42000] [Microsoft] [ODBC Microsoft Access Driver] 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()

Tkinter Traceback ( ): "C:\Python34\lib\tkinter__init __. Py" , 1487, return self.func(* args) "C:/Users/amarquart/PycharmProjects/Grid testing/Source/Grid testing.py" , 20, insert_data    (?,?,?,?,?,?) "", params) pyodbc.Error: ('HYC00', '[HYC00] [Microsoft] [ODBC Microsoft Access Driver] (106) (SQLBindParameter)')

+3
2

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

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

: , , :

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

SQL pyodbc Access. , placeholders (?) , , . docs, , executemany.

, conn.commit(), cur.commit(),

: [], , , . , , Access.

+3

Source: https://habr.com/ru/post/1611297/

More articles:


All Articles