Remap Mysql tuple to dictionary

Discussion in 'Python' started by Pom, Sep 25, 2006.

  1. Pom

    Pom Guest

    Hello

    I want to convert a Mysql resulset to a dictionary.

    I made some code myself, and want to ask you if I do this the right way.

    def remapmysql(a):
    return (a[0], (a[1:]))

    def test_map():
    count = 100000 # count of simulated records
    l1 = range(0, count)
    l2 = range(count , 2 * count )
    l3 = range(2 * count, 3 * count )
    z1 = zip(l1, l2, l3) # simulate a mysql resultset

    d1 = dict(map(remapmysql,z1))

    return d1
    Pom, Sep 25, 2006
    #1
    1. Advertising

  2. Pom wrote:

    > I want to convert a Mysql resulset to a dictionary.


    that is, you want to convert an array of (key, value, ...) tuples to a
    dictionary containing key: (value, ...) pairs, right ?

    > I made some code myself, and want to ask you if I do this the right way.
    >
    > def remapmysql(a):
    > return (a[0], (a[1:]))
    >
    > def test_map():
    > count = 100000 # count of simulated records
    > l1 = range(0, count)
    > l2 = range(count , 2 * count )
    > l3 = range(2 * count, 3 * count )
    > z1 = zip(l1, l2, l3) # simulate a mysql resultset
    >
    > d1 = dict(map(remapmysql,z1))
    >
    > return d1


    looks fine to me.

    if you care about performance, and is using a recent Python, you could
    try doing

    d1 = dict((row[0], row[1:]) for row in z1)

    instead, and see if that runs faster (this uses a generator expression
    instead of a callback and a full list).

    </F>
    Fredrik Lundh, Sep 25, 2006
    #2
    1. Advertising

  3. Pom

    Tim Chase Guest

    > def remapmysql(a):
    > return (a[0], (a[1:]))
    >
    > def test_map():
    > count = 100000 # count of simulated records
    > l1 = range(0, count)
    > l2 = range(count , 2 * count )
    > l3 = range(2 * count, 3 * count )
    > z1 = zip(l1, l2, l3) # simulate a mysql resultset
    >
    > d1 = dict(map(remapmysql,z1))
    >
    > return d1


    I'm not sure the map() is needed, as it could just be

    >>> d1 = dict((row[0], row[1:]) for row in z1)


    which worked in my tests.

    However either seems to work fairly well.

    -tkc
    Tim Chase, Sep 25, 2006
    #3
  4. Pom

    Pom Guest

    Fredrik Lundh wrote:
    > if you care about performance, and is using a recent Python, you could

    yes i do ;-)

    > try doing
    >
    > d1 = dict((row[0], row[1:]) for row in z1)
    >
    > instead, and see if that runs faster (this uses a generator expression
    > instead of a callback and a full list).
    >
    > </F>
    >


    I changed it and it saves me some time so I leave it like that!

    with 100000 test records:
    >>>

    dict+map (1, 2, 3) -> {1: (2, 3)}: 1.343 seconds.
    dict+gen-expr (1, 2, 3) -> {1: (2, 3)}: 0.861 seconds.
    >>>

    dict+map (1, 2, 3) -> {1: (2, 3)}: 1.397 seconds.
    dict+gen-expr (1, 2, 3) -> {1: (2, 3)}: 0.943 seconds.

    with 500000 test records:
    >>>

    dict+map (1, 2, 3) -> {1: (2, 3)}: 13.297 seconds.
    dict+gen-expr (1, 2, 3) -> {1: (2, 3)}: 8.335 seconds.
    >>>

    dict+map (1, 2, 3) -> {1: (2, 3)}: 14.548 seconds.
    dict+gen-expr (1, 2, 3) -> {1: (2, 3)}: 9.793 seconds.
    >>>



    thank you!!
    Pom, Sep 25, 2006
    #4
  5. Pom

    Pom Guest

    Tim Chase wrote:
    >> def remapmysql(a):
    >> return (a[0], (a[1:]))
    >>
    >> def test_map():
    >> count = 100000 # count of simulated records
    >> l1 = range(0, count)
    >> l2 = range(count , 2 * count )
    >> l3 = range(2 * count, 3 * count )
    >> z1 = zip(l1, l2, l3) # simulate a mysql resultset
    >>
    >> d1 = dict(map(remapmysql,z1))
    >>
    >> return d1

    >
    > I'm not sure the map() is needed, as it could just be
    >
    > >>> d1 = dict((row[0], row[1:]) for row in z1)

    >
    > which worked in my tests.
    >
    > However either seems to work fairly well.
    >
    > -tkc
    >


    thank you!!

    changed it, see previous post.
    Pom, Sep 25, 2006
    #5
  6. On Mon, 25 Sep 2006 16:26:38 GMT, Pom <> declaimed
    the following in comp.lang.python:

    > Hello
    >
    > I want to convert a Mysql resulset to a dictionary.
    >

    One thing I've not seen answered (or expanded upon)...

    > I made some code myself, and want to ask you if I do this the right way.
    >
    > def remapmysql(a):
    > return (a[0], (a[1:]))
    >
    > def test_map():
    > count = 100000 # count of simulated records


    You mention here 100,000 records in a result set... Do you really
    envision performing a

    crsr.fetchall() on that result set BEFORE converting to dictionary?

    Since, as I recall, MySQL does not have server-side cursor
    operations, the MySQLdb emulation has already received the full result
    data.... COPY 1

    crsr.fetchall() will create a list of tuples of all the data... COPY
    2

    Your remap operation will create a dictionary using the first field
    as the key, and the rest of the fields as a list identified by that
    key.... COPY 3

    I don't know when "COPY 1" gets freed -- on the next crsr.execute()
    or after the last row has been fetched from the cursor.

    "COPY 2" will hang around until you reuse it or explicitly delete
    it.

    In other words -- you could have up to THREE copies of your 100,000
    record result set consuming memory at one time.

    It might be more economical to perform the conversion while fetching
    the data:

    mdict = {}
    for rec in crsr:
    mdict[rec[0]] = rec[1:]

    --
    Wulfraed Dennis Lee Bieber KD6MOG

    HTTP://wlfraed.home.netcom.com/
    (Bestiaria Support Staff: )
    HTTP://www.bestiaria.com/
    Dennis Lee Bieber, Sep 26, 2006
    #6
  7. Pom

    Pom Guest

    Dennis Lee Bieber wrote:
    > It might be more economical to perform the conversion while fetching
    > the data:
    >
    > mdict = {}
    > for rec in crsr:
    > mdict[rec[0]] = rec[1:]
    >


    I didn't think of that. I just took the fetchall() from my first
    version (where I looped through the tuples, which was very slow)

    I just dropped the .fetchall() part, and as you said: it works fine,
    with 1 copy less. (but it doesn't save me time)

    dict((int(row[0]), row[1:]) for row in cursor)


    thanks!
    Pom, Sep 26, 2006
    #7
  8. In message <2rTRg.95640$-ops.be>, Pom wrote:

    > I want to convert a Mysql resulset to a dictionary.


    Here's a function that does this one row at a time:

    def GetEachRecord(TableName, Fields, Condition, Values, Extra = "") :
    """generator which does an SQL query which can return 0 or more
    result rows, yielding each record in turn as a mapping from
    field name to field value. TableName can be a single table name,
    or a comma-separated list of names for a join. Extra allows
    specification of order/group clauses."""
    Cursor = sql.conn.cursor() # modify this as appropriate
    Cursor.execute \
    (
    ", ".join(Fields)
    +
    " from "
    +
    TableName
    +
    " where "
    +
    Condition
    +
    " "
    +
    Extra,
    Values
    )
    while True :
    NextRow = Cursor.fetchone()
    if NextRow == None :
    Cursor.close()
    raise StopIteration
    #end if
    yield dict(zip(Fields, NextRow))
    #end while
    #end GetEachRecord

    You'd use this something like

    for Link in GetEachRecord(...) :
    ... Link[fieldname] ... blah-blah ...
    Lawrence D'Oliveiro, Sep 26, 2006
    #8
  9. Dennis Lee Bieber wrote:

    > Your remap operation will create a dictionary using the first field
    > as the key, and the rest of the fields as a list identified by that
    > key.... COPY 3


    the tuple-to-dictionary conversion mostly shuffles pointers around,
    though; it's not a full copy of all the data in the result set.

    </F>
    Fredrik Lundh, Sep 26, 2006
    #9
  10. Lawrence D'Oliveiro wrote:

    > yield dict(zip(Fields, NextRow))


    the OP didn't ask for a field name => value mapping, though.

    </F>
    Fredrik Lundh, Sep 26, 2006
    #10
  11. Fredrik Lundh, Sep 26, 2006
    #11
  12. In message <>, Fredrik
    Lundh wrote:

    > Lawrence D'Oliveiro wrote:
    >
    >> yield dict(zip(Fields, NextRow))

    >
    > the OP didn't ask for a field name => value mapping, though.


    What other kind of mapping could you produce?
    Lawrence D'Oliveiro, Sep 26, 2006
    #12
  13. Lawrence D'Oliveiro wrote:

    > What other kind of mapping could you produce?


    and here we go again. how about reading the code the OP posted, or the
    first few followups?

    </F>
    Fredrik Lundh, Sep 26, 2006
    #13
  14. In message <efaj9p$fqq$>, I wrote:

    > In message <>, Fredrik
    > Lundh wrote:
    >
    >> Lawrence D'Oliveiro wrote:
    >>
    >>> yield dict(zip(Fields, NextRow))

    >>
    >> the OP didn't ask for a field name => value mapping, though.

    >
    > What other kind of mapping could you produce?


    All right, sorry, looks like they want to load the entire table into RAM and
    key it off the first field. Kind of defeats the point of having SQL, but
    there you go...
    Lawrence D'Oliveiro, Sep 26, 2006
    #14
  15. Lawrence D'Oliveiro wrote:

    > Kind of defeats the point of having SQL, but there you go...


    there are plenty of reasons to use Python data structures instead of the
    SQL engine for data crunching. especially if you care about efficiency.

    </F>
    Fredrik Lundh, Sep 26, 2006
    #15
  16. In message <>, Fredrik
    Lundh wrote:

    > Lawrence D'Oliveiro wrote:
    >
    > > Kind of defeats the point of having SQL, but there you go...

    >
    > there are plenty of reasons to use Python data structures instead of the
    > SQL engine for data crunching. especially if you care about efficiency.


    I think you have something else in mind when you say "efficiency", from what
    I'm thinking of.

    SQL databases like MySQL are _designed_ for efficiency.
    Lawrence D'Oliveiro, Sep 26, 2006
    #16
  17. Lawrence D'Oliveiro wrote:

    > SQL databases like MySQL are _designed_ for efficiency.


    unlike the Python data types, you mean ?

    </F>
    Fredrik Lundh, Sep 26, 2006
    #17
  18. In message <>, Fredrik
    Lundh wrote:

    > Lawrence D'Oliveiro wrote:
    >
    > > SQL databases like MySQL are _designed_ for efficiency.

    >
    > unlike the Python data types, you mean ?


    Did I say it was unlike anything?
    Lawrence D'Oliveiro, Sep 26, 2006
    #18
  19. >
    > SQL databases like MySQL are _designed_ for efficiency.


    Efficiency with respect to what? That statement is plain wrong. They are
    designed for a pretty general case of data storage efficiency, in the
    domain of relational algebra. And for a lot of use-cases, they offer a good
    ratio of ease-of-use, speed and reliability and the overhead they
    introduce.

    But there are lots of cases where hand-tailored data structures - in python
    as well as in C - are way better suited. Think of graph representations for
    example, especially for reachability queries and the like. Does google use
    ORACLE (or whatever DB) for their index? Nope.

    So - without knowing the usecase,

    """
    All right, sorry, looks like they want to load the entire table into RAM and
    key it off the first field. Kind of defeats the point of having SQL, but
    there you go...
    """

    is a pretty strong thing to say.

    Diez
    Diez B. Roggisch, Sep 26, 2006
    #19
  20. On 9/26/06, Lawrence D'Oliveiro <_zealand> wrote:
    > All right, sorry, looks like they want to load the entire table into RAM and
    > key it off the first field. Kind of defeats the point of having SQL, but
    > there you go...


    Keeping an in-memory cache of small, unchanging, frequently-read
    tables is a very common, and often very effective performance tweak in
    database driven systems.

    --
    Cheers,
    Simon B,
    Simon Brunning, Sep 26, 2006
    #20
    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. Michal Mikolajczyk
    Replies:
    1
    Views:
    803
    Larry Bates
    Apr 20, 2004
  2. Jeff Epler
    Replies:
    0
    Views:
    949
    Jeff Epler
    Apr 20, 2004
  3. Bill Scherer
    Replies:
    0
    Views:
    610
    Bill Scherer
    Apr 20, 2004
  4. Les Caudle
    Replies:
    0
    Views:
    323
    Les Caudle
    Aug 23, 2006
  5. guddu
    Replies:
    1
    Views:
    720
    Öö Tiib
    Apr 18, 2010
Loading...

Share This Page