Load table from Oracle to MYSQL using python

Tried below code to load rows from Source(Oracle) to Target(MYSQL)

for row in source_cursor:
        target_cursor.execute("INSERT INTO JOB (SUBJECT_AREA,WORKFLOW_NAME,VERSION_NUMBER,SUBJECT_ID,WORKFLOW_ID,WORKFLOW_RUN_ID,WORKLET_RUN_ID,CHILD_RUN_ID,INSTANCE_ID,INSTANCE_NAME,TASK_ID,TASK_TYPE_NAME,TASK_TYPE,START_TIME,END_TIME,RUN_ERR_CODE,RUN_ERR_MSG,RUN_STATUS_CODE,TASK_NAME,TASK_VERSION_DECIMAL,SERVER_ID,SERVER_NAME)   VALUES(:1,:2,:3,:4,:5,:6,:7,:8,:9,:10,:11,:12,:13,:14,:15,:16,:17,:18,:19,:20,:21,:22)",(row[0], row[1], row[2], row[3], row[4], row[5], row[6], row[7], row[8], row[9], row[10], row[11], row[12], row[13], row[14], row[15], row[16], row[17], row[18], row[19], row[20],row[21]))

Error

 File "/usr/local/lib/python2.7/site-packages/MySQLdb/cursors.py", line 187, in execute
    query = query % tuple([db.literal(item) for item in args])
TypeError: not all arguments converted during string formatting

Edited Updated Code_:-

SQL="SELECT SUBJECT_AREA, WORKFLOW_NAME, VERSION_NUMBER, SUBJECT_ID, WORKFLOW_ID, WORKFLOW_RUN_ID, WORKLET_RUN_ID, CHILD_RUN_ID, INSTANCE_ID, INSTANCE_NAME,TASK_ID, TASK_TYPE_NAME, TASK_TYPE, TO_CHAR(START_TIME, 'YYYY/MM/DD HH24:MI:SS'), TO_CHAR(END_TIME, 'YYYY/MM/DD HH24:MI:SS'), RUN_ERR_CODE, RUN_ERR_MSG, RUN_STATUS_CODE, TASK_NAME, TASK_VERSION_NUMBER, SERVER_ID, SERVER_NAME FROM JOB WHERE TRUNC(END_TIME) = TRUNC(SYSDATE) or END_TIME IS NULL"

source_cursor = connection.cursor()
source_query=source_cursor.execute(SQL)

target_cursor = myDB.cursor()
sql = """INSERT INTO JOB_target (SUBJECT_AREA, WORKFLOW_NAME, VERSION_NUMBER,SUBJECT_ID, WORKFLOW_ID, WORKFLOW_RUN_ID, WORKLET_RUN_ID,CHILD_RUN_ID, INSTANCE_ID, INSTANCE_NAME, TASK_ID, TASK_TYPE_NAME, TASK_TYPE, START_TIME, END_TIME, RUN_ERR_CODE,RUN_ERR_MSG, RUN_STATUS_CODE, TASK_NAME, TASK_VERSION_DECIMAL,SERVER_ID, SERVER_NAME)  VALUES({prms})""".format(prms=", ".join(['%s'] * 22))
target_cursor.executemany(sql, [row for row in source_cursor])
source_cursor.close()
target_cursor.close()
connection.close()

Error:

mysql_connection_v2.py:40: Warning: Data truncated for column 'START_TIME' at row 1
  target_cursor.executemany(sql, [row for row in source_cursor])
mysql_connection_v2.py:40: Warning: Data truncated for column 'START_TIME' at row 2
  target_cursor.executemany(sql, [row for row in source_cursor])
mysql_connection_v2.py:40: Warning: Data truncated for column 'START_TIME' at row 3
  target_cursor.executemany(sql, [row for row in source_cursor])
mysql_connection_v2.py:40: Warning: Data truncated for column 'START_TIME' at row 4

Answer

In Python, while all strive to adhere to PEP 249, no two DB-APIs are exactly the same especially in parameter implementation. Specifically, while the module, cxOracle, supports arbitrary placeholders like numbered sequence :1, :2, :3, etc. the module MySQLdb only supports %s or%(name)s parameter style.

Therefore, adjust prepared SQL statement accordingly. Additionally, consider format to dynamically write placeholders via a list (["%s", "%s", %s", ...]) to avoid long unnumbered write-out and executemany to avoid looping.

sql = """INSERT INTO JOB (SUBJECT_AREA, WORKFLOW_NAME, VERSION_NUMBER, 
                          SUBJECT_ID, WORKFLOW_ID, WORKFLOW_RUN_ID, WORKLET_RUN_ID, 
                          CHILD_RUN_ID, INSTANCE_ID, INSTANCE_NAME, TASK_ID, 
                          TASK_TYPE_NAME, TASK_TYPE, START_TIME, END_TIME, RUN_ERR_CODE,
                          RUN_ERR_MSG, RUN_STATUS_CODE, TASK_NAME, TASK_VERSION_DECIMAL, 
                          SERVER_ID, SERVER_NAME)   
         VALUES({prms})
      """.format(prms=", ".join(['%s'] * 22))

target_curosr.excecutemany(sql, tuple(source_cursor))
connection.commit()

But if you were using a loop, simply pass the row itself and not unpack each of its elements or use indexing to needed point:

for row in source_cursor:
     target_cursor.execute(sql, row)

for row in source_cursor:
     target_cursor.execute(sql, row[:21])