Way to run 2 queries in MySQL sequentially: using cursor.execute

Question

I have a script that does two things: a) It reads a csv file (bank transactions) and populates it into a transaction table in a MySQL database. b) Update other columns in the transaction table according to the transaction description mapped to the mapping file (debit category, credit category, etc.).

The following is my script

import mysql.connector as msql
import pandas as pd
from mysql.connector import Error

transdata = pd.read_csv('updt_stat.csv', index_col=False, delimiter=',')
transdata.fillna(0, inplace=True)
transdata = transdata.sort_values('Txn Date')

try:
    conn = msql.connect(
    host = 'localhost',
    user = 'root',
    password = 'root',
    database = 'npalace'  
    )
    if conn.is_connected:
        cursor = conn.cursor()
        sql = "SET FOREIGN_KEY_CHECKS=0"
        cursor.execute(sql)
        print('Database Connected !')
except Error as e:
    print("Error connecting database", e)

for i,row in transdata.iterrows():
    sql = "INSERT INTO npalace.t_bank_pnb (txn_no, txn_date, descr, branch_name, cheque_no, \
        dr_amount, cr_amount, balance, updated_on) VALUES (%s,STR_TO_DATE(%s,'%d-%m-%Y'),%s,%s,%s,%s,%s,%s,curdate())"
    cursor.execute(sql, tuple(row))
    print('Record Inserted')
    conn.commit() 

sql1 = """
            UPDATE npalace.t_bank_pnb
            JOIN npalace.map_pnb ON npalace.map_pnb.descript LIKE CONCAT('%', npalace.t_bank_pnb.descr, '%')
            SET
                npalace.t_bank_pnb.dr_category = npalace.map_pnb.dr_cat,
                npalace.t_bank_pnb.cr_category = npalace.map_pnb.cr_cat,
                npalace.t_bank_pnb.flat_no = npalace.map_pnb.flat_num
            WHERE npalace.t_bank_pnb.updated_on = CURDATE()
        """   

cursor.execute(sql1)
conn.commit()

conn.close()

print(cursor.rowcount, "record(s) affected")

When I run the script, the first script - sql runs perfectly. However, the second script is not running or there are no changes in the table.

I ensured that all relevant tables exist in the database. I've also formatted the csv file correctly (I can share it if needed).

I think the problem may be in the syntax of the second query. This query takes a description string from the transaction table and sees if it has any substrings from another mapping table. When a match occurs, it extracts the other fields and copies them back to the transaction table.

Can someone please help me find the correct method?

Thanks in advance

Answer 1

Ok, after some research I found the error.

My gut feeling was right, this was a script error. The correct SQL query statement is as follows:

sql1 = """
   UPDATE npalace.t_bank_pnb
   JOIN npalace.map_pnb 
   SET npalace.t_bank_pnb.dr_category = npalace.map_pnb.dr_cat,
       npalace.t_bank_pnb.cr_category = npalace.map_pnb.cr_cat,
       npalace.t_bank_pnb.flat_no = npalace.map_pnb.flat_num
   WHERE npalace.t_bank_pnb.updated_on = CURDATE()
     AND npalace.t_bank_pnb.descr LIKE CONCAT('%', npalace.map_pnb.descript, '%')
"""

The key is to include the substring comparison in the WHERE operator.