Dictionary Responses

[hauserkristen]

Connectors for other DBs, MySQL and Postgres for examples, allow a parameter within the cursor or connection so the response to a query can be returned as a dictionary of column names and values.

I cannot currently find this functionality within redshift_connector but it exists in psycopg2 for Redshift connections. Is there a plan to implement something like that or does it currently exist and I am missing it?

Thanks!

[Brooke-white]

Hi @hauserkristen , thank you for reaching out. I will chat with the driver team about adding this feature and provide an update shortly :)

[Brooke-white]

Hi @hauserkristen ,

This feature isn't something we plan on adding at this time, but I've included a snippit below showing how the description attribute on our Cursor class can be used with the existing fetchall() method to transform the list to a dictionary. Please let me know if you have any questions.

        cursor.execute('''SELECT 1 as col1, 2 as col2, 3 as col3
                        UNION ALL SELECT 4 as col1, 5 as col2, 6 as col3
                        UNION ALL SELECT 7 as col1, 8 as col2, 9 as col3
                        UNION ALL SELECT 10 as col1, 11 as col2, 12 as col3
                        ''')
        results = cursor.fetchall()
        print(results)
        print(cursor.description)
        print({cursor.description[cidx][0]:data for cidx, data in enumerate(zip(*results))})

([1, 2, 3], [4, 5, 6], [7, 8, 9], [10, 11, 12])
[(b'col1', 23, None, None, None, None, None), (b'col2', 23, None, None, None, None, None), (b'col3', 23, None, None, None, None, None)]
{b'col1': (1, 4, 7, 10), b'col2': (2, 5, 8, 11), b'col3': (3, 6, 9, 12)}

[gmcrocetti]

Hi @Brooke-white,
Even though the team is not interested in adding it, would you be willing to review a submission ?
A row_factory parameter on the Cursor class and a DictCursor subclass should suffice and mimics psycopg's interface.
ps. I'm willing to tackle it..

[Brooke-white]

Hey @gmcrocetti , I'm more than happy to review a submission :) thank you for offering to take this on!

[gmcrocetti]

Hey Brooke, it seems it won't be as simple as initially thought.
For the time being psycopg2 is a better fit for me but nonetheless I'm willing to contribute.
I'm in a hurry now but will try to take a look on how the binary protocol would integrate with a ¨RowBuilder class"... If you have ideas, let me know here or thorough another channel of choice.

[Brooke-white]

Hey @gmcrocetti , sounds good. I will update here with any findings I have, though I don't anticipate binary protocol would require any different handling for Cursor related code, given the code pertaining to it is focused around (1) when a connection to redshift is first established (2) what transformation is applied when data is read from the server. By the time all data has been read from the server, (1) and (2) have already happened.

When you have time, could you expand more on your thoughts about the RowBuilder class + row_factory parameter?

[Fredericco72]

Hi everyone, unrelated to this thread I have been looking at a lightweight solution to this problem, I've also been missing the functionality of DictCursor since transitioning to the Redshift Connector. One solution I have found is with using named tuples. Although it has just occurred to me that column names with spaces will cause errors... (all of our data tables use snake case, so it hasn't been an issue)

My implementation is very light touch:

Add

self._row_tuple: typing.Optional[typing.Callable] = None

to the __init__ statement of the Cursor object. Then add

self._row_tuple = namedtuple("Row", [column[0].lower() for column in self.description])

to the execute method of the Cursor object, just after self._c.execute(self, operation, args)
and finally update the __next__ method with

return self._row_tuple(*self._cached_rows.popleft())

I looked at adding the named tuple logic to core.Cursor.handle_DATA_ROW, but I couldn't figure out a nice place to put the named tuple creation logic, as it relies on the cursor.description.

Unfortunately this change does not work for everybody since it relies on column names conforming to the restrictions of namedtuple names. I guess if we cached the row names (or even cursor.description), then for dictionary rows we could have in the __next__ method:

return {desc[0]: column for desc, column in zip(cursor.description, self._cached_rows.popleft())

Then have some kind of flag that allows you to swap between rows as lists and rows as dictionaries?

The ideal solution would be to allow all these accessors:

for row in cursor:
    row[0] # By index
    row["column_1"] # By key
    row.column_1 # By attribute

This solution would not be a breaking change as all current users have to use the index notation, but it also allows users to access data similarly to DictCursor and how you access data in Pandas.