[EMAIL PROTECTED] 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', '[EMAIL PROTECTED]'],
> [2, 1, 'address','something stree'],
> [2, 3, 'email','[EMAIL PROTECTED]']]
> the dict can be:
> dict={1:['something street', '1111111111' ,
> '[EMAIL PROTECTED]'],
> 2:['something street', '', '[EMAIL PROTECTED]' ]}
> or a list can be:
> list=[[1,'something street', '1111111111' ,
> '[EMAIL PROTECTED]'],
> [2,'something street', '', '[EMAIL PROTECTED]' ]]
>
> 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', '[EMAIL PROTECTED]'],
[2, 1, 'address','two-address'],
[2, 3, 'email','[EMAIL PROTECTED]']]
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
--
http://mail.python.org/mailman/listinfo/python-list
