PYTHON : Looping counter (with embedded database select function)

I  am analyzing  the additions and deletions to a previous customer order. The analysis produces a summary COUNTER evaluation - for example,  added = ({10: 3, 11: 5, 12: 8, 13: 5})   - where the keys are the course numbers ordered and the values are the incremental number ordered. Deletes are treated the same way.  

To present the results, I need to use the course numbers to interrogate the database and extract info for each course number such as
NAME, Cost....

I have successfully written a basic code to operate on counter 'added' above. The code is shown below and the attached image 'results from testing.cgi' gives the results - this works fine.  

---------------------

I have taken this basic approach and embedded it into the application page. Unfortunately I keep getting the error :

File "C:/xampp2/htdocs/OFFICE_15/action_OrderFormTWO.cgi", line 225, in activityinfo
    %(courseNumber))
NameError: name 'courseNumber' is not defined



Spent two days trying to spot the problem on the application code. I am attaching this 'application code'. The code in question starts on line 281.

Hopefully you will spot the stupid mistake I am making in the application code !!

many thanks

james kane










Testing Code

#!C:\Python34\python.exe
import collections
import cgi,cgitb
import mysql.connector as conn
def htmlTop():
    print("""Content-type:text/html\n\n
        <!DOCTYPE html>
        <html lang="en">
            <head>
                <meta http-equiv="Content-Type" content="text/html;charset=iso-8859-1">
                <title> My Server-side template</title>
        </head>
        <body>""")

def htmlTail():
    print(""" its done</body>
        </html>""")

def connectDB():
    db=conn.connect(host='localhost' ,user='root' ,passwd='844cheminduplan' ,db='office')
    cursor = db.cursor()
    return db, cursor 

def activityinfo(db,cursor):
    cursor.execute("""select courseNumber,Activity, tariffstwelve, tariffsthirtysix from activities WHERE courseNumber = "%s" """
    %(courseNumber)) 
    activityinfo=cursor.fetchall()
    return activityinfo

#main program
if __name__== "__main__":
    try:
        htmlTop()
        added = ({10: 3, 11: 5, 12: 8, 13: 5}) 
        print(added[10], "this is added value of key 10")
        db,cursor=connectDB()
        for each in added:
            print(each,"key")
            print(added[each],"quantity")
            courseNumber = each
            print(courseNumber, "courseNumber")
            activityinfo(db,cursor)
            activitydata = activityinfo(db,cursor)
            print(activitydata[0][0])
            print(activitydata[0][1])
            print(activitydata[0][2])
            print(added[each]*activitydata[0][2])
        htmlTail()
    except:
        cgi.print_exception()

Open in new window



RESULTS FROM ABOVE CODE

results from testing.cgi
APPLICATION CODE

#!C:\Python34\python.exe
import collections
import cgi
import cgitb
import mysql.connector as conn
def htmlTop():
    print("""Content-type:text/html\n\n
        <!DOCTYPE html>
        <html lang="en">
            <head>
                <meta http-equiv="Content-Type" content="text/html;charset=iso-8859-1">
                <title> My Server-side template</title>
        </head>
        <body>""")

def htmlTail():
    print(""" its done</body>
        </html>""")

def connectDB():
    db=conn.connect(host='localhost' ,user='root' ,passwd='844cheminduplan' ,db='office')
    cursor = db.cursor()
    return db, cursor 
    

def getData():
    formData = cgi.FieldStorage()
    #selection = formData.getlist('selection')
    selection = formData.getlist('selection')
    selectionTWO = formData.getlist('selectionTWO')
    selectionTHREE = formData.getlist('selectionTHREE')
    memberID = formData.getlist('memberID')
    return selection,selectionTWO,selectionTHREE,memberID

# This function below is to select the T1ytd, T2ytd and T3ytd values from table registations. These are
# are to be used with the T1ytdlatest, T2ytdlatest, T3ytdlatest values to determine the delta
# purchases and also the revised values for T1ytd.... etc
def SelectfromDB(db,cursor):
    cursor.execute("""select T1ytd,T2ytd,T3ytd from registrations WHERE memberID = "%s" """
    %(memberID)) 
    order_ytd=cursor.fetchall()
    return order_ytd 




def killcommasselection(selection):
    newlist =[]
    for m in selection:
        MC= int(m[:])
        newlist.append(MC)
        selection = newlist
    return selection



def killcommasselectionTHREE(selectionTHREE):
    newlist =[]
    for m in selectionTHREE:
        MC= int(m[:])
        newlist.append(MC)
        selectionTHREE = newlist
    return selectionTHREE



def killcommasselectionTWO(selectionTWO):
    newlist =[]
    for m in selectionTWO:
        MC= int(m[:])
        newlist.append(MC)
        selectionTWO = newlist
    return selectionTWO


    
#def insertinfo2(db,cursor):
    #strSQL = "insert into registrations(memberID, T1ytdlatest, T2ytdlatest, T3ytdlatest, T1ytd, T2ytd, T3ytd) values ({0}, '{1}', '{2}', '{3}', '{1}', '{2}', '{3}')".format(mylist[0], mylist[1], mylist[2], mylist[3])
    #cursor.execute(strSQL)
    #db.commit()     
    #db.close() 
    

#-------------------------------------------------------------------------------------------------------------------    
# NOTE on the following two update functions.  UpdateInfo2 updates the columns T1ytdlatest,  T2ytdlatest, T3ytdlatest 
#wheras UpdateInfo1 updates T1ytd, T2ytd and T3ytd. Remember how this wworks  - the very first  update to Registrations
# is to the ytdlatest columns. Immediately after that the delta order and ytd orders and receipts are done by netting 
# together the values of ytd and tydlates columns. Initially for the first order the ytd colums are blank []'s.
# AFTER THE RECEIPT AND MEMBER STATUS ARE UPDATED - THEN - UpdateInfo1 is run to update ytd columns. At this moment 
#the ytd and ytdlatest columns are the same values.

#On the next update cycle, as for the first, the ytdlatest columns are updated first. Then the delta calculations are done
#for the recipts and after that, the ytd columns are updated.

# NOTE ALSO that mylist contains all the informaton for updating.

# Note on  structure of the update function.  The SET and WHERE lines use '{0}', '{1}', '{2}', '{3}' - think of these
# as buckets. We define the content of the buckets via the .format line. In this line we select the values to be stored
# in the buckets. The .format values need to be lined up with the names given to the {}'s. So for example mylist[0] must 
#be the last one of the format string as it needs to corrospond with the last item in the SET/WHERE lines above.
    
def UpdateInfo2(db,cursor):
    cursor.execute ("""
       UPDATE registrations
       SET  T1ytdlatest='{0}', T2ytdlatest='{1}', T3ytdlatest='{2}'
       WHERE memberID={3}
    """.format(mylist[1], mylist[2], mylist[3],mylist[0]))
    db.commit()
    db.close 
    
def UpdateInfo1(db,cursor):
    cursor.execute ("""
       UPDATE registrations
       SET  T1ytd='{0}', T2ytd='{1}', T3ytd='{2}'
       WHERE memberID={3}
    """.format(mylist[1], mylist[2], mylist[3],mylist[0]))
    db.commit()
    db.close
#-----------------------------------------------------------------------------------------------------

def deltacalculate (T1ytd,T2ytd,T3ytd,T1ytdlatest,T2ytdlatest,T3ytdlatest):
    T1delta_dropped=[x for x in T1ytd if not x in T1ytdlatest]
    T1delta_nochange=[x for x in T1ytd if x in T1ytdlatest]
    T1delta_added=[x for x in T1ytdlatest if not x in T1ytd]
    
    T2delta_dropped=[x for x in T2ytd if not x in T2ytdlatest]
    T2delta_nochange=[x for x in T2ytd if x in T2ytdlatest]
    T2delta_added=[x for x in T2ytdlatest if not x in T2ytd]
    
    T3delta_dropped=[x for x in T3ytd if not x in T3ytdlatest]
    T3delta_nochange=[x for x in T3ytd if x in T3ytdlatest]
    T3delta_added=[x for x in T3ytdlatest if not x in T3ytd] 
    
    dropped = T1delta_dropped + T2delta_dropped + T3delta_dropped
    added = T1delta_added + T2delta_added + T3delta_added
    nochange = T1delta_nochange + T2delta_nochange + T3delta_nochange    
    return (dropped, added, nochange)


def dropcounter(dropped, added):
    import collections
    add_cnt = collections.Counter(added)
    new_dropped = []
    for value in dropped:
        in_add_cnt = add_cnt.get(value, 0)
        if in_add_cnt:
            added.remove(value) # preserves the order, but is a little costly
            add_cnt[value] -= 1
        else:
            new_dropped.append(value)
    dropped =  new_dropped
    drop_cnt = collections.Counter(dropped)
    return drop_cnt,dropped

    
def addcounter(dropped, added):
    import collections
    drop_cnt = collections.Counter(dropped)
    new_added = []
    for value in added:
        in_drop_cnt = drop_cnt.get(value, 0)
        if in_drop_cnt:
            dropped.remove(value) # preserves the order, but is a little costly
            drop_cnt[value] -= 1
        else:
            new_added.append(value)
    added =  new_added
    add_cnt = collections.Counter(added)   
    return add_cnt,added

    
def nochangecounter(nochange):
    import collections
    nochange_cnt = collections.Counter(nochange)
    return nochange_cnt

def confirmpurchase():
    print("<table width='909' border='1' cellspacing='0' cellpadding='0' summary='qsfsdsddqssd'>")
    print("<caption align='left'>")
    print("Votre panier d'achet")
    print("</caption>")
    print("<tr>")
    print("<th width='153' scope='row'>Activité No.</th>")
    print("<td width='426'>Description </td>")
    print("<td width='51'>Trimestres</td>")
    #print("<td width='47'>T2</td>")
    #print("<td width='45'>T3</td>")
    print("<td width='134'>Prix (euro)</td>")
    print("</tr>")
    print("<tr>")
    print("<th scope='row'>&nbsp;</th>")
    print("<td>&nbsp;</td>")
    print("<td>&nbsp;</td>")
    #print("<td>&nbsp;</td>")
    #print("<td>&nbsp;</td>")
    print("<td>&nbsp;</td>")
    print("</tr>")
    print("</table>")
    print("<table width='911' border='0' cellspacing='0' cellpadding='0'>")
    print("<tr>")
    print("<th width='766' class='style2' scope='row'><div align='right'>Tarif de l'activity_Libre................</div></th>")
    print("<td width='145'>&nbsp;</td>")
    print("</tr>")
    print("<tr>")
    print("<th class='style2' scope='row'><div align='right' class='style2'>Tarif de l'adhésion.........................</div></th>")
    print("<td>&nbsp;</td>")
    print("</tr>")
    print("<tr>")
    print("<th class='style2' scope='row'><div align='right' class='style2'>Coût total.......................................</div></th>")
    print("<td>&nbsp;</td>")
    print("</tr>")
    print("</table>")




