0

I am trying to send INSERT statements to mysql from Python using pymysql. Based on some conditions if I find a particular file, I insert it's contents to it's corresponding table in database. Currently I have two tables CACCT and PAUTH. I have separate functions for table specific INSERTs. The functions are:

 1. load_json_sql_CACCT()
 2. load_json_sql_PAUTH()

My main function is recon().

mysql_conn_dev = mysql.connect(host="xxxx",
                     user="xxx",
                     db="xxx")

cur = mysql_conn_dev.cursor(mysql.cursors.DictCursor)

tables = ["CACCT", "PAUTH"]

def recon():
    global unzipped_flg
    unzipped_flg = ''
    for item in tables:
        name_pattern = item + '_' + recon_date + '.*'
        for subdir, dirs, files in os.walk(src):
            for file in files:
                filepath = subdir + os.sep + file
                if fnmatch.fnmatch(file, name_pattern) and os.stat(filepath).st_size != 0:
                    if not result_match.empty:

                        if item == 'PAUTH':
                            load_json_sql_PAUTH(item, src + item + '_MISSING_RECORDS_' + recon_date + '.json')
                        elif item == 'CACCT':
                            load_json_sql_CACCT(item, src + item + '_MISSING_RECORDS_' + recon_date + '.json')
                    else:
                        print('No mismatch for ' + item)

def load_json_sql_CACCT(item, json_file):
    missing_record_list = []
    with open(json_file) as f:
        for json_obj in f:
            data_dict = json.loads(json_obj)
            missing_record_list.append(data_dict)
    print(json_file)
    for record in missing_record_list:
        print(record)

        if item == 'CACCT':
            with mysql_conn_dev.cursor() as cursor:
                cur.execute(
                r"INSERT INTO sayyed_sandpit.CACCT_UNI (instanceNumber,classNumber,parentClassNumber,subLevel,subId,edition,oid,u_acct_no,u_astatus,u_cust_no,u_myGlobal,u_preferred,u_status) "
                r"VALUES ("
                r"CAST(substring_index(substring_index(%s, '.', 2), '.', -1) as unsigned integer)"
                r",substring_index(%s, '.', 1)"
                r",0"
                r",0"
                r",0"
                r",%s"
                r",CONCAT(%s, '.0.0.0')"
                r",%s"
                r",%s"
                r",%s"
                r",CASE WHEN %s is not null then CONCAT(TRIM(LEADING '[' FROM TRIM(TRAILING ']' FROM %s)), '.0.0.0') end"
                r",%s"
                r",%s)",
                (record["OID"], record["OID"], record["edition"], record["OID"], record["acct_no"],
                 record["astatus"], record["cust_no"], record["myGlobal"], record["myGlobal"], record["preferred"], record["status"]))
            mysql_conn_dev.commit()
            print("CACCT inserted")


def load_json_sql_PAUTH(item, json_file):
    missing_record_list = []
    with open(json_file) as f:
        for json_obj in f:
            data_dict = json.loads(json_obj)
            missing_record_list.append(data_dict)

    for record in missing_record_list:
        print(record)
        with mysql_conn_dev.cursor() as cursor:
            cursor.execute(
                r"INSERT INTO sayyed_sandpit.PAUTH_UNI (instanceNumber, classNumber, parentClassNumber, subLevel, subId, edition,...) "
                r"VALUES ()",
                (record["OID"], record["OID"], record["edition"], record["OID"],...))

        mysql_conn_dev.commit()



if __name__ == '__main__':
    recon()
    #load_json_sql_PAUTH()

    mysql_conn.close()

For some reason it has been failing only for:

load_json_sql_PAUTH()

If I execute load_json_sql_PAUTH() separately own it's own, it works perfectly but when I put it in the conditional logic in recon(), it fails by as loosing connection as soon as it hits the INSERT statement.

Below is the error message:

    load_json_sql_PAUTH(item, src + item + '_MISSING_RECORDS_' + recon_date + '.json')
  File "C:/Users/Shahsa/PycharmProjects/mymisc/recon_json.py", line 370, in load_json_sql_test
    cursor.execute(
  File "C:\Users\Shahsa\PycharmProjects\mymisc\venv\lib\site-packages\pymysql\cursors.py", line 170, in execute
    result = self._query(query)
  File "C:\Users\Shahsa\PycharmProjects\mymisc\venv\lib\site-packages\pymysql\cursors.py", line 328, in _query
    conn.query(q)
  File "C:\Users\Shahsa\PycharmProjects\mymisc\venv\lib\site-packages\pymysql\connections.py", line 517, in query
    self._affected_rows = self._read_query_result(unbuffered=unbuffered)
  File "C:\Users\Shahsa\PycharmProjects\mymisc\venv\lib\site-packages\pymysql\connections.py", line 732, in _read_query_result
    result.read()
  File "C:\Users\Shahsa\PycharmProjects\mymisc\venv\lib\site-packages\pymysql\connections.py", line 1075, in read
    first_packet = self.connection._read_packet()
  File "C:\Users\Shahsa\PycharmProjects\mymisc\venv\lib\site-packages\pymysql\connections.py", line 657, in _read_packet
    packet_header = self._read_bytes(4)
  File "C:\Users\Shahsa\PycharmProjects\mymisc\venv\lib\site-packages\pymysql\connections.py", line 706, in _read_bytes
    raise err.OperationalError(
pymysql.err.OperationalError: (2013, 'Lost connection to MySQL server during query')
smoshah
  • 55
  • 1
  • 9
  • is there any error in the mysql error log. – danblack Mar 31 '20 at 04:06
  • do you mean the log in database? I am new to mysql...I did try running SELECT * FROM mysql. general_log which returned nothing so I am guessing it's not turned on. I am using Windows... – smoshah Mar 31 '20 at 04:10
  • The log of the mysqld database. Not the queries in the general log. See the mysql documentation as where to find this on Window. Include its recently contents in your question. – danblack Mar 31 '20 at 04:14

1 Answers1

0

I fixed the problem by creating separate connections in each of the functions. Something similar is mentioned by @xvga in this question

smoshah
  • 55
  • 1
  • 9