Using python to select from persistent class INFORMATION.SCHEMA.TABLES fails
I have a simple python script to pull table information from cache 2018.1.2 which is on a windows 10 machine. I get no results though the same query in the management portal works correctly:
import os
import sys
import intersys.pythonbind3 as ipyb
import numpy as np
# Connect to the Cache' database
url = "localhost[1972]:%SYS"
user = "xxxx"
password = "zzzzz"
accessKey = (url, user, password)
def main(accessKey):
tbList = []
# Get the connection and database handles
conn = ipyb.connection()
conn.connect_now(accessKey[0],accessKey[1],accessKey[2], None)
database = ipyb.database(conn)
tHdl = ipyb.query(database)
sql = "SELECT Table_Type,Table_Schema,Table_Name,Owner FROM INFORMATION_SCHEMA.TABLES"
tHdl.prepare(sql)
tHdl.execute()
# grab 10 rows
for x in range(0,10):
print(tHdl.fetch([None]))
if __name__ == '__main__':
main( accessKey)
Trying to get the first 10 rows, I get 10 empty lists. Not sure what I am missing since the query executes correctly in the management portal. There are no errors
Comments
Any exceptions on stack?
import sys, traceback
traceback.format_exception(sys.last_type, sys.last_value, sys.last_traceback)Can you run SELECT 1 query?
I added the import of the traceback lib as you suggested. Putting the traceback statement after the execute or fetch caused an error stating that "last_type" does not exist which would I should see if no errors were found (as I understand it) . I changed the select to "select * ....." and that produced the error:
File "getTables.py", line 45, in main
tHdl.prepare(sql)
intersys.pythonbind3.cache_exception: file=intersys/pythonbind3.c line=3355 err=-1 message=cbind_prepare_gen_query()
an even more confusing error for which I have not found any info on the error condition.
I changed the the statement to "select count(*)...." that was a mistake. The python script never returned.
Yes, looks like no exception is thrown.
Try this query:
sql = "SELECT 1" "select 1" generates the same error:
tHdl.prepare(sql)
intersys.pythonbind3.cache_exception: file=intersys/pythonbind3.c line=3355 err=-1 message=cbind_prepare_gen_query()
What is more perplexing is if I create a python script using an ODBC connection instead of pythonbind3, the select does work. But I prefer not to use ODBC.
I am running Python 3.7.4 if that makes a difference.
That's a fairly recent version of Python. Not sure if old Python Binding works with it. I can recommend you to:
- Contact the WRC
- Upgrade to InterSystems IRIS which would allow you to use new Native API for Python
- Use PythonGateway
- Use PyODBC or JayDeBeAapi packages for data transfer
Eduard Lebedyuk; thank-you for your help.
Unfortunately, switching to IRIS is not an option as we are preparing to support clients who are not prepared to upgrade to IRIS; at least not yet. I will, though, look at PythonGateway.
Again, thank-you for the help.
Kevin McGinn
Hi Kevin,
Python 3.6.7 is the latest version that I have been able to successfully target for Cache. Listed below are my personal doc notes from the last time I reinstalled Cache and built the python binding (almost a year ago). All paths are specific to my cache install and my personal choices in how I configure / name Anaconda environments.
- Add cache and cache/bin to path
- Set up python binding
- This was a struggle….complicated by placing path additions for CACHE after the Conda environemts were set in .bashrc. Looked like they were there, but they weren't in the notebook. Place them before.
- When rebuilding python for Cache be in the /cache/dev/python directory sudo run full anaconda path python to setup python3 /home/xxx/anaconda3/envs/py367/bin/python setup3.py install
Note: no higher than 3.6.7 python at the moment so make sure anaconda env py367 is created: conda create -n py367 python=3.6.7 anaconda
TESTED SUCCESSFULLY!!!!!