2011-03-08

quick hack to roughly transfer table structures from Oracle to PostgreSQL

Sometimes you just wish to mirror some data in PostgreSQL, which is stored by rules you cannot influence in Oracle. (For full-scale migration also check out ora2pg, and you can also order professional migration services :) )

To transfer only a small number of table structures, I found some lines of Python really handy:
import cx_Oracle # I recommend using the unicode variant
cn=cx_Oracle.connect(u"user",u"pwd",u"tnsname")
cs=cn.cursor()
# just select one row from the table we want to mirror
# bonus point: with naming the columns we also can mirror only part of that table
cs.execute("select * from tabletomirror where rownum < 2")

# this defines a mapping between cx_Oracle types and PostgreSQL types.
# as the title states, we are talking about "roghly"
# improving the "numeric" by using precision, scale etc. is left as an exercise for the reader
# (as is mapping CLOBS and whatever else there might be .. :) )
ora2pg= {cx_Oracle.NUMBER:"numeric", cx_Oracle.UNICODE:"text", 
         cx_Oracle.STRING:"text",cx_Oracle.DATETIME:"timestamp"}


# cs.description contains a good-enough description of the oracle
# table structure
# YES, primary keys etc. are not transferred automatically ...
# that's why I call it a quick hack 
collist=[]
for oname, otyp, display_size, internal_size, \
   precision, scale, null_ok in cs.description:
    collist.append("{oname} {otype}".format(
                        oname=oname, otype=ora2pg[otyp]))

createtablecommand = """create table tabnameinpgsql as ({0});""".format(
                     ", ".join(collist))
print ctablecommand

1 comment: