Checking a variable in a mysql table and adjusting numbers-CodePudding

I have a table in MySQL named set_payment7777. It stores the information of some customers. It stores their surname, an amount, and a variable. This variable is an enum and it can be either N or Y. What I am doing currently is to take the entries that have been done between 2 dates and write them into a file. The code is:

query = "SELECT customer_surname, amount, variable FROM set_payment7777 WHERE date_ >= %s AND date_ <= %s"
mycursor.execute(query, (from_date, to_date))
for row in mycursor:
    file.write("\t".join(map(str, row)))
    file.write("\n")

Now the difficult part, I want if the variable is set to Y I want it to multiply the amount by the number of months between these two dates.

I have achieved to get the number of months:

num_months = (to_date.year - from_date.year) * 12   (to_date.month - from_date.month)

but I can't check if the variable is set to Y to multiply the rows I am getting in the for loop by num_months. (if the variable is set to N I want to leave the amount as before) Note: there can be multiple entries that some might have the variable set to Y and some set to N

CodePudding user response：

Because the result is a container of lists, you use the numerical indexes to access the list elements. If this is too opaque and brittle, you can look into a DictCursor. For now, something like this should work:

query = """SELECT customer_surname, amount, variable 
           FROM set_payment7777 
           WHERE date_ >= %s AND date_ <= %s"""
mycursor.execute(query, (from_date, to_date))
for row in mycursor:
    if row[2].upper() == 'Y':  # i choose to do .upper() or .lower() just in case
        num_months = ((to_date.year - from_date.year) * 12   
                      (to_date.month - from_date.month))
        row[1] *= num_months 
    file.write("\t".join(map(str, row)))
    file.write("\n")

Also, you can look into using the csv module for writing a tab-delimited file. This would be the preferable way in general, I think, unless you had a specific need that couldn't be handled by that module.