PYTHON 3.4 CGI - Inserting form data into mysql table

I have a form which uploads data to an action page - which then picks up the data via  cgi.fieldstorage, massages that data and finally attempts to insert it into an mysql table. I am having a problem with the insertion.

Example  data from fieldstorage = ['5', '6'] ['5', '6', '7', '8'] ['5', '6'] ['1274'] - the first three elements represent the order which has been submitted via the form. The last element is the memberID (member identification)

This data is massaged into  [5, 6] [5, 6, 7, 8] [5, 6] 1274 and then should be inserted into a mysql table - shown below.

mysql table
The memberID (eg 1274)  is being successfully  inserted as shown in the table. All other values are failing to be inserted and result in an error message as shown below. The NULL values are the default values set within the table.

Example to illustrate error message - memberID is 1023 in this example
The error message is strange for me,  I did/do not think I am trying to insert a tuple ???

Thanks for any help to solve the problem.  I am attaching the action page code. Lines 63 to 73 show the function for inserting the data.

#!C:\Python34\python.exe
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


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 insertinfo(db,cursor):
    sql = "insert into registrations(memberID)values('{0}')".format(memberID),
    "insert into registrations(T1ytdlatest)values('{0}')".format(selection),
    "insert into registrations(T2ytdlatest)values('{0}')".format(selectionTWO),
    "insert into registrations(T3ytdlatest)values('{0}')".format(selectionTHREE),
    "insert into registrations(T1ytd)values('{0}')".format(selection),
    "insert into registrations(T2ytd)values('{0}')".format(selectionTWO),
    "insert into registrations(T3ytd)values('{0}')".format(selectionTHREE)
    cursor.execute(sql)
    db.commit()     
    db.close()
    

#main program
if __name__== "__main__":
    try:
        htmlTop()
        db,cursor=connectDB()
        selection,selectionTWO,selectionTHREE,memberID = getData()
        print(selection)
        print(selectionTWO)
        print(selectionTHREE)
        print(memberID)
        
        killcommasselection(selection)
        selection = killcommasselection(selection)
        killcommasselectionTWO(selectionTWO)
        selectionTWO = killcommasselectionTWO(selectionTWO)
        killcommasselectionTHREE(selectionTHREE)
        selectionTHREE = killcommasselectionTHREE(selectionTHREE)
        memberID = ''.join(memberID)
        
        print(selection)
        print(selectionTWO)
        print(selectionTHREE)
        print(memberID)
        insertinfo(db,cursor)
       
        
        print("Connection made")
        htmlTail()
    except:
        cgi.print_exception()

Open in new window



Thanks for your help

james
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.

Walter RitzelSenior Software EngineerCommented:
James,
your insert commands are not correct - you should have just one insert command, with the values on each column, like this:
strSQL = "insert into table(memberID, T1ytdlatest, T2ytdlatest, T3ytdlatest, T1ytd, T2ytd, T3ytd) values (%s, %s, %s, %s, %s, %s, %s)".format(memberID, selection, selectionTWO, selectionTHREE, selection, selectionTWO, selectionTHREE)
 cursor.execute(strSQL)
    db.commit()     
    db.close()

Open in new window


But I think your understanding on how to translate the data you have on the form to a database table is suffering from what we call an impendance mismatch. Because you are saving a list inside a database column (the [5,6] and such in python are lists), and I'm not sure if the way you are saving the information is the best way. If you want to post a question to expose in more detail what you have and ask for advices on design, that could be a good idea.
0
jameskaneAuthor Commented:
Many thanks Walter for your help. I think I may have sorted out that mismatch business. modified the insertinfo(db,cursor) function to take a 2D list called mylist. This is an example of my list

 ['1173', [8, 9], [8, 9, 10, 11], [8, 9]]  where 1173 is the memberID for the example.

If I take the old insertinfo(db,cursor) and feed it MYLIST - it works   - of couse it only works if you have only 1
statement at a time. I tested it out one at a time and it worked. Clearly the insertfunction is, of course incorrect.
So, just before receiving your input, I felt that the syntax of the insertinfo(db,cursor) was the only problem remaining.

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

A SUCCESSFUL INSERT INTO THE REGISTRATIONS TABLE
NEW FUNCTION

I have inserted MYLIST into the new function you sent me as follows.

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

Unfortunately I get the following error message :

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 '%s, %s, %s, %s, %s, %s, %s)' at l

Hopefully there is an obvious error in this syntax
mylist.jpg
0
Walter RitzelSenior Software EngineerCommented:
My bad. It should be:
    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])

Open in new window

0

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
Angular Fundamentals

Learn the fundamentals of Angular 2, a JavaScript framework for developing dynamic single page applications.

jameskaneAuthor Commented:
YES !!

database
thanks very much
0
jameskaneAuthor Commented:
as usual ...  MANY THANKS !

james
0
Walter RitzelSenior Software EngineerCommented:
You're welcome. But just let me remember you: you have saved a string that represents a list in python. When you load the information back from the database you should use evaluate that to a list again (using the eval() function) before be able to iterate through it, for example.
0
jameskaneAuthor Commented:
Got it (I think !)

thanks again

james
0
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.