从 Python 追加到 Access 的 SQL 查询

Question

我正在尝试将Excel文件读入Python pandas数据帧，然后将数据帧附加到预先存在的访问数据库中。

我收到以下错误：

错误("HYC00

"， "[HYC00] [Microsoft][ODBC Microsoft 访问 驱动程序]可选功能未实现 (106( (SQLBindParameter('(

我读到此错误通常是 pyodbc 4.0.25 的结果。 我已将我的版本降级到 pyodbc 4.0.24，但仍然收到错误。

import glob
import os
import pandas as pd
import time
import pyodbc
# specify the folder that the files are sitting in 
list_of_files = glob.glob(r'C:UsersCraigGTest*xlsx') 
# define the newest file in the folder
filename = min(list_of_files, key=os.path.getmtime)
# put excel file in data frame
df = pd.read_excel(filename)
print(pyodbc.version)
conn = pyodbc.connect(r'DRIVER={Microsoft Access Driver (*.mdb, *.accdb)};' +
                      r'DBQ=C:\Users\CraigG\Test\Db\Master.accdb;')
cursor = conn.cursor()
for index, row in df.iterrows():
   AccountNum = row["Account #"]
   CustomerNum = row["Customer #"]
   CustomerName = row["Customer Name"]
   AccountName = row["Account Name"]
   SalesRegisterNum = row["Sales Register #"]
   InvoiceDate = row["Invoice Date"]
   BillingMonthDate = row["BillingMonthDate"]
   WrittenBy = row["Written By"]
   Mfr = row["Mfr"]
   CatalogNo = row["CatalogNo"]
   LineType = row["LineType"]
   UPC = row["UPC"]
   QTYShip = row["QTY Ship"] 
   Price = row["Price"]
   PriceUOM = row["Price UOM"]
   ExtenedPrice = row["Extened Price"]
   Cost = row["Cost"]
   CostUOM = row["Cost UOM"]
   ExtendedCost = row["Extended Cost"] 
   SPACost = row["SPA Cost"]
   SPACostUOM = row["SPA Cost UOM"]
   ExtendedSPACost = row["Extended SPA Cost"]
   PCNum = row["PC #"]
   sql = "INSERT INTO Master ([Account #], [Customer #], [Customer Name], [Account Name], [Sales Register #], [Invoice Date], [BillingMonthDate], [Written By], [Mfr], [CatalogNo], [LineType], [UPC], [QTY Ship], [Price], [Price UOM], [Extened Price], [Cost], [Cost UOM], [Extended Cost], [SPA Cost], [SPA Cost UOM], [Extended SPA Cost], [PC #]) VALUES(?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?)"
   params = (AccountNum, CustomerNum, CustomerName, AccountName, 
             SalesRegisterNum, InvoiceDate, BillingMonthDate, WrittenBy, Mfr, 
             CatalogNo, LineType, UPC, QTYShip, Price, PriceUOM, ExtenedPrice, Cost, 
             CostUOM, ExtendedCost, SPACost, SPACostUOM, ExtendedSPACost, PCNum)
   cursor.execute(sql, params)
   cursor.commit()

Answer 1

不需要熊猫作为媒介。离开图书馆进行数据科学！MS Access与其兄弟MS Excel配合得很好。只需在工作簿中查询纯 SQL 调用，无需任何循环。

下面假设 Excel 数据与名为 Sheet1 的工作表中的单元格A1开始的 Access 表保持相同的列名。根据需要调整FROM子句。

import glob
import os
import pyodbc
# specify the folder that the files are sitting in 
list_of_files = glob.glob(r'C:UsersCraigGTest*xlsx') 
# define the newest file in the folder
filename = min(list_of_files, key=os.path.getmtime)
sql = r"""INSERT INTO Master ([Account #], [Customer #], [Customer Name], [Account Name], [Sales Register #], 
                              [Invoice Date], [BillingMonthDate], [Written By], [Mfr], [CatalogNo], [LineType], 
                              [UPC], [QTY Ship], [Price], [Price UOM], [Extened Price], [Cost], [Cost UOM], 
                              [Extended Cost], [SPA Cost], [SPA Cost UOM], [Extended SPA Cost], [PC #]) 
          SELECT e.[Account #], e.[Customer #], e.[Customer Name], e.[Account Name], e.[Sales Register #], 
                 e.[Invoice Date], e.[BillingMonthDate], e.[Written By], e.[Mfr], e.[CatalogNo], e.[LineType], 
                 e.[UPC], e.[QTY Ship], e.[Price], e.[Price UOM], e.[Extened Price], e.[Cost], e.[Cost UOM], 
                 e.[Extended Cost], e.[SPA Cost], e.[SPA Cost UOM], e.[Extended SPA Cost], e.[PC #]
          FROM [Excel 12.0 Xml;HDR=Yes;Database={xl}].[Sheet1$] AS e;
       """.format(xl=filename)
conn = pyodbc.connect(r'DRIVER={Microsoft Access Driver (*.mdb, *.accdb)};' +
                      r'DBQ=C:UsersCraigGTestDbMaster.accdb;')    
cursor = conn.cursor()
cursor.execute(sql)
cursor.commit()
cursor.close()
conn.close()