2

I need to populate a table in MS Access using a pandas df. This table includes an auto-number column and another column with default value =now().

I tried to pass the following dataframe keeping the ID and RowInsertedTime columns as blank, however this hasn't worked out.

d = {'ID': ['',''], 'col1': [1, 2], 'col2': [3, 4], 'RowInsertedTime': ['','']}
df = pd.DataFrame(data=d)

I executed my code using the pyodbc library as shown below:

connection_string = (
    r'DRIVER={Microsoft Access Driver (*.mdb, *.accdb)};'
    r'DBQ=C:\Users\Public\MyTest.accdb;'
)
cnxn = pyodbc.connect(connection_string, autocommit=True)
crsr = cnxn.cursor()

# insert the rows from the DataFrame into the Access table    
crsr.executemany(
    f"INSERT INTO [{table_name}] (ID, col1, col2, RowInsertedTime) VALUES (?, ?, ?, ?)",
    df.itertuples(index=False))

Unfortunately this is returning the following error:

DataError: ('22018', '[22018] [Microsoft][ODBC Microsoft Access Driver] Data type mismatch in criteria expression. (-3030) (SQLExecDirectW)')

Any idea how I can get the auto values for the blank fields showing inside my MS Access table?

Mtra
  • 201
  • 4
  • 10
  • Possible duplicate of [Using Autonumbering in Access - INSERT statements](https://stackoverflow.com/questions/771485/using-autonumbering-in-access-insert-statements) – HackSlash Apr 29 '19 at 17:06

1 Answers1

1

You must leave out the columns you aren't updating. 

crsr.executemany(
    f"INSERT INTO [{table_name}] (col1, col2) VALUES (?, ?)",
    df.itertuples(index=False))

Access will fill in the blanks.

HackSlash
  • 4,944
  • 2
  • 18
  • 44