MYSQL conditional select USAGE WITH PYTHON

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
jameskaneAsked:
Who is Participating?
I wear a lot of hats...

"The solutions and answers provided on Experts Exchange have been extremely helpful to me over the last few years. I wear a lot of hats - Developer, Database Administrator, Help Desk, etc., so I know a lot of things but not a lot about one thing. Experts Exchange gives me answers from people who do know a lot about one thing, in a easy to use platform." -Todd S.

ZberteocCommented:
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
jameskaneAuthor Commented:
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
ZberteocCommented:
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
Cloud Class® Course: Microsoft Office 2010

This course will introduce you to the interfaces and features of Microsoft Office 2010 Word, Excel, PowerPoint, Outlook, and Access. You will learn about the features that are shared between all products in the Office suite, as well as the new features that are product specific.

jameskaneAuthor Commented:
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
clockwatcherCommented:
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
jameskaneAuthor Commented:
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
clockwatcherCommented:
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
jameskaneAuthor Commented:
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
clockwatcherCommented:
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

Experts Exchange Solution brought to you by

Your issues matter to us.

Facing a tech roadblock? Get the help and guidance you need from experienced professionals who care. Ask your question anytime, anywhere, with no hassle.

Start your 7-day free trial
jameskaneAuthor Commented:
Many thanks clockwatcher -  for the solution PLUS the education and pointers. Can't expect any more than that !!

Very much appreciated.

jim
0
It's more than this solution.Get answers and train to solve all your tech problems - anytime, anywhere.Try it for free Edge Out The Competitionfor your dream job with proven skills and certifications.Get started today Stand Outas the employee with proven skills.Start learning today for free Move Your Career Forwardwith certification training in the latest technologies.Start your trial today
MySQL Server

From novice to tech pro — start learning today.

Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.