将数据从python pandas 数据框导出或写入到MS Access表 [英] Exporting or writing data from python pandas data frame to MS Access table

问题描述

我正在尝试将数据从python熊猫数据帧导出到现有的MS Access表，我想用已更新的数据(在python中)替换MS Access表 我尝试使用pandas.to_sql，但收到错误消息.我觉得这很奇怪，使用pandas.read_sql可以无缝工作吗?

I am trying to export data from a python pandas data frame to an existing MS Access table, I would like to replace the MS access table with data that has been updated (in python) I have tried to use pandas.to_sql, but I get an error message. I find this strange this using pandas.read_sql works seamlessly?

这是我的代码:

import pyodbc
import pandas as pd
conn_str = (
r'DRIVER={Microsoft Access Driver (*.mdb, *.accdb)};'
r'DBQ=H:\Work\IndexTrader\Data\database\IndexData.accdb;'
)
cnxn = pyodbc.connect(conn_str)
SQL = 'SELECT * FROM Index_data;

从MS Access读取数据很好，请参见下文

Reading data from MS Access is fine, see below

dfins = pd.read_sql(SQL, cnxn)

但是，当我尝试回写并替换MS Extra中的表时，它不起作用吗?

However when I try to write back and replace the table in MS excess it doesn't work?

dfins.to_sql('Index_data', cnxn, if_exists='replace')
cnxn.close()

我得到的错误是:

DatabaseError: Execution failed on sql 'SELECT name FROM sqlite_master WHERE type='table' AND name=?;': ('42S02', "[42S02] [Microsoft][ODBC Microsoft Access Driver] The Microsoft Access database engine cannot find the input table or query 'sqlite_master'. Make sure it exists and that its name is spelled correctly. (-1305) (SQLExecDirectW)")

如果有替代pandas.to_sql的替代方法，那也将有所帮助，我只需要知道如何导出数据即可.

If there is an alternative way instead of pandas.to_sql, that would help as well, I just need to know how to export my data.

推荐答案

如评论中所述，to_sql仅支持slite3

As said in the comments, to_sql only supports slite3

一种示例方法，用于逐行分析数据帧，并将每一行插入表中:

An example approach to parse the data frame row by row, and insert each row into a table:

import pyodbc
import pandas as pd
conn_str = (
r'DRIVER={Microsoft Access Driver (*.mdb, *.accdb)};'
r'DBQ=C:\Users\Erik\Desktop\TestDb.accdb;'
)
cnxn = pyodbc.connect(conn_str)
SQL = 'SELECT * FROM Index_data;'
dfins = pd.read_sql(SQL, cnxn)
for index, row in dfins.iterrows():
    with cnxn.cursor() as crsr:
       crsr.execute("INSERT INTO Output(Field1, Field2) VALUES(?,?)", row["F1"], row["F2"] ) 

这会将DataFrame的列F1和F2插入到名为Output的表的字段Field1和Field2中.

This inserts columns F1 and F2 of the DataFrame into fields Field1 and Field2 of a table named Output.

这有两个主要条件才能正常工作:

This has two main conditions to properly work:

1. 您需要有一个Access表才能接收数据
2. 表需要具有正确的字段和正确的数据类型才能接收数据

您可以使用预先存在的表，例如从数据表中获取数据的索引表，尽管我不建议这样做(丢失数据的风险).如果这样做，则需要首先清除该表.要清除索引表:

You can use a pre-existing table, like the index table you're getting the data from, though I don't recommend it (risk of losing data). If you do so, you need to clear the table first. To clear out the index table:

with cnxn.cursor() as crsr:
    crsr.execute("DELETE * FROM Index_data;")

这篇关于将数据从python pandas 数据框导出或写入到MS Access表的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！