Table column names for SQL queries with JOINS

[cyrillay]

Hello,

In the ColumnDescription returned inside the cursor.description, it feels like there is some useful metadata missing.
Could we have the table from which the column is from ?

For instance, when doing :

cursor.execute("SELECT a.description AS foo, b.description AS bar FROM a LEFT JOIN b ON a.id=b.id")

cursor.description contains the column names, but not their alias. In this case, it would return ['description', 'description'], when one could expect ['foo', 'bar'] so it is impossible to differentiate ambiguous columns, to assign them to the right elements in the result.

I think this method in __init__.py is the one which needs enhancement :

def _set_description(self, column_metadata):
      # see https://www.postgresql.org/docs/9.5/datatype.html
      self.description = []
      for column in column_metadata:
          col_desc = ColumnDescription(name=column["name"], type_code=self._pg_type_map.get(column["typeName"].lower(), str))
          self.description.append(col_desc)

The column_metadata should contain a tableName field, according to the boto3 docs, see the response syntax in here. Could it be added to the ColumnDescription object ?

Would it be possible to implement this feature ? Is there another way I am missing ?

Thanks in advance !

[kislyuk]

Hi, the column description format is part of PEP 249: https://www.python.org/dev/peps/pep-0249/#description. That format is not extensible to accommodate the extra metadata that you want to see passed through from the response. We can add a separate cursor attribute to relay the full column metadata instead. I think I'll just call it cursor.column_metadata.

To your point about names vs. aliases, I'll need an example of what other PEP 249 drivers return in this field for your example query, to understand how to set the names to be more useful. Can you provide an example?

[cyrillay]

Hi kislyuk,

Thank your for your answer.

To get examples, I used the official python mysql connector.
It implements the DB API / PEP-249 specification.

Here are the results of my tests :

• With a join between two tables that have same column names, without any alias, cursor.column_names and cursor.description return multiple times the same names, without any way to differentiate them, like the current behaviour of this python aurora-data-api.

• But, with aliased columns in the query, cursor.column_names and cursor.description return the aliased name, unlike the current behaviour

• I also noticed a feature of the mysql connector, to have a cursor which directly returns each row as a dictionary with the column names. This would be super useful in my opinion, and this is what is done in another JS-based connector, discussed in this this thread. Currently the dict creation has to be done manually by the end user.

Here is my test suite :

$ mysqld
$ mysql -u root
> USE mysql;
> CREATE TABLE animals(id VARCHAR(20), data VARCHAR(10));
> CREATE TABLE animals_specs(id VARCHAR(20), data VARCHAR(10));

$ .../3.7.2/bin/python -m venv env
$ . ./env/bin/activate
$ pip install mysql-connector-python
$ python3 test-suite.py

import mysql.connector

cnx = mysql.connector.connect(user='root', database='mysql')
cursor = cnx.cursor()

query = ("SELECT id, data FROM animals")
cursor.execute(query)
print(f"Column names without alias nor joins : {cursor.column_names} | {cursor.description}")
cursor.reset()

query = ("SELECT id AS foo, data AS bar FROM animals")
cursor.execute(query)
print(f"Column names with aliases and no joins : {cursor.column_names} | {cursor.description}")
cursor.reset()

query = ("SELECT * FROM animals LEFT JOIN animals_specs ON animals.id = animals_specs.id")
cursor.execute(query)
print(f"Column names without aliases but with joins : {cursor.column_names} | {cursor.description}")
cursor.reset()

query = ("SELECT animals.id, animals_specs.id FROM animals LEFT JOIN animals_specs ON animals.id = animals_specs.id")
cursor.execute(query)
print(f"Column names without aliases but with joins : {cursor.column_names} | {cursor.description}")
cursor.reset()

query = ("SELECT animals.id AS foo, animals_specs.id AS bar FROM animals LEFT JOIN animals_specs ON animals.id = animals_specs.id")
cursor.execute(query)
print(f"Column names with aliases and joins : {cursor.column_names} | {cursor.description}")
cursor.reset()

cnx.close()

What do you think ?
Could we considerate the dict cursor as a feature ?
Does returning the aliased names now makes sense ?

[dthauvin]

Hello , could be very usefull .

[weallwegot]

Could we considerate the dict cursor as a feature ?

Also a big fan of the dict cursor and/or NamedTupleCursor like we see available in the psycopg2 cursor_factory keyword argument

I prototyped an implementation for simple queries here:

from collections import namedtuple
with aurora_data_api.connect(rds_data_client=rds_client, aurora_cluster_arn=db_cluster_arn, secret_arn=db_credentials_secrets_store_arn, database=database_name) as conn:
    with conn.cursor() as cursor:
        cursor.execute(sql)
        response = cursor.fetchall()
# dynamically create namedtuple based on column names
record_nt = namedtuple('Record',' '.join([x.name for x in cursor.description]))
result_nts = []
for row in response:
    result_nts.append(record_nt(*row))
return result_nts

[guneshsji]

@weallwegot based on your approach, I created my own to map column names and array of record tuples to form a dictionary that can be passed down as response from server.

conn = aurora_data_api.connect(aurora_cluster_arn, secret_arn, database) 

with conn: 
    with conn.cursor() as cursor:
        cursor.execute("Select * from Category")
        record_arr_of_tuples = cursor.fetchall()
        record_col_name_arr = [x.name for x in cursor.description]
        
        response = fetch_records(record_col_name_arr, record_arr_of_tuples)
        
        return {
            'statusCode': 200,
            'headers': {'Content-Type': 'application/json'},
            'body': json.dumps({'status': 200, 'body': response})
        }

def fetch_records(record_col, record_arr):
    res = [dict(zip(k,v)) for k, v in [((*record_col,),v) for v in record_arr]]
    
    return res