Dennis Lee Bieber wrote: > On Wed, 29 Oct 2008 04:35:31 -0700 (PDT), "[EMAIL PROTECTED]" > <[EMAIL PROTECTED]> 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', '[EMAIL PROTECTED]'], >> [2, 1, 'address','something stree'], >> [2, 3, 'email','[EMAIL PROTECTED]']] > > 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 -- http://mail.python.org/mailman/listinfo/python-list
