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

[rewonka]

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', 's********@some thing.net'],
[2, 1, 'address','some thing stree'],
[2, 3, 'email','s***** ***@something.n et']]
the dict can be:
dict={1:['something street', '1111111111' ,
's********@some thing.net'],
2:['something street', '', 's********@some thing.net' ]}
or a list can be:
list=[[1,'something street', '1111111111' ,
's********@some thing.net'],
[2,'something street', '', 's********@some thing.net' ]]

I tried to make a dict, but i think it is slower then make a list, and
i tried the "one lined for" to make a list, it's look like little bit
faster than make a dict.

def empty_list_make (sql_result):
return [ [line[0],"", "", ""] for line in sql_result]

than fill in the list with another for loop.
I hope there is an easyest way to do something like this ??
any idea ?

[alex23]

On Oct 29, 9:35*pm, "rewo...@gmail. com" <rewo...@gmail. comwrote:

I'm trying to find the fastest way to convert an sql result into a
dict or list.

>>from collections import defaultdict
results = defaultdict(def aultdict)
for contact_id, field_id, field_name, value in sql_result:

... results[contact_id][field_id] = value
... results[contact_id][field_name] = value
...

This lets you reference things in a straightforward way:

>>results[1]['email']

'someth...@some thing.net'

If you'd prefer to use only the ids for reference:

>>results = defaultdict(def aultdict)
for contact_id, field_id, field_name, value in sql_result:

... results[contact_id][field_id] = (field_name, value)
...

>>results[1][1]

('address', 'something street')

Hope this helps.

[Steve Holden]

re*****@gmail.c om wrote:

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', 's********@some thing.net'],
[2, 1, 'address','some thing stree'],
[2, 3, 'email','s***** ***@something.n et']]
the dict can be:
dict={1:['something street', '1111111111' ,
's********@some thing.net'],
2:['something street', '', 's********@some thing.net' ]}
or a list can be:
list=[[1,'something street', '1111111111' ,
's********@some thing.net'],
[2,'something street', '', 's********@some thing.net' ]]

I tried to make a dict, but i think it is slower then make a list, and
i tried the "one lined for" to make a list, it's look like little bit
faster than make a dict.

def empty_list_make (sql_result):
return [ [line[0],"", "", ""] for line in sql_result]

than fill in the list with another for loop.
I hope there is an easyest way to do something like this ??
any idea ?

Why not go for full attribute access? The following code is untested,
yada yada yada.

class recstruct:
def __init__(self, names, data):
self.__dict__.u pdate(dict(zip( names, data))

FIELDS = "A B C D".split()
sql = "SELECT %s FROM table" % ", ",join(FIEL DS)
curs.execute(sq l)
for data in curs.fetchall() :
row = recstruct(FIELD S, data)
print row.A, row.B ...

regards
Steve
--
Steve Holden [phone] [phone]
Holden Web LLC http://www.holdenweb.com/

[Peter Otten]

Dennis Lee Bieber wrote:

On Wed, 29 Oct 2008 04:35:31 -0700 (PDT), "re*****@gmail. com"
<re*****@gmail. comdeclaimed the following in comp.lang.pytho n:

>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', 's********@some thing.net'],
[2, 1, 'address','some thing stree'],
[2, 3, 'email','s***** ***@something.n et']]

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