Want to protect your cyber security and still get fast solutions? Ask a secure question today.Go Premium

x
  • Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 1005
  • Last Modified:

how to insert form data into mysql table using cgi written in python 3.4

I am submitting two text field values from a form to cgi page written in Python 3.4. The CGI page should insert the form information into an MYSQL table (size).  When I run a test I get the following error message :
_____________________________________________________________________________________________________________
raceback (most recent call last):

  File "C:/xampp2/htdocs/PizzaKitchen/insertrecords.cgi", line 50, in <module>
    insertPizzaSize(db,cursor,PizzaSize)
  File "C:/xampp2/htdocs/PizzaKitchen/insertrecords.cgi", line 41, in insertPizzaSize
    cursor.execute(sql)
  File "C:\Python34\lib\site-packages\mysql\connector\cursor.py", line 504, in execute
    self._handle_result(self._connection.cmd_query(stmt))
  File "C:\Python34\lib\site-packages\mysql\connector\connection.py", line 766, in cmd_query
    result = self._handle_result(self._send_cmd(ServerCmd.QUERY, query))
  File "C:\Python34\lib\site-packages\mysql\connector\connection.py", line 684, in _handle_result
    raise errors.get_exception(packet)
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 '%(inches)s,

__________________________________________________________________________________________________________


The two files are shown below.   Appreciate someone taking a look.

Thanks in advance,   james

_____________________________________________________________________________________________________________

Form :

<form action="insertrecords.cgi" method="post" name="admin_form_size" target="_self">

<table width="270" border="1" cellspacing="5" cellpadding="3">
  <caption>
    PIZZA KITCHEN
  </caption>
  <tr>
    <td colspan="2"> Current Sizes</td>
    </tr>
  <tr>
    <td width="148">Number of inches</td>
    <td width="95">cost_fill</td>
  </tr>
  <tr>
    <td><label for="Inches"></label>
      <input type="text" name="Inches" id="Inches"></td>
    <td>
      <input type="text" name="cost_fill" id="cost_fill"></td>
  </tr>
 
  <tr>
    <td colspan="2">Add New Size</td>
    </tr>
  <tr>
    <td>Number of Inches</td>
    <td>Cost</td>
  </tr>
  <tr>
    <td><label for="Inches2"></label>
      <input type="text" name="Inches2" id="Inches2"></td>
    <td><label for="Cost"></label>
      <input type="text" name="Cost2" id="Cost"></td>
  </tr>
  <tr>
    <td colspan="2"><input type="submit" name="submit" id="submit" value="Add new size"></td>
    </tr>
</table>
</form>

____________________________________________________________________________________________________

CGI PAGE insertrecords.cgi

