Solved

MYSQL conditional select USAGE WITH PYTHON

Posted on 2014-09-26
10
815 Views
Last Modified: 2014-09-29
I am using python 3.4 and apache server and mysql within XXAMP. There are no problems relating to mysql / python connect to the server. This is working correctly.

Going up a learning curve so this problem is probably 101 !  But I am stuck.

I have a form consisting of one dropdown menu and one row of radio buttons (image attached). The form  is dynamically filled from a mysql table called PERSON. It is attached. This form points to an action page which is a .cgi page written in python.The cgi code is shown below.

My objective is to simply print out, in a table, the selection made from the dropdown  menu and radio buttons in the form.  The following function is how I planned to retrieve the form data

def getData():
      formData = cgi.FieldStorage()
      personid1 = formData.getvalue('drop')
      personid2 = formData.getvalue('rbutton')
      return personid1, personid2

This only provides 'personid'  for the  form selection.  However, my objective is to printout the firstname and lastnames chosen via the form  not just the personid.

At this point I extended the scope of the cgi page - connecting back to the table and running an sql query to select the firstname and last name of the personid's  provided by fieldstorage. The key function for this is

def selectPeople(db,cursor):
      xyz = getData()
      sql = "select * from person where personid= 'personid1' or 'personid2';"
      cursor.execute(sql)
      #fetch the results as a list
      people = cursor.fetchall()
      return people

There is a problem with the SQL statement - it does not return any selections. If I change the sql statement to be
sql="select*from person where personid = 2 or 4
I do get the table output showing the correct names - so the table works.

In summary, can you help me with the sql statement and can you advise if there is any alternative to  CGI.FIELDSTORAGE which provide more information about the form selection.


++++++++++++++++++++++++++++++++   cgi acion page for form.-----------------------------------------------------


#!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='exampledb')
      cursor = db.cursor()
      return db, cursor
     
def getData():
      formData = cgi.FieldStorage()
      personid1 = formData.getvalue('drop')
      personid2 = formData.getvalue('rbutton')
      return personid1, personid2


def selectPeople(db,cursor):
      xyz = getData()
      sql = "select * from person where personid= 'personid1' or 'personid2';"
      cursor.execute(sql)
      #fetch the results as a list
      people = cursor.fetchall()
      return people

def displayPeople(people):
      print("<table border='1'>")
      print("<tr>")
      print("<th>ID</th>")
      print("<th>First Name</th>")
      print("<th>Last Name</th>")
      print("</tr>")
      for each in people:
            print("<tr>")
            print("<td>{0}</td>".format(each[0]))
            print("<td>{0}</td>".format(each[1]))
            print("<td>{0}</td>".format(each[2]))
            print("</tr>")
     
      print("</table>")

           
#main program
if __name__== "__main__":
      try:
            htmlTop()
            xyz = getData()
            print("{0[0]} {0[1]}<br/>\n".format(xyz))
            db,cursor = connectDB()
            people = selectPeople(db,cursor)
            cursor.close()
            displayPeople(people)
            htmlTail()
      except:
            cgi.print_exception()
MYTABLE.jpg
FORM.JPG
0
Comment
Question by:jameskane
  • 5
  • 3
  • 2
10 Comments
 
LVL 26

Expert Comment

by:Zberteoc
ID: 40347481
SQL statement is wrong. It should be:
sql = "select * from person where personid= 'personid1' or personid= 'personid2';"

Open in new window

or
sql = "select * from person where personid in ('personid1', 'personid2');"

Open in new window

And actually giving the fact that the personid column is an integer you don't need the quotes around the id values, however it would work with them too:
sql = "select * from person where personid= personid1 or personid= personid2;"

Open in new window

or
sql = "select * from person where personid in (personid1, personid2);"

Open in new window

0
 

Author Comment

by:jameskane
ID: 40347595
Many thanks for the reply Zberteoc.

I tried all the 4 possible entries with no success. Here are the results :-


sql = "select * from person where personid= 'personid1' or personid= 'personid2';"
no error, but no records appear in table

sql = "select * from person where personid in ('personid1', 'personid2');"
no error, but no records appear in table

sql = "select * from person where personid= personid1 or personid= personid2;"
mysql.connector.errors.ProgrammingError: 1054 (42S22): Unknown column 'personid1' in 'where clause'

sql = "select * from person where personid in (personid1, personid2);"
mysql.connector.errors.ProgrammingError: 1054 (42S22): Unknown column 'personid1' in 'where clause'