def activityinfo(db,cursor):
    cursor.execute("""select courseNumber,Activity, tariffstwelve, tariffsthirtysix from activities WHERE courseNumber = "%s" """
    %(courseNumber)) 
    activityinfo=cursor.fetchall()
    return activityinfo
    
#main program
if __name__== "__main__":
    try:
        htmlTop()
        db,cursor=connectDB()
        selection,selectionTWO,selectionTHREE,memberID = getData()
        killcommasselection(selection)
        selection = killcommasselection(selection)
        killcommasselectionTWO(selectionTWO)
        selectionTWO = killcommasselectionTWO(selectionTWO)
        killcommasselectionTHREE(selectionTHREE)
        selectionTHREE = killcommasselectionTHREE(selectionTHREE)
        memberID = ''.join(memberID)
        mylist =[memberID, selection, selectionTWO, selectionTHREE]
        
        T1ytdlatest = mylist[1]
        T2ytdlatest = mylist[2]
        T3ytdlatest = mylist[3]
        
        #print(T1ytdlatest, "T1ytdlatest")
        #print(T2ytdlatest, "T2ytdlatest")
        #print(T3ytdlatest, "T3ytdlatest")
        #print (mylist, "this is mylist,")
        print(memberID, "this is memberID")
        
        UpdateInfo2(db,cursor)
        
        #print("Update SUCCESSFULL")
        
        order_ytd = SelectfromDB(db,cursor)
        #print(order_ytd, "this is the list T1ytd, T2ytd, T3ytd from database")
        T1ytd=eval(order_ytd[0][0])
        T2ytd=eval(order_ytd[0][1])
        T3ytd=eval(order_ytd[0][2])
        
        #print(T1ytd, "T1ytd")
        #print(T2ytd, "T1ytd")
        #print(T3ytd, "T1ytd")
        
        dropped,added,nochange = deltacalculate (T1ytd,T2ytd,T3ytd,T1ytdlatest,T2ytdlatest,T3ytdlatest)
        #print(dropped, "dropped")
        #print(added, "added")
        #print(nochange, "nochange")
        
        
        drop_cnt,dropped = dropcounter(dropped,added)
        add_cnt,added = addcounter(dropped, added)
        nochange_cnt = nochangecounter(nochange)
        
        print (drop_cnt, "this is the drop count")
        #print(dropped,"this is dropped")
        print (add_cnt, "this is the add count")
        #print(added,"this is added")
        #print (nochange_cnt, "this is the nochange count")
        #print(nochange,"this is nochange")
        
        
        
        db,cursor=connectDB()
        if len(add_cnt) != 0:
            for each in add_cnt:
                courseNumber = each
                activityinfo(db,cursor)
                activitydata = activityinfo(db,cursor)
                print(activitydata[0][0])
                print(activitydata[0][1])
                print(activitydata[0][2])
                print(add_cnt[each])
                if add_cnt[each] == 3:
                    print(activitydata[0][3]) #using tariff36 pricing
                else:
                    print(add_cnt[each]*activitydata[0][2]) #using tariff12 pricing
                    
        db,cursor=connectDB()       
        if len(drop_cnt) != 0:
            for each in drop_cnt:
                courseNumber = each
                activityinfo(db,cursor)
                activitydata = activityinfo(db,cursor)
                print(activitydata[0][0])
                print(activitydata[0][1])
                print(activitydata[0][2])
                print(-drop_cnt[each])
                if drop_cnt[each] == 3:
                    print(-activitydata[0][3]) #using tariff36 pricing
                else:
                    print(-drop_cnt[each]*activitydata[0][2])  #using tariff12 pricing                        
            
            
        
        #confirmpurchase()
        
        htmlTail()
    except:
        cgi.print_exception()

Open in new window

jameskaneAsked:
Who is Participating?

[Product update] Infrastructure Analysis Tool is now available with Business Accounts.Learn More

x
I wear a lot of hats...

"The solutions and answers provided on Experts Exchange have been extremely helpful to me over the last few years. I wear a lot of hats - Developer, Database Administrator, Help Desk, etc., so I know a lot of things but not a lot about one thing. Experts Exchange gives me answers from people who do know a lot about one thing, in a easy to use platform." -Todd S.

gelonidaCommented:
You have a function, that tries to use the variable courseNumber) without having it declared.


what I propose is that you change the function


def activityinfo(db,cursor):

to

def activityinfo(db, cursor, courseNumber):

and that you change toe function call from

activityinfo(db,cursor) to

activityinfo(db, cursor, courseNumber)
jameskaneAuthor Commented:
Many thanks Gelonida, but no luck I'm afraid.

Here is the error message after making the changes

Error message
Here is the updated application page.  See lines 217, 300,301,285,286.
Updated application page

#!C:\Python34\python.exe
import collections
import cgi
import cgitb
import mysql.connector as conn
def htmlTop():
    print("""Content-type:text/html\n\n
        <!DOCTYPE html>
        <html lang="en">
            <head>
                <meta http-equiv="Content-Type" content="text/html;charset=iso-8859-1">
                <title> My Server-side template</title>
        </head>
        <body>""")

def htmlTail():
    print(""" its done</body>
        </html>""")

def connectDB():
    db=conn.connect(host='localhost' ,user='root' ,passwd='844cheminduplan' ,db='office')
    cursor = db.cursor()
    return db, cursor 
    