#!C:\Python34\python.exe
import cgi
import mysql.connector as conn
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>
             </head>
             <body>""")

def htmlTail():
      print("""</body>
             </html>""")
     
def connectDB():
      db=conn.connect(host='localhost' ,user='root' ,passwd='' ,db='pizza')
      cursor = db.cursor()
      return db, cursor

def getData():
      formData = cgi.fieldStorage()
      Inches = formData.getvalue('Inches')
      Cost_fill = formData.getvalue('Cost_fill')
      return Inches,Cost_fill
     
def createPizzaSizeList():
      #create blank list
      PizzaSize = []
      PizzaSize.append([0])
      PizzaSize.append([1])
      return PizzaSize


def insertPizzaSize(db,cursor,PizzaSize):
      #sql = "insert into size (Inches, Cost_fill) VALUES (%(inches)s, %(cost_fill)s)"
      for each in PizzaSize:
            sql = "insert into size (Inches, Cost_fill) VALUES (%(inches)s, %(cost_fill)s)"
            cursor.execute(sql)
      db.commit()
     
#main program
if __name__== "__main__":
      try:
            htmlTop()
            db,cursor = connectDB()
            PizzaSize = createPizzaSizeList()
            insertPizzaSize(db,cursor,PizzaSize)
           
            htmlTail()
      except:
            cgi.print_exception()
0
jameskane
Asked:
jameskane
  • 6
  • 5
1 Solution
 
Dave BaldwinFixer of ProblemsCommented:
I got your code working.  One of the main problems was inconsistent capitalization of variable names.  The names have to be identical in every place they are found, both in the Python Program and in the HTML page.  To work on this computer, I had to use 'py' instead of 'cgi'.

PizzaOrder.html
<!DOCTYPE HTML PUBLIC "-//W3C//DTD HTML 4.01 Transitional//EN"
 "http://www.w3.org/TR/html4/loose.dtd">

<html>
<head>
<title>PIZZA py</title>
</head>
<body>
<h1>PIZZA py</h1>
<form action="insertrecords.py" method="post" name="admin_form_size">

<table width="270" border="1" cellspacing="5" cellpadding="3">
  <caption>
    PIZZA KITCHEN
  </caption>
  <tr>
    <td colspan="2"> Current Sizes</td>
    </tr>
  <tr>
    <td width="148">Inches</td>
    <td width="95">Cost_fill</td>
  </tr>
  <tr>
    <td><label for="Inches"></label>
      <input type="text" name="Inches" id="Inches"></td>
    <td>
      <input type="text" name="Cost_fill" id="Cost_fill"></td>
  </tr>
 
  <tr>
    <td colspan="2">Add New Size</td>
    </tr>
  <tr>
    <td>Number of Inches</td>
    <td>Cost</td>
  </tr>
  <tr>
    <td><label for="Inches2"></label>
      <input type="text" name="Inches2" id="Inches2"></td>
    <td><label for="Cost"></label>
      <input type="text" name="Cost2" id="Cost"></td>
  </tr>
  <tr>
    <td colspan="2"><input type="submit" name="submit" id="submit" value="Add new size"></td>
    </tr>
</table>
</form>
</body>
</html>

Open in new window


insertrecords.py
#!C:\Python27\python.exe
import cgi, cgitb
import mysql.connector as conn

formData = cgi.FieldStorage()

def htmlTop():
      print("""Content-type:text/html\n\n
             <DOCTYPE html>
             <html lang="en">
             <head>
             <meta charset="utf-8"/>
             <title>insertrecords.py</title>
             </head>
             <body>""")

def htmlTail():
      print("""We're Done.</body>
             </html>""")
     
def connectDB():
      db=conn.connect(host='localhost' ,user='root' ,passwd='' ,db='pizza')
      cursor = db.cursor()
      return db, cursor

def getData():
      Inches = formData.getvalue('Inches')
      Cost_fill = formData.getvalue('Cost_fill')
      return Inches,Cost_fill
     
def createPizzaSizeList():
      #create blank list
      PizzaSize = []
      #add pizza to the list
      #PizzaSize.append("[0]" , "[1]")
      PizzaSize.append([0])
      #PizzaSize.append([1])
      return PizzaSize


def insertPizzaSize(db,cursor,PizzaSize):
      for each in PizzaSize:
            sql = "insert into size(Inches, Cost_fill)values('{0}','{1}')".format(Inches,Cost_fill)
            cursor.execute(sql)
      db.commit()
     
     
#main program
if __name__== "__main__":
      try:
            htmlTop()
            db, cursor = connectDB()
            Inches,Cost_fill = getData()
            PizzaSize = createPizzaSizeList()
            insertPizzaSize(db,cursor,PizzaSize)
            htmlTail()
      except:
            cgi.print_exception() 

Open in new window

0
 
jameskaneAuthor Commented:
Thanks very much Dave.  I would never have picked up on that capitalization problem - and the other tweeks !

I took the page and renamed it .cgi - however on running it I get the following error message :
______________________________________________________________________________________________________________
Server error!

The server encountered an internal error and was unable to complete your request.

Error message:
couldn't create child process: 720002: insertrecords.cgi

If you think this is a server error, please contact the webmaster.

Error 500

localhost
Apache/2.4.10 (Win32) OpenSSL/1.0.1i PHP/5.5.15

_________________________________________________________________________________________________________

James
0
 
jameskaneAuthor Commented:
Woops sorry Dave,  just realized that I needed to change the  #!C:\Python27\python.exe to #!C:\Python34\python.exe.  

However, still have a problem - get the following error message


___________________________________________________________________________________________________________
Traceback (most recent call last):

  File "C:/xampp2/htdocs/PizzaKitchen/insertrecords.cgi", line 55, in <module>
    insertPizzaSize(db,cursor,PizzaSize)
  File "C:/xampp2/htdocs/PizzaKitchen/insertrecords.cgi", line 44, in insertPizzaSize
    cursor.execute(sql)
  File "C:\Python34\lib\site-packages\mysql\connector\cursor.py", line 504, in execute
    self._handle_result(self._connection.cmd_query(stmt))
  File "C:\Python34\lib\site-packages\mysql\connector\connection.py", line 766, in cmd_query
    result = self._handle_result(self._send_cmd(ServerCmd.QUERY, query))
  File "C:\Python34\lib\site-packages\mysql\connector\connection.py", line 684, in _handle_result
    raise errors.get_exception(packet)
mysql.connector.errors.IntegrityError: 1062 (23000): Duplicate entry '0' for key 'PRIMARY'

______________________________________________________________________________________________________
0
[Webinar] Database Backup and Recovery

Does your company store data on premises, off site, in the cloud, or a combination of these? If you answered “yes”, you need a data backup recovery plan that fits each and every platform. Watch now as as Percona teaches us how to build agile data backup recovery plan.

 
Dave BaldwinFixer of ProblemsCommented:
I have been able to get it running on Python 2.7 and 3.4 on IIS and Apache 2.4 and on my XAMPP system.  Try changing the extension to 'py' and add '.py' to the line in 'httpd.conf' that starts with "AddHandler cgi-script.." and restart Apache.  'cgi' is usually expected to mean Perl, not Python.  Yes I saw the pages that said to use 'cgi' but I think it creates a conflict when both Perl and Python are on the server.
0
 
Dave BaldwinFixer of ProblemsCommented:
I don't know what to say about those errors.  I just installed Python 3.4 and the MySQL Python Connector (for 3.4) on my XAMPP system and everything came up running.
0
 
jameskaneAuthor Commented:
Dave, I have sorted out the Primary error above and now the program runs through to the end - with the We're Done (thanks for that !)  message.

The problem is that the cgi is setting cost_fill and inches each to 0 - regardless of the entries I make in the form.
That is why I got the Primary error above - there was already a 0 0 entry in the table - the addition of another caused the error.
0
 
Dave BaldwinFixer of ProblemsCommented:
Did you see that I commented out "#PizzaSize.append([1])" because it was doubling the INSERTs.  I added an autoincrement primary key to the table but it sounds like you are using one of those fields as the key because you wouldn't get that error if you weren't.  Without the key, MySQL doesn't care how many duplicates you put in a table.  Also.. cost_fill and inches is Cost_fill and Inches or it doesn't work.
0
 
jameskaneAuthor Commented:
Hi Dave,  very close now. You are right of course about the primary key. I fixed that and also sorted out a couple of CAPITAL letter issues (sorry for that again !!)

Now  the date is being uploaded ...... BUT..... you won't believe this ..... I am getting two records loaded each time !

Have checked that the "#PizzaSize.append([1])  - that is still there

thanks for your patience

james

________________________________________________________
#!C:\Python34\python.exe
import cgi, cgitb
import mysql.connector as conn

formData = cgi.FieldStorage()

def htmlTop():
      print("""Content-type:text/html\n\n
             <DOCTYPE html>
             <html lang="en">
             <head>
             <meta charset="utf-8"/>
             <title>insertrecords.py</title>
             </head>
             <body>""")

def htmlTail():
      print("""We're Done.</body>
             </html>""")
     
def connectDB():
      db=conn.connect(host='localhost' ,user='root' ,passwd='' ,db='pizza')
      cursor = db.cursor()
      return db, cursor

def getData():
      Inches = formData.getvalue('Inches')
      Cost_fill = formData.getvalue('Cost_fill')
      return Inches,Cost_fill
     
def createPizzaSizeList():
      #create blank list
      PizzaSize = []
      #add pizza to the list
      #PizzaSize.append("[0]" , "[1]")
      PizzaSize.append([0])
      #PizzaSize.append([1])
      return PizzaSize


def insertPizzaSize(db,cursor,PizzaSize):
      for each in PizzaSize:
            sql = "insert into size(Inches, Cost_fill)values('{0}','{1}')".format(Inches,Cost_fill)
            cursor.execute(sql)
      db.commit()
     
     
#main program
if __name__== "__main__":
      try:
            htmlTop()
            db, cursor = connectDB()
            Inches,Cost_fill = getData()
            PizzaSize = createPizzaSizeList()
            insertPizzaSize(db,cursor,PizzaSize)
            htmlTail()
      except:
            cgi.print_exception()
0
 
Dave BaldwinFixer of ProblemsCommented:
It looks good to me at this point.  Commenting out that one line fixed the double INSERT for me so I don't know why it is doing it for you now.

I've been trying to get it to work on my Ubuntu Linux box and it's not cooperating there.  The Python file will run thru IDLE but I get Accessed Denied thru the web browser and server.
0
 
jameskaneAuthor Commented:
Ahhh !!!   Must have been some cache problem - works perfectly now.  

However, I have been struggling with the logic that says it works if we take out PizzaSize.append([1]) it solves the doubles problem.

To be honest cgi I used is an adaptation of  a cgi page I used to create a dynamic form and I got to thinking about the correctness of that.  So I removed the
def createPizzaSizeList():
      #create blank list
      PizzaSize = []
      #add pizza to the list
      #PizzaSize.append("[0]" , "[1]")
      PizzaSize.append([0])
      #PizzaSize.append([1])
      return PizzaSize
and made some adjustments. Now I have something which looks more logical..... AND IT WORKS as well !
Here is the new page :
__________________________________________________
#!C:\Python34\python.exe
import cgi, cgitb
import mysql.connector as conn

formData = cgi.FieldStorage()

def htmlTop():
      print("""Content-type:text/html\n\n
             <DOCTYPE html>
             <html lang="en">
             <head>
             <meta charset="utf-8"/>
             <title>insertrecords.py</title>
             </head>
             <body>""")

def htmlTail():
      print("""We're Done.</body>
             </html>""")
     
def connectDB():
      db=conn.connect(host='localhost' ,user='root' ,passwd='' ,db='pizza')
      cursor = db.cursor()
      return db, cursor

def getData():
      Inches = formData.getvalue('Inches')
      Cost_fill = formData.getvalue('Cost_fill')
      return Inches,Cost_fill
     
def createPizzaSizeList():
      #create blank list
      PizzaSize = []
      #add pizza to the list
      #PizzaSize.append("[0]" , "[1]")
      PizzaSize.append([0])
      #PizzaSize.append([1])
      return PizzaSize


def insertPizzaSize(db,cursor):
      sql = "insert into size(Inches, Cost_fill)values('{0}','{1}')".format(Inches,Cost_fill)
      cursor.execute(sql)
      db.commit()
     
     
#main program
if __name__== "__main__":
      try:
            htmlTop()
            db, cursor = connectDB()
            Inches,Cost_fill = getData()
            insertPizzaSize(db,cursor)
            htmlTail()
      except:
            cgi.print_exception()
______________________________________________________________________

I also cleaned up the update form

<!DOCTYPE HTML>
<html>
<head>
<meta charset="utf-8">
<title>Untitled Document</title>
</head>

<body>

<form action="insertrecords.cgi" method="post" name="admin_form_size" target="_self">

<table width="270" border="1" cellspacing="5" cellpadding="3">
  <caption>
    PIZZA KITCHEN
  </caption>

 
  <tr>
    <td colspan="2">Add New Size</td>
    </tr>
  <tr>
    <td>Number of Inches</td>
    <td>Cost</td>
  </tr>
  <tr>
    <td><label for="Inches"></label>
      <input type="text" name="Inches" id="Inches"></td>
    <td><label for="Cost_fill"></label>
      <input type="text" name="Cost_fill" id="Cost_fill"></td>
  </tr>
  <tr>
    <td colspan="2"><input type="submit" name="submit" id="submit" value="Add new size"></td>
    </tr>
</table>



</form>
</body>
</html>
______________________________________________________________________________________________________________

Interesting to see if that works on your Ubuntu set up.

I think I will use this revised  cgi as a template.  I find the syntax difficult on sql/python front - probably made more confusing by some subtle changes between version 2.7 and 3.4.  Have not been able to find and decent documentation on this area.
0
 
jameskaneAuthor Commented:
Thanks again for your patience and  expertise !

james
0

Featured Post

[Webinar] Database Backup and Recovery

Does your company store data on premises, off site, in the cloud, or a combination of these? If you answered “yes”, you need a data backup recovery plan that fits each and every platform. Watch now as as Percona teaches us how to build agile data backup recovery plan.

  • 6
  • 5
Tackle projects and never again get stuck behind a technical roadblock.
Join Now