MYSQL LOAD DATA IN FILE and get LAST_INSERT_ID and INSERT DATA into Another table

Question

I'm trying to kill multiple birds with 1 stone so to speak. I am using windows commandline mysql.exe to load local infile called from another program that I'm using and I have found there is 3 other tables that need to be updated and they use an auto incremented PK from the main table. table names are case_info, mitigation, and processing. FIELD NAMES in case_info are case_id, client_id, sales_data, expenses_data, and income_data. FIELD NAMES in mitigation are mit_id, client_id, and mitigation_data. FIELD NAMES in processing are proc_id, client_id, and processing_data. The command I am passing to the cli

mysql.exe -uUSER -pSOMEPASSWORD -hEXAMPLE.COM TABLENAME  -e \"load data local infile '" +leads+ "' 
into table clients 
fields terminated by ',' 
LINES TERMINATED BY '\r\n'
(first_name,address,city,state,zip,saledate,file_id,last_updated,last_updated_by);"+ chr(34)`

The client_id is AI,PK so I need to run an insert statement on the 3 tables listed above to insert text into the *_data fields and the client_id field on all of the 3 tables while doing the load data local infile after each record is loaded. Im not sure if that is possible or not if it isn't can someone help me craft a query to run all at once or to run after the load to get the client_id from clients table where it's not found on the other tables to be updated and insert the data? The data to be loaded into the other tables is the same for every record to start and updated from the main app.

Answer 1

After messing around for a few hours I was able to come up with a workable solution sloppy as it may be here it is. I tested it on temp tables. I did have to edit the DEFAULT value of the fields with the data which once the client_id is inserted it's fills the default value and solves my problem.

import mysql.connector
import pymysql
import sys
pathto = easygui.fileopenbox()
db = mysql.connector.connect(host='HOSTNAME', user='USER', passwd='PASSWORD', db='DATABASE')
mycursor = db.cursor()
mycursor.execute("select max(client_id) from c_temp")
myresult = mycursor.fetchone()
maxid = str(myresult).replace('(','')
maxid = maxid.replace(',','')
maxid = maxid.replace(')','')
path = pathto.replace('\\','/')
if maxid =='None':
    print('NO MAX ID FOUND')
    maxid = str(0)
print(maxid)
print(path)
#import leads
def csv_to_mysql(load_sql, host, user, password):
    try:
        con = pymysql.connect(host=host,
                                user=user,
                                password=password,
                                autocommit=True,
                                local_infile=1)
        print('Connected to DB: {}'.format(host))
        # Create cursor and execute Load SQL
        cursor = con.cursor()
        cursor.execute(load_sql)
        print('Successfully loaded the table from csv.')
        con.close()

    except Exception as e:
        print('Error: {}'.format(str(e)))
        sys.exit(1)

# Execution Example
load_sql = "LOAD DATA LOCAL INFILE '"+path+"' INTO TABLE hometrust.c_temp\
 fields terminated by ',' LINES TERMINATED BY '\r\n'(first_name,address,city,state,zip,saledate,file_id);"
host = 'HOSTNAME'
user = 'USER'
password = 'PASSWORD'
csv_to_mysql(load_sql, host, user, password)

db.autocommit=False
try:
 mycursor.execute("INSERT INTO case_temp (client_id) SELECT client_id FROM c_temp WHERE client_id >"+maxid+";")
 mycursor.execute("INSERT INTO mit_temp (client_id) SELECT client_id FROM c_temp WHERE client_id >"+maxid+";")
 mycursor.execute("INSERT INTO proc_temp (client_id) SELECT client_id FROM c_temp WHERE client_id >"+maxid+";")
 db.commit()
except:
 db.rollback()