getting error during describe a table in vectorwise using python ingresdbi module

Question

I am using python ingress module for connectivity with vectorwise database. For describe a table I am using the code below:

import ingresdbi
local_db =  ingresdbi.connect(database ='x',uid ='y',driver ='z',pwd ='p')
local_db_cursor = local_db.cursor()
local_db_cursor.execute('help tran_applog ; ' )

I am getting this error :

Syntax error.  Last symbol read was: 'help'."

Solutions will be appreciated. Thanks

Answer 1

The problem you've got is that 'help' isn't a real SQL statement that's understood by the DBMS server. It's really a terminal monitor command that gets converted into some queries against the system catalogs under the covers.

The alternative depends a little on what you're trying to get from the "describe table". The system catalogs relating to table and column information are iitables and iicolumns and you can do a select against them. Check the documentation or experiment.

Alternatively there appears to be a row descriptor you can get from ingresdbi, see the example here http://community.actian.com/wiki/Python_Row_Description

HTH

Answer 2

I believe you should do it like in any other shell script: echo "help tran_applog;" | sql mydatabase

Reason: "HELP" is not a standard SQL statement.

As suggested by PaulM, your best option to get metadata about tables is to query the system catalogs (iitables, iicolumns, iirelation, etc).

Start with something like:

SELECT C.column_name, C.column_datatype 
FROM iitables T, iicolumns C 
WHERE T.table_name = C.table_name 
  AND T.table_name = 'tran_applog';\g