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', '(e-mail address removed)'],
[2, 1, 'address','something stree'],
[2, 3, 'email','(e-mail address removed)']]
the dict can be:
dict={1:['something street', '1111111111' ,
'(e-mail address removed)'],
2:['something street', '', '(e-mail address removed)' ]}
or a list can be:
list=[[1,'something street', '1111111111' ,
'(e-mail address removed)'],
[2,'something street', '', '(e-mail address removed)' ]]

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 ?

 

[Peter Otten]

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', '(e-mail address removed)'],
[2, 1, 'address','something stree'],
[2, 3, 'email','(e-mail address removed)']]
the dict can be:
dict={1:['something street', '1111111111' ,
'(e-mail address removed)'],
2:['something street', '', '(e-mail address removed)' ]}
or a list can be:
list=[[1,'something street', '1111111111' ,
'(e-mail address removed)'],
[2,'something street', '', '(e-mail address removed)' ]]

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 ?

I think it won't get much easier than this:

dod = {}
to_index = [None] + range(3)
for contact_id, field_id, field_name, value in data:
if contact_id not in dod:
dod[contact_id] = [""]*len(to_index)
dod[contact_id][to_index[field_id]] = value

A database expert might do it in SQL, but my try got a bit messy:

import sqlite3 as sqlite

conn = sqlite.connect(":memory:")
cs = conn.cursor()
cs.execute("create table tmp (contact_id, field_id, field_name, value);")

data = [[1, 1, 'address', 'one-address'],
[1, 2, 'telnumber', 'one-telephone'],
[1, 3, 'email', 'one@email'],
[2, 1, 'address','two-address'],
[2, 3, 'email','two@email']]

cs.executemany("insert into tmp values (?, ?, ?, ?)", data)

def make_query(field_defs, table="tmp"):
field_defs = [("alias%s" % index, id, name)
for index, (id, name) in enumerate(field_defs)]
fields = ", ".join("%s.value as %s" % (alias, name)
for alias, id, name in field_defs)

format = ("left outer join %(table)s as %(alias)s "
"on main.contact_id = %(alias)s.contact_id "
"and %(alias)s.field_id=%(field_id)s ")
joins = "\n".join(format
% dict(table=table, alias=alias, field_id=id)
for alias, id, name in field_defs)

return ("select distinct main.contact_id, %(fields)s "
"from %(table)s as main\n %(joins)s" % dict(
table=table, fields=fields, joins=joins))

field_defs = list(
cs.execute("select distinct field_id, field_name from tmp"))

# XXX sanitize field ids and names

sql = make_query(field_defs)
for row in cs.execute(sql):
print row

Note that you get None for empty fields, not "".

Peter

 

[alex23]

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

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

This lets you reference things in a straightforward way:

>>> results[1]['email']

'(e-mail address removed)'

If you'd prefer to use only the ids for reference:
... results[contact_id][field_id] = (field_name, value)
...

>>> results[1][1]

('address', 'something street')

Hope this helps.

 

[Steve Holden]

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', '(e-mail address removed)'],
[2, 1, 'address','something stree'],
[2, 3, 'email','(e-mail address removed)']]
the dict can be:
dict={1:['something street', '1111111111' ,
'(e-mail address removed)'],
2:['something street', '', '(e-mail address removed)' ]}
or a list can be:
list=[[1,'something street', '1111111111' ,
'(e-mail address removed)'],
[2,'something street', '', '(e-mail address removed)' ]]

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

 

[Peter Otten]

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', '(e-mail address removed)'],
[2, 1, 'address','something stree'],
[2, 3, 'email','(e-mail address removed)']]

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