Column name duplicates unexpected behaviour

[philiprekers]

Using fetch_all() with a JOIN in the query, resulting in a result containing the same column name twice, shows only one of both resulting columns in output dictionary.

Example:

Two tables contain a column called name.
We join both tables and the result table now has two columns called name.
With fetch_all() we only get one result set for a column called name (dictionary has unique keys).

Possible fixes:

• Throw a warning when the result set has a duplicate column name
• Rename columns in output dictionary when the result set has a duplicate column name
• If possible use table alias for each of the repeated column names as prefix in output dictionary key, separated by underscore

[severinsimmler]

What do you think is the expected behavior, @benedikt-budig? I'd say just printing a warning, because it's the user's error if he doesn't rename the column in the SQL query. Not sure yet how SQLAlchemy handles this.

[severinsimmler]

Could you please provide an example of the JOIN you do where this happens, @Philipduerholt?

[severinsimmler]

Something likes this?

from databank import Database

db = Database("sqlite:///test.db")

db.execute("CREATE TABLE a (id INTEGER PRIMARY KEY, name TEXT);")
db.execute("CREATE TABLE b (id INTEGER PRIMARY KEY, name TEXT);")
db.execute("INSERT INTO a (id, name) VALUES (0, 'foo');")
db.execute("INSERT INTO b (id, name) VALUES (0, 'bar');")

If you do now:

>>> db.fetch_one("SELECT a.name, b.name FROM a JOIN b on a.id = b.id;")
{'name': 'bar'}

[severinsimmler]

SQLAlchemy returns a tuple ("foo", "bar"), but we transform it to a dictionary with the same key.

[severinsimmler]

Closing this as this is actually expected behavior. If there are duplicate names, one has to do something like:

>>> db.fetch_one("SELECT a.name AS a_name, b.name AS b_name FROM a JOIN b on a.id = b.id;")
{'a_name': 'foo', 'b_name': 'bar'}