def getData():
    formData = cgi.FieldStorage()
    #selection = formData.getlist('selection')
    selection = formData.getlist('selection')
    selectionTWO = formData.getlist('selectionTWO')
    selectionTHREE = formData.getlist('selectionTHREE')
    memberID = formData.getlist('memberID')
    return selection,selectionTWO,selectionTHREE,memberID

# This function below is to select the T1ytd, T2ytd and T3ytd values from table registations. These are
# are to be used with the T1ytdlatest, T2ytdlatest, T3ytdlatest values to determine the delta
# purchases and also the revised values for T1ytd.... etc
def SelectfromDB(db,cursor):
    cursor.execute("""select T1ytd,T2ytd,T3ytd from registrations WHERE memberID = "%s" """
    %(memberID)) 
    order_ytd=cursor.fetchall()
    return order_ytd 




def killcommasselection(selection):
    newlist =[]
    for m in selection:
        MC= int(m[:])
        newlist.append(MC)
        selection = newlist
    return selection



def killcommasselectionTHREE(selectionTHREE):
    newlist =[]
    for m in selectionTHREE:
        MC= int(m[:])
        newlist.append(MC)
        selectionTHREE = newlist
    return selectionTHREE



def killcommasselectionTWO(selectionTWO):
    newlist =[]
    for m in selectionTWO:
        MC= int(m[:])
        newlist.append(MC)
        selectionTWO = newlist
    return selectionTWO


    
#def insertinfo2(db,cursor):
    #strSQL = "insert into registrations(memberID, T1ytdlatest, T2ytdlatest, T3ytdlatest, T1ytd, T2ytd, T3ytd) values ({0}, '{1}', '{2}', '{3}', '{1}', '{2}', '{3}')".format(mylist[0], mylist[1], mylist[2], mylist[3])
    #cursor.execute(strSQL)
    #db.commit()     
    #db.close() 
    

#-------------------------------------------------------------------------------------------------------------------    
# NOTE on the following two update functions.  UpdateInfo2 updates the columns T1ytdlatest,  T2ytdlatest, T3ytdlatest 
#wheras UpdateInfo1 updates T1ytd, T2ytd and T3ytd. Remember how this wworks  - the very first  update to Registrations
# is to the ytdlatest columns. Immediately after that the delta order and ytd orders and receipts are done by netting 
# together the values of ytd and tydlates columns. Initially for the first order the ytd colums are blank []'s.
# AFTER THE RECEIPT AND MEMBER STATUS ARE UPDATED - THEN - UpdateInfo1 is run to update ytd columns. At this moment 
#the ytd and ytdlatest columns are the same values.

#On the next update cycle, as for the first, the ytdlatest columns are updated first. Then the delta calculations are done
#for the recipts and after that, the ytd columns are updated.

# NOTE ALSO that mylist contains all the informaton for updating.

# Note on  structure of the update function.  The SET and WHERE lines use '{0}', '{1}', '{2}', '{3}' - think of these
# as buckets. We define the content of the buckets via the .format line. In this line we select the values to be stored
# in the buckets. The .format values need to be lined up with the names given to the {}'s. So for example mylist[0] must 
#be the last one of the format string as it needs to corrospond with the last item in the SET/WHERE lines above.
    
def UpdateInfo2(db,cursor):
    cursor.execute ("""
       UPDATE registrations
       SET  T1ytdlatest='{0}', T2ytdlatest='{1}', T3ytdlatest='{2}'
       WHERE memberID={3}
    """.format(mylist[1], mylist[2], mylist[3],mylist[0]))
    db.commit()
    db.close 
    
def UpdateInfo1(db,cursor):
    cursor.execute ("""
       UPDATE registrations
       SET  T1ytd='{0}', T2ytd='{1}', T3ytd='{2}'
       WHERE memberID={3}
    """.format(mylist[1], mylist[2], mylist[3],mylist[0]))
    db.commit()
    db.close
#-----------------------------------------------------------------------------------------------------

def deltacalculate (T1ytd,T2ytd,T3ytd,T1ytdlatest,T2ytdlatest,T3ytdlatest):
    T1delta_dropped=[x for x in T1ytd if not x in T1ytdlatest]
    T1delta_nochange=[x for x in T1ytd if x in T1ytdlatest]
    T1delta_added=[x for x in T1ytdlatest if not x in T1ytd]
    
    T2delta_dropped=[x for x in T2ytd if not x in T2ytdlatest]
    T2delta_nochange=[x for x in T2ytd if x in T2ytdlatest]
    T2delta_added=[x for x in T2ytdlatest if not x in T2ytd]
    
    T3delta_dropped=[x for x in T3ytd if not x in T3ytdlatest]
    T3delta_nochange=[x for x in T3ytd if x in T3ytdlatest]
    T3delta_added=[x for x in T3ytdlatest if not x in T3ytd] 
    
    dropped = T1delta_dropped + T2delta_dropped + T3delta_dropped
    added = T1delta_added + T2delta_added + T3delta_added
    nochange = T1delta_nochange + T2delta_nochange + T3delta_nochange    
    return (dropped, added, nochange)


def dropcounter(dropped, added):
    import collections
    add_cnt = collections.Counter(added)
    new_dropped = []
    for value in dropped:
        in_add_cnt = add_cnt.get(value, 0)
        if in_add_cnt:
            added.remove(value) # preserves the order, but is a little costly
            add_cnt[value] -= 1
        else:
            new_dropped.append(value)
    dropped =  new_dropped
    drop_cnt = collections.Counter(dropped)
    return drop_cnt,dropped

    
