PDO and database abstraction

[Luiz Geron]

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

 

[=?ISO-8859-1?Q?Gerhard_H=E4ring?=]

Hi all,
I'm testing the PDO wrapper to database modules [1] and I'm wondering
how few things like this there are around.

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

My problem, actually, is the paramstyle of modules.

That problem exactly what these solutions try to solve, this and
multiple SQL dialects and handling types like date etc. for multiple
backends.

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?

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

 

[Luiz Geron]

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.

 

[Steve Holden]

Hi all,
I'm testing the PDO wrapper to database modules [1] and I'm wondering
how few things like this there are around.

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

My problem, actually, is the paramstyle of modules.

That problem exactly what these solutions try to solve, this and
multiple SQL dialects and handling types like date etc. for multiple
backends.

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?

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.

[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

 

[Luiz Geron]

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?