Fastest way to convert sql result into a dict or list ?

[Peter Otten]

Dennis Lee Bieber wrote:

> On Wed, 29 Oct 2008 04:35:31 -0700 (PDT), "rewonka at gmail.com"
> <rewonka at gmail.com> declaimed the following in comp.lang.python:
> 
>> Hello,
>> 
>> I'm trying to find the fastest way to convert an sql result into a
>> dict or list.
>> What i mean, for example:
>> my sql result:
>> contact_id, field_id, field_name, value
>> sql_result=[[1, 1, 'address', 'something street'],
>>                  [1, 2, 'telnumber', '1111111111'],
>>                  [1, 3, 'email', 'something at something.net'],
>>                  [2, 1, 'address','something stree'],
>>                  [2, 3, 'email','something at something.net']]
> 
> Off-hand, field_ID and field_name are equivalent and only one would
> be needed (either you know that "2" is a telnumber, or you just take the
> name directly).
> 
>> I hope there is an easyest way to do something like this ??
>> any idea ?
> 
> Let the database do it?
> 
> select
> c.contact_id as contact,
> c.value as address,
> t.value as telephone,
> e.value as email
> from thetable as c
> inner join thetable as t
> on c.contact_id = t.contact_id and c.field_id = 1 and t.field_id = 2
> inner join thetable as e
> on c.contact_id = e.contact_id and c.field_id = 1 and e.field_id= 3
> 
> If the join complains about the "= constant" clauses, try
> 
> select
> c.contact_id as contact,
> c.value as address,
> t.value as telephone,
> e.value as email
> from thetable as c
> inner join thetable as t
> on c.contact_id = t.contact_id
> inner join thetable as e
> on c.contact_id = e.contact_id
> where c.field_id = 1 and t.field_id = 2 and e.field_id = 3
> 
> (technically, the latter first finds all combinations
> 
> c.address, t.address, e.address
> c.address, t.address, e.telephone
> etc.
> 
> and then removes the results where c is not the address, t is not the
> phone, and e is not the email; doing them on the joins should mean a
> smaller intermediate result is generated)

You will lose contact information if you use an inner join and there are
contacts that lack fields (like contact #2 without a telephone number). Use
an outer join like in my (generated) sql to fix that and "distinct" to
suppress duplicate contact_id-s. The following should work with SQLite3:

select distinct 
c.contact_id, a.value as address, 
t.value as telnumber, 
e.value as email
from contacts as c
left outer join contacts as a 
on c.contact_id = a.contact_id and a.field_id=1
left outer join contacts as t 
on c.contact_id = t.contact_id and t.field_id=2
left outer join contacts as e 
on c.contact_id = e.contact_id and e.field_id=3

Peter