Remap Mysql tuple to dictionary

[Pom]

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

[Fredrik Lundh]

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>

[Tim Chase]

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

[Pom]

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]

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]

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!

[Lawrence D'Oliveiro]

In message <2r*********************@phobos.telenet-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 ...

[Fredrik Lundh]

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]

Lawrence D'Oliveiro wrote:

yield dict(zip(Fields, NextRow))

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

</F>

[Fredrik Lundh]

Fredrik Lundh wrote:

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

footnote: but for those who want that, I strongly recommend using
something like Greg Stein's dtuple module:

http://aspn.activestate.com/ASPN/Coo...n/Recipe/81252
http://www.lyra.org/greg/python/dtuple.py

</F>

[Lawrence D'Oliveiro]

In message <ma**************************************@python.o rg>, 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?

[Fredrik Lundh]

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>

[Lawrence D'Oliveiro]

In message <ef**********@lust.ihug.co.nz>, I wrote:

In message <ma**************************************@python.o rg>, 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...

[Fredrik Lundh]

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>

[Lawrence D'Oliveiro]

In message <ma**************************************@python.o rg>, 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.

[Fredrik Lundh]

Lawrence D'Oliveiro wrote:

SQL databases like MySQL are _designed_ for efficiency.

unlike the Python data types, you mean ?

</F>

[Lawrence D'Oliveiro]

In message <ma**************************************@python.o rg>, 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?

[Diez B. Roggisch]

>

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

[Simon Brunning]

On 9/26/06, Lawrence D'Oliveiro <ld*@geek-central.gen.new_zealandwrote:

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,
si***@brunningonline.net

[Steve Holden]

Lawrence D'Oliveiro wrote:

In message <ma**************************************@python.o rg>, 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?

Can you please stop this incessant carping? c.l.py used to be a fun
place to hang out.

regards
Steve
--
Steve Holden [phone] [phone]
Holden Web LLC/Ltd http://www.holdenweb.com
Skype: holdenweb http://holdenweb.blogspot.com
Recent Ramblings http://del.icio.us/steve.holden

[Steve Holden]

Lawrence D'Oliveiro wrote:

In message <2r*********************@phobos.telenet-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 ...

This is a spectacularly bad (non-)solution to the original problem. It
also shows a fine disregard for readability requirements. I suppose you
are using a generator to avoid data duplication, but for 100,000 records
this could be regarded as a premature optimisation on modern computers.

regards
Steve
--
Steve Holden [phone] [phone]
Holden Web LLC/Ltd http://www.holdenweb.com
Skype: holdenweb http://holdenweb.blogspot.com
Recent Ramblings http://del.icio.us/steve.holden

[Frank Millman]

Steve Holden wrote:

>
Can you please stop this incessant carping? c.l.py used to be a fun
place to hang out.

Hey, Steve, don't let it get to you. It's still 98% fun.

I am reminded of a spoof Latin motto from the days of my youth -

NIL ILLEGITIMO CARBORUNDUM

Translation available on request.

Frank Millman

[Fredrik Lundh]

Frank Millman wrote:

I am reminded of a spoof Latin motto from the days of my youth -

NIL ILLEGITIMO CARBORUNDUM

isn't that usually written

Illegitimi non carborundum

?

or is that just due to differences between british latin and american latin ?

</F>

[Tim Chase]

> NIL ILLEGITIMO CARBORUNDUM

>
isn't that usually written

Illegitimi non carborundum

or is that just due to differences between british latin and
american latin ?

Wouldn't those differences make it
"carbourundum" vs.
"carborundum" respectively?

:*)

(however, yes, dredging my grade-school Latin, I believe Fredrik
is correct in the common ordering of the phrase, though I can't
vouch for the declension of "illegitimi" vs. "illegitimo")

-tkc

[Frank Millman]

Fredrik Lundh wrote:

Frank Millman wrote:

I am reminded of a spoof Latin motto from the days of my youth -

NIL ILLEGITIMO CARBORUNDUM

isn't that usually written

Illegitimi non carborundum

?

or is that just due to differences between british latin and american latin ?

</F>

or my bad memory - my youth was a long time ago :-)

Frank

[Simon Brunning]

On 9/26/06, Fredrik Lundh <fr*****@pythonware.comwrote:

Frank Millman wrote:

I am reminded of a spoof Latin motto from the days of my youth -

NIL ILLEGITIMO CARBORUNDUM

isn't that usually written

Illegitimi non carborundum

According to the Wikipedia, neither is actually correct latin -
<http://en.wikipedia.org/wiki/Illegitimi_non_carborundum- though I
think you are right about which form is the more common.

or is that just due to differences between british latin and american latin ?

American Latin? Is that Lingua::Romana::Perligata?

--
Cheers,
Simon B,
si***@brunningonline.net

[Steve Holden]

Frank Millman wrote:

Fredrik Lundh wrote:

>>Frank Millman wrote:

>>>I am reminded of a spoof Latin motto from the days of my youth -

NIL ILLEGITIMO CARBORUNDUM

isn't that usually written

Illegitimi non carborundum

?

or is that just due to differences between british latin and american latin ?

</F>

or my bad memory - my youth was a long time ago :-)

You were lucky, you *had* a youth [... etc.]

regards
Steve
--
Steve Holden [phone] [phone]
Holden Web LLC/Ltd http://www.holdenweb.com
Skype: holdenweb http://holdenweb.blogspot.com
Recent Ramblings http://del.icio.us/steve.holden

[Lawrence D'Oliveiro]

In message <ma**************************************@python.o rg>, Steve
Holden wrote:

I suppose you are using a generator to avoid data duplication, but for
100,000 records this could be regarded as a premature optimisation on
modern computers.

I was using a generator to avoid loading all those 100,000 records into
memory at once.