Mailing List Archive: [issue5105] sqlite3.Row class, handling duplicate column names resulting from a SQL join

[issue5105] sqlite3.Row class, handling duplicate column names resulting from a SQL join

Jan 29, 2009, 3:53 PM

Post #1 of 5 (526 views)

New submission from Brandon Adams <sockonafish@gmail.com>:

When using sqlite3.Row as the row_factory for a sqlite3 connection and
performing a SQL join that returns rows from two or more tables with
identical column names, the returned sqlite3.Row object contains
duplicate keys. Subsequently, when trying to access values by key, only
the value contained in the lowest numbered index matching the key is
returned. Additionally, attempting to use the keys returned to create a
new table in the database fails due to a duplicate column name error.

A better behavior would be for the sqlite3.Row object's indices to be
prefixed with table names for cases in which a query produces results
from two or more tables.

----------
components: Library (Lib)
messages: 80798
nosy: sockonafish
severity: normal
status: open
title: sqlite3.Row class, handling duplicate column names resulting from a SQL join
type: behavior
versions: Python 2.6

_______________________________________
Python tracker <report@bugs.python.org>
<http://bugs.python.org/issue5105>
_______________________________________
_______________________________________________
Python-bugs-list mailing list
Unsubscribe: http://mail.python.org/mailman/options/python-bugs-list/list-python-bugs%40lists.gossamer-threads.com

[issue5105] sqlite3.Row class, handling duplicate column names resulting from a SQL join [ In reply to ]

report at bugs

Jan 30, 2009, 5:34 PM

Post #2 of 5 (385 views)

Permalink

Gabriel Genellina <gagsl-py2@yahoo.com.ar> added the comment:

I think this is a sqlite issue, not a Python one.
cursor.description should return unique column names.

----------
nosy: +gagenellina

_______________________________________
Python tracker <report@bugs.python.org>
<http://bugs.python.org/issue5105>
_______________________________________
_______________________________________________
Python-bugs-list mailing list
Unsubscribe: http://mail.python.org/mailman/options/python-bugs-list/list-python-bugs%40lists.gossamer-threads.com

[issue5105] sqlite3.Row class, handling duplicate column names resulting from a SQL join [ In reply to ]

report at bugs

Jan 30, 2009, 6:22 PM

Post #3 of 5 (392 views)

Permalink

Gabriel Genellina <gagsl-py2@yahoo.com.ar> added the comment:

See http://www.sqlite.org/cvstrac/tktview?tn=3221

Try upgrading to the latest sqlite release. Or set "pragma
full_column_names=ON;"

import sqlite3
conn = sqlite3.connect(':memory:')
cursor = conn.cursor()
cursor.execute("create table T (a, b, c)")
cursor.execute("insert into T values (1, 2, 3)")
cursor.execute("insert into T values (2, 3, 4)")
conn.commit()
cursor.execute("pragma short_column_names=OFF;")
cursor.execute("pragma full_column_names=ON;")
cursor.execute("select * from T T1, T T2 where T1.a=T2.b")
print [d[0] for d in cursor.description]
# output: ['T1.a', 'T1.b', 'T1.c', 'T2.a', 'T2.b', 'T2.c']

_______________________________________
Python tracker <report@bugs.python.org>
<http://bugs.python.org/issue5105>
_______________________________________
_______________________________________________
Python-bugs-list mailing list
Unsubscribe: http://mail.python.org/mailman/options/python-bugs-list/list-python-bugs%40lists.gossamer-threads.com

[issue5105] sqlite3.Row class, handling duplicate column names resulting from a SQL join [ In reply to ]

report at bugs

Jan 30, 2009, 7:51 PM

Post #4 of 5 (384 views)

Permalink

Brandon Adams <sockonafish@gmail.com> added the comment:

Ah, that's it. Thanks for the tip, this issue can be closed now.

_______________________________________
Python tracker <report@bugs.python.org>
<http://bugs.python.org/issue5105>
_______________________________________
_______________________________________________
Python-bugs-list mailing list
Unsubscribe: http://mail.python.org/mailman/options/python-bugs-list/list-python-bugs%40lists.gossamer-threads.com

[issue5105] sqlite3.Row class, handling duplicate column names resulting from a SQL join [ In reply to ]

report at bugs

Feb 5, 2009, 6:16 AM

Post #5 of 5 (373 views)

Permalink

Changes by Georg Brandl <georg@python.org>:

----------
resolution: -> works for me
status: open -> closed

_______________________________________
Python tracker <report@bugs.python.org>
<http://bugs.python.org/issue5105>
_______________________________________
_______________________________________________
Python-bugs-list mailing list
Unsubscribe: http://mail.python.org/mailman/options/python-bugs-list/list-python-bugs%40lists.gossamer-threads.com