在SQL Server中运行XP_CMDShell以运行Python脚本。 Xlwings部分投掷且访问被拒绝＆＃x27;错误
我已经坚持了一段时间，但是如果我做到这一点 - 没有什么。 我正在尝试使用XP_CMDShell使用以下代码运行SQL Server的Python脚本
EXEC sp_configure 'xp_cmdshell', 1
RECONFIGURE
GO
EXEC xp_cmdshell '<PythonInstallationFolderPath>\python.exe "<.py FilePath>"'
EXEC sp_configure 'xp_cmdshell', 0
RECONFIGURE
GO
。在Python脚本中，我有一个代码可以在Excel表上进行一些格式：
import xlwings as xw
# create an excel instance app object as a context manager to make sure files are closed safely
app = xw.App(visible = True)
# open workbook
excel_book = app.books.open(fr'<ExcelFilePath>\Dummy_Name.xlsx')
# fetch active sheet
ws = excel_book.sheets.active
# select Cell A1 and press Ctrl + A
tbl_range = ws.range("A1").expand('table')
# create table
ws.api.ListObjects.Add(1, ws.api.Range(tbl_range.address))
# save and close workbook and app object
excel_book.save()
excel_book.close()
app.quit()
当我从Python IDE运行时，此代码运行完美（例如Anaconda提示或VSCODE）。 但是，当我使用命令XP_CMDSHELL调用SQL Server的Python脚本时，如上所示，它失败了以下错误：
Traceback (most recent call last):
File "", line 80, in <module>
app = xw.App(visible = False)
File "C:\Anaconda\lib\site-packages\xlwings\main.py", line 212, in __init__
self.impl = xlplatform.App(spec=spec, add_book=add_book)
File "C:\Anaconda\lib\site-packages\xlwings\_xlwindows.py", line 296, in __init__
self._xl = COMRetryObjectWrapper(DispatchEx('Excel.Application'))
File "C:\Anaconda\lib\site-packages\win32com\client\__init__.py", line 113, in DispatchEx
dispatch = pythoncom.CoCreateInstanceEx(clsid, None, clsctx, serverInfo, (pythoncom.IID_IDispatch,))[0]
pywintypes.com_error: (-2147024891, 'Access is denied.', None, None)
我可以确定地说上述错误与上面的XLWINGS部分相关联。那是因为如果我删除了XLWINGS部分，则我的Python脚本中的其余代码可以正确运行。 从追溯中，我可以理解访问被拒绝。因此，我尝试了一些想法，例如使用xw.app（可见= false），而不是xw.app（cindible = true），并尝试使用上下文管理器运行。但是这些果酱工作。
我是XLWING的新手，也是Shell Commands的新手。我发现此错误与Pywin32库中的其他stackoverflow问题和此类答案有关： win32com代码不起作用iis infimorterror：no模块win32com.client.client
i Phosume xlwings库或xp_cmdshell do呼叫Pywin32库以执行上述操作。 我还发现该库是一个高级库，可以与Windows交谈，这向我暗示我很难进行调试。因此，我正在寻求您的专家帮助。 我已经陷入困境了一段时间，真的很喜欢探索探索或帮助我指出我在做错什么的方向。
I have been stuck with this for a while but if I get this right - nothing like it.
I am trying to run a python script from SQL Server using xp_cmdshell using the below code
EXEC sp_configure 'xp_cmdshell', 1
RECONFIGURE
GO
EXEC xp_cmdshell '<PythonInstallationFolderPath>\python.exe "<.py FilePath>"'
EXEC sp_configure 'xp_cmdshell', 0
RECONFIGURE
GO
The above code works fine - it runs the python script (I can say that it runs because the python script saves some data from me in an excel sheet). In the python script, I have a code to do some formatting on the excel sheet:
import xlwings as xw
# create an excel instance app object as a context manager to make sure files are closed safely
app = xw.App(visible = True)
# open workbook
excel_book = app.books.open(fr'<ExcelFilePath>\Dummy_Name.xlsx')
# fetch active sheet
ws = excel_book.sheets.active
# select Cell A1 and press Ctrl + A
tbl_range = ws.range("A1").expand('table')
# create table
ws.api.ListObjects.Add(1, ws.api.Range(tbl_range.address))
# save and close workbook and app object
excel_book.save()
excel_book.close()
app.quit()
This code runs perfect when I run it from a Python IDE (like anaconda prompt or VSCode).
However, when I call the python script from SQL Server using the command xp_cmdshell as shown above, it fails with the below error:
Traceback (most recent call last):
File "", line 80, in <module>
app = xw.App(visible = False)
File "C:\Anaconda\lib\site-packages\xlwings\main.py", line 212, in __init__
self.impl = xlplatform.App(spec=spec, add_book=add_book)
File "C:\Anaconda\lib\site-packages\xlwings\_xlwindows.py", line 296, in __init__
self._xl = COMRetryObjectWrapper(DispatchEx('Excel.Application'))
File "C:\Anaconda\lib\site-packages\win32com\client\__init__.py", line 113, in DispatchEx
dispatch = pythoncom.CoCreateInstanceEx(clsid, None, clsctx, serverInfo, (pythoncom.IID_IDispatch,))[0]
pywintypes.com_error: (-2147024891, 'Access is denied.', None, None)
I can with certainty say that the above error is associated with the xlwings section above. Thats because if I remove the xlwings section, the remaining code in my python script runs correctly.
From the traceback, I can understand that access is denied. Hence, I tried a few ideas like using xw.App(visible = False) instead of xw.App(visible = True) and trying to also run using context manager. But these dint work.
I am new to xlwings and also to shell commands. I found that this error has relation to pywin32 library from other stackoverflow question and answers like these:
Win32com codes not working on IIS
ImportError: No module named win32com.client
I presume xlwings library or xp_cmdshell does some sort of call to pywin32 library to perform the operations above.
I also found that this library is an advanced library for conversing with Windows which suggests to me that it will be difficult for me to debug. Hence, I am seeking your expert help.
I have been stuck with this for sometime now and would really appreciate a direction to explore towards or maybe help me pinpoint what I am doing wrong.
如果你对这篇内容有疑问，欢迎到本站社区发帖提问 参与讨论，获取更多帮助，或者扫码二维码加入 Web 技术交流群。
绑定邮箱获取回复消息
由于您还没有绑定你的真实邮箱，如果其他用户或者作者回复了您的评论，将不能在第一时间通知您！
发布评论
评论（1）
您上面提到的追溯表明，Anaconda被用于执行脚本。尝试使用本地python：
the traceback you mentioned above shows that it anaconda is being used to execute script. try using local python instead :