#!/usr/bin/env python import MySQLdb import sys devdb_host = "10.10.1.1" sdb_host = "10.10.10.1" devdb = MySQLdb.connect(devdb_host, "x", "xx!", "mydb") sdb = MySQLdb.connect(sdb_host, "x", "xx!", "mydb") sdb_cursor = sdb.cursor() devdb_cursor = devdb.cursor() userId = "00011379" delete_all = "delete a, b from base a left join detail b on a.baseSeq = b.baseSeq where a.userId = '" + userId + "'" select_base_where = "where recognitionDate>='20150601' and userId = '" + userId + "'" select_base_count = "select count(1) from base " + select_base_where sdb_cursor.execute(select_base_count) rowCount = sdb_cursor.fetchone()[0] print "target base count = "+str(rowCount) select_base = "select * from base " + select_base_where select_base_detail = "select * from base_detail where baseSeq = " insert_mbase = "insert into mbase (a, b, c) VALUES ('{0}', {1}, {2}, '{3}', {4}, '{5}', {6}, {7}, {8}, '{9}', '{10}', '{11}', null, null, 0)" insert_detail = "insert into finance.mbase_detail (a, b, c) VALUES ({0}, {1}, {2}, {3}, {4}, {5}, {6}, {7}, {8}, null, null)" # DELETE devdb_cursor.execute(delete_all) devdb.commit() # SELECT BASE sdb_cursor.execute(select_base) for r in sdb_cursor.fetchall(): # INSERT BASE insert_query = insert_mbase.format(r[1], r[2], r[3], r[4], r[5], r[6], r[7], r[8], r[9], r[10], r[11], r[12]) devdb_cursor.execute(insert_query) devdb_cursor.execute("select last_insert_id()"); baseSeq = devdb_cursor.fetchone()[0] # SELECT DETAILS sdb_cursor.execute(select_base_detail + str(r[0])) # INSERT DETAILS for pbd in sdb_cursor.fetchall(): query = insert_detail.format(baseSeq, pbd[2], pbd[3], pbd[4], pbd[5], pbd[6], pbd[7], pbd[8], pbd[9]) devdb_cursor.execute(query) devdb.commit() sys.stdout.write(str(baseSeq)+",") print "----" print "Success." devdb.close() sdb.close()