Start Free Trial

asked on

Problem inserting PYTHON list into mysql table.

I have a function, shown below, which fails to insert two items into a mysql table (chequemgmt). memberID and cheques are the mysql columns, with memberID being the key. I attach the table to show their definitions.
The values used in the format part of the insert function are memberID and Cheques_Used. Both these variables exist and are available before the function is used. Typical values are

memberID = 4545511

Cheques_Used = [['1', 'CAILLOT', 'james', 'bnp', '54545', '120', 'Mars', '369673', 'pre_regist', 'septembre 24, 2015'], ['2', 'CAILLOT', 'james', 'bnp', '85475', '100', 'Fevrier', '369673', 'pre_regist', 'septembre 24, 2015'], ['3', 'CAILLOT', 'CAILLOT', 'bnp', '85555', '50', 'Septembre', '369673', 'pre_regist', 'septembre 24, 2015']] ..... this is a list with 3 sublists - each sublist has 11 items.

I am attaching the error message received.

Hopefully you can spot the silly mistake I have obviously made !!

Thanks

def InsertCheques(db,cursor):
    strSQL = "insert into chequemgmt(memberID, cheques) values ({0}, {1})".format(memberID, Checks_Used)
    cursor.execute(strSQL)
    db.commit()     
    db.close()

Open in new window

User generated image

User generated image

mysql_table.jpg

Looks like you need to escape the list once you format it as a string. Try printing out the final query string and post that here.

ASKER

Thanks very much Steve for the help. Not quite sure what it is you want. Here is an example of a list which should be uploaded to the database

[['1', 'CAILLOT', 'james', 'bnp', '4544', '120', 'Fevrier', '369673', 'pre_regist', 'septembre 25, 2015'], ['2', 'CAILLOT', 'CAILLOT', 'bnp', '4544', '100', 'Mars', '369673', 'pre_regist', 'septembre 25, 2015'], ['3', 'CAILLOT', 'CAILLOT', 'bnp', '4544', '122', 'Avril', '369673', 'pre_regist', 'septembre 25, 2015']]

Are you saying that is actually a string, even though it prints like a list ?

Sorry if I seem a bit slow at this --- not too much experience !!

james

[['1', 'CAILLOT', 'james', 'bnp', '4544', '120', 'Fevrier', '369673', 'pre_regist', 'septembre 25, 2015'], ['2', 'CAILLOT', 'CAILLOT', 'bnp', '4544', '100', 'Mars', '369673', 'pre_regist', 'septembre 25, 2015'], ['3', 'CAILLOT', 'CAILLOT', 'bnp', '4544', '122', 'Avril', '369673', 'pre_regist', 'septembre 25, 2015']] Checks_Used

Open in new window

ASKER

Steve, further to my last reply, I am attaching the code for the action page which has the purpose of processing the form which is fed into it PLUS creating a new entry into the chequemgmt table for the member (memberID) in question.

