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

Discussion in 'Python' started by rewonka@gmail.com, Oct 29, 2008.

  1. Guest

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

    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, 2008
    #1
    1. Advertising

  2. Peter Otten Guest

    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', ''],
    > [2, 1, 'address','something stree'],
    > [2, 3, 'email','']]
    > the dict can be:
    > dict={1:['something street', '1111111111' ,
    > ''],
    > 2:['something street', '', '' ]}
    > or a list can be:
    > list=[[1,'something street', '1111111111' ,
    > ''],
    > [2,'something street', '', '' ]]
    >
    > 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
    Peter Otten, Oct 29, 2008
    #2
    1. Advertising

  3. alex23 Guest

    On Oct 29, 9:35 pm, "" <> wrote:
    > 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']

    ''

    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.
    alex23, Oct 29, 2008
    #3
  4. Steve Holden Guest

    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', ''],
    > [2, 1, 'address','something stree'],
    > [2, 3, 'email','']]
    > the dict can be:
    > dict={1:['something street', '1111111111' ,
    > ''],
    > 2:['something street', '', '' ]}
    > or a list can be:
    > list=[[1,'something street', '1111111111' ,
    > ''],
    > [2,'something street', '', '' ]]
    >
    > 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/
    Steve Holden, Oct 30, 2008
    #4
  5. Peter Otten Guest

    Dennis Lee Bieber wrote:

    > On Wed, 29 Oct 2008 04:35:31 -0700 (PDT), ""
    > <> declaimed 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', ''],
    >> [2, 1, 'address','something stree'],
    >> [2, 3, 'email','']]

    >
    > 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
    Peter Otten, Oct 30, 2008
    #5
    1. Advertising

Want to reply to this thread or ask your own question?

It takes just 2 minutes to sign up (and it's free!). Just click the sign up button to choose a username and then you can ask your own questions on the forum.
Similar Threads
  1. Harry Zoroc
    Replies:
    1
    Views:
    918
    Gregory Vaughan
    Jul 12, 2004
  2. Godzilla
    Replies:
    15
    Views:
    493
    jigloo
    Jul 15, 2007
  3. Replies:
    3
    Views:
    403
  4. Michael Tan
    Replies:
    32
    Views:
    934
    Ara.T.Howard
    Jul 21, 2005
  5. Magicloud Magiclouds

    What is the fastest way to convert a object?

    Magicloud Magiclouds, Jul 17, 2007, in forum: Ruby
    Replies:
    13
    Views:
    157
    Magicloud Magiclouds
    Jul 17, 2007
Loading...

Share This Page