INSERT INTO Access database from pandas DataFrame

Question

Please could somebody tell me how should look like insert into the database but of the all data frame in python?

I found this but don't know how to insert all data frame called test_data with two figures: ID, Employee_id.

I also don't know how to insert the next value for ID (something like nextval)

Thank you

import pyodbc 
conn = pyodbc.connect(r'Driver={Microsoft Access Driver (*.mdb)};DBQ=C:\Users\test_database.mdb;')
cursor = conn.cursor()  
cursor.execute('''
                INSERT INTO employee_table (ID, employee_id)
                VALUES(?????????)
              ''')
conn.commit()

Answer 1

Update, June 2020:

Now that the sqlalchemy-access dialect has been revived the best solution would be to use pandas' to_sql method.

https://pandas.pydata.org/pandas-docs/stable/reference/api/pandas.DataFrame.to_sql.html

---

(previous answer)

You can use pyodbc's executemany method, passing the rows using pandas' itertuples method:

print(pyodbc.version)  ## 4.0.24 (not 4.0.25, which has a known issue with Access ODBC)
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()

# prepare test environment
table_name = "employee_table"
if list(crsr.tables(table_name)):
    crsr.execute(f"DROP TABLE [{table_name}]")
crsr.execute(f"CREATE TABLE [{table_name}] (ID COUNTER PRIMARY KEY, employee_id TEXT(25))")

# test data
df = pd.DataFrame([[1, 'employee1'], [2, 'employee2']], columns=['ID', 'employee_id'])

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

Update: Parameterized queries like this work again with pyodbc version 4.0.27 but not 4.0.25 (as mentioned above) or 4.0.26. Attempting to use these versions will result in an "Optional feature not implimented" error. This issue is discussed here https://github.com/mkleehammer/pyodbc/issues/509.

Answer 2

Using to_sql you may do :

test_data.to_sql('employee_table', engine, index=False, if_exists='append')

This will add the values of the test_data at the end of your employee table.