By using this site, you agree to our updated Privacy Policy and our Terms of Use. Manage your Cookies Settings.
455,767 Members | 1,357 Online
Bytes IT Community
+ Ask a Question
Need help? Post your question and get tips & solutions from a community of 455,767 IT Pros & Developers. It's quick & easy.

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

P: n/a
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********@something.net'],
[2, 1, 'address','something stree'],
[2, 3, 'email','s********@something.net']]
the dict can be:
dict={1:['something street', '1111111111' ,
's********@something.net'],
2:['something street', '', 's********@something.net' ]}
or a list can be:
list=[[1,'something street', '1111111111' ,
's********@something.net'],
[2,'something street', '', 's********@something.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 ?
Oct 29 '08 #1
Share this Question
Share on Google+
3 Replies


P: n/a
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(defaultdict)
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...@something.net'

If you'd prefer to use only the ids for reference:
>>results = defaultdict(defaultdict)
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.
Oct 29 '08 #2

P: n/a
re*****@gmail.com 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********@something.net'],
[2, 1, 'address','something stree'],
[2, 3, 'email','s********@something.net']]
the dict can be:
dict={1:['something street', '1111111111' ,
's********@something.net'],
2:['something street', '', 's********@something.net' ]}
or a list can be:
list=[[1,'something street', '1111111111' ,
's********@something.net'],
[2,'something street', '', 's********@something.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__.update(dict(zip(names, data))

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

regards
Steve
--
Steve Holden +1 571 484 6266 +1 800 494 3119
Holden Web LLC http://www.holdenweb.com/

Oct 30 '08 #3

P: n/a
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.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', 's********@something.net'],
[2, 1, 'address','something stree'],
[2, 3, 'email','s********@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
Oct 30 '08 #4

This discussion thread is closed

Replies have been disabled for this discussion.