To recap, I am able to get a result if I hard wire two examples into the sql :
sql = "select * from person where personid = 2 or  personid = 5;"
If I select 2 and 5 on the form I get the correct table .

Again, many thanks for taking the time on this !
noentries.JPG
ENTRIES.JPG
0
 
LVL 26

Expert Comment

by:Zberteoc
ID: 40347708
Only try the queries that I posted. Yours are syntactically incorrect. Try one thing: run the query directly in MySQL see if you get any results. I hope that you actually replaced the person1 and personid2 with real ID values and by that I mean 1,2,3... This is what you should try:

select * from person where personid= 1or personid= 2;

Run this directly in MySQL with PHPadmin see if you get anything. If yes then try the same query it in your code.
0
 

Author Comment

by:jameskane
ID: 40347981
hI  Zberteoc,

Yes,
 select * from person where personid= 1 or personid= 2;

works perfectly .

However, I do of course want to use a variable in the sql in place of the 1 or 2 .

This is the function which feeds the sql statement - where drop and rbutton are the  field names.

def getData():
      formData = cgi.FieldStorage()
      personid1 = formData.getvalue('drop')
      personid2 = formData.getvalue('rbutton')
      return personid1, personid2

 I need to use personid1 or personid2 in the sql statement, which is what I did in the examples. There must be something wrong with the getData() ??

Sorry, but I am a little lost. Hope the above makes sense ?
0
 
LVL 25

Expert Comment

by:clockwatcher
ID: 40348120
You need to use a parameterized query with placeholders for your data.    See the section on parameters in the pyodbc docs (https://code.google.com/p/pyodbc/wiki/GettingStarted).

def selectPeople(db,cursor):
       (personid1, personid2) = getData()
       sql = "select * from person where personid= ? or personid = ?"
       cursor.execute(sql, (personid1, personid2) )
       #fetch the results as a list
       people = cursor.fetchall()
       return people

Open in new window

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: 40348305
Many thanks for the input clockwatcher. The link is very interesting !

I used your selectpeople function as shown below. Unfortunately it still results in an error.

mysql.connector.errors.ProgrammingError: Not all parameters were used in the SQL statement

----------------------- extract from code------------------------------------------
def getData():
      formData = cgi.FieldStorage()
      personid1 = formData.getvalue('drop')
      personid2 = formData.getvalue('rbutton')
      return personid1, personid2
     

def selectPeople(db,cursor):
      (personid1, personid2) = getData()
      sql = "select * from person where personid = ? or personid = ?"
      cursor.execute(sql, (personid1, personid2) )
      #fetch the results as a list
      people = cursor.fetchall()
      return people
please note that I am sure the code is 'python' correct in the real page. I am using  WIND IDE and I ran a check on the syntax of the page.
----------------------------------------------------------------------------------------------------------------------------------
For information ...

I am attaching an image of the error message - at the top  you will see 3,4 - these are the two form selections I made when running the revised page. The were generated from the "main program" - shown below.  This tells me that I am getting the data ok from getData() .

#main program
if __name__== "__main__":
      try:
            htmlTop()
            xyz = getData()
            print("{0[0]} {0[1]}<br/>\n".format(xyz))            
            db,cursor = connectDB()
            people = selectPeople(db,cursor)
            cursor.close()
            displayPeople(people)
            htmlTail()
      except:
            cgi.print_exception()
aaaa.JPG
0
 
LVL 25

Expert Comment

by:clockwatcher
ID: 40348700
Didn't realize you were using the MySQL connector rather than an ODBC connector.  By default it expects a different placeholder than a '?' for its parameters.  Try changing your sql statement to:
sql = "select * from person where personid = %s or personid = %s"

Open in new window

0
 

Author Comment

by:jameskane
ID: 40348756
Thanks for taking the time on this - especially on weekend !!

I implemented the change as show below but, although the error has been eliminated,   there is no result. I attach an image of the result - you will see that the table is empty, although at the top the personIDs I selected show up.

Sorry for the time this has taken !!

---------------------------------------------------------------------------------------------

def getData():
      formData = cgi.FieldStorage()
      personid1 = formData.getvalue('drop')
      personid2 = formData.getvalue('rbutton')
      return personid1, personid2
     

def selectPeople(db,cursor):
      (personid1, personid2) = getData()
      #sql = "select * from person where personid = ? or personid = ?"
      sql = "select * from person where personid = %s or personid = %s"
      cursor.execute(sql, (personid1, personid2) )
      #fetch the results as a list
      people = cursor.fetchall()
      return people
bbbb.JPG
0
 
LVL 25

Accepted Solution

by:
clockwatcher earned 500 total points
ID: 40349056
Your problem now is that you can't make multiple calls to cgi.FieldStorage().  Calling FieldStorage() consumes the HTTP request body stream.  After the first call the request body is empty and that second call will have an empty body to deal with.   You can verify that with this example:

if __name__== "__main__":
       try:
             htmlTop()
             print("<p>First call to getData</p>")           
             xyz = getData()
             print("{0[0]} {0[1]}<br/>\n".format(xyz)) 
             print("<p>Second call to getData</p>")            
             xyz = getData()
             print("{0[0]} {0[1]}<br/>\n".format(xyz))             
             htmlTail()
       except:
             cgi.print_exception() 

Open in new window


If you run the above, you'll notice the second call to getData returns None, None.  And those Nones are what you're passing into your SQL query which is why you're not getting back any records.

A more pythonic way of handling all of this is to encapsulate your functionality into a class.
#!/usr/bin/env python3
import cgi
import mysql.connector as conn

class PeopleCGI(object):
    def __init__(self, host="localhost", user="root", passwd="", db="exampledb"):
        self.db = conn.connect(host=host, user=user, passwd=passwd, db=db)
        self.cursor = self.db.cursor()
        self.formData = cgi.FieldStorage()

    def __enter__(self):
        return self

    def __exit__(self, type, value, traceback):
        self.cursor.close()
        self.db.close()

    def htmlTop(self):
        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(self):
        print("""</body>
              </html>""")


    def getData(self):
        formData = self.formData
        personid1 = formData.getvalue('drop')
        personid2 = formData.getvalue('rbutton')
        return personid1, personid2

    def selectPeople(self):
        personid1, personid2 = self.getData()
        sql = "select * from person where personid=%s or personid=%s"
        self.cursor.execute(sql, (personid1, personid2))
        #fetch the results as a list
        people = self.cursor.fetchall()
        return people

    def displayPeople(self, people):
        print("<table border='1'>")
        print("<tr>")
        print("<th>ID</th>")
        print("<th>First Name</th>")
        print("<th>Last Name</th>")
        print("</tr>")
        for each in people:
            print("<tr>")
            print("<td>{0}</td>".format(each[0]))
            print("<td>{0}</td>".format(each[1]))
            print("<td>{0}</td>".format(each[2]))
            print("</tr>")
        print("</table>")

    def people_drop(self):
        sql = "select personid,rtrim(concat(firstname,' ',lastname)) from person"
        self.cursor.execute(sql)
        return '<select name="drop">' + ''.join(['<option value="{0}">{1}</option>'.format(*row) for row in self.cursor.fetchall()]) + "</select>"

    def people_radio(self):
        sql = "select personid,rtrim(concat(firstname,' ',lastname)) from person"
        self.cursor.execute(sql)
        return '<br>'.join(['<input type="radio" name="rbutton" value="{0}">{1}'.format(row[0], row[1]) for row in self.cursor.fetchall()])

    def htmlForm(self):
        print('<form method="post">{0}<br />{1}<br /><input type="submit"></form><hr />'.format(self.people_drop(), self.people_radio()))

    def outputHTML(self):
        self.htmlTop()
        self.htmlForm()
        xyz = self.getData()
        print("{0[0]} {0[1]}<br/>\n".format(xyz))
        people = self.selectPeople()
        self.displayPeople(people)
        self.htmlTail()

#main program
if __name__== "__main__":
    try:
        with PeopleCGI(user='root', passwd='', db='exampledb') as htmlPage:
            htmlPage.outputHTML()
    except:
        cgi.print_exception()

Open in new window

0
 

Author Closing Comment

by:jameskane
ID: 40349438
Many thanks clockwatcher -  for the solution PLUS the education and pointers. Can't expect any more than that !!

Very much appreciated.

jim
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

Introduction On September 29, 2012, the Python 3.3.0 was released; nothing extremely unexpected,  yet another, better version of Python. But, if you work in Microsoft Windows, you should notice that the Python Launcher for Windows was introduced wi…
Load balancing is the method of dividing the total amount of work performed by one computer between two or more computers. Its aim is to get more work done in the same amount of time, ensuring that all the users get served faster.
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 …
Learn the basics of modules and packages in Python. Every Python file is a module, ending in the suffix: .py: Modules are a collection of functions and variables.: Packages are a collection of modules.: Module functions and variables are accessed us…

757 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

21 Experts available now in Live!

Get 1:1 Help Now