Using Python to update Mysql Table with subqueries

Question

I am trying to update a table multiple times using for loop. My code is as follows:

prmtr='Energy Sales(GWh)'

try:
    connection = mysql.connector.connect(host='localhost',
                database='Tariff_True_Cost',
                user='root',
                password='2002LhrcanttGBHS2005')

    cursor = connection.cursor(buffered=True)
    mySql_insert_query = """UPDATE national_avg_tariff SET Value=(SELECT * FROM (SELECT(revenue_energy_inputs_for_cem.energy_mix_pctage.Percentage*input_and_assumptions_for_cem.total_disco_energy_losses.Total_Consumption_with_ATC_Losses) FROM                               revenue_energy_inputs_for_cem.energy_mix_pctage INNER JOIN input_and_assumptions_for_cem.total_disco_energy_losses ON                    revenue_energy_inputs_for_cem.energy_mix_pctage.FiscalYear=input_and_assumptions_for_cem.total_disco_energy_losses.Year AND
revenue_energy_inputs_for_cem.energy_mix_pctage.FiscalYear=%s AND revenue_energy_inputs_for_cem.energy_mix_pctage.Sector=%s AND revenue_energy_inputs_for_cem.energy_mix_pctage.Category=%s)temptbl5) where FiscalYear=%s AND Sector=%s AND Category=%s AND Parameter=%s;"""
    record = (year,sec,ct,year,sec,ct,prmtr,)
    
for year in FiscalYear:
        for sec in Sector:
            if sec=='Residential':
                for ct in Residential:
                    cursor.execute(mySql_insert_query,record,)
                    connection.commit()
                    print("Record inserted successfully into the table")
                
                    
            elif sec=='Commercial-A2':
                for ct in Commercial:
                    cursor.execute(mySql_insert_query,record,)
                    connection.commit() 
                    print("Record inserted successfully into the table")
                
            elif sec=='General Services-A3':
                for ct in General_Services:
                    cursor.execute(mySql_insert_query,record,)
                    connection.commit() 
                    print("Record inserted successfully into the table")
                
            elif sec=='Industrial':
                for ct in Industrial:
                    cursor.execute(mySql_insert_query,record,)
                    connection.commit() 
                    print("Record inserted successfully into the table")
                
            elif sec=='Single Point Supply for further distribution':
                for ct in Single_Point:
                    cursor.execute(mySql_insert_query,record,)
                    connection.commit()
                    print("Record inserted successfully into the table")
                
            elif sec=='Agricultural Tube-wells - Tariff D':
                for ct in Agricultural:
                    cursor.execute(mySql_insert_query,record,)
                    connection.commit()
                    print("Record inserted successfully into the table")
                
            else:
                for ct in Others:
                    cursor.execute(mySql_insert_query,record,)
                    connection.commit()
                    print("Record inserted successfully into the table")
        
except mysql.connector.Error as error:
        print("Failed to insert into MySQL table {}".format(error))
        
        
finally:
        if connection.is_connected():
            cursor.close()
            connection.close()
            print("MySQL connection is closed")

---

I get everything done. I get the message " Record inserted successfully into the table" number of times. But in MySQL, the table doesn't get updated.

Why??

Columns from two different tables belonging to two different schemas are being multiplied to get the value. If I make every parameter specific e.g. turning %s to specific year like 2024, another %s to a Sector like Industrial and another %s to a particular category like 'B2 Time of Use-Peak', then I get the value by running it in MySQL. But since the data is quite large, hence I am forced to use for loop in python to insert the data in mysql table.

Answer 1

update record before each query

record = (year,sec,ct,year,sec,ct,prmtr,)
cursor.execute(mySql_insert_query,record,)

It is running successfully now because each update is updating an empty set.