def addcounter(dropped, added):
    import collections
    drop_cnt = collections.Counter(dropped)
    new_added = []
    for value in added:
        in_drop_cnt = drop_cnt.get(value, 0)
        if in_drop_cnt:
            dropped.remove(value) # preserves the order, but is a little costly
            drop_cnt[value] -= 1
        else:
            new_added.append(value)
    added =  new_added
    add_cnt = collections.Counter(added)   
    return add_cnt,added

    
def nochangecounter(nochange):
    import collections
    nochange_cnt = collections.Counter(nochange)
    return nochange_cnt

def confirmpurchase():
    print("<table width='909' border='1' cellspacing='0' cellpadding='0' summary='qsfsdsddqssd'>")
    print("<caption align='left'>")
    print("Votre panier d'achet")
    print("</caption>")
    print("<tr>")
    print("<th width='153' scope='row'>Activité No.</th>")
    print("<td width='426'>Description </td>")
    print("<td width='51'>Trimestres</td>")
    #print("<td width='47'>T2</td>")
    #print("<td width='45'>T3</td>")
    print("<td width='134'>Prix (euro)</td>")
    print("</tr>")
    print("<tr>")
    print("<th scope='row'>&nbsp;</th>")
    print("<td>&nbsp;</td>")
    print("<td>&nbsp;</td>")
    #print("<td>&nbsp;</td>")
    #print("<td>&nbsp;</td>")
    print("<td>&nbsp;</td>")
    print("</tr>")
    print("</table>")
    print("<table width='911' border='0' cellspacing='0' cellpadding='0'>")
    print("<tr>")
    print("<th width='766' class='style2' scope='row'><div align='right'>Tarif de l'activity_Libre................</div></th>")
    print("<td width='145'>&nbsp;</td>")
    print("</tr>")
    print("<tr>")
    print("<th class='style2' scope='row'><div align='right' class='style2'>Tarif de l'adhésion.........................</div></th>")
    print("<td>&nbsp;</td>")
    print("</tr>")
    print("<tr>")
    print("<th class='style2' scope='row'><div align='right' class='style2'>Coût total.......................................</div></th>")
    print("<td>&nbsp;</td>")
    print("</tr>")
    print("</table>")




def activityinfo(db,cursor,courseNumber):
    cursor.execute("""select courseNumber,Activity, tariffstwelve, tariffsthirtysix from activities WHERE courseNumber = "%s" """
    %(courseNumber)) 
    activityinfo=cursor.fetchall()
    return activityinfo
    
#main program
if __name__== "__main__":
    try:
        htmlTop()
        db,cursor=connectDB()
        selection,selectionTWO,selectionTHREE,memberID = getData()
        killcommasselection(selection)
        selection = killcommasselection(selection)
        killcommasselectionTWO(selectionTWO)
        selectionTWO = killcommasselectionTWO(selectionTWO)
        killcommasselectionTHREE(selectionTHREE)
        selectionTHREE = killcommasselectionTHREE(selectionTHREE)
        memberID = ''.join(memberID)
        mylist =[memberID, selection, selectionTWO, selectionTHREE]
        
        T1ytdlatest = mylist[1]
        T2ytdlatest = mylist[2]
        T3ytdlatest = mylist[3]
        
        #print(T1ytdlatest, "T1ytdlatest")
        #print(T2ytdlatest, "T2ytdlatest")
        #print(T3ytdlatest, "T3ytdlatest")
        #print (mylist, "this is mylist,")
        print(memberID, "this is memberID")
        
        UpdateInfo2(db,cursor)
        
        #print("Update SUCCESSFULL")
        
        order_ytd = SelectfromDB(db,cursor)
        #print(order_ytd, "this is the list T1ytd, T2ytd, T3ytd from database")
        T1ytd=eval(order_ytd[0][0])
        T2ytd=eval(order_ytd[0][1])
        T3ytd=eval(order_ytd[0][2])
        
        #print(T1ytd, "T1ytd")
        #print(T2ytd, "T1ytd")
        #print(T3ytd, "T1ytd")
        
        dropped,added,nochange = deltacalculate (T1ytd,T2ytd,T3ytd,T1ytdlatest,T2ytdlatest,T3ytdlatest)
        #print(dropped, "dropped")
        #print(added, "added")
        #print(nochange, "nochange")
        
        
        drop_cnt,dropped = dropcounter(dropped,added)
        add_cnt,added = addcounter(dropped, added)
        nochange_cnt = nochangecounter(nochange)
        
        print (drop_cnt, "this is the drop count")
        #print(dropped,"this is dropped")
        print (add_cnt, "this is the add count")
        #print(added,"this is added")
        #print (nochange_cnt, "this is the nochange count")
        #print(nochange,"this is nochange")
        
        
        
        db,cursor=connectDB()
        if len(add_cnt) != 0:
            for each in add_cnt:
                courseNumber = each
                activityinfo(db,cursor,courseNumber)
                activitydata = activityinfo(db,cursor,courseNumber)
                print(activitydata[0][0])
                print(activitydata[0][1])
                print(activitydata[0][2])
                print(add_cnt[each])
                if add_cnt[each] == 3:
                    print(activitydata[0][3]) #using tariff36 pricing
                else:
                    print(add_cnt[each]*activitydata[0][2]) #using tariff12 pricing
                    
        db,cursor=connectDB()       
        if len(drop_cnt) != 0:
            for each in drop_cnt:
                courseNumber = each
                activityinfo(db,cursor,courseNumber)
                activitydata = activityinfo(db,cursor,courseNumber)
                print(activitydata[0][0])
                print(activitydata[0][1])
                print(activitydata[0][2])
                print(-drop_cnt[each])
                if drop_cnt[each] == 3:
                    print(-activitydata[0][3]) #using tariff36 pricing
                else:
                    print(-drop_cnt[each]*activitydata[0][2])  #using tariff12 pricing                        
            
            
        
        #confirmpurchase()
        
        htmlTail()
    except:
        cgi.print_exception()

