?
Solved

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

Posted on 2014-10-18
11
Medium Priority
?
857 Views
Last Modified: 2014-10-19
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
Comment
Question by:jameskane
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
  • 6
  • 5
11 Comments
 
LVL 84

Expert Comment

by:Dave Baldwin
ID: 40389678
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
 

Author Comment

by:jameskane
ID: 40389697
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
 

Author Comment

by:jameskane
ID: 40389710
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
Free Tool: IP Lookup

Get more info about an IP address or domain name, such as organization, abuse contacts and geolocation.

One of a set of tools we are providing to everyone as a way of saying thank you for being a part of the community.

 
LVL 84

Expert Comment

by:Dave Baldwin
ID: 40389721
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
 
LVL 84

Expert Comment

by:Dave Baldwin
ID: 40389723
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
 

Author Comment

by:jameskane
ID: 40389725
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
 
LVL 84

Accepted Solution

by:
Dave Baldwin earned 2000 total points
ID: 40389736
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
 

Author Comment

by:jameskane
ID: 40389846
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
 
LVL 84

Expert Comment

by:Dave Baldwin
ID: 40389875
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
 

Author Comment

by:jameskane
ID: 40389963
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
 

Author Closing Comment

by:jameskane
ID: 40389982
Thanks again for your patience and  expertise !

james
0

Featured Post

Free Tool: Path Explorer

An intuitive utility to help find the CSS path to UI elements on a webpage. These paths are used frequently in a variety of front-end development and QA automation tasks.

One of a set of tools we're offering as a way of saying thank you for being a part of the community.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Flask is a microframework for Python based on Werkzeug and Jinja 2. This requires you to have a good understanding of Python 2.7. Lets install Flask! To install Flask you can use a python repository for libraries tool called pip. Download this f…
When we want to run, execute or repeat a statement multiple times, a loop is necessary. This article covers the two types of loops in Python: the while loop and the for loop.
Learn the basics of strings in Python: declaration, operations, indices, and slicing. Strings are declared with quotations; for example: s = "string": Strings are immutable.: Strings may be concatenated or multiplied using the addition and multiplic…
Learn the basics of while and for loops in Python.  while loops are used for testing while, or until, a condition is met: The structure of a while loop is as follows:     while <condition>:         do something         repeate: The break statement m…
Suggested Courses

765 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question