Search code examples
pythonsqljaydebeapi

jaydebeapi Getting column alias names

Is there a way to return the aliased column names from a sql query returned from JayDeBeApi?

For example, I have the following query:

sql = """ SELECT visitorid AS id_alias FROM table LIMIT 1 """

I then run the following (connect_to_vdm() establishes a connection to my DB): 

curs = connect_to_vdm().cursor()
curs.execute(sql)
vals = curs.fetchall()

I normally retrieve column names like so:

desc = curs.description
column_names = [col[0] for col in desc]

This returns the original column name "visitorid" and not the alias specified in the query "id_alias".

I know I could swap the names for the value in Python, but hoping to be able to have this done within the query since it is already defined in the Select statement. This behaves as expected in a SQL client, but I cannot seem to get the Aliases to return when using python/JayDeBeApi. Is there a way to do this using JayDeBeApi?

EDIT: I have discovered that structuring my query with a CTE seems to help fix the problem, but still wondering if there is a more straightforward solution out there. Here is how I rewrote the same query:

sql = """ WITH cte (id_alias) AS (SELECT visitorid AS id_alias FROM table LIMIT 1) SELECT id_alias from cte"""
Solution

  • I was able to fix this using a CTE (Common Table Expression)

    sql = """ WITH cte (id_alias) AS (SELECT visitorid AS id_alias FROM table LIMIT 1) SELECT id_alias from cte"""