从pandas DataFrame插入Access数据库 [英] INSERT INTO Access database from pandas DataFrame

问题描述

请有人告诉我，如何插入到数据库中，但看起来像是python中的所有数据框一样?

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

我发现了这一点，但不知道如何插入带有两个数字的所有名为test_data的数据框:ID，Employee_id.

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

我也不知道如何为ID插入下一个值(类似于nextval)

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

谢谢

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

推荐答案

您可以使用pyodbc的executemany方法，并使用pandas的itertuples方法传递行:

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

更新: 像这样的参数化查询可以在pyodbc版本4.0.27上再次使用，但不能在4.0.25(如上所述)或4.0.26上使用.尝试使用这些版本将导致未隐含可选功能"错误.在这里 https://github.com/mkleehammer/pyodbc/issues/509.

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.

这篇关于从pandas DataFrame插入Access数据库的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！