Open in new window

jameskaneAuthor Commented:
This is the error code when I run the cgi app page on WING IDE.


Content-type:text/html


        <!DOCTYPE html>
        <html lang="en">
            <head>
                <meta http-equiv="Content-Type" content="text/html;charset=iso-8859-1">
                <title> My Server-side template</title>
        </head>
        <body>
 this is memberID

<H3>Traceback (most recent call last):</H3>
<PRE>  File &quot;C:\xampp2\htdocs\OFFICE_15\action_OrderFormTWO .cgi&quot;, line 248, in &lt;module&gt;
    UpdateInfo2(db,cursor)
  File &quot;C:\xampp2\htdocs\OFFICE_15\action_OrderFormTWO .cgi&quot;, line 107, in UpdateInfo2
    &quot;&quot;&quot;.format(mylist[1], mylist[2], mylist[3],mylist[0]))
  File &quot;C:\Python34\lib\site-packages\mysql\connector\cursor.py&quot;, line 504, in execute
    self._handle_result(self._connection.cmd_query(stmt))
  File &quot;C:\Python34\lib\site-packages\mysql\connector\connection.py&quot;, line 766, in cmd_query
    result = self._handle_result(self._send_cmd(ServerCmd.QUERY, query))
  File &quot;C:\Python34\lib\site-packages\mysql\connector\connection.py&quot;, line 684, in _handle_result
    raise errors.get_exception(packet)
<B>mysql.connector.errors.ProgrammingError: 1064 (42000): You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near &#x27;&#x27; at line 3
</B></PRE>
#!C:\Python34\python.exe
import collections
import cgi
import cgitb
import mysql.connector as conn
def htmlTop():
    print("""Content-type:text/html\n\n
        <!DOCTYPE html>
        <html lang="en">
            <head>
                <meta http-equiv="Content-Type" content="text/html;charset=iso-8859-1">
                <title> My Server-side template</title>
        </head>
        <body>""")

def htmlTail():
    print(""" its done</body>
        </html>""")

def connectDB():
    db=conn.connect(host='localhost' ,user='root' ,passwd='844cheminduplan' ,db='office')
    cursor = db.cursor()
    return db, cursor 
    

def getData():
    formData = cgi.FieldStorage()
    #selection = formData.getlist('selection')
    selection = formData.getlist('selection')
    selectionTWO = formData.getlist('selectionTWO')
    selectionTHREE = formData.getlist('selectionTHREE')
    memberID = formData.getlist('memberID')
    return selection,selectionTWO,selectionTHREE,memberID

# This function below is to select the T1ytd, T2ytd and T3ytd values from table registations. These are
# are to be used with the T1ytdlatest, T2ytdlatest, T3ytdlatest values to determine the delta
# purchases and also the revised values for T1ytd.... etc
def SelectfromDB(db,cursor):
    cursor.execute("""select T1ytd,T2ytd,T3ytd from registrations WHERE memberID = "%s" """
    %(memberID)) 
    order_ytd=cursor.fetchall()
    return order_ytd 




def killcommasselection(selection):
    newlist =[]
    for m in selection:
        MC= int(m[:])
        newlist.append(MC)
        selection = newlist
    return selection



def killcommasselectionTHREE(selectionTHREE):
    newlist =[]
    for m in selectionTHREE:
        MC= int(m[:])
        newlist.append(MC)
        selectionTHREE = newlist
    return selectionTHREE



def killcommasselectionTWO(selectionTWO):
    newlist =[]
    for m in selectionTWO:
        MC= int(m[:])
        newlist.append(MC)
        selectionTWO = newlist
    return selectionTWO


    
#def insertinfo2(db,cursor):
    #strSQL = "insert into registrations(memberID, T1ytdlatest, T2ytdlatest, T3ytdlatest, T1ytd, T2ytd, T3ytd) values ({0}, '{1}', '{2}', '{3}', '{1}', '{2}', '{3}')".format(mylist[0], mylist[1], mylist[2], mylist[3])
    #cursor.execute(strSQL)
    #db.commit()     
    #db.close() 
    

#-------------------------------------------------------------------------------------------------------------------    
# NOTE on the following two update functions.  UpdateInfo2 updates the columns T1ytdlatest,  T2ytdlatest, T3ytdlatest 
#wheras UpdateInfo1 updates T1ytd, T2ytd and T3ytd. Remember how this wworks  - the very first  update to Registrations
# is to the ytdlatest columns. Immediately after that the delta order and ytd orders and receipts are done by netting 
# together the values of ytd and tydlates columns. Initially for the first order the ytd colums are blank []'s.
# AFTER THE RECEIPT AND MEMBER STATUS ARE UPDATED - THEN - UpdateInfo1 is run to update ytd columns. At this moment 
#the ytd and ytdlatest columns are the same values.

#On the next update cycle, as for the first, the ytdlatest columns are updated first. Then the delta calculations are done
#for the recipts and after that, the ytd columns are updated.

# NOTE ALSO that mylist contains all the informaton for updating.

