Best python module for Oracle, but portable to other RDBMSes

[dananrg]

I'm a little confused about what's out there for database modules at:

http://python.org/topics/database/modules.html

What I'd like to do is use Python to access an Oracle 9.X database for
exporting a series of tables into one aggregated table as a text file,
for import into a mainframe database. The catch is that I'd like to
write code that wouldn't need to be changed (much) if we switched from
Oracle to some other RDBMS (still need to export out as a text file for
import to the mainframe database.

Looks like I'd probably want to use something that conforms to the
DB-API 2.0. On the module list, I see DCOracle2, but it hasn't been
updated since 2001. Anyone here use this module, and does it work with
both Oracle 9.2 and Oracle 10g? Are there other DB-API 2.0 compliant
modules I should be looking at? What are the advantages and
disadvantages of the ones that can access Oracle 9.2 and Oracle 10g
databases?

Thanks in advance.

[Olivier]

da*****@yahoo.c om a écrit :

I'm a little confused about what's out there for database modules at:

http://python.org/topics/database/modules.html

What I'd like to do is use Python to access an Oracle 9.X database for
exporting a series of tables into one aggregated table as a text file,
for import into a mainframe database. The catch is that I'd like to
write code that wouldn't need to be changed (much) if we switched from
Oracle to some other RDBMS (still need to export out as a text file for
import to the mainframe database.

Looks like I'd probably want to use something that conforms to the
DB-API 2.0.

You want to use cx_Oracle :

http://www.python.net/crew/atuining/...cle/index.html

which is nowadays the very best python Oracle driver.

Olivier

[Jonathan Gardner]

On database portability...

While it is noble to try to have a generic interface to these
libraries, the end result is that the databases are always different
enough that the interface just has to work differently. My experience
in going from one database to another is that you should revisit your
entire database interface anyway. (I remember going from Sybase to
Oracle when we had Perl's DBI way back when. It was still very, very
messy.)

So, pick a good module, learn it inside and out, and plan on using a
completely different module if you use a different database, with
perhaps at least a slightly different interface.

[dananrg]

Thanks Olivier and Jonathan.

Do either of you, or anyone else, know of a good open source data
modeling / ER-diagram / CASE tools? I'd like to be able to build
relatively simple schemas in one open source tool and be able to create
a database on different platforms as needed (e.g. MySQL, PostgreSQL,
Oracle, etc).

Just wondering what's out there.

Thanks.

[dananrg]

How about DBdesigner4 or Dia as free ER diagrammers?
da*****@yahoo.c om wrote:

Thanks Olivier and Jonathan.

Do either of you, or anyone else, know of a good open source data
modeling / ER-diagram / CASE tools? I'd like to be able to build
relatively simple schemas in one open source tool and be able to create
a database on different platforms as needed (e.g. MySQL, PostgreSQL,
Oracle, etc).

Just wondering what's out there.

Thanks.

[Jonathan Gardner]

I've never seen the points of those tools. Just lay it out on paper or
document it somewhere. Be consistant with your naming scheme and it
shouldn't be hard to see the relations. If found that the people who
don't understand how tables should relate to one another are also the
same people who don't understand the special arrows DBAs like to use.

[dananrg]

What would be the next best Oracle database module for Python next to
cx_oracle? I'd like to compare two and choose one, just for the sake of
seeing how two modules doing the same thing operate.

Also, does installing cx_oracle create registry entries or require
admin privs on a Windows XP machine? I see that cx_oracle is
distributed as an EXE.

Thanks.

[dananrg]

Also, what's the difference between something like cx_oracle and an
ODBC module? If I were to use an ODBC module (not trying to torture
myself here, I promise, but I just want to see what alternatives exist
and how they work).

[Gerhard Häring]

-----BEGIN PGP SIGNED MESSAGE-----
Hash: SHA1

da*****@yahoo.c om wrote:

What would be the next best Oracle database module for Python next to
cx_oracle?
That would probably be DCOracle2.
I'd like to compare two and choose one, just for the sake of
seeing how two modules doing the same thing operate.

Also, does installing cx_oracle create registry entries or require
admin privs on a Windows XP machine? I see that cx_oracle is
distributed as an EXE.

It's most probably created using distutils and "python setup.py
bdist_wininst". These installers only use the registry to look up the
path Python where is installed. Of course it will need a correctly
working Oracle client to operate.

- -- Gerhard
-----BEGIN PGP SIGNATURE-----
Version: GnuPG v1.4.1 (GNU/Linux)
Comment: Using GnuPG with Thunderbird - http://enigmail.mozdev.org

iD8DBQFEBJxBdIO 4ozGCH14RAqXCAJ 9Vq6L8SLvnhlBCD c4EzwloJYp28ACf Vt8J
TNN+XgNxFLmQscu 9wpPIK4M=
=txAA
-----END PGP SIGNATURE-----

[Magnus Lycka]

Jonathan Gardner wrote:

On database portability...

While it is noble to try to have a generic interface to these
libraries, the end result is that the databases are always different
enough that the interface just has to work differently.

Considering the use case in question...

"What I'd like to do is use Python to access an Oracle 9.X database for
exporting a series of tables into one aggregated table as a text file,
for import into a mainframe database."

....it certainly seems reasonable to achieve this without too much
modifications between database engines. The problem I see directly
is if it uses the system tables to figure out what to export, but
if it doesn't, I don't forsee any big problems. There are even
ebcdic codecs in Python! :)

Read the DB-API 2 spec well. You might also want to look at
http://www.thinkware.se/cgi-bin/thin...mingWithPython

I guess the main differences would be the connect string and
parameter passing.

Using ODBC (e.g. via mxODBC) should remove these problems.
Assuming that you use a subset of SQL which is supported by all
your engines (seems reasonable for this use case) it should be
enough to change ODBC data source to select data from either
Oracle or some other server.

Once upon a time, ODBC meant a significant performance penalty.
I don't know if that is still true.

Note that mxODBC has a licence that doesn't allow it to be used
freely in commercial contexts.

There is also an ODBC driver in the Python Windows extensions,
but I don't think it's been actively developed for many years.
It's not complient with DB API 2. There is another DB API 2
driver for ODBC sources on Windows called adodbapi, but in my
experience, it's slow and has problems with unicode strings in
its error handling.