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

[Peter Otten]

rewonka at 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', 'something at something.net'],
>                  [2, 1, 'address','something stree'],
>                  [2, 3, 'email','something at something.net']]
> the dict can be:
> dict={1:['something street', '1111111111' ,
> 'something at something.net'],
>         2:['something street', '', 'something at something.net' ]}
> or a list can be:
> list=[[1,'something street', '1111111111' ,
> 'something at something.net'],
>        [2,'something street', '', 'something at 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 ?

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 at email'],
                 [2, 1, 'address','two-address'],
                 [2, 3, 'email','two at 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