# Note on  structure of the update function.  The SET and WHERE lines use '{0}', '{1}', '{2}', '{3}' - think of these
# as buckets. We define the content of the buckets via the .format line. In this line we select the values to be stored
# in the buckets. The .format values need to be lined up with the names given to the {}'s. So for example mylist[0] must 
#be the last one of the format string as it needs to corrospond with the last item in the SET/WHERE lines above.
    
def UpdateInfo2(db,cursor):
    cursor.execute ("""
       UPDATE registrations
       SET  T1ytdlatest='{0}', T2ytdlatest='{1}', T3ytdlatest='{2}'
       WHERE memberID={3}
    """.format(mylist[1], mylist[2], mylist[3],mylist[0]))
    db.commit()
    db.close 
    
def UpdateInfo1(db,cursor):
    cursor.execute ("""
       UPDATE registrations
       SET  T1ytd='{0}', T2ytd='{1}', T3ytd='{2}'
       WHERE memberID={3}
    """.format(mylist[1], mylist[2], mylist[3],mylist[0]))
    db.commit()
    db.close
#-----------------------------------------------------------------------------------------------------

def deltacalculate (T1ytd,T2ytd,T3ytd,T1ytdlatest,T2ytdlatest,T3ytdlatest):
    T1delta_dropped=[x for x in T1ytd if not x in T1ytdlatest]
    T1delta_nochange=[x for x in T1ytd if x in T1ytdlatest]
    T1delta_added=[x for x in T1ytdlatest if not x in T1ytd]
    
    T2delta_dropped=[x for x in T2ytd if not x in T2ytdlatest]
    T2delta_nochange=[x for x in T2ytd if x in T2ytdlatest]
    T2delta_added=[x for x in T2ytdlatest if not x in T2ytd]
    
    T3delta_dropped=[x for x in T3ytd if not x in T3ytdlatest]
    T3delta_nochange=[x for x in T3ytd if x in T3ytdlatest]
    T3delta_added=[x for x in T3ytdlatest if not x in T3ytd] 
    
    dropped = T1delta_dropped + T2delta_dropped + T3delta_dropped
    added = T1delta_added + T2delta_added + T3delta_added
    nochange = T1delta_nochange + T2delta_nochange + T3delta_nochange    
    return (dropped, added, nochange)


def dropcounter(dropped, added):
    import collections
    add_cnt = collections.Counter(added)
    new_dropped = []
    for value in dropped:
        in_add_cnt = add_cnt.get(value, 0)
        if in_add_cnt:
            added.remove(value) # preserves the order, but is a little costly
            add_cnt[value] -= 1
        else:
            new_dropped.append(value)
    dropped =  new_dropped
    drop_cnt = collections.Counter(dropped)
    return drop_cnt,dropped

    
def addcounter(dropped, added):
    import collections
    drop_cnt = collections.Counter(dropped)
    new_added = []
    for value in added:
        in_drop_cnt = drop_cnt.get(value, 0)
        if in_drop_cnt:
            dropped.remove(value) # preserves the order, but is a little costly
            drop_cnt[value] -= 1
        else:
            new_added.append(value)
    added =  new_added
    add_cnt = collections.Counter(added)   
    return add_cnt,added

    
def nochangecounter(nochange):
    import collections
    nochange_cnt = collections.Counter(nochange)
    return nochange_cnt

def confirmpurchase():
    print("<table width='909' border='1' cellspacing='0' cellpadding='0' summary='qsfsdsddqssd'>")
    print("<caption align='left'>")
    print("Votre panier d'achet")
    print("</caption>")
    print("<tr>")
    print("<th width='153' scope='row'>Activité No.</th>")
    print("<td width='426'>Description </td>")
    print("<td width='51'>Trimestres</td>")
    #print("<td width='47'>T2</td>")
    #print("<td width='45'>T3</td>")
    print("<td width='134'>Prix (euro)</td>")
    print("</tr>")
    print("<tr>")
    print("<th scope='row'>&nbsp;</th>")
    print("<td>&nbsp;</td>")
    print("<td>&nbsp;</td>")
    #print("<td>&nbsp;</td>")
    #print("<td>&nbsp;</td>")
    print("<td>&nbsp;</td>")
    print("</tr>")
    print("</table>")
    print("<table width='911' border='0' cellspacing='0' cellpadding='0'>")
    print("<tr>")
    print("<th width='766' class='style2' scope='row'><div align='right'>Tarif de l'activity_Libre................</div></th>")
    print("<td width='145'>&nbsp;</td>")
    print("</tr>")
    print("<tr>")
    print("<th class='style2' scope='row'><div align='right' class='style2'>Tarif de l'adhésion.........................</div></th>")
    print("<td>&nbsp;</td>")
    print("</tr>")
    print("<tr>")
    print("<th class='style2' scope='row'><div align='right' class='style2'>Coût total.......................................</div></th>")
    print("<td>&nbsp;</td>")
    print("</tr>")
    print("</table>")




def activityinfo(db,cursor,courseNumber):
    cursor.execute("""select courseNumber,Activity, tariffstwelve, tariffsthirtysix from activities WHERE courseNumber = "%s" """
    %(courseNumber)) 
    activityinfo=cursor.fetchall()
    return activityinfo
    
#main program
if __name__== "__main__":
    try:
        htmlTop()
        db,cursor=connectDB()
        selection,selectionTWO,selectionTHREE,memberID = getData()
        killcommasselection(selection)
        selection = killcommasselection(selection)
        killcommasselectionTWO(selectionTWO)
        selectionTWO = killcommasselectionTWO(selectionTWO)
        killcommasselectionTHREE(selectionTHREE)
        selectionTHREE = killcommasselectionTHREE(selectionTHREE)
        memberID = ''.join(memberID)
        mylist =[memberID, selection, selectionTWO, selectionTHREE]
        
        T1ytdlatest = mylist[1]
        T2ytdlatest = mylist[2]
        T3ytdlatest = mylist[3]
        
        #print(T1ytdlatest, "T1ytdlatest")
        #print(T2ytdlatest, "T2ytdlatest")
        #print(T3ytdlatest, "T3ytdlatest")
        #print (mylist, "this is mylist,")
        print(memberID, "this is memberID")
        
        UpdateInfo2(db,cursor)
        
        #print("Update SUCCESSFULL")
        
        order_ytd = SelectfromDB(db,cursor)
        #print(order_ytd, "this is the list T1ytd, T2ytd, T3ytd from database")
        T1ytd=eval(order_ytd[0][0])
        T2ytd=eval(order_ytd[0][1])
        T3ytd=eval(order_ytd[0][2])
        
        #print(T1ytd, "T1ytd")
        #print(T2ytd, "T1ytd")
        #print(T3ytd, "T1ytd")
        
        dropped,added,nochange = deltacalculate (T1ytd,T2ytd,T3ytd,T1ytdlatest,T2ytdlatest,T3ytdlatest)
        #print(dropped, "dropped")
        #print(added, "added")
        #print(nochange, "nochange")
        
        
        drop_cnt,dropped = dropcounter(dropped,added)
        add_cnt,added = addcounter(dropped, added)
        nochange_cnt = nochangecounter(nochange)
        
        print (drop_cnt, "this is the drop count")
        #print(dropped,"this is dropped")
        print (add_cnt, "this is the add count")
        #print(added,"this is added")
        #print (nochange_cnt, "this is the nochange count")
        #print(nochange,"this is nochange")
        
        
        
        db,cursor=connectDB()
        if len(add_cnt) != 0:
            for each in add_cnt:
                courseNumber = each
                activityinfo(db,cursor,courseNumber)
                activitydata = activityinfo(db,cursor,courseNumber)
                print(activitydata[0][0])
                print(activitydata[0][1])
                print(activitydata[0][2])
                print(add_cnt[each])
                if add_cnt[each] == 3:
                    print(activitydata[0][3]) #using tariff36 pricing
                else:
                    print(add_cnt[each]*activitydata[0][2]) #using tariff12 pricing
                    
        db,cursor=connectDB()       
        if len(drop_cnt) != 0:
            for each in drop_cnt:
                courseNumber = each
                activityinfo(db,cursor,courseNumber)
                activitydata = activityinfo(db,cursor,courseNumber)
                print(activitydata[0][0])
                print(activitydata[0][1])
                print(activitydata[0][2])
                print(-drop_cnt[each])
                if drop_cnt[each] == 3:
                    print(-activitydata[0][3]) #using tariff36 pricing
                else:
                    print(-drop_cnt[each]*activitydata[0][2])  #using tariff12 pricing                        
            
            
        
        #confirmpurchase()
        
        htmlTail()
    except:
        cgi.print_exception()

Open in new window

Protecting & Securing Your Critical Data

Considering 93 percent of companies file for bankruptcy within 12 months of a disaster that blocked access to their data for 10 days or more, planning for the worst is just smart business. Learn how Acronis Backup integrates security at every stage

gelonidaCommented:
Is the source code really the code, that you execute?

The error message (the back trace) does not seem to match your code.
I don't see a call to confirmpurchase() in line 300
According to the error message you seem to call the function  activity_info()  in line 196 but you do NOT pass the parameter courseNumber as I suggested.


The traceback of your other post seems completely unrelated and indicates, that you have an invalid SQL query.

Experts Exchange Solution brought to you by

Your issues matter to us.

Facing a tech roadblock? Get the help and guidance you need from experienced professionals who care. Ask your question anytime, anywhere, with no hassle.

Start your 7-day free trial
jameskaneAuthor Commented:
Yes, the code I attached in response Your Comment 2015-07-06 at 11:55:22ID: 40868155
is the code I run. The function appears (in the form you suggested) in lines 217,285,286,300,301.

confirmpurchase() is on line 313 and was commented out, so it does not run. I do not understand why it is called on the error message - and line 196 is just html table code.

activityinfo(db, cursor) does not appear in the code I sent you.

I just do not understand the trace I'm afraid.

The IDE error messge - referring to the SQL syntax error is not a syntax error I now believe. It is an error coming from the fact that there is no value available for courseNumber.
jameskaneAuthor Commented:
woops, I think I may have found the problem thanks  to your question if I am running the same code I sent you.  Get back in a few minutes.
jameskaneAuthor Commented:
FIXED !!!!

Problem was that I made a naming mistake in saving the page code. I saved it as
action_OrderFormTWO .cgi ---------------- note the space between the O and the .cgi . Ahhhh!!!
Should have been saved as action_OrderFormTWO.cgi.  

So the application, using this page continued to get the OLD page without the space - while I was editing the page with the space in the name .

Thank you very much for pointing me to the problem !!!

james
jameskaneAuthor Commented:
many thanks for your patience and insight !
It's more than this solution.Get answers and train to solve all your tech problems - anytime, anywhere.Try it for free Edge Out The Competitionfor your dream job with proven skills and certifications.Get started today Stand Outas the employee with proven skills.Start learning today for free Move Your Career Forwardwith certification training in the latest technologies.Start your trial today
Python

From novice to tech pro — start learning today.