mysql -> record array

[Erin Sheldon]

On 11/14/06, John Hunter <jdhunter at ace.bsd.uchicago.edu> wrote:
>
> Has anyone written any code to facilitate dumping mysql query results
> (mainly arrays of floats) into numpy arrays directly at the extension
> code layer.  The query results->list->array conversion can be slow.
>
> Ideally, one could do this semi-automagically with record arrays and
> table introspection....

I've been considering this as well.  I use both postgres and Oracle
in my work, and I have been using the python interfaces (cx_Oracle
and pgdb) to get result lists and convert to numpy arrays.

The question I have been asking myself is "what is the advantage
of such an approach?".  It would be faster, but by how
much?  Presumably the bottleneck for most applications will
be data retrieval rather than data copying in memory.  The
process numpy.array(results, dtype=) is pretty fast and simple
if the client is DB 2.0 compliant and returns a list of tuples
(pgdb does not sadly).  Also the memory usage will be about
the same since a copy must be made in order to create the
lists or python arrays in either case.

On the other hand, the database access modules for all major
databases, with DB 2.0 semicomplience, have already been written.
This is not an insignificant amount of work.  Writing our own
interfaces for each  of our favorite databases would require an
equivalent amount of work.

I think a set of timing tests would be useful.  I will try some
using Oracle or postgres over the next few days.  Perhaps
you could do the same with mysql.

Erin

-------------------------------------------------------------------------
Take Surveys. Earn Cash. Influence the Future of IT
Join SourceForge.net's Techsay panel and you'll get the chance to share your
opinions on IT & business topics through brief surveys - and earn cash
http://www.techsay.com/default.php?page=join.php&p=sourceforge&CID=DEVDEV