My goal is to display SQL queries via the Python console using the Win32com module. I am able to use a comobject to access and successfully display the fields of a SQL query however when i try to display rows i am falling into Exceptions.
Background
I have used PYODBC which works great however there is a limitation based off what SQL providers are installed and whether TLS 1.2 is enforced. The software sometimes is installed to an external SQL server and therefore no provider on the software's server that can always establish a connection. This is why i am now using the kernal of the software via com objects to access the DB as this circumvents the pitfalls of the provider needing to be installed or the latest Windows update to allow TLS 1.2 connections etc.
Win32com Code that works for me using Fields
import win32com.client
import win32com
"""Connection"""
objprox = win32com.client.Dispatch("****.DbObjectProxy") #Blanked out for security of Software
"""Set Query"""
sql1 = "select * from ServiceConsumer_t"
dbq1 = objprox.DoDatabaseQuery(sql1)
dbq1.Open(sql1)
"""Specify & Print result"""
while not dbq1.EOF:
col1 = dbq1.Fields("ID").Value
dbq1.MoveNext()
print(col1)
"""Close Session"""
dbq1.Close()
Output of the above is:
{9CAFD41E-D322-4234-BF80-CF6E11A724A0}
{CE4AAE72-0889-41E8-BDB2-ED96696DDB91}
{DC18008F-2C84-4EB4-BCCB-D94FF96E0564}
{1AAB143C-8393-4C1E-BE94-7AB44788D4E4}
This is correct as I am specifying the ID column to output and using MoveNext() to iterate. This shows I am close to my goal, however, the below code for displaying the rows never appears to work, I am now lost on why?
Win32com Code that does not work for me to display rows:
import win32com.client
import win32com
"""Connection"""
objprox = win32com.client.Dispatch("*****.DbObjectProxy") #Blanked out for security of Software
"""Set Query"""
sql2 = "select * from ServiceConsumer_t"
dbq2 = objprox.DoDatabaseQuery(sql2)
dbq2.Open(sql2)
"""Specify & Print result"""
while not dbq2.EOF:
dbq2.MoveFirst()
res = dbq2.GetRows()
dbq2.MoveNext()
print(res)
"""Close Session"""
dbq2.Close()
From this, I simply get the exception that the object GetRows has no attribute. Looking online there is very little surrounding this. Please can you suggest why the code is not working for displaying all row results? Ideally, I would like the column names displayed too.