asked on

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.

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.

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

Walter Ritzel

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.

jameskane

ASKER

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()

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

ASKER CERTIFIED SOLUTION

Walter Ritzel

membership

This solution is only available to members.

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

Start Free Trial

jameskane

ASKER

YES !!

thanks very much

jameskane

ASKER

as usual ... MANY THANKS !

james

Walter Ritzel

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.

jameskane

ASKER

Got it (I think !)

thanks again

james