Python row copier

Below is a script I wrote to copy a set of tables from one schema (or database) to another.

It simply takes every table in the source schema and builds up an array based copy for it, with the usage of bind variables and bulk inserting. It assumes the target table structures have already been built, but without data. It also doesn’t handle constraint violations, although that shouldn’t be too difficult to implement.

import cx_Oracle, sys

#-------------------------------------------------------------------------------------------
"""
#Author:	Steve Howard
#Date:		January 30, 2011
#Purpose:	Simple array based row copier.  It works best for smaller row sets (less
		than one million rows per table), as it reads an entire resultset into
		memory, inserts it into the target table, then commits and releases the
		memory.
		
		A possible enhancement to this design is to periodically perform a bulk
		insert into the target once the scan of the source table gets to a
		specified number of rows or size, and then clear the array and start
		reloading again.  This shouldn't be too difficult to implement, actually.
		
		Added what is above, so it should scale better.
"""
#------------------------------------------------------------------------------------------

source = cx_Oracle.connect("rep","rep")
select = source.cursor()
thistable = source.cursor()

select.execute("select table_name from user_tables")
target = cx_Oracle.connect("rep","rep")
insert = target.cursor()

found = False
thisrow=[]
for table in select:
  rows = []
  s = ""
  i = -1
  try:
    thistable.execute("select * from " + table[0])
    colcount = len(thistable.description)
    for col in range(colcount):
      i = i + 1
      s = s + ":b" + str(i) + ","
    s = s[0:len(s) - 1]
    found = False
    j = 0
    for row in thistable:
      j = j + 1
      # we have at least one row to copy
      found = True
      for col in range(colcount):
        thisrow.append(row[col])
      rows.append(thisrow)
      thisrow=[]
      if j % 1000 == 0:
        insert.prepare("insert into " + table[0] + " values(" + s + ")")
        insert.executemany(None, rows)
        target.commit()
        rows = []

  except:
    print str(sys.exc_info()[1])
  #only copy if we actually found data
  if found == True:
    insert.prepare("insert into " + table[0] + " values(" + s + ")")
    insert.executemany(None, rows)
    target.commit()

select.close()
source.close()
target.close()