如何从 Excel>Python>Microsft Access 传输数据

Question

我已经为此工作好几天了。请有人提供任何您可以提供的建议。这是我当前的代码（它不起作用，我知道为什么它不起作用）：

import pyodbc

import openpyxl

path = ('C:\\Access_Test.xlsx')

wb = openpyxl.load_workbook(path)

sheet = wb.active

b2 = a2 = sheet['A2']

b2 = sheet['B2']

c2 = sheet['C2']

d2 = sheet['D2']

e2 = sheet['E2']

f2 = sheet['F2']

g2 = sheet['G2']

h2 = sheet['H2']

i2 = sheet['I2']

j2 = sheet['J2']

k2 = sheet['K2']

l2 = sheet['L2']

m2 = sheet['M2']

n2 = sheet['N2']

o2 = sheet['O2']

test2 = (")'")

test =  (a2.value, b2.value, c2.value, d2.value, e2.value, f2.value, g2.value, h2.value, i2.value, j2.value, k2.value, l2.value, m2.value),(test2)  

#Everything to this point is fine.  I can read & print everything from the Excel document (though the formatting is an issue with how the query statements work in pyodbc).

driver = '{Microsoft Access Driver(*.mdb, *accdb)}'

filepath = 'C:\\Users\\Db_Mngr\\Desktop\\PythonTests\\Microsoft Studio Projects\\HUD Report-2001-Copy For Python.mdb'

#Find data sources

myDataSources = pyodbc.dataSources()

access_driver = myDataSources['MS Access Database']

#This is the full command to open the Access database

cnxn = pyodbc.connect(driver=access_driver, dbq=filepath, autocommit=True)

crsr = cnxn.cursor()

crsr.execute(str(test))

如果我使用：

print:(test)

我的输出如下所示（此测试的所有数据都是假的）：

("'''INSERT INTO Python_Test([Case2], [Last], [First], [Initial Intake], [Intake], [Age], [Gender], [Ethnic], [Race], [DOB], [SSN], [Educ Lvl], [Marital])VALUES", ('Sep00000', 'Test', 'Test', '01/01/2020', '01/01/2020', 1, 'Male', 'A. Hispanic', 'E. White', '01/01/2020', 0, 'High School'), ")'")

正如你所看到的，对于 pyodbc 来说，这大约是 70% 正确的，但它显然会抛出错误（开头太多引号，“VALUES”后面的引号，“VALUES”后面的“”......等等，你明白我的意思）。有谁能够尝试解释如何使这段代码发挥作用吗？

删除开头的额外引号不一定是最大的问题，我想我可以解决这个问题；但“VALUES”部分之后发生的一切都是一团糟。

如有任何反馈，将不胜感激！

Answer 1

我猜你只需要正确格式化字符串即可成为有效的 SQL 查询。尝试这样的事情sql = f"INSERT INTO table([Case2], [Last], ...) VALUES ({a2.value}, {b2.value}, ...)"或者sql = ''.join(test)

Answer 2

这是工作代码！import pyodbcimport openpyxlpath = ('C:\\Users\\Db_Mngr\\Desktop\\PythonTests\\Microsoft Studio Projects\\Access_Test.xlsx') #Set the path to the Excel document that you want to transfer data fromwb = openpyxl.load_workbook(path)sheet = wb.activeb2 = sheet['B2']c2 = sheet['C2']d2 = sheet['D2']e2 = sheet['E2']f2 = sheet['F2']g2 = sheet['G2']h2 = sheet['H2']i2 = sheet['I2']j2 = sheet['J2']k2 = sheet['K2']l2 = sheet['L2']m2 = sheet['M2']n2 = sheet['N2']o2 = sheet['O2']#This is the trouble spot.  If you've ever worked with this stuff you know that the formatting has to be PERFECT.  A single space out of place throws errors. startcmmd = "'''INSERT INTO Python_Test([Case2], [Last], [First], [Initial Intake], [Intake], [Age], [Gender], [Ethnic], [Race], [DOB], [SSN], [Educ Lvl], [Marital])VALUES('{}', '{}', '{}', '{}', '{}', '{}', '{}', '{}', '{}', '{}', '{}', '{}', '{}')'''".format(b2.value, c2.value, d2.value, e2.value, f2.value, g2.value, h2.value, i2.value, j2.value, k2.value, l2.value, m2.value, n2.value)#Get connected to your Access documentdriver = '{Microsoft Access Driver(*.mdb, *accdb)}'filepath = 'C:\\Users\\Db_Mngr\\Desktop\\PythonTests\\Microsoft Studio Projects\\HUD Report-2001-Copy For Python.mdb'myDataSources = pyodbc.dataSources()access_driver = myDataSources['MS Access Database']#set up your cursor    cnxn = pyodbc.connect(driver=access_driver, dbq=filepath, autocommit=True)crsr = cnxn.cursor()#Now execute!  Don't forget to run this with eval!crsr.execute(eval(startcmmd))