PDO and database abstraction

Discussion in 'Python' started by Luiz Geron, Oct 25, 2005.

  1. Luiz Geron

    Luiz Geron Guest

    Hi all,
    I'm testing the PDO wrapper to database modules [1] and I'm wondering
    how few things like this there are around. My problem, actually, is the
    paramstyle of modules. I want to use kinterbasdb in the same code I use
    cx_oracle, for example, but paramstyle changes from one to other, than
    I searched for things like this and found nothing really usefull. The
    problem with PDO is that it was so dificult to find, since a few people
    seems to use it, and I haven't yet figured how to change the paramstyle
    on it, so I want to ask: Do you use a thing like this that you would
    recommend to me?

    Thanks,
    Luiz Carlos Geron

    [1] http://pdo.neurokode.com
     
    Luiz Geron, Oct 25, 2005
    #1
    1. Advertisements

  2. Actually there are several Object-Relation Mappers (ORM) for Python, and
    also a few other attempts to provide a more convenient layer on top of
    DB-API modules.

    This wiki page has links:
    http://wiki.python.org/moin/HigherLevelDatabaseProgramming
    That problem exactly what these solutions try to solve, this and
    multiple SQL dialects and handling types like date etc. for multiple
    backends.
    I always wrote my own thin layer on top of DB-API modules and used it to
    implement a *specific* database interface for my applications. This
    would then have one or more database backends. Actually never more than
    two so far.

    If you want to go for a more popular ORM, you can try out SqlObject. But
    it doesn't have Oracle support, yet. There were patches and there are
    apparently again new attempts to integrate Oracle support, but nothing
    official/finished yet apparently.

    Personally, I recently checked out different ORMs for Python one
    afternoon. Only superficially, but here's my biased uninformed opinion.

    - SqlObject (1) has an active community, and its use in Subway and
    TurboGears will create even more momentum for it. By looking at its code
    (for hacking in Oracle support, which I managed to do for a one-table
    test case), I found it to have *a lot* features, including caching and
    others. I don't particularly like that, I'd prefer a thin-to-medium
    layer myself.

    - There was something to Modeling (2) I didn't like. It's just a gut
    feeling that it tries to do too much for my taste.

    - PyDO2 did work with Oracle out of the box, the SQLite and PostgreSQL
    adapters looked reasonable too from a quick code inspection. It does
    seem to do one thing and do it right, which is a philosophy I like in
    libraries and wrappers. If I'm to use a ORM for a future project, I'd
    first go with PyDO2.

    HTH,

    -- Gehard

    (1) http://sqlobject.org/
    (2) http://modeling.sourceforge.net/
    (3) http://skunkweb.sourceforge.net/PyDO2/manual.html
     
    =?ISO-8859-1?Q?Gerhard_H=E4ring?=, Oct 25, 2005
    #2
    1. Advertisements

  3. Luiz Geron

    Luiz Geron Guest

    Excuse me if I wasn't clear. I don't want to use ORMs, since I really
    have to execute arbitrary sql queries, and then I can't use this object
    mapping. I'm going to write my own wrapper like you did, it is not so
    difficult to do.
     
    Luiz Geron, Oct 25, 2005
    #3
  4. Luiz Geron

    Steve Holden Guest

    [ORM stuff ...]

    Another way is to actually parameterise your queries for the paramstyle
    according to which packend module you are using. For example, in a
    current development I have queries that are generated like this:

    sql = ("SELECT %s FROM %s WHERE %s=%s" %
    (",".join(f[1] for f in self.FIELDS),
    table, keyfield, db.pmark))

    The "db" module imports one or other of a number of back-end modules,
    and sets "pmark" to the appropriate parameter marker ("%s" or "?" are
    the ones I have used: positionals would be a little trickier, now I
    think of it).

    Of course you still have to be careful of SQL syntax variations and
    other backend differences (the usual one being "find the primary key
    value of the last-inserted row on this connection/cursor").

    regards
    Steve
     
    Steve Holden, Oct 25, 2005
    #4
  5. Luiz Geron

    Luiz Geron Guest

    This is one example where I need to use such abstraction:
    I have a dictionary with the fields and values to be inserted into the
    database, with a code like this:

    dic = {'field1' : 1, 'field2' : 2} #this dict comes from a Cherrypy
    request
    cur.execute('update table set field_one = :value1, field2 = :value2' ,
    dic)

    and I want to use it even with kinterbasdb, wich does not support named
    paramstyle. The problem with your use of % is that the db module does
    not automatically put '' in varchar fields, and so on. This is what PDO
    does, or say that does. I think that I should make a wrapper that
    simply swap :var to ? when the db paramstyle is qmark, and so on. Is
    this correct?
     
    Luiz Geron, Oct 25, 2005
    #5
    1. Advertisements

Ask a Question

Want to reply to this thread or ask your own question?

You'll need to choose a username for the site, which only take a couple of moments (here). After that, you can post your question and our members will help you out.