python + ODBC + Oracle + MySQL - money

Discussion in 'Python' started by hot.favorite@gmail.com, Nov 10, 2005.

  1. Guest

    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..
    , Nov 10, 2005
    #1
    1. Advertising

  2. Guest

    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
    , Nov 10, 2005
    #2
    1. Advertising

  3. 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
    Grig Gheorghiu, Nov 10, 2005
    #3
  4. 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
    =?ISO-8859-1?Q?Gerhard_H=E4ring?=, Nov 10, 2005
    #4
  5. 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
    Grig Gheorghiu, Nov 10, 2005
    #5
    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. Nagendra
    Replies:
    0
    Views:
    243
    Nagendra
    Sep 29, 2004
  2. Nagendra
    Replies:
    3
    Views:
    310
    Benji York
    Sep 30, 2004
  3. Godzilla
    Replies:
    9
    Views:
    506
    Godzilla
    Mar 23, 2007
  4. Wes Gamble
    Replies:
    1
    Views:
    151
    Gerardo Santana Gómez Garrido
    Apr 5, 2006
  5. Feyruz
    Replies:
    4
    Views:
    2,117
    Sherm Pendley
    Oct 14, 2005
Loading...

Share This Page