python + ODBC + Oracle + MySQL - money

[hot.favorite]

Hi,

I'm fairly new to Python so please pardon any dumbness on my part.

I plan to write an app in Python that will run on Linux and would need
to connect to Oracle and MySQL. I could use MySQLdb for MySQL and
cx_oracle for Oracle, but 2 different APIs in the same app is kind of
painful.

So I have unixODBC that gives me ODBC on Linux. The best ODBC access
for Python I know is mxODBC. But that is not free for commercial use.
Could someone tell me if there are other choices I have?

Thanks..

[rios.andy]

Well having two different Databases from one app could be painful, but
I think that using Python and a "Divide and Conquer" aproach might be
your best GPL way of handling this. Start up a set of python Classes
that just does the access to the MySQL database. Get these working,
just concentrate on building the Database accesses you will need for
your app, and give the functions sensible, and relatively verbose,
names. Next take step two, doing that same thing (In a different
directory, with a slightly different naming convention for classes
maybe), but for the Oracle Database, and test that out and get that up
and running. Now you have two apps, one for MySQL and one for Oracle.

Now the step that you might not catch on about until you have more
expereience using Python. Because of the very slick and intelligent way
that Python handles naming and operator overloading you just need to
write another set of classes that is you application.
This application can just make use of the other two DB apps you just
created by calling those classes (Hence why I suggested careful naming,
and following some sort of convention for the naming). This will
eventually translate into your app, remember you can do all the fancy
User Interface work and Program Logic work in the third set of classes
(The APP classes). I'm not sure how complicated the app is, but this
sounds like a reasonalbe high level aproach. And if you boss asks
questions about this methodology just tell him/her know that you got
recommend this approach by a Graduate of the University of Toronto with
a degree in Computre Engineering. :-P

So, hope this helps, feel free to ask more questions, hopefully others
will have some more ideas to share.

My two cents,
Andy

[Grig Gheorghiu]

In my testing, I need to connect to Oracle, SQL Server and DB2 on
various platforms. I have a base class with all the common code, and
derived classes for each specific database type using specific database
modules such as cxOracle, mxODBC and pyDB2. The derived classes are
pretty thin, containing only some syntax peculiarities for a given
database type. The code is clean and portable.

Grig

[Gerhard Häring]

Grig Gheorghiu wrote:

In my testing, I need to connect to Oracle, SQL Server and DB2 on
various platforms. I have a base class with all the common code, and
derived classes for each specific database type using specific database
modules such as cxOracle, mxODBC and pyDB2. The derived classes are
pretty thin, containing only some syntax peculiarities for a given
database type. The code is clean and portable.

So maybe you're lucky that all your database modules use the same access
to query parameters. MySQLdb and cx_Oracle would be different in that
MySQLdb has paramstyle = "format" and cx_Oracle has paramstyle =
"qmark/named", i. e. to query a specific record of the person table you
would use

p_id = 4711
cur.execute("select firstname from person where person_id=%s", (p_id,))

using MySQLdb, and:

cur.execute("select firstname from person where person_id=?", (p_id,))

using cx_Oracle.

Now, probably a lot of people have written wrappers for DB-API modules
that translate one paramstyle to the other. The most sensible solution
is to translate the format/pyformat one to others.

Often, one other solution is to use a higher-level database interface
uses your database modules internally, but has the same consistent
interface for the outside.

In my recent evaluations, I liked PyDO2 for this
(http://skunkweb.sourceforge.net/pydo2.html). Unlike SQLObject, it is
able to use MySQL and Oracle now, though there is work underway to add
Oracle support to SQLObject.

OTOH, the advice to use MySQLdb and cx_Oracle directly is probably a
good one, especially for a newcomer to Python. It's a good way to learn
Python and learning the Python DB-API is a good idea if you want to do a
database application in Python. You can use higher-level interfaces (or
write them yourself) later on.

And if this is serious work with business value, then just buying a
mxODBC license and go on with the real problem is probably the most
sensible solution.

You can use the time saved for learning Python, then, which is perhaps
more fun :-)

Cheers,

-- Gerhard

[Grig Gheorghiu]

Yes, I did run into the difference in the parameter styles, so I deal
with that in the database-specific classes. It's not a huge difficulty
though.

Grig