Solved

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

Posted on 2014-10-18
11
631 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
  • 6
  • 5
11 Comments
 
LVL 82

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
 
LVL 82

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 82

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
How your wiki can always stay up-to-date

Quip doubles as a “living” wiki and a project management tool that evolves with your organization. As you finish projects in Quip, the work remains, easily accessible to all team members, new and old.
- Increase transparency
- Onboard new hires faster
- Access from mobile/offline

 

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 82

Accepted Solution

by:
Dave Baldwin earned 500 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 82

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

How your wiki can always stay up-to-date

Quip doubles as a “living” wiki and a project management tool that evolves with your organization. As you finish projects in Quip, the work remains, easily accessible to all team members, new and old.
- Increase transparency
- Onboard new hires faster
- Access from mobile/offline

Join & Write a Comment

Here I am using Python IDLE(GUI) to write a simple program and save it, so that we can just execute it in future. Because when we write any program and exit from Python then program that we have written will be lost. So for not losing our program we…
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…
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 if, else, and elif statements in Python 2.7. Use "if" statements to test a specified condition.: The structure of an if statement is as follows: (CODE) Use "else" statements to allow the execution of an alternative, if the …

760 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

Need Help in Real-Time?

Connect with top rated Experts

16 Experts available now in Live!

Get 1:1 Help Now