Pushing data from Oracle to MySQL using Jython

For various reasons, I had a sudden need to move a bunch of data from an oracle box (located on a server in the states) to mysql (located on my laptop). Well, mainly so I could do development without being at work. Something to do with wanting to go home for the weekend.

Now, there being some 69895 rows, I wasn't about to do it by hand. So I had three options. Firstly dump the data using sqlplus, re-arrange it, and load it into mysql. Not terribly fun. The second option was to write a java app. Boring. So I thought I'd try something different. Push stuff around using jython. Here's the code. Note, no error handling as this is a throw-away once off. It is. Honest. :)

from com.ziclix.python.sql import zxJDBC


mysqlConn = zxJDBC.connect("jdbc:mysql://localhost/test", "<user>", "<pass>", "org.gjt.mm.mysql.Driver")
mysqlCursor = mysqlConn.cursor(1)


oracleConn = zxJDBC.connect("jdbc:oracle:thin:@<host>:<port>:<oid>","<user>", "<pass>", "oracle.jdbc.driver.OracleDriver")
oracleCursor = oracleConn.cursor(1)


oracleCursor.execute(" SELECT * FROM <table> ")


insert = "INSERT INTO <table> VALUES (?, ?, ?, ?, ?, ?, ?)"
values = oracleCursor.fetchone()
while None != values:
    print "Inserting ", values
    mysqlCursor.executemany(insert, values)
    values = oracleCursor.fetchone()


Not bad huh?

References:

• Database Development in Jython with zxJDBC By Robert Bill.
• Python Database API Specification 2.0

Last update: 15/09/2002; 10:23:17 PM.