python/sqlite3: multiple columns with the same name not correctly treated by sqlite3.Row
<p>I am accessing a sqlite database taken from my phone (specifically, from the gnucash app). I want to access the columns by name, so I set conn.row_factory = sqlite3.Row on my connection. Then I join two tables with the following query:</p>

<pre><code>&gt;&gt;&gt; rows = curr.execute('select * from splits s left join transactions t on s.transaction_uid = t.uid')
</code></pre>

<p>The column names are correctly reported, but without the table alias:</p>

<pre><code>&gt;&gt;&gt; row = rows.fetchone()
&gt;&gt;&gt; print(row.keys())
['_id', 'uid', 'memo', 'type', 'value_num', 'value_denom', 'quantity_num', 'quantity_denom', 'account_uid', 'transaction_uid', 'created_at', 'modified_at', '_id', 'uid', 'name', 'description', 'timestamp', 'is_exported', 'is_template', 'currency_code', 'scheduled_action_uid', 'created_at', 'modified_at', 'commodity_uid']
</code></pre>

<p>so, when I access values, I get the same values for duplicate columns:</p>

<pre><code>&gt;&gt;&gt; for k in row.keys():
...   print(k, row[k])
...
_id 3690
uid a93bf33079924d82afcca4fd6acc0823
memo None
type CREDIT
value_num 1882.9999999999998
value_denom 100
quantity_num 1882.9999999999998
quantity_denom 100
account_uid sbilancio-e966169c8dfe
transaction_uid 2ad90ed9766b4716b8e443c8c19b35a0
created_at 2015-11-03 13:55:03
modified_at 2015-11-03 13:55:03
_id 3690
uid a93bf33079924d82afcca4fd6acc0823
name Sbilancio
description 
timestamp 1412340295000
is_exported 0
is_template 0
currency_code EUR
scheduled_action_uid None
created_at 2015-11-03 13:55:03
modified_at 2015-11-03 13:55:03
commodity_uid None
</code></pre>

<p>The values for _id, uid, created_at and modified_at come from SPLITS, and the one from TRANSACTIONS are not available.</p>

<p>You may tell me I shouldn't use "SELECT *", but that doesn't solve the problem:</p>

<pre><code>&gt;&gt;&gt; rows = curr.execute('select s._id, t._id from splits s left join transactions t on s.transaction_uid = t.uid where s._id = 3758')
&gt;&gt;&gt; row = rows.fetchone()
&gt;&gt;&gt; print(row.keys())
['_id', '_id']
&gt;&gt;&gt; for k in row.keys():
...   print(k, row[k])
... 
_id 3758
_id 3758
</code></pre>

<p>while using column aliases, luckily, works:</p>

<pre><code>&gt;&gt;&gt; rows = curr.execute('select s._id s_id, t._id t_id from splits s left join transactions t on s.transaction_uid = t.uid where s._id = 3758')
&gt;&gt;&gt; row = rows.fetchone()
&gt;&gt;&gt; print(row.keys())
['s_id', 't_id']
</code></pre>

<p>Would you consider this behaviour a bug? And what would you expect from sqlite3, in such a case? I think that if table aliases are given they should be prepended to column names, otherwise a sqlite3.OperationalError exception should be thrown, as for syntax errors in the query. Other ideas?</p>