python - 从 Python 附加到 Access 的 SQL 查询

Question

我正在尝试将 Excel 文件读取到 Python pandas 数据框中，然后将该数据框附加到预先存在的 Access 数据库中。

我一直收到以下错误:

Error('HYC00', '[HYC00] [Microsoft][ODBC Microsoft Access Driver]Optional feature not implemented (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:\Users\CraigG\Test\*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

不需要 Pandas 作为媒介。离开图书馆去数据科学! MS Access 与它的兄弟 MS Excel 配合得很好。只需在工作簿中查询一个没有任何循环的纯 SQL 调用。

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

import glob
import os
import pyodbc

# specify the folder that the files are sitting in 
list_of_files = glob.glob(r'C:\Users\CraigG\Test\*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:\Users\CraigG\Test\Db\Master.accdb;')    
cursor = conn.cursor()

cursor.execute(sql)
cursor.commit()

cursor.close()
conn.close()