Retrieve IBM i column headings from SQL using pyodbc

I want to launch SQL querys from a PC to a DB2-database on IBM i. The files were created with the old method (source file, 10-character's file and field name, and detailled column-heading) In SQL from the emulator, I have an option to retrieve the column headings with the long name (Preferences / Results / column headings), so I think the jdbc driver can export them (I think it is allowed when checking Edition / JDBC Configuration/ Other / Extended metadata).

I can't retrieve this long name column heading when using pyodbc from python, using the driver "iSeries Access ODBC Driver". I don't see where tohave it, I searched in https://www.ibm.com/docs/en/i/7.1?topic=apis-connection-string-keywords the right option for the connection string, but did'nt find anything. The 'description' cursor attribute in pyodbc does retrieve the column name and length, but no extended attribute

Is this not possible at all to retrieve the long name? Is this option accessible in the jdbc driver?

Solution

Here is a simple working example of what I needed, based on nfgl's comments

# With JayDeBeApi :
#   pip install JayDeBeApi
# And jdbc Driver :
#   JTOPEN in https://sourceforge.net/projects/jt400/
#          Copy the file jt400.jar in this directory

import os
import jaydebeapi


def run_sql(sql):

    # Create a jdbc connexion
    # Connexion string  https://www.ibm.com/docs/en/i/7.5?topic=details-connection-string-keywords           
    system   = 'MYAS400'
    username = 'MYNAME'
    password = 'MYPSW'
    connection_string='jdbc:as400://' + system
    driver_file_path = os.path.join(os.path.dirname(__file__) , "jt400.jar")
    cnxn = jaydebeapi.connect("com.ibm.as400.access.AS400JDBCDriver",
                          connection_string,
                          {'user':username, 'password':password, 'extended metadata': 'true'},
                          driver_file_path )
    with cnxn:
        # Create a cursor
        cursor = cnxn.cursor()
        cursor.execute(sql)

        if cursor.description != None:
            header = tuple((cursor._meta.getColumnLabel(i+1)) for i in range(len(cursor.description)))
            print(header)
            print ('*' * 100)
            result = cursor.fetchone()
            while result != None:
                print(result)
                result = cursor.fetchone()


def main():

    run_sql("SELECT x, y, z, 'A' as \"New Column\" FROM mylib.mytable WHERE thefield = 'OK'" )


if __name__ == "__main__":
    main()