#!C:\Python34\python.exe
import cgi,cgitb
import mysql.connector as conn
import collections
import datetime
def htmlTop():
    print("""Content-type:text/html\n\n
        <!DOCTYPE html>
        <html lang="en">
            <head>
                <meta charset="UTF-8">
                <title> My Server-side template</title>
                <link href="style.css" rel="stylesheet" type="text/css">

                <script>var __adobewebfontsappname__="dreamweaver"</script>
                <script src="http://use.edgefonts.net/source-sans-pro:n2,i2,n3,i3,n4,i4,n6,i6,n7,i7,n9,i9:default;source-serif-pro:n4:default.js" type="text/javascript"></script>
        </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 InsertCheques(db,cursor):
    strSQL = "insert into chequemgmt(memberID, cheques) values ({0}, {1})".format(memberID, Checks_Used)
    cursor.execute(strSQL)
    db.commit()     
    db.close()
    
    
def InsertCheques2(db,cursor):#this works just as well as one above
    cursor.execute("""
            INSERT into chequemgmt (memberID, cheques)
            VALUES ({0}, {1})
            """.format(memberID, Checks_Used))
    db.commit()
    db.close()

def page():
    print("<div id='wrapper'> ")
    
    print("<header>")
    print("<h2 id='stargazer'>") 
    print("ABAC APPLICATION")
    print("</h2>")
    print("<nav>")
    print("<ul>")
    print("<li>")
    print("<a href='#'>")
    print("ABOUT")
    print("</a>")
    print("</li>")
    print("<li>")
    print("<a href='#'>")
    print("IMAGES")
    print("</a>")
    print("<li>")
    print("<a href='#'>")
    print("ARTICLES")
    print("</a>")
    print("<li>")
    print("</ul>")
    print("</nav>")
    print("</header>")
    print("<div id='more'>")
    print("<hr>")
    print("</div>")
    print("<div id='content1'>")   
    print("<p>")
    print("Lorem ipsum dolor sit amet, consectetur adipiscing elit. Proin at molestie sem, rhoncus luctus dui. Nulla id felis lectus. Nulla vitae pellentesque nisl. Pellentesque ullamcorper erat in orci tempus placerat sit amet a magna. Duis eu vulputate tortor. Curabitur sollicitudin eu sapien nec malesuada. Mauris nec massa arcu. In rutrum orci nibh, ut iaculis nisl bibend. Lorem ipsum dolor sit amet, consectetur adipiscing elit. Proin at molestie sem, rhoncus luctus dui. Nulla id felis lectus. Nulla vitae pellentesque nisl. Pellentesque ullamcorper erat in orci tempus placerat sit amet a magna. Duis eu vulputate tortor. Curabitur sollicitudin eu sapien nec malesuada. Mauris nec massa arcu. In rutrum orci nibh, ut iaculis nisl bibend.") 
    print("</p>")
    print("</div>")
    
    print('</div>')    


#main program
if __name__== "__main__":
    try:
        htmlTop()
        form = cgi.FieldStorage() 
            # Get data from fields
        i = form.getvalue('i')
        Nom = form.getvalue('Nom')
        Nom_Account = form.getvalue('Nom_Account')
        Bank = form.getvalue('Bank')
        Cheque_Num = form.getvalue('Cheque_Num')
        Cheque_Value = form.getvalue('Cheque_Value')
        Month = form.getvalue('Month')
        memberID = form.getvalue('memberID')
        Status = form.getvalue('Status')
        datestamp = form.getvalue('datestamp')
        #above are the variables and values passed froom registration_finalpage.cgi
        
        Checkslist = []
        Checkslist.append(i)
        Checkslist.append(Nom)
        Checkslist.append(Nom_Account)
        Checkslist.append(Bank)
        Checkslist.append(Cheque_Num)
        Checkslist.append(Cheque_Value)
        Checkslist.append(Month)
        #below you have 3 hidden fields from form on registration_finalpage.cgi
        Checkslist.append(memberID)
        Checkslist.append(Status)
        Checkslist.append(datestamp)
        #above we have made the two dimensional list 'Checkslist' from the variables passed to us
        #each sublist is a list of 5 values for the variable it represents
        #its now necessary to convert these lists into lists which have values of each of the variables
        # below we use LIST COMPREHENSIONS to create 5 individual lists which have values for each of the variables 
        
        Firstcheck = [row[0] for row in Checkslist]
        Secondcheck = [row[1] for row in Checkslist]
        Thirdcheck = [row[2] for row in Checkslist]
        Fourthcheck = [row[3] for row in Checkslist]
        Fifthcheck = [row[4] for row in Checkslist]
        
        #now we consolidate these lists in one two dimensional list called checks
        
        Checks = []
        Checks.append(Firstcheck)
        Checks.append(Secondcheck)
        Checks.append(Thirdcheck)
        Checks.append(Fourthcheck)
        Checks.append(Fifthcheck)
        
        
        
        print(i, "i")
        print(Nom, "Nom")
        print(Nom_Account, "Nom_Account")
        print(Bank, "bank")
        print(Cheque_Num, "Cheque_Num")
        print(Cheque_Value, "Cheque_Value")
        print(Month, "Month")
        print(Status, "Status")
        print(memberID, "memberID")
        print(datestamp, "THIS IS DATESTAMP")
        print(Checkslist, "This is the list of the checks")
        
        print(Firstcheck, "this is first cheque")
        print(Secondcheck, "this is second cheque")
        print(Thirdcheck, "this is third cheque")
        print(Fourthcheck, "this is fourth cheque")
        print(Fifthcheck, "this is fifth cheque")
        print(Checks, "!!!!!!!!!!!!!!!")
        
        # Now we take the Checks list created above and create a new one 'Checks_Used' which does not have any
        # of the lists which contain a cheque_value with 0 value.  The form in registration_finalpage.cgi has 5 form elements - normally
        # only 3 will be used. Each form element has the Cheque_Values default set at 0.  So, if they are not used
        # they retain that 0 value and then get deleted by the process below
        
        Checks_Used = [s for s in Checks if s[5] != '0' ] 
        
        #Checks_Used = [['1', 'CAILLOT', 'CAILLOT', 'bnp', '85555', '50', 'Septembre', '369673', 'pre_regist', 'septembre 24, 2015'] ]
        print (Checks_Used, "Checks_Used")        
     
        
        #db,cursor=connectDB()
        #InsertCheques2(db,cursor)              
        
        page()
        db,cursor=connectDB()
        
        InsertCheques2(db,cursor)
        print(Nom, "this is Nom")

        htmlTail()
    except:
        cgi.print_exception()

Open in new window

Sorry about the all the comments and stuff, in the code, but its my 'development' page

A Python list can not be inserted into mysql directly. You will have to dump it to a json before inserting.

import json

mylist = json.dumps(my_list)

Now it will be a valid JSON string. Notice all keys and values will have double quotes not singles.

you can insert that into mysql.

James, try the below. Steve was partially correct: you most probably need to escape your string before insert. The thing is that mysql.connector does it for you, if you use the proper syntax.

#!C:\Python34\python.exe
import cgi,cgitb
import mysql.connector as conn
import collections
import datetime
def htmlTop():
    print("""Content-type:text/html\n\n
        <!DOCTYPE html>
        <html lang="en">
            <head>
                <meta charset="UTF-8">
                <title> My Server-side template</title>
                <link href="style.css" rel="stylesheet" type="text/css">

                <script>var __adobewebfontsappname__="dreamweaver"</script>
                <script src="http://use.edgefonts.net/source-sans-pro:n2,i2,n3,i3,n4,i4,n6,i6,n7,i7,n9,i9:default;source-serif-pro:n4:default.js" type="text/javascript"></script>
        </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 InsertCheques(db,cursor):
    strSQL = "insert into chequemgmt(memberID, cheques) values ({0}, {1})".format(memberID, Checks_Used)
    cursor.execute(strSQL)
    db.commit()     
    
    
def InsertCheques2(db,cursor):#this works just as well as one above
    cursor.execute("""
            INSERT into chequemgmt (memberID, cheques)
            VALUES ({0}, {1})
            """.format(memberID, Checks_Used.replace("'","\'")))
    db.commit()

def InsertCheques3(db,cursor):#this works just as well as one above
    cursor.execute("INSERT into chequemgmt (memberID, cheques) VALUES (%s, %s)",(memberID, Checks_Used))
    db.commit()


def page():
    print("<div id='wrapper'> ")
    
    print("<header>")
    print("<h2 id='stargazer'>") 
    print("ABAC APPLICATION")
    print("</h2>")
    print("<nav>")
    print("<ul>")
    print("<li>")
    print("<a href='#'>")
    print("ABOUT")
    print("</a>")
    print("</li>")
    print("<li>")
    print("<a href='#'>")
    print("IMAGES")
    print("</a>")
    print("<li>")
    print("<a href='#'>")
    print("ARTICLES")
    print("</a>")
    print("<li>")
    print("</ul>")
    print("</nav>")
    print("</header>")
    print("<div id='more'>")
    print("<hr>")
    print("</div>")
    print("<div id='content1'>")   
    print("<p>")
    print("Lorem ipsum dolor sit amet, consectetur adipiscing elit. Proin at molestie sem, rhoncus luctus dui. Nulla id felis lectus. Nulla vitae pellentesque nisl. Pellentesque ullamcorper erat in orci tempus placerat sit amet a magna. Duis eu vulputate tortor. Curabitur sollicitudin eu sapien nec malesuada. Mauris nec massa arcu. In rutrum orci nibh, ut iaculis nisl bibend. Lorem ipsum dolor sit amet, consectetur adipiscing elit. Proin at molestie sem, rhoncus luctus dui. Nulla id felis lectus. Nulla vitae pellentesque nisl. Pellentesque ullamcorper erat in orci tempus placerat sit amet a magna. Duis eu vulputate tortor. Curabitur sollicitudin eu sapien nec malesuada. Mauris nec massa arcu. In rutrum orci nibh, ut iaculis nisl bibend.") 
    print("</p>")
    print("</div>")
    
    print('</div>')    


#main program
if __name__== "__main__":
    try:
        htmlTop()
        form = cgi.FieldStorage() 
            # Get data from fields
        i = form.getvalue('i')
        Nom = form.getvalue('Nom')
        Nom_Account = form.getvalue('Nom_Account')
        Bank = form.getvalue('Bank')
        Cheque_Num = form.getvalue('Cheque_Num')
        Cheque_Value = form.getvalue('Cheque_Value')
        Month = form.getvalue('Month')
        memberID = form.getvalue('memberID')
        Status = form.getvalue('Status')
        datestamp = form.getvalue('datestamp')
        #above are the variables and values passed froom registration_finalpage.cgi
        
        Checkslist = []
        Checkslist.append(i)
        Checkslist.append(Nom)
        Checkslist.append(Nom_Account)
        Checkslist.append(Bank)
        Checkslist.append(Cheque_Num)
        Checkslist.append(Cheque_Value)
        Checkslist.append(Month)
        #below you have 3 hidden fields from form on registration_finalpage.cgi
        Checkslist.append(memberID)
        Checkslist.append(Status)
        Checkslist.append(datestamp)
        #above we have made the two dimensional list 'Checkslist' from the variables passed to us
        #each sublist is a list of 5 values for the variable it represents
        #its now necessary to convert these lists into lists which have values of each of the variables
        # below we use LIST COMPREHENSIONS to create 5 individual lists which have values for each of the variables 
        
        Firstcheck = [row[0] for row in Checkslist]
        Secondcheck = [row[1] for row in Checkslist]
        Thirdcheck = [row[2] for row in Checkslist]
        Fourthcheck = [row[3] for row in Checkslist]
        Fifthcheck = [row[4] for row in Checkslist]
        
        #now we consolidate these lists in one two dimensional list called checks
        
        Checks = []
        Checks.append(Firstcheck)
        Checks.append(Secondcheck)
        Checks.append(Thirdcheck)
        Checks.append(Fourthcheck)
        Checks.append(Fifthcheck)
        
        
        
        print(i, "i")
        print(Nom, "Nom")
        print(Nom_Account, "Nom_Account")
        print(Bank, "bank")
        print(Cheque_Num, "Cheque_Num")
        print(Cheque_Value, "Cheque_Value")
        print(Month, "Month")
        print(Status, "Status")
        print(memberID, "memberID")
        print(datestamp, "THIS IS DATESTAMP")
        print(Checkslist, "This is the list of the checks")
        
        print(Firstcheck, "this is first cheque")
        print(Secondcheck, "this is second cheque")
        print(Thirdcheck, "this is third cheque")
        print(Fourthcheck, "this is fourth cheque")
        print(Fifthcheck, "this is fifth cheque")
        print(Checks, "!!!!!!!!!!!!!!!")
        
        # Now we take the Checks list created above and create a new one 'Checks_Used' which does not have any
        # of the lists which contain a cheque_value with 0 value.  The form in registration_finalpage.cgi has 5 form elements - normally
        # only 3 will be used. Each form element has the Cheque_Values default set at 0.  So, if they are not used
        # they retain that 0 value and then get deleted by the process below
        
        Checks_Used = [s for s in Checks if s[5] != '0' ] 
        
        #Checks_Used = [['1', 'CAILLOT', 'CAILLOT', 'bnp', '85555', '50', 'Septembre', '369673', 'pre_regist', 'septembre 24, 2015'] ]
        print (Checks_Used, "Checks_Used")        
     
        
        #db,cursor=connectDB()
        #InsertCheques2(db,cursor)              
        
        page()
        db,cursor=connectDB()
        
        #InsertCheques2(db,cursor)
        InsertCheques3(db,cursor)
        print(Nom, "this is Nom")

        htmlTail()

	db.close()
    except:
        cgi.print_exception()

Open in new window

ASKER

Thanks Mark, I tried it but still have the problem.

Attached is the revised code page.

You see (lines 159,160)
Checks_Used = [s for s in Checks if s[5] != '0' ]
Checks_Used_json = json.dumps(Checks_Used) #json version

You see (lines 37 - 43) the revised insert function.

I get the same error - page, note that the list has be converted to json.

User generated image

#!C:\Python34\python.exe
import json
import cgi,cgitb
import mysql.connector as conn
import collections
import datetime
def htmlTop():
    print("""Content-type:text/html\n\n
        <!DOCTYPE html>
        <html lang="en">
            <head>
                <meta charset="UTF-8">
                <title> My Server-side template</title>
                <link href="style.css" rel="stylesheet" type="text/css">

                <script>var __adobewebfontsappname__="dreamweaver"</script>
                <script src="http://use.edgefonts.net/source-sans-pro:n2,i2,n3,i3,n4,i4,n6,i6,n7,i7,n9,i9:default;source-serif-pro:n4:default.js" type="text/javascript"></script>
        </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 InsertCheques(db,cursor):
    strSQL = "insert into chequemgmt(memberID, cheques) values ({0}, {1})".format(memberID, Checks_Used)
    cursor.execute(strSQL)
    db.commit()     
    db.close()
    
    
def InsertCheques2(db,cursor):#this works just as well as one above
    cursor.execute("""
            INSERT into chequemgmt (memberID, cheques)
            VALUES ({0}, {1})
            """.format(memberID, Checks_Used_json))
    db.commit()
    db.close()

def page():
    print("<div id='wrapper'> ")
    
    print("<header>")
    print("<h2 id='stargazer'>") 
    print("ABAC APPLICATION")
    print("</h2>")
    print("<nav>")
    print("<ul>")
    print("<li>")
    print("<a href='#'>")
    print("ABOUT")
    print("</a>")
    print("</li>")
    print("<li>")
    print("<a href='#'>")
    print("IMAGES")
    print("</a>")
    print("<li>")
    print("<a href='#'>")
    print("ARTICLES")
    print("</a>")
    print("<li>")
    print("</ul>")
    print("</nav>")
    print("</header>")
    print("<div id='more'>")
    print("<hr>")
    print("</div>")
    print("<div id='content1'>")   
    print("<p>")
    print("Lorem ipsum dolor sit amet, consectetur adipiscing elit. Proin at molestie sem, rhoncus luctus dui. Nulla id felis lectus. Nulla vitae pellentesque nisl. Pellentesque ullamcorper erat in orci tempus placerat sit amet a magna. Duis eu vulputate tortor. Curabitur sollicitudin eu sapien nec malesuada. Mauris nec massa arcu. In rutrum orci nibh, ut iaculis nisl bibend. Lorem ipsum dolor sit amet, consectetur adipiscing elit. Proin at molestie sem, rhoncus luctus dui. Nulla id felis lectus. Nulla vitae pellentesque nisl. Pellentesque ullamcorper erat in orci tempus placerat sit amet a magna. Duis eu vulputate tortor. Curabitur sollicitudin eu sapien nec malesuada. Mauris nec massa arcu. In rutrum orci nibh, ut iaculis nisl bibend.") 
    print("</p>")
    print("</div>")
    
    print('</div>')    


#main program
if __name__== "__main__":
    try:
        htmlTop()
        form = cgi.FieldStorage() 
            # Get data from fields
        i = form.getvalue('i')
        Nom = form.getvalue('Nom')
        Nom_Account = form.getvalue('Nom_Account')
        Bank = form.getvalue('Bank')
        Cheque_Num = form.getvalue('Cheque_Num')
        Cheque_Value = form.getvalue('Cheque_Value')
        Month = form.getvalue('Month')
        memberID = form.getvalue('memberID')
        Status = form.getvalue('Status')
        datestamp = form.getvalue('datestamp')
        #above are the variables and values passed froom registration_finalpage.cgi
        
        Checkslist = []
        Checkslist.append(i)
        Checkslist.append(Nom)
        Checkslist.append(Nom_Account)
        Checkslist.append(Bank)
        Checkslist.append(Cheque_Num)
        Checkslist.append(Cheque_Value)
        Checkslist.append(Month)
        #below you have 3 hidden fields from form on registration_finalpage.cgi
        Checkslist.append(memberID)
        Checkslist.append(Status)
        Checkslist.append(datestamp)
        #above we have made the two dimensional list 'Checkslist' from the variables passed to us
        #each sublist is a list of 5 values for the variable it represents
        #its now necessary to convert these lists into lists which have values of each of the variables
        # below we use LIST COMPREHENSIONS to create 5 individual lists which have values for each of the variables 
        
        Firstcheck = [row[0] for row in Checkslist]
        Secondcheck = [row[1] for row in Checkslist]
        Thirdcheck = [row[2] for row in Checkslist]
        Fourthcheck = [row[3] for row in Checkslist]
        Fifthcheck = [row[4] for row in Checkslist]
        
        #now we consolidate these lists in one two dimensional list called checks
        
        Checks = []
        Checks.append(Firstcheck)
        Checks.append(Secondcheck)
        Checks.append(Thirdcheck)
        Checks.append(Fourthcheck)
        Checks.append(Fifthcheck)
        
        
        
        print(i, "i")
        print(Nom, "Nom")
        print(Nom_Account, "Nom_Account")
        print(Bank, "bank")
        print(Cheque_Num, "Cheque_Num")
        print(Cheque_Value, "Cheque_Value")
        print(Month, "Month")
        print(Status, "Status")
        print(memberID, "memberID")
        print(datestamp, "THIS IS DATESTAMP")
        print(Checkslist, "This is the list of the checks")
        
        print(Firstcheck, "this is first cheque")
        print(Secondcheck, "this is second cheque")
        print(Thirdcheck, "this is third cheque")
        print(Fourthcheck, "this is fourth cheque")
        print(Fifthcheck, "this is fifth cheque")
        print(Checks, "!!!!!!!!!!!!!!!")
        
        # Now we take the Checks list created above and create a new one 'Checks_Used' which does not have any
        # of the lists which contain a cheque_value with 0 value.  The form in registration_finalpage.cgi has 5 form elements - normally
        # only 3 will be used. Each form element has the Cheque_Values default set at 0.  So, if they are not used
        # they retain that 0 value and then get deleted by the process below
        
        Checks_Used = [s for s in Checks if s[5] != '0' ] 
        Checks_Used_json = json.dumps(Checks_Used) #json version
        
        #Checks_Used = [['1', 'CAILLOT', 'CAILLOT', 'bnp', '85555', '50', 'Septembre', '369673', 'pre_regist', 'septembre 24, 2015'] ]
        print (Checks_Used, "Checks_Used")        
     
        
        #db,cursor=connectDB()
        #InsertCheques2(db,cursor)              
        
        page()
        db,cursor=connectDB()
        
        InsertCheques2(db,cursor)
        print(Nom, "this is Nom")

        htmlTail()
    except:
        cgi.print_exception()

Open in new window

ASKER

Hi Walter, thanks for taking the time on this.

Unfortunately no success yet. I tried your code exactly as you sent it. See error message below

Then i used the code you sent again, but with an embedded json conversion as suggested by Mark. So this version has your changes and Marks json. Again - no luck. See error message below. Note that the input was indeed json input.

However, in both cases neither error points to sql syntax

I am also attaching the code you sent, with the json changes.

User generated image

User generated image

#!C:\Python34\python.exe
import json
import cgi,cgitb
import mysql.connector as conn
import collections
import datetime
def htmlTop():
    print("""Content-type:text/html\n\n
        <!DOCTYPE html>
        <html lang="en">
            <head>
                <meta charset="UTF-8">
                <title> My Server-side template</title>
                <link href="style.css" rel="stylesheet" type="text/css">

                <script>var __adobewebfontsappname__="dreamweaver"</script>
                <script src="http://use.edgefonts.net/source-sans-pro:n2,i2,n3,i3,n4,i4,n6,i6,n7,i7,n9,i9:default;source-serif-pro:n4:default.js" type="text/javascript"></script>
        </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 InsertCheques(db,cursor):
    strSQL = "insert into chequemgmt(memberID, cheques) values ({0}, {1})".format(memberID, Checks_Used)
    cursor.execute(strSQL)
    db.commit()     


def InsertCheques2(db,cursor):#this works just as well as one above
    cursor.execute("""
            INSERT into chequemgmt (memberID, cheques)
            VALUES ({0}, {1})
            """.format(memberID, Checks_Used.replace("'","\'")))
    db.commit()

def InsertCheques3(db,cursor):#this works just as well as one above
    cursor.execute("INSERT into chequemgmt (memberID, cheques) VALUES (%s, %s)",(memberID, Checks_Used_json))
    db.commit()


def page():
    print("<div id='wrapper'> ")

    print("<header>")
    print("<h2 id='stargazer'>") 
    print("ABAC APPLICATION")
    print("</h2>")
    print("<nav>")
    print("<ul>")
    print("<li>")
    print("<a href='#'>")
    print("ABOUT")
    print("</a>")
    print("</li>")
    print("<li>")
    print("<a href='#'>")
    print("IMAGES")
    print("</a>")
    print("<li>")
    print("<a href='#'>")
    print("ARTICLES")
    print("</a>")
    print("<li>")
    print("</ul>")
    print("</nav>")
    print("</header>")
    print("<div id='more'>")
    print("<hr>")
    print("</div>")
    print("<div id='content1'>")   
    print("<p>")
    print("Lorem ipsum dolor sit amet, consectetur adipiscing elit. Proin at molestie sem, rhoncus luctus dui. Nulla id felis lectus. Nulla vitae pellentesque nisl. Pellentesque ullamcorper erat in orci tempus placerat sit amet a magna. Duis eu vulputate tortor. Curabitur sollicitudin eu sapien nec malesuada. Mauris nec massa arcu. In rutrum orci nibh, ut iaculis nisl bibend. Lorem ipsum dolor sit amet, consectetur adipiscing elit. Proin at molestie sem, rhoncus luctus dui. Nulla id felis lectus. Nulla vitae pellentesque nisl. Pellentesque ullamcorper erat in orci tempus placerat sit amet a magna. Duis eu vulputate tortor. Curabitur sollicitudin eu sapien nec malesuada. Mauris nec massa arcu. In rutrum orci nibh, ut iaculis nisl bibend.") 
    print("</p>")
    print("</div>")

    print('</div>')    


#main program
if __name__== "__main__":
    try:
        htmlTop()
        form = cgi.FieldStorage() 
            # Get data from fields
        i = form.getvalue('i')
        Nom = form.getvalue('Nom')
        Nom_Account = form.getvalue('Nom_Account')
        Bank = form.getvalue('Bank')
        Cheque_Num = form.getvalue('Cheque_Num')
        Cheque_Value = form.getvalue('Cheque_Value')
        Month = form.getvalue('Month')
        memberID = form.getvalue('memberID')
        Status = form.getvalue('Status')
        datestamp = form.getvalue('datestamp')
        #above are the variables and values passed froom registration_finalpage.cgi

        Checkslist = []
        Checkslist.append(i)
        Checkslist.append(Nom)
        Checkslist.append(Nom_Account)
        Checkslist.append(Bank)
        Checkslist.append(Cheque_Num)
        Checkslist.append(Cheque_Value)
        Checkslist.append(Month)
        #below you have 3 hidden fields from form on registration_finalpage.cgi
        Checkslist.append(memberID)
        Checkslist.append(Status)
        Checkslist.append(datestamp)
        #above we have made the two dimensional list 'Checkslist' from the variables passed to us
        #each sublist is a list of 5 values for the variable it represents
        #its now necessary to convert these lists into lists which have values of each of the variables
        # below we use LIST COMPREHENSIONS to create 5 individual lists which have values for each of the variables 

        Firstcheck = [row[0] for row in Checkslist]
        Secondcheck = [row[1] for row in Checkslist]
        Thirdcheck = [row[2] for row in Checkslist]
        Fourthcheck = [row[3] for row in Checkslist]
        Fifthcheck = [row[4] for row in Checkslist]

        #now we consolidate these lists in one two dimensional list called checks

        Checks = []
        Checks.append(Firstcheck)
        Checks.append(Secondcheck)
        Checks.append(Thirdcheck)
        Checks.append(Fourthcheck)
        Checks.append(Fifthcheck)



        print(i, "i")
        print(Nom, "Nom")
        print(Nom_Account, "Nom_Account")
        print(Bank, "bank")
        print(Cheque_Num, "Cheque_Num")
        print(Cheque_Value, "Cheque_Value")
        print(Month, "Month")
        print(Status, "Status")
        print(memberID, "memberID")
        print(datestamp, "THIS IS DATESTAMP")
        print(Checkslist, "This is the list of the checks")

        print(Firstcheck, "this is first cheque")
        print(Secondcheck, "this is second cheque")
        print(Thirdcheck, "this is third cheque")
        print(Fourthcheck, "this is fourth cheque")
        print(Fifthcheck, "this is fifth cheque")
        print(Checks, "!!!!!!!!!!!!!!!")

        # Now we take the Checks list created above and create a new one 'Checks_Used' which does not have any
        # of the lists which contain a cheque_value with 0 value.  The form in registration_finalpage.cgi has 5 form elements - normally
        # only 3 will be used. Each form element has the Cheque_Values default set at 0.  So, if they are not used
        # they retain that 0 value and then get deleted by the process below

        Checks_Used = [s for s in Checks if s[5] != '0' ] 
        
        Checks_Used_json = json.dumps(Checks_Used) #json version
        print (Checks_Used_json, "Checks_Used_json")    
           


        #db,cursor=connectDB()
        #InsertCheques2(db,cursor)              

        page()
        db,cursor=connectDB()

        #InsertCheques2(db,cursor)
        InsertCheques3(db,cursor)
        print(Nom, "this is Nom")

        htmlTail()

        db.close()
    except:
        cgi.print_exception()

Open in new window

Ok it is hard to pinpoint the exact issue here because we can't see enough. Can you please post a print out of the entire Inset query as a string so we can see exactly what is in it.

Also, it looks like the error is still complaining about trying to insert a List. Do a print type(Checks_Used) and make sure it is of type string.

ASKER

Mark, I'm a bit embarrassed to say - not sure how to "Can you please post a print out of the entire Inset query as a string ". Can you expand on that a little ?

ASKER

Mark, testing the json version of Checks_Used in the python shell

james = [["1", "DELODDERE", "james", "sfd", "1111", "120", "Fevrier", "161080", "pre_regist", "septembre 25, 2015"], ["2", "DELODDERE", "james", "fs", "44444", "122", "Mars", "161080", "pre_regist", "septembre 25, 2015"], ["3", "DELODDERE", "DELODDERE", "sfd", "55555", "121", "Avril", "161080", "pre_regist", "septembre 25, 2015"]]

type(james)
<class 'list'>

That's what I thought. It should be of type 'string' not 'list'
If you print your list (print Checks_Used) your list will have single quotes around the values. Once you convert the list to a json string like so:

Checks_Used = json.dumps(Checks_Used) print that out like so...

print Checks_Used

Now all values will have double quotes. It sounds as though the final value that gets sent into the query is still a list.

That is why I want to see your insert query.

Whatever is in your query call is what I want to see. Something like:

print "INSERT INTO table_name (col1, col2, col3) VALUES ('value1', 'value2', 'value3');"

If value3 is your list converted to a json then it would look like this:

print "INSERT INTO table_name (col1, col2, col3) VALUES ('value1', 'value2', '{0}');".format(Checks_Used)

When you run that code it will print out the exact query that myqsl will try to run. This is what I want to see. You should be able to take that printed text and paste it into mysql directly and see if it runs.

ASKER

Mark, hopefully this is what you need :

I have created a basic page to run, into which I hard wired values for memberID and Cheques_Used. It has the insert function and connection to insert into the table. This page is below.

#!C:\Python34\python.exe
import cgi,cgitb
import mysql.connector as conn
import collections
import datetime
def htmlTop():
    print("""Content-type:text/html\n\n
        <!DOCTYPE html>
        <html lang="en">
            <head>
                <meta charset="UTF-8">
                <title> My Server-side template</title>
                <link href="style.css" rel="stylesheet" type="text/css">

                <script>var __adobewebfontsappname__="dreamweaver"</script>
                <script src="http://use.edgefonts.net/source-sans-pro:n2,i2,n3,i3,n4,i4,n6,i6,n7,i7,n9,i9:default;source-serif-pro:n4:default.js" type="text/javascript"></script>
        </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 InsertCheques3(db,cursor):#this works just as well as one above
    cursor.execute("INSERT into chequemgmt (memberID, cheques) VALUES (%s, %s)",(memberID, Checks_Used))
    db.commit()

def page():
    print("<div id='wrapper'> ")
    
    print("<header>")
    print("<h2 id='stargazer'>") 
    print("ABAC APPLICATION")
    print("</h2>")
    print("<nav>")
    print("<ul>")
    print("<li>")
    print("<a href='#'>")
    print("ABOUT")
    print("</a>")
    print("</li>")
    print("<li>")
    print("<a href='#'>")
    print("IMAGES")
    print("</a>")
    print("<li>")
    print("<a href='#'>")
    print("ARTICLES")
    print("</a>")
    print("<li>")
    print("</ul>")
    print("</nav>")
    print("</header>")
    print("<div id='more'>")
    print("<hr>")
    print("</div>")
    print("<div id='content1'>")   
    print("<p>")
    print("Lorem ipsum dolor sit amet, consectetur adipiscing elit. Proin at molestie sem, rhoncus luctus dui. Nulla id felis lectus. Nulla vitae pellentesque nisl. Pellentesque ullamcorper erat in orci tempus placerat sit amet a magna. Duis eu vulputate tortor. Curabitur sollicitudin eu sapien nec malesuada. Mauris nec massa arcu. In rutrum orci nibh, ut iaculis nisl bibend. Lorem ipsum dolor sit amet, consectetur adipiscing elit. Proin at molestie sem, rhoncus luctus dui. Nulla id felis lectus. Nulla vitae pellentesque nisl. Pellentesque ullamcorper erat in orci tempus placerat sit amet a magna. Duis eu vulputate tortor. Curabitur sollicitudin eu sapien nec malesuada. Mauris nec massa arcu. In rutrum orci nibh, ut iaculis nisl bibend.") 
    print("</p>")
    print("</div>")
    
    print('</div>')    


#main program
if __name__== "__main__":
    try:
        htmlTop()
        page()
        memberID = 454545
        
        
        
        Checks_Used =  [["1", "DELODDERE", "DELODDERE", "bnp", "111", "120", "Fevrier", "161080", "pre_regist", "septembre 26, 2015"], ["2", "DELODDERE", "DELODDERE", "BNP", "555", "122", "Mars", "161080", "pre_regist", "septembre 26, 2015"], ["3", "DELODDERE", "DELODDERE", "bnp", "555", "124", "Avril", "161080", "pre_regist", "septembre 26, 2015"]]
       
        db,cursor=connectDB()
        InsertCheques3(db,cursor)        
       
       
       
        htmlTail()
    except:
        cgi.print_exception()

Open in new window

I ran this page on Wing IDE and got the I/O debug trace. This is shown below

Content-type:text/html


        <!DOCTYPE html>
        <html lang="en">
            <head>
                <meta charset="UTF-8">
                <title> My Server-side template</title>
                <link href="style.css" rel="stylesheet" type="text/css">

                <script>var __adobewebfontsappname__="dreamweaver"</script>
                <script src="http://use.edgefonts.net/source-sans-pro:n2,i2,n3,i3,n4,i4,n6,i6,n7,i7,n9,i9:default;source-serif-pro:n4:default.js" type="text/javascript"></script>
        </head>
        <body>
<div id='wrapper'> 
<header>
<h2 id='stargazer'>
ABAC APPLICATION
</h2>
<nav>
<ul>
<li>
<a href='#'>
ABOUT
</a>
</li>
<li>
<a href='#'>
IMAGES
</a>
<li>
<a href='#'>
ARTICLES
</a>
<li>
</ul>
</nav>
</header>
<div id='more'>
<hr>
</div>
<div id='content1'>
<p>
Lorem ipsum dolor sit amet, consectetur adipiscing elit. Proin at molestie sem, rhoncus luctus dui. Nulla id felis lectus. Nulla vitae pellentesque nisl. Pellentesque ullamcorper erat in orci tempus placerat sit amet a magna. Duis eu vulputate tortor. Curabitur sollicitudin eu sapien nec malesuada. Mauris nec massa arcu. In rutrum orci nibh, ut iaculis nisl bibend. Lorem ipsum dolor sit amet, consectetur adipiscing elit. Proin at molestie sem, rhoncus luctus dui. Nulla id felis lectus. Nulla vitae pellentesque nisl. Pellentesque ullamcorper erat in orci tempus placerat sit amet a magna. Duis eu vulputate tortor. Curabitur sollicitudin eu sapien nec malesuada. Mauris nec massa arcu. In rutrum orci nibh, ut iaculis nisl bibend.
</p>
</div>
</div>

<H3>Traceback (most recent call last):</H3>
<PRE>  File &quot;C:\xampp2\htdocs\junk.cgi&quot;, line 85, in &lt;module&gt;
    InsertCheques3(db,cursor)
  File &quot;C:\xampp2\htdocs\junk.cgi&quot;, line 32, in InsertCheques3
    cursor.execute(&quot;INSERT into chequemgmt (memberID, cheques) VALUES (%s, %s)&quot;,(memberID, Checks_Used))
  File &quot;C:\Python34\lib\site-packages\mysql\connector\cursor.py&quot;, line 491, in execute
    psub = _ParamSubstitutor(self._process_params(params))
  File &quot;C:\Python34\lib\site-packages\mysql\connector\cursor.py&quot;, line 379, in _process_params
    &quot;Failed processing format-parameters; %s&quot; % err)
<B>mysql.connector.errors.ProgrammingError: Failed processing format-parameters; Python &#x27;list&#x27; cannot be converted to a MySQL type
</B></PRE>

Open in new window

ASKER

Further to above. With experimenting by changing the hard wired values I find that I successfully upload

Checks_Used = '[[1, DELODDERE, DELODDERE, bnp, 111, 120, Fevrier, 161080, pre_regist, septembre 26, 2015], [1, DELODDERE, DELODDERE, bnp, 111, 120, Fevrier, 161080, pre_regist, septembre 26, 2015]]'

ASKER CERTIFIED SOLUTION

membership

This solution is only available to members.

To access this solution, you must be a member of Experts Exchange.

Start Free Trial

ASKER

Mark, did this and here is the output you wanted :

INSERT INTO chequemgmt (memberID, cheques) values (1235, [["1", "DELODDERE", "james", "sfd", "1111", "120", "Fevrier", "161080", "pre_regist", "septembre 25, 2015"], ["2", "DELODDERE", "james", "fs", "44444", "122", "Mars", "161080", "pre_regist", "septembre 25, 2015"], ["3", "DELODDERE", "DELODDERE", "sfd", "55555", "121", "Avril", "161080", "pre_regist", "septembre 25, 2015"]])
its done</body>
</html>

The page ran without error - but nothing was inserted into the table.

I am attaching the page I ran to get the above.

#!C:\Python34\python.exe
import cgi,cgitb
import mysql.connector as conn
import collections
import datetime
import json
import traceback
def htmlTop():
    print("""Content-type:text/html\n\n
        <!DOCTYPE html>
        <html lang="en">
            <head>
                <meta charset="UTF-8">
                <title> My Server-side template</title>
                <link href="style.css" rel="stylesheet" type="text/css">

                <script>var __adobewebfontsappname__="dreamweaver"</script>
                <script src="http://use.edgefonts.net/source-sans-pro:n2,i2,n3,i3,n4,i4,n6,i6,n7,i7,n9,i9:default;source-serif-pro:n4:default.js" type="text/javascript"></script>
        </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 InsertMark(db,cursor):
    cursor.execute("INSERT INTO chequemgmt (memberID, cheques) values ({0}, {1})".format(memberID, Json_string))
    db.commit()
    
    

def page():
    print("<div id='wrapper'> ")
    
    print("<header>")
    print("<h2 id='stargazer'>") 
    print("ABAC APPLICATION")
    print("</h2>")
    print("<nav>")
    print("<ul>")
    print("<li>")
    print("<a href='#'>")
    print("ABOUT")
    print("</a>")
    print("</li>")
    print("<li>")
    print("<a href='#'>")
    print("IMAGES")
    print("</a>")
    print("<li>")
    print("<a href='#'>")
    print("ARTICLES")
    print("</a>")
    print("<li>")
    print("</ul>")
    print("</nav>")
    print("</header>")
    print("<div id='more'>")
    print("<hr>")
    print("</div>")
    print("<div id='content1'>")   
    print("<p>")
    print("Lorem ipsum dolor sit amet, consectetur adipiscing elit. Proin at molestie sem, rhoncus luctus dui. Nulla id felis lectus. Nulla vitae pellentesque nisl. Pellentesque ullamcorper erat in orci tempus placerat sit amet a magna. Duis eu vulputate tortor. Curabitur sollicitudin eu sapien nec malesuada. Mauris nec massa arcu. In rutrum orci nibh, ut iaculis nisl bibend. Lorem ipsum dolor sit amet, consectetur adipiscing elit. Proin at molestie sem, rhoncus luctus dui. Nulla id felis lectus. Nulla vitae pellentesque nisl. Pellentesque ullamcorper erat in orci tempus placerat sit amet a magna. Duis eu vulputate tortor. Curabitur sollicitudin eu sapien nec malesuada. Mauris nec massa arcu. In rutrum orci nibh, ut iaculis nisl bibend.") 
    print("</p>")
    print("</div>")
    
    print('</div>')    


#main program
if __name__== "__main__":
    try:
        htmlTop()
        page()
        
        # make a valid list
        Checks_Used =[ ['1', 'DELODDERE', 'james', 'sfd', '1111', '120', 'Fevrier', '161080', 'pre_regist', 'septembre 25, 2015'], ['2', 'DELODDERE', 'james', 'fs', '44444', '122', 'Mars', '161080', 'pre_regist', 'septembre 25, 2015'], ['3', 'DELODDERE', 'DELODDERE', 'sfd', '55555', '121', 'Avril', '161080', 'pre_regist', 'septembre 25, 2015']]
    
        Json_string = json.dumps(Checks_Used)  # now we have a valid json and it will be of type "string"
    
        memberID = 1235
        
        #db,cursor=connectDB()
        #cursor.execute("INSERT INTO chequemgmt (memberID, cheques) values ({0}, {1})".format(memberID, Json_string))
        #db.commit() 
        
        strSQL = "INSERT INTO chequemgmt (memberID, cheques) values ({0}, {1})".format(memberID, Json_string)
    

        # here is what I want to see the output of
        print("==================================================")
        print(strSQL)        
            
    
        htmlTail()
    except:
        cgi.print_exception()

Open in new window

This is the output

On lines 88,89,90 are an alternative to your sql - trying to accomplish the insert to the db. On running that version, I get the old sql error message as shown below

Content-type:text/html


        <!DOCTYPE html>
        <html lang="en">
            <head>
                <meta charset="UTF-8">
                <title> My Server-side template</title>
                <link href="style.css" rel="stylesheet" type="text/css">

                <script>var __adobewebfontsappname__="dreamweaver"</script>
                <script src="http://use.edgefonts.net/source-sans-pro:n2,i2,n3,i3,n4,i4,n6,i6,n7,i7,n9,i9:default;source-serif-pro:n4:default.js" type="text/javascript"></script>
        </head>
        <body>
<div id='wrapper'> 
<header>
<h2 id='stargazer'>
ABAC APPLICATION
</h2>
<nav>
<ul>
<li>
<a href='#'>
ABOUT
</a>
</li>
<li>
<a href='#'>
IMAGES
</a>
<li>
<a href='#'>
ARTICLES
</a>
<li>
</ul>
</nav>
</header>
<div id='more'>
<hr>
</div>
<div id='content1'>
<p>
Lorem ipsum dolor sit amet, consectetur adipiscing elit. Proin at molestie sem, rhoncus luctus dui. Nulla id felis lectus. Nulla vitae pellentesque nisl. Pellentesque ullamcorper erat in orci tempus placerat sit amet a magna. Duis eu vulputate tortor. Curabitur sollicitudin eu sapien nec malesuada. Mauris nec massa arcu. In rutrum orci nibh, ut iaculis nisl bibend. Lorem ipsum dolor sit amet, consectetur adipiscing elit. Proin at molestie sem, rhoncus luctus dui. Nulla id felis lectus. Nulla vitae pellentesque nisl. Pellentesque ullamcorper erat in orci tempus placerat sit amet a magna. Duis eu vulputate tortor. Curabitur sollicitudin eu sapien nec malesuada. Mauris nec massa arcu. In rutrum orci nibh, ut iaculis nisl bibend.
</p>
</div>
</div>

<H3>Traceback (most recent call last):</H3>
<PRE>  File &quot;C:\xampp2\htdocs\OFFICE_15\marktest.cgi&quot;, line 89, in &lt;module&gt;
    cursor.execute(&quot;INSERT INTO chequemgmt (memberID, cheques) values ({0}, {1})&quot;.format(memberID, Json_string))
  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;[[&quot;1&quot;, &quot;DELODDERE&quot;, &quot;james&quot;, &quot;sfd&quot;, &quot;1111&quot;, &quot;120&quot;, &quot;Fevrier&quot;, &quot;161080&quot;, &quot;pre_reg&#x27; at line 1
</B></PRE>

Open in new window

James, can you try, on the solution I sent you, just convert the check_used to string?
like: str(cheques_used)

ASKER

Hi Walter, sorry that did not work. I am attaching the page I ran, the error message and a check on the list to string conversion in the python shell.

User generated image

#!C:\Python34\python.exe
import cgi,cgitb
import mysql.connector as conn
import collections
import datetime
def htmlTop():
    print("""Content-type:text/html\n\n
        <!DOCTYPE html>
        <html lang="en">
            <head>
                <meta charset="UTF-8">
                <title> My Server-side template</title>
                <link href="style.css" rel="stylesheet" type="text/css">

                <script>var __adobewebfontsappname__="dreamweaver"</script>
                <script src="http://use.edgefonts.net/source-sans-pro:n2,i2,n3,i3,n4,i4,n6,i6,n7,i7,n9,i9:default;source-serif-pro:n4:default.js" type="text/javascript"></script>
        </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 InsertCheques(db,cursor):
    strSQL = "insert into chequemgmt(memberID, cheques) values ({0}, {1})".format(memberID, Checks_Used)
    cursor.execute(strSQL)
    db.commit()     


def InsertCheques2(db,cursor):#this works just as well as one above
    cursor.execute("""
            INSERT into chequemgmt (memberID, cheques)
            VALUES ({0}, {1})
            """.format(memberID, Checks_Used.replace("'","\'")))
    db.commit()

def InsertCheques3(db,cursor):#this works just as well as one above
    cursor.execute("INSERT into chequemgmt (memberID, cheques) VALUES (%s, %s)",(memberID, Checks_Used))
    db.commit()


def page():
    print("<div id='wrapper'> ")

    print("<header>")
    print("<h2 id='stargazer'>") 
    print("ABAC APPLICATION")
    print("</h2>")
    print("<nav>")
    print("<ul>")
    print("<li>")
    print("<a href='#'>")
    print("ABOUT")
    print("</a>")
    print("</li>")
    print("<li>")
    print("<a href='#'>")
    print("IMAGES")
    print("</a>")
    print("<li>")
    print("<a href='#'>")
    print("ARTICLES")
    print("</a>")
    print("<li>")
    print("</ul>")
    print("</nav>")
    print("</header>")
    print("<div id='more'>")
    print("<hr>")
    print("</div>")
    print("<div id='content1'>")   
    print("<p>")
    print("Lorem ipsum dolor sit amet, consectetur adipiscing elit. Proin at molestie sem, rhoncus luctus dui. Nulla id felis lectus. Nulla vitae pellentesque nisl. Pellentesque ullamcorper erat in orci tempus placerat sit amet a magna. Duis eu vulputate tortor. Curabitur sollicitudin eu sapien nec malesuada. Mauris nec massa arcu. In rutrum orci nibh, ut iaculis nisl bibend. Lorem ipsum dolor sit amet, consectetur adipiscing elit. Proin at molestie sem, rhoncus luctus dui. Nulla id felis lectus. Nulla vitae pellentesque nisl. Pellentesque ullamcorper erat in orci tempus placerat sit amet a magna. Duis eu vulputate tortor. Curabitur sollicitudin eu sapien nec malesuada. Mauris nec massa arcu. In rutrum orci nibh, ut iaculis nisl bibend.") 
    print("</p>")
    print("</div>")

    print('</div>')    


#main program
if __name__== "__main__":
    try:
        htmlTop()
        form = cgi.FieldStorage() 
            # Get data from fields
        i = form.getvalue('i')
        Nom = form.getvalue('Nom')
        Nom_Account = form.getvalue('Nom_Account')
        Bank = form.getvalue('Bank')
        Cheque_Num = form.getvalue('Cheque_Num')
        Cheque_Value = form.getvalue('Cheque_Value')
        Month = form.getvalue('Month')
        memberID = form.getvalue('memberID')
        Status = form.getvalue('Status')
        datestamp = form.getvalue('datestamp')
        #above are the variables and values passed froom registration_finalpage.cgi

        Checkslist = []
        Checkslist.append(i)
        Checkslist.append(Nom)
        Checkslist.append(Nom_Account)
        Checkslist.append(Bank)
        Checkslist.append(Cheque_Num)
        Checkslist.append(Cheque_Value)
        Checkslist.append(Month)
        #below you have 3 hidden fields from form on registration_finalpage.cgi
        Checkslist.append(memberID)
        Checkslist.append(Status)
        Checkslist.append(datestamp)
        #above we have made the two dimensional list 'Checkslist' from the variables passed to us
        #each sublist is a list of 5 values for the variable it represents
        #its now necessary to convert these lists into lists which have values of each of the variables
        # below we use LIST COMPREHENSIONS to create 5 individual lists which have values for each of the variables 

        Firstcheck = [row[0] for row in Checkslist]
        Secondcheck = [row[1] for row in Checkslist]
        Thirdcheck = [row[2] for row in Checkslist]
        Fourthcheck = [row[3] for row in Checkslist]
        Fifthcheck = [row[4] for row in Checkslist]

        #now we consolidate these lists in one two dimensional list called checks

        Checks = []
        Checks.append(Firstcheck)
        Checks.append(Secondcheck)
        Checks.append(Thirdcheck)
        Checks.append(Fourthcheck)
        Checks.append(Fifthcheck)



        print(i, "i")
        print(Nom, "Nom")
        print(Nom_Account, "Nom_Account")
        print(Bank, "bank")
        print(Cheque_Num, "Cheque_Num")
        print(Cheque_Value, "Cheque_Value")
        print(Month, "Month")
        print(Status, "Status")
        print(memberID, "memberID")
        print(datestamp, "THIS IS DATESTAMP")
        print(Checkslist, "This is the list of the checks")

        print(Firstcheck, "this is first cheque")
        print(Secondcheck, "this is second cheque")
        print(Thirdcheck, "this is third cheque")
        print(Fourthcheck, "this is fourth cheque")
        print(Fifthcheck, "this is fifth cheque")
        print(Checks, "!!!!!!!!!!!!!!!")

        # Now we take the Checks list created above and create a new one 'Checks_Used' which does not have any
        # of the lists which contain a cheque_value with 0 value.  The form in registration_finalpage.cgi has 5 form elements - normally
        # only 3 will be used. Each form element has the Cheque_Values default set at 0.  So, if they are not used
        # they retain that 0 value and then get deleted by the process below

        Checks_Used = [s for s in Checks if s[5] != '0' ] 
        Checks_Used = str(Checks_Used)
        #Checks_Used = [['1', 'CAILLOT', 'CAILLOT', 'bnp', '85555', '50', 'Septembre', '369673', 'pre_regist', 'septembre 24, 2015'] ]
        print (Checks_Used, "Checks_Used")        


        #db,cursor=connectDB()
        #InsertCheques2(db,cursor)              

        page()
        db,cursor=connectDB()

        #InsertCheques2(db,cursor)
        InsertCheques3(db,cursor)
        print(Nom, "this is Nom")

        htmlTail()

        db.close()
    except:
        cgi.print_exception()

Open in new window

User generated image

ASKER

After some trial and error I find that

If I take the original list which needs to be uploaded and :

1) convert it to a string
2) apply Json

Then it will upload and the uploaded 'thing' looks like my original list, even though it is a string.

If I now try and download the string using select I get from select a weird looking list. However if I deploy a [0][0] select select on the list, I get something that looks exactly like the list I wanted to upload -- problem is, its a string and I can't process it any futher

If I was able to convert a this string to a list then the problem would be solved - but this is probably not possible ?????

To illustrate the above, I am attaching two cgi files which are the upload and select files as referred to above. I am also attaching the output from each of these.

The Upload file

#!C:\Python34\python.exe
import cgi,cgitb
import mysql.connector as conn
import collections
import datetime
import json
import traceback
def htmlTop():
    print("""Content-type:text/html\n\n
        <!DOCTYPE html>
        <html lang="en">
            <head>
                <meta charset="UTF-8">
                <title> My Server-side template</title>
                <link href="style.css" rel="stylesheet" type="text/css">

                <script>var __adobewebfontsappname__="dreamweaver"</script>
                <script src="http://use.edgefonts.net/source-sans-pro:n2,i2,n3,i3,n4,i4,n6,i6,n7,i7,n9,i9:default;source-serif-pro:n4:default.js" type="text/javascript"></script>
        </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 InsertMark(db,cursor):
    cursor.execute("INSERT INTO chequemgmt (memberID, cheques) values ({0}, {1})".format(memberID, Json_string))
    db.commit()
    
    

def page():
    print("<div id='wrapper'> ")
    
    print("<header>")
    print("<h2 id='stargazer'>") 
    print("ABAC APPLICATION")
    print("</h2>")
    print("<nav>")
    print("<ul>")
    print("<li>")
    print("<a href='#'>")
    print("ABOUT")
    print("</a>")
    print("</li>")
    print("<li>")
    print("<a href='#'>")
    print("IMAGES")
    print("</a>")
    print("<li>")
    print("<a href='#'>")
    print("ARTICLES")
    print("</a>")
    print("<li>")
    print("</ul>")
    print("</nav>")
    print("</header>")
    print("<div id='more'>")
    print("<hr>")
    print("</div>")
    print("<div id='content1'>")   
    print("<p>")
    print("Lorem ipsum dolor sit amet, consectetur adipiscing elit. Proin at molestie sem, rhoncus luctus dui. Nulla id felis lectus. Nulla vitae pellentesque nisl. Pellentesque ullamcorper erat in orci tempus placerat sit amet a magna. Duis eu vulputate tortor. Curabitur sollicitudin eu sapien nec malesuada. Mauris nec massa arcu. In rutrum orci nibh, ut iaculis nisl bibend. Lorem ipsum dolor sit amet, consectetur adipiscing elit. Proin at molestie sem, rhoncus luctus dui. Nulla id felis lectus. Nulla vitae pellentesque nisl. Pellentesque ullamcorper erat in orci tempus placerat sit amet a magna. Duis eu vulputate tortor. Curabitur sollicitudin eu sapien nec malesuada. Mauris nec massa arcu. In rutrum orci nibh, ut iaculis nisl bibend.") 
    print("</p>")
    print("</div>")
    
    print('</div>')    


#main program
if __name__== "__main__":
    try:
        htmlTop()
        page()
        
        # make a valid list
        Checks_Used =[ ['1', 'DELODDERE', 'james', 'sfd', '1111', '120', 'Fevrier', '161080', 'pre_regist', 'septembre 25, 2015'], ['2', 'DELODDERE', 'james', 'fs', '44444', '122', 'Mars', '161080', 'pre_regist', 'septembre 25, 2015'], ['3', 'DELODDERE', 'DELODDERE', 'sfd', '55555', '121', 'Avril', '161080', 'pre_regist', 'septembre 25, 2015']]
        
         
        print(type(Checks_Used), "Checks_Used type")
        print(Checks_Used, "this is Checks_Used at start")
        
        Checks_Used = str(Checks_Used)
        
        print(type(Checks_Used), "Check_Used type - after str operation")
        print(Checks_Used, "This is Checks_Used after string conversion")
        
        Json_string = json.dumps(Checks_Used)  
        print(type(Json_string), "Json_string type with str operation and json conversion")
        print(Json_string, "this is Json_string after str and json operations - just before sent to database")
        
        memberID = 3214500
        
        db,cursor=connectDB()
        cursor.execute("INSERT INTO chequemgmt (memberID, cheques) values ({0}, {1})".format(memberID, Json_string))
        db.commit() 
        
               
            
    
        htmlTail()
    except:
        cgi.print_exception()

Open in new window

The Select file

#!C:\Python34\python.exe
import cgi,cgitb
import mysql.connector as conn
import collections
import datetime
import json
import traceback
def htmlTop():
    print("""Content-type:text/html\n\n
        <!DOCTYPE html>
        <html lang="en">
            <head>
                <meta charset="UTF-8">
                <title> My Server-side template</title>
                <link href="style.css" rel="stylesheet" type="text/css">

                <script>var __adobewebfontsappname__="dreamweaver"</script>
                <script src="http://use.edgefonts.net/source-sans-pro:n2,i2,n3,i3,n4,i4,n6,i6,n7,i7,n9,i9:default;source-serif-pro:n4:default.js" type="text/javascript"></script>
        </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 InsertMark(db,cursor):
    cursor.execute("INSERT INTO chequemgmt (memberID, cheques) values ({0}, {1})".format(memberID, Json_string))
    db.commit()
 
def SelectfromDB(db,cursor):
    cursor.execute("""select cheques from chequemgmt WHERE memberID = "%s" """
    %(memberID)) 
    order=cursor.fetchall()
    return order   
    

def page():
    print("<div id='wrapper'> ")
    
    print("<header>")
    print("<h2 id='stargazer'>") 
    print("ABAC APPLICATION")
    print("</h2>")
    print("<nav>")
    print("<ul>")
    print("<li>")
    print("<a href='#'>")
    print("ABOUT")
    print("</a>")
    print("</li>")
    print("<li>")
    print("<a href='#'>")
    print("IMAGES")
    print("</a>")
    print("<li>")
    print("<a href='#'>")
    print("ARTICLES")
    print("</a>")
    print("<li>")
    print("</ul>")
    print("</nav>")
    print("</header>")
    print("<div id='more'>")
    print("<hr>")
    print("</div>")
    print("<div id='content1'>")   
    print("<p>")
    print("Lorem ipsum dolor sit amet, consectetur adipiscing elit. Proin at molestie sem, rhoncus luctus dui. Nulla id felis lectus. Nulla vitae pellentesque nisl. Pellentesque ullamcorper erat in orci tempus placerat sit amet a magna. Duis eu vulputate tortor. Curabitur sollicitudin eu sapien nec malesuada. Mauris nec massa arcu. In rutrum orci nibh, ut iaculis nisl bibend. Lorem ipsum dolor sit amet, consectetur adipiscing elit. Proin at molestie sem, rhoncus luctus dui. Nulla id felis lectus. Nulla vitae pellentesque nisl. Pellentesque ullamcorper erat in orci tempus placerat sit amet a magna. Duis eu vulputate tortor. Curabitur sollicitudin eu sapien nec malesuada. Mauris nec massa arcu. In rutrum orci nibh, ut iaculis nisl bibend.") 
    print("</p>")
    print("</div>")
    
    print('</div>')    


#main program
if __name__== "__main__":
    try:
        htmlTop()
        page()
        
       
        memberID = 3214500
        
        db,cursor=connectDB()
       
        
        order = SelectfromDB(db,cursor)
        print(type(order), "this is type returned from SelectfromDB")
        print(order, "returned from the selectfromDB function")
        cheques = order[0][0]
        print(cheques, " returned from order[0][0] ")
        print(type(cheques), "this is the type for order[0][0]")
        
        
    
        htmlTail()
    except:
        cgi.print_exception()

Open in new window

Output from Upload file

<class 'list'> Checks_Used type
[['1', 'DELODDERE', 'james', 'sfd', '1111', '120', 'Fevrier', '161080', 'pre_regist', 'septembre 25, 2015'], ['2', 'DELODDERE', 'james', 'fs', '44444', '122', 'Mars', '161080', 'pre_regist', 'septembre 25, 2015'], ['3', 'DELODDERE', 'DELODDERE', 'sfd', '55555', '121', 'Avril', '161080', 'pre_regist', 'septembre 25, 2015']] this is Checks_Used at start
<class 'str'> Check_Used type - after str operation
[['1', 'DELODDERE', 'james', 'sfd', '1111', '120', 'Fevrier', '161080', 'pre_regist', 'septembre 25, 2015'], ['2', 'DELODDERE', 'james', 'fs', '44444', '122', 'Mars', '161080', 'pre_regist', 'septembre 25, 2015'], ['3', 'DELODDERE', 'DELODDERE', 'sfd', '55555', '121', 'Avril', '161080', 'pre_regist', 'septembre 25, 2015']] This is Checks_Used after string conversion
<class 'str'> Json_string type with str operation and json conversion
"[['1', 'DELODDERE', 'james', 'sfd', '1111', '120', 'Fevrier', '161080', 'pre_regist', 'septembre 25, 2015'], ['2', 'DELODDERE', 'james', 'fs', '44444', '122', 'Mars', '161080', 'pre_regist', 'septembre 25, 2015'], ['3', 'DELODDERE', 'DELODDERE', 'sfd', '55555', '121', 'Avril', '161080', 'pre_regist', 'septembre 25, 2015']]" this is Json_string after str and json operations - just before sent to database

Open in new window

Output from Select file

<class 'list'> this is type returned from SelectfromDB
[("[['1', 'DELODDERE', 'james', 'sfd', '1111', '120', 'Fevrier', '161080', 'pre_regist', 'septembre 25, 2015'], ['2', 'DELODDERE', 'james', 'fs', '44444', '122', 'Mars', '161080', 'pre_regist', 'septembre 25, 2015'], ['3', 'DELODDERE', 'DELODDERE', 'sfd', '55555', '121', 'Avril', '161080', 'pre_regist', 'septembre 25, 2015']]",)] returned from the selectfromDB function
[['1', 'DELODDERE', 'james', 'sfd', '1111', '120', 'Fevrier', '161080', 'pre_regist', 'septembre 25, 2015'], ['2', 'DELODDERE', 'james', 'fs', '44444', '122', 'Mars', '161080', 'pre_regist', 'septembre 25, 2015'], ['3', 'DELODDERE', 'DELODDERE', 'sfd', '55555', '121', 'Avril', '161080', 'pre_regist', 'septembre 25, 2015']]  returned from order[0][0] 
<class 'str'> this is the type for order[0][0]

Open in new window

SOLUTION

membership

This solution is only available to members.

To access this solution, you must be a member of Experts Exchange.

Start Free Trial

ASKER

PROBLEM RESOLVED !!

To recap

Uploading :

1 Convert list to string
2 Convert string to JSON FORMAT

Downloading :
1 Use EVAL to convert the downloaded string back to a List

Can't say I understand the logic of the above - I would have thought for example that it was necessary to 'de-JSONise' the returned string, but in fact I got a string back which was exactly the same format that the string I uploaded - no inverted commas in sight.

Again, not sure why it was necessary to convert my original list to a string for uploading. But fact is this was necessary ?

Again, not sure why it was necessary to JSONise the string I uploaded, but it was

BUT, it all works perfectly. Maybe I worry a bit that future releases of python may knock the solution over ,

Thanks very much everyone for you help and patience !!

james