为了账号安全,请及时绑定邮箱和手机立即绑定

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

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

犯罪嫌疑人X 2023-08-22 14:39:39
我已经为此工作好几天了。请有人提供任何您可以提供的建议。这是我当前的代码(它不起作用,我知道为什么它不起作用):import pyodbcimport openpyxlpath = ('C:\\Access_Test.xlsx')wb = openpyxl.load_workbook(path)sheet = wb.activeb2 = 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 sourcesmyDataSources = pyodbc.dataSources()access_driver = myDataSources['MS Access Database']#This is the full command to open the Access databasecnxn = 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”部分之后发生的一切都是一团糟。如有任何反馈,将不胜感激!
查看完整描述

2 回答

?
慕姐8265434

TA贡献1813条经验 获得超2个赞

我猜你只需要正确格式化字符串即可成为有效的 SQL 查询。尝试这样的事情

sql = f"INSERT INTO table([Case2], [Last], ...) VALUES ({a2.value}, {b2.value}, ...)"

或者

sql = ''.join(test)


查看完整回答
反对 回复 2023-08-22
?
潇潇雨雨

TA贡献1833条经验 获得超4个赞

这是工作代码!


import pyodbc


import openpyxl


path = ('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 from

wb = openpyxl.load_workbook(path)

sheet = wb.active



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']


#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 document


driver = '{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))



查看完整回答
反对 回复 2023-08-22
  • 2 回答
  • 0 关注
  • 1589 浏览
慕课专栏
更多

添加回答

举报

0/150
提交
取消
意见反馈 帮助中心 APP下载
官方微信