Cursor not returning column alias

[cah-jeffgraham]

I'm not receiving the alias 'my_name' for the 'name' column in the following query:

select '123' as foo, name as my_name from table

Oddly enough I am receiving the 'foo' alias for '123'. However, I can force the my_name alias by using a function:

select '123' as foo, replace(name,'^','^') as my_name from table

This is the function I'm using to fetch from the cursor into a dictionary:

def db_extract(cnn, file, patient):
    with open(file) as f:
        sql = f.read()

    events = []
    csr = cnn.cursor()
    try:
        csr.execute(sql)
        columns = tuple([d[0] for d in csr.description])
        for row in csr.fetchall():
            events.append(dict(zip(columns, row)))
    except Exception as e:
        print(e)
        exit()
    return events

I'm connecting to a MemSQL via mysql-connector-java-8.0.12.jar with TSL.

I also found a similar issue on SE, but the resolution is weak.

There are several hacks I can do to work around the issue, but found it odd and unappealing to change my SQL.

[Vadus]

one simple workaround is to wrap your SQL with another select * from (<your-SQL>);

[georg90]

For some reason this is not working for me..

sql = ("select * from (SELECT SOME_COL AS PrettyColumn from XY)")

I am on DB2 for z/OS if that matters

[pybokeh]

I am having the same problem and doing @Vadus ' workaround does not work for me. I'm querying against DB2 AIX LUW. This is a real bummer since I want to migrate away from pyodbc so that I can use 64-bit Python instead of 32-bit Python since the ODBC data sources I am working with are registered with 32-bit drivers. Unless there is a workaround, looks like I have to go back to using pyodbc and maintain both 32-bit and 64-bit Python environment. 👎

EDIT: Using JDBC 4.0 Driver (db2jcc4.jar) for DB2 version 10.1 from IBM's site.

[pybokeh]

Found a fix for me! I was lucky with my Google-Fu. The fix for me was to simply append ":useJDBC4ColumnNameAndLabelSemantics=false;" right after your database name in the connection string. For example:

conn = jdba.connect('com.ibm.db2.jcc.DB2Driver',
'jdbc:db2://host:port/my_database:useJDBC4ColumnNameAndLabelSemantics=false;', 
[user, pwd], 
jars=['D:/path_to/db2jcc4.jar'])

[els-pnw]

Try adding an AS statement at the end, this worked for me on our JDV environment.

SELECT * FROM (SELECT stuff FROM things) AS A