[DB-SIG] Mirroring DBs with Python

[Tom Bryan]

On Fri, 7 Jul 2000, Timothy Wilson wrote:

> I'm interested is putting Python to work at my school doing what would
> seem to be a relatively straight-forward task. I'd appreciate it if
> someone would comment on the feasiblity of this or the best general
> approach.

Very feasible, assuming that the speed of your link is fast enough to suck
down the data you need.

> Let's assume I've got Oracle running on Solaris at a remote site,
> accessible via the Internet. 

Hmm...does that mean that you have a user account on the database?  That
is, can you login to the Oracle database yourself?  Or is this some sort
of Web database accessible via some CGI/HTML forms interface?  It's still
feasible, but it will greatly affect your approach.

> The database is made of many tables with data referring to many
> different schools. I'd like to do a query on that remote database,
> pulling out the data that refers to my school only, and mirror the
> results to a local DB running on Linux while maintaining the exact
> same table structure. The local DB would likely be PostgreSQL or
> MySQL. (The overall goal is to make that data accessible via Zope.)
> That local DB could be read-only.

There are multiple ways to accomplish this task, and the way you choose
depends at least partially on the details of your situation.  Here's one
example: 
1. You have Oracle for Linux installed on your machine.
2. You have Postgre/My-SQL for Linux installed on your machine.
3. You build DCOracle on your machine.
4. You bulid the Postgre/My-SQL Python extensions on your machine.
5. You write a Python program to 
 a) log into the other database to extract the data
 b) log into your database to populate the data

Other options might include using mxODBC to interface with both databases,
or using Python to extract data to flat text files that will later be bulk
loaded, or using Python to extract data and write an SQL file of insert
statements.

> Does this sound reasonable?

Yes, depending upon which solution you choose.  The reasonable choice will
make sense given your resources (hardware and bandwidth), your experience
with Python and with databases, and the amount of data that you're trying
to mirror.

> Would it make a difference if Oracle was on NT instead of Solaris?

For most of the solutions proposed above...not much.  You could also
"push" the data from the machine where the Oracle database is
installed.  In that case, you may choose to use a different extension to
interface with Oracle on Windows NT, and you could play with COM on
Windows NT.  For the most part, it should be mostly transparent to your
code.

---Tom