使用 SQLAlchemy 从 Pandas Dataframe 创建 Python MS Access 数据库表

Question

我正在尝试从 Python 创建一个 MS Access 数据库，并且想知道是否可以直接从 pandas 数据框创建一个表。我知道我可以使用 pandas dataframe.to_sql() 函数将数据帧成功写入 SQLite 数据库，或者通过使用 sqlalchemy 引擎来处理其他一些数据库格式(但不幸的是不能 Access )，但我不能不要让所有零件组合在一起。这是我一直在测试的代码片段:

import pandas as pd
import sqlalchemy
import pypyodbc     # Used to actually create the .mdb file
import pyodbc

# Connection function to use for sqlalchemy
def Connection():
    MDB = 'C:\\database.mdb'
    DRV = '{Microsoft Access Driver (*.mdb)}'
    connection_string = 'Driver={Microsoft Access Driver (*.mdb)};DBQ=%s' % MDB
    return pyodbc.connect('DRIVER={};DBQ={}'.format(DRV,MDB))


# Try to connect to the database
try:
    Conn = Connection()
# If it fails because its not been created yet, create it and connect to it
except:
    pypyodbc.win_create_mdb(MDB)
    Conn = Connection()

# Create the sqlalchemy engine using the pyodbc connection
Engine = sqlalchemy.create_engine('mysql+pyodbc://', creator=Connection)

# Some dataframe
data = {'Values'     : [1., 2., 3., 4.],
        'FruitsAndPets'  : ["Apples", "Oranges", "Puppies", "Ducks"]}
df = pd.DataFrame(data)

# Try to send it to the access database (and fail)
df.to_sql('FruitsAndPets', Engine, index = False)

我不确定我正在尝试做的事情对于我正在使用的当前包是否可行，但我想在将我自己的 hacky 数据框写入 MS Access 表函数之前检查这里。也许我的 sqlalchemy 引擎设置错误？

这是我在引擎中使用 mssql+pyodbc 的错误的结尾:

cursor.execute(statement, parameters)
sqlalchemy.exc.DBAPIError: (Error) ('HY000', "[HY000] [Microsoft][ODBC Microsoft Access Driver] Could not find file 'C:\\INFORMATION_SCHEMA.mdb'. (-1811) (SQLExecDirectW)") u'SELECT [COLUMNS_1].[TABLE_SCHEMA], [COLUMNS_1].[TABLE_NAME], [COLUMNS_1].[COLUMN_NAME], [COLUMNS_1].[IS_NULLABLE], [COLUMNS_1].[DATA_TYPE], [COLUMNS_1].[ORDINAL_POSITION], [COLUMNS_1].[CHARACTER_MAXIMUM_LENGTH], [COLUMNS_1].[NUMERIC_PRECISION], [COLUMNS_1].[NUMERIC_SCALE], [COLUMNS_1].[COLUMN_DEFAULT], [COLUMNS_1].[COLLATION_NAME] \nFROM [INFORMATION_SCHEMA].[COLUMNS] AS [COLUMNS_1] \nWHERE [COLUMNS_1].[TABLE_NAME] = ? AND [COLUMNS_1].[TABLE_SCHEMA] = ?' (u'FruitsAndPets', u'dbo')

以及引擎中mysql+pyodbc的结束错误:

cursor.execute(statement, parameters)
sqlalchemy.exc.ProgrammingError: (ProgrammingError) ('42000', "[42000] [Microsoft][ODBC Microsoft Access Driver] Invalid SQL statement; expected 'DELETE', 'INSERT', 'PROCEDURE', 'SELECT', or 'UPDATE'. (-3500) (SQLExecDirectW)") "SHOW VARIABLES LIKE 'character_set%%'" ()

请注意，我不关心我是使用 sqlalchemy 还是 pandas to_sql() 我只是在寻找一些简单的方法来轻松地将数据框导入我的 MS Access 数据库。如果那是转储到 JSON，那么一个循环函数可以手动使用 SQL 插入行，无论如何，如果它运行良好，我会接受它。

Answer 1

对于那些仍在研究这个问题的人来说，基本上你不能在没有很大困难的情况下将 pandas to_sql 方法用于 MS Access。如果您决定这样做，这里有一个链接，有人修复了 sqlalchemy 的 Access 方言(并且大概 OP 的代码可以与这个引擎一起工作):

connecting sqlalchemy to MSAccess

将数据框导入 MS Access 的最佳方法是从记录构建 INSERT 语句，然后简单地通过 pyodbc 或 pypyodbc 连接并使用游标执行它们。您必须一次插入一个，如果您有大量数据，最好将其分成 block (大约 5000 个)。