[FRPythoneers] Re: Object-relational mapper for Python and MySQL?

Steve Staneff staneff at constructiondatares.com
Thu Feb 26 12:59:38 MST 2009


We've run into a similar problem with SQL in text reports (although using Firebird, not MySQL).  There's a lot of
repetition in our reports, which allowed us to use another approach.

For example, the following two statements combined give a fully formed sql statement (our complexities are usually in
the WHERE clauses, not the joins).  "strings" is the WHERE clause; the rest of the statement is specific to its place in
the program:
	strings = SQLconstructor(parameter1, parameter2, parameter3,...)
	sql = "SELECT FIRST 10 T1.field1, SUM(T1.field2) FROM table1 T1 " + strings + " GROUP BY T1.field1 ORDER BY
SUM(T1.field2) DESC;"

SQLconstructor is an imported object from a library module that we wrote.  It's got domain-specific logic about what
combinations of input parameters create what sort of WHERE statement output.
We didn't bother with reading metadata for this code, because it's so application-specific.  But we've done it elsewhere
using the kinterbasdb module to access Firebird, and I'm very sure someone's done something similar for MySQL.  In
kinterbasdb, if the cursor object instance is 'cur', then cur.description contains all sorts of good stuff.  This is
governed by the cursor, though, and doesn't read the metadata of the whole database - only the table(s) you've just
executed the cursor against (cur.execute("select first 1 * from table1") gets table1's data into the cursor).

If you decide to go this route and have any questions, feel free to contact me via the address below.  Good luck,
whichever way you choose!

Dr. Stephen T. Staneff
President, Construction Data and Research, Inc.
reply to:	staneff at computer.org <or>
		staneff at constructiondatares.com

> Message: 1
> Date: Wed, 25 Feb 2009 15:13:14 -0700
> From: Vince Dean <vdean at ucar.edu>
> Subject: [FRPythoneers] Object-relational mapper for Python and MySQL?
> To: Front Range Python Users -- General Discussion
> 	<frpythoneers at lists.community.tummy.com>
> Message-ID: <49A5C27A.9090004 at ucar.edu>
> Content-Type: text/plain; charset=ISO-8859-1; format=flowed
> I'd appreciate suggestions for Python tools to simplify queries
> against a MySQL database.
> I am writing a number of reports in Python against a MySQL
> database, using MySQLdb.  Not surprisingly, I'm writing a
> lot of boilerplate Python and SQL.
> I know that there are a number of Python modules to support
> this sort of work (SQLAlchemy, SQLOject, Django, etc.), and
> I'd welcome thoughts on which might be my best fit.  Here is
> our situation.
> * I'm doing queries only; the data is maintained by another
> (Java) application.
> * Efficiency is not a big issue.  The system has few users
> and the reports are run infrequently.  Ease of development is
> a bigger concern.
> * The database already exists; I cannot change column or table
> names to conform to a convention.
> * It's not a small database.  We have 123 tables and 770
> columns, but I am writing queries against a relatively small
> fraction of them.
> * Queries frequently involve four or more tables.  Some of
> the logic is easily handled by SQL joins; other parts require
> program logic to navigate through the relationships.
> * It would be useful, but not essential, if the tool could
> extract some of the data model it needs directly from the
> database schema.
> * I just need a tool to help with the database queries and return
> the results to Python, for some pretty simple text reports.
> I'm not building a Web application.
> Any thoughts?
> Thanks,
> Vince


More information about the FRPythoneers mailing list