我面臨以下問題：在我們公司，我們使用的軟體的 GUI 是用 MS Access/VBA 編程的。現在應該將部分業務邏輯移至 Python，但仍應保留 MS Access 部分。現在實作并作業了以下場景：用戶在 Access 中鍵入一個字串，該字串在 VBA 中讀出，并通過命令列呼叫 Python 腳本并將該字串作為命令列引數提供給腳本. Python 反過來連接到供應商的資料庫，使用傳遞的字串作為引數，并將結果存盤在我們的 MS SQL 資料庫中。供應商為其資料庫提供了 Python API，因此通過 Python 進行了必要的中間步驟。這種情況每天發生幾次，每次啟動腳本或解釋器大約需要 3 秒。這需要太長時間。以下是不需要的：
Sub CallPython()
Dim PythonExe As String, PythonScript As String, PythonArgs As String, PythonOutput As String
Dim PythonCommand As String
Dim objShell As Object
PythonExe = """C:\Program Files\Python37\python.exe"""
PythonScript = """[path_to_our_script]\insert_article.py"""
PythonArgs = "-id 123456"
Set objShell = VBA.CreateObject("Wscript.Shell")
PythonCommand = PythonExe & " " & PythonScript & " " & PythonArgs
'MsgBox PythonCommand
objShell.Run PythonCommand
End Sub
我看過以下關于IPC技術的頁面，但我在這方面沒有太多經驗，所以我不能說太多復雜性。有沒有人有上述場景的經驗并且可以分享他們對更智能解決方案的知識？
uj5u.com熱心網友回復：
這個主題確實非常廣泛和復雜。
我自己通過命名管道使用 R 和 Access 之間的雙向直接通信完成了這項作業，這些命名管道的處理方式與 Python（或 R）端的檔案非常相似。但是，Access 端需要許多 API 宣告來設定管道，就我而言，查看進度，這樣我們就可以異步報告進度而不會鎖定應用程式。
命名管道的基礎知識可以在這里找到：
https://docs.microsoft.com/en-us/windows/win32/ipc/multithreaded-pipe-server
和這里：
https://docs.microsoft.com/en-us/windows/win32/ipc/named-pipe-client
如果只有一個客戶端（您的 Python 應用程式），命名管道服務器可以是單執行緒的，因此您可以忽略大多數多執行緒內容。
我在 VBA 中需要的宣告是：
Private Type SECURITY_ATTRIBUTES
nLength As Long
lpSecurityDescriptor As LongPtr
bInheritHandle As Long
End Type
Private Type PROCESS_INFORMATION
hProcess As LongPtr
hThread As LongPtr
dwProcessId As Long
dwThreadId As Long
End Type
Private Type STARTUPINFO
cb As Long
lpReserved As LongPtr
lpDesktop As LongPtr
lpTitle As LongPtr
dwX As Long
dwY As Long
dwXSize As Long
dwYSize As Long
dwXCountChars As Long
dwYCountChars As Long
dwFillAttribute As Long
dwFlags As Long
wShowWindow As Integer
cbReserved2 As Integer
lpReserved2 As LongPtr
hStdInput As LongPtr
hStdOutput As LongPtr
hStdError As LongPtr
End Type
Private Const STARTF_USESHOWWINDOW As Long = &H1
Private Const STARTF_USESTDHANDLES As Long = &H100
Private Const SW_HIDE As Long = 0&
Private Const ERROR_SUCCESS As Long = 0
Private Const STILL_ACTIVE As Long = 259
Private Const PIPE_TYPE_BYTE As Long = 0
Private Const PIPE_ACCESS_INBOUND = 1
Private Const PIPE_ACCESS_OUTBOUND = 2
Private Const PIPE_ACCESS_DUPLEX As Long = 3
Private Const PIPE_WAIT As Long = 0
Private Const PIPE_NOWAIT As Long = 1
Private Const PIPE_ACCEPT_REMOTE_CLIENTS As Long = 0
Private Const ERROR_PIPE_CONNECTED = 535
Private Const ERROR_PIPE_LISTENING = 536
Private Declare PtrSafe Function CreatePipe Lib "kernel32" (ByRef hReadPipe As LongPtr, ByRef hWritePipe As LongPtr, ByVal lpPipeAttributes As LongPtr, ByVal nSize As Long) As Long
Private Declare PtrSafe Function CreateProcess Lib "kernel32" Alias "CreateProcessW" (ByVal lpApplicationName As LongPtr, ByVal lpCommandLine As LongPtr, ByVal lpProcessAttributes As LongPtr, ByVal lpThreadAttributes As LongPtr, ByVal bInheritHandles As Long, ByVal dwCreationFlags As Long, ByVal lpEnvironment As LongPtr, ByVal lpCurrentDirectory As LongPtr, lpStartupInfo As STARTUPINFO, lpProcessInformation As PROCESS_INFORMATION) As Long
Private Declare PtrSafe Function ReadFile Lib "kernel32" (ByVal hFile As LongPtr, lpBuffer As Any, ByVal nNumberOfBytesToRead As Long, lpNumberOfBytesRead As Long, ByVal lpOverlapped As LongPtr) As Long
Private Declare PtrSafe Function WriteFile Lib "kernel32" (ByVal hFile As LongPtr, lpBuffer As Any, ByVal nNumberOfBytesToWrite As Long, ByRef nNumberOfBytesWritten As Long, ByVal lpOverlapped As LongPtr) As Long
Private Declare PtrSafe Function FlushFileBuffers Lib "kernel32" (ByVal hFile As LongPtr) As Long
Private Declare PtrSafe Function CloseHandle Lib "kernel32" (ByVal hObject As LongPtr) As Long
Private Declare PtrSafe Function GetExitCodeProcess Lib "kernel32" (ByVal hProcess As LongPtr, ByRef lpExitCode As Long) As Long
Private Declare PtrSafe Function TerminateProcess Lib "kernel32" (ByVal hProcess As LongPtr, ByVal uExitCode As Long) As Long
Private Declare PtrSafe Function CreateNamedPipeW Lib "kernel32" (ByVal lpName As LongPtr, ByVal dwOpenMode As Long, ByVal dwPipeMode As Long, ByVal nMaxInstances As Long, ByVal nOutBufferSize As Long, ByVal nInBufferSize As Long, ByVal nDefaultTimeOut As Long, lpSecurityAttributes As Any) As LongPtr
Private Declare PtrSafe Function ConnectNamedPipe Lib "kernel32" (ByVal hNamedPipe As LongPtr, lpOverlapped As Any) As Long
Private Declare PtrSafe Function DisconnectNamedPipe Lib "kernel32" (ByVal hNamedPipe As LongPtr) As Long
Private Declare PtrSafe Function PeekNamedPipe Lib "kernel32" (ByVal hNamedPipe As LongPtr, lpBuffer As Any, ByVal nBufferSize As Long, ByRef lpBytesRead As Long, ByRef lpTotalBytesAvail As Long, ByRef lpBytesLeftThisMessage As Long) As Long
VBA 部分的基本內容是：
CreateNamedPipeW通過with創建一個命名管道PIPE_ACCESS_OUTBOUND（或者兩個，如果你想要輸入和輸出，一進一出）- 生成一個監聽器行程（你的 Python 行程），
CreateProcess這樣你就可以得到它的 ID - 發送命令時，通過檢查行程是否處于活動狀態
GetExitCode，通過連接到管道ConnectNamedPipe，使用寫入管道WriteFile，然后FlushFileBuffers釋放檔案句柄CloseHandle，然后使用斷開與管道的連接DisconnectNamedPipe
在 Python 行程中，在一個回圈中，通過打開管道open，讀取并處理訊息，然后再次通過打開管道open。open應該停止，直到發送下一條訊息。
如果您想使用回傳訊息來獲取進度，請確保PeekNamedPipe在 Python 運行緩慢或遇到錯誤時不要停止您的 Access 應用程式。
您可能希望將所有這些包裝在一個預先宣告的自我修復 VBA 類中，以在 VBA 處于活動狀態時保持您的 Python 程式處于活動狀態，而不必等待 Python 啟動/讀取您的程式等，所有這些都是不平凡的。
最后，只使用本地 http 會簡單得多，因為您可以使用預先存在的工具來發送和接收 http 請求。但是可以在 VBA 和 Python/R/任何可以讀取命名管道（= 讀取檔案）的編程語言之間進行直接 IPC。
不幸的是，這是我所能帶給你的。真的，重新考慮“不是網路服務器”。如果行程在同一臺機器上，則可以對網路服務器進行防火墻保護，使用網路服務器比命名管道有更多開銷，但要容易得多。我有點希望我走那條路。
轉載請註明出處，本文鏈接：https://www.uj5u.com/houduan/478487.html