Database migration script

Question

I'm working on a database migration script written in python, which will take data from a MySQL database and insert them into a PostgreSQL database with a different Schema (different table structures, different datatypes and so on).

Performances does matter, since I will have to handle with sizable databases.

I use mysql.connector and psycopg2 adapters in order to make python talks with the two databases.

My problem is that I need performance, but also the power of modifying/converting mysql data before inserting rows in the new fresh database. I will give you an example in order to show why there actually is a conflict of interest.

Following is a snippet of code without performance optimization, but at least, where transformation/conversion of data was possible:

cur_msql.execute("SELECT customer_id, customer_name, contact_name, address, city, postal_code, country FROM mysqlcustomers")

for row in cur_msql:

  # /!\ checking if customer_name is NULL (NOT NULL field in destination database)
  if row['customer_name'] == NULL:
    row['customer_name'] = row['contact_name']

  try:
    cur_psql.execute("INSERT INTO psqlcustomers (customer_id, customer_name, contact_name, address, city, postal_code, country) \
                      VALUES (%(customer_id)s, %(customer_name)s, %(contact_name)s, %(address)s, %(city)s, %(postal_code)s, %(country)s)", row)
  except psycopg2.Error as e:
    print "cannot execute that query", e.pgerror
    sys.exit("leaving early the script")

As you can see here I have the possibility to make an if statement for each row retrieved from mysql, to e.g. put the value of a field inside another field (or casting field datatype and so on...). Which is something that I really need to do in my scenario (elaboration of db row).

Meanwhile, I have found out that performing many insert statement as above (each mysql row is inserted separately) is killing the performance.

So I'm thinking to switch to something like that, which prepares all the blocks of statements, and then insert it all in one block:

cur_msql.execute("SELECT customer_id, customer_name, contact_name, address, city, postal_code, country FROM mysqlcustomers")

args_str = ','.join(cur_psql.mogrify("(%(customer_id)s, %(customer_name)s, %(contact_name)s, %(address)s, %(city)s, %(postal_code)s, %(country)s)", x) for x in cur_msql)

try:
  cur_psql.execute("INSERT INTO psqlcustomers (customer_id, customer_name, contact_name, address, city, postal_code, country) \
                    VALUES " + args_str)
except psycopg2.Error as e:
  print "cannot execute that query", e.pgerror
  sys.exit("leaving early the script")

With this approach the script can be about 100x faster! But the problem with this is, that I can no more elaborate the data (transformation/conversion/casting...) because all the rows get inserted into an object and I'm not able to iterate through it anymore...

Is it possible to approach the problem in a way where I can:

1. Not kill the performance
2. Still elaborate/transform the data of each row?

Answer 1

There's no reason why you can't modify the data retrived from MySQL and still insert multiple rows at once into PostgreSQL, for example something like this:

fields = '''
customer_id customer_name contact_name address city postal_code country
'''.split()

cur_msql.execute("SELECT {} FROM mysqlcustomers".format(','.join(fields)))

rows = []
for row in cur_msql:
    if row['customer_name'] == NULL:
        row['customer_name'] = row['contact_name']
    rows.append(row)

params = '({})'.format(','.join('%s' for _ in fields))
query = "INSERT INTO psqlcustomers ({}) VALUES {}".format(
    ','.join(fields), ','.join(params for _ in rows))

cur_psql.execute(query, rows)

Answer 2

Performance is important, correctness is usually even more important. In your case I would possibly opt for a combined solution of transformation detailining and performance issue.

Build one block to handle all standard cases, that is for when the row['customer_name'] has good values. This will give you a bulk case for the primary part of your data. Next build a special case for when it is NULL, and handles this in a case by case basis like in your first example.

Experience usually dictates that 80 % of the migration work, takes 20 % of the time, whilst the remaining 20 % of work, take 80 % of the time.

Once you realise this, you can focus on writing good, precise and fast migration of the general cases, but make sure that you have routines and procedure for all the exceptions which will arise.