Writing ADODB Recordset to Pivot Cache With Python

Question

I am working on a project where I am converting some VBA code to Python, in order to have Python interact with Excel in much the same way VBA would. In this particular case, I am utilizing the win32com library to have Python extract data from an Oracle Database via an ADODB Connection and write the resulting recordset directly to a pivot cache. I.e. creating a pivot table with data from an external source.

import win32com.client

Excel = win32com.client.gencache.EnsureDispatch('Excel.Application')
win32c = win32com.client.constants

# Create and Open Connection
conn = win32com.client.Dispatch(r'ADODB.Connection')
DSN = 'Provider=OraOLEDB.Oracle; Data Source=localhost:1521/XEPDB1; User Id=system; Password=password;'
conn.Open(DSN)

# Create Excel File
wb = Excel.Workbooks.Add()
Sheet1 = wb.Worksheets("Sheet1")

# Create Recordset
RS = win32com.client.Dispatch(r'ADODB.Recordset')
RS.Open('SELECT * FROM employees', conn, 1, 3)

# Create Pivot Cache
PivotCache = wb.PivotCaches().Create(SourceType=win32c.xlExternal, Version=win32c.xlPivotTableVersion15)

# Write Recordset to Pivot Cache
PivotCache.Recordset = RS # <~~ This is where it breaks!

# Create Pivot Table
Pivot = PivotCache.CreatePivotTable(TableDestination:=Sheet1.Cells(2, 2), TableName:='Python Test Pivot', DefaultVersion:=win32c.xlPivotTableVersion15)

# Close Connection
RS.Close()
conn.Close()

# View Excel
Excel.Visible = 1

I am successful in extracting the data via ADODB and creating an Excel file, but when I try to write the resulting recordset to the pivot cache by setting PivotCache.Recordset = RS, I get the following error.

[Running] venv\Scripts\python.exe "c:\Project\Test\debug_file_test.py"
Traceback (most recent call last):
  File "c:\Project\Test\debug_file_test.py", line 29, in <module>
    PivotCache.Recordset = RS # <~~ This is where it breaks!
  File "c:\Project\venv\lib\site-packages\win32com\client\__init__.py", line 482, in __setattr__
    self._oleobj_.Invoke(*(args + (value,) + defArgs))
pywintypes.com_error: (-2147352567, 'Exception occurred.', (0, None, 'No such interface supported\r\n', None, 0, -2146827284), None)

[Done] exited with code=1 in 0.674 seconds

Can anybody shed some light on what I am doing wrong?

Answer 1

I ended up finding a solution to the issue, and want to post an answer for anyone who may come across this question at some point.

Instead of creating the recordset by Recordset.Open() I tried using the command object and create the recordset by cmd.Execute(). As it turns out that Execute returns a tuple, I had to pass cmd.Execute()[0] to the recordset in order to make it work.

This doesn't answer why my initial code doesn't work, but it does provide an answer for how to write an ADODB recordset to a PivotCache with Python.

import win32com.client

#Initiate Excel Application
Excel = win32com.client.gencache.EnsureDispatch('Excel.Application')
win32c = win32com.client.constants

# Create and Open Connection
conn = win32com.client.Dispatch('ADODB.Connection')
cmd = win32com.client.Dispatch('ADODB.Command')
DSN = 'Provider=OraOLEDB.Oracle; Data Source=localhost:1521/XEPDB1; User Id=system; Password=password;'
conn.Open(DSN)

# Define Command Properties
cmd.ActiveConnection = conn
cmd.ActiveConnection.CursorLocation = win32c.adUseClient
cmd.CommandType = win32c.adCmdText
cmd.CommandText = 'SELECT * FROM employees'

# Create Excel File
wb = Excel.Workbooks.Add()
Sheet1 = wb.Worksheets("Sheet1")

# Create Recordset
RS = win32com.client.Dispatch('ADODB.Recordset')
RS = cmd.Execute()[0]

# Create Pivot Cache
PivotCache = wb.PivotCaches().Create(SourceType=win32c.xlExternal, Version=win32c.xlPivotTableVersion15)
PivotCache.Recordset = RS

# Create Pivot Table
Pivot = PivotCache.CreatePivotTable(TableDestination:=Sheet1.Cells(2, 2), TableName:='Python Test Pivot', DefaultVersion:=win32c.xlPivotTableVersion15)

# Close Connection
RS.Close()
conn.Close()

# View Excel
Excel.Visible = 1

Update

As hinted by @Parfait the code above also works if RS = cmd.Execute()[0] is replaced by

RS.Open(cmd)

Which I actually prefer because that secures alignment between the VB syntax and the Python syntax.