Web Database Development in MySQL/Python

by K. Yue

1. Python in Web Development

• HOWTO Use Python in the web: https://docs.python.org/3.3/howto/webservers.html

Set up Python on Apache in XAMPP with barebone CGI

In the httpd.conf (Apache startup configuration file), which is likely in c:\xampp\apache:

1. Enable CGI by adding the line "LoadModule cgi_module modules/mod_cgi.so"
2. Find the line "AddHandler cgi-script" and add ".py" at the end. E.g. "AddHandler cgi-script .cgi .pl .asp .py"
3. Add the line "ScriptInterpreterSource Registry-Strict" at the end: this is telling Apache to use Windows registry to identify the interpreter.

This will set Apache to call Python interpreter to handle Web requests with the extension .py.

• It is slow.
• You may install mod_python, which integrates Apache and Python for much faster execution. You may also use Django. However, TA will grade your work using the basic CGI module.

One common problem of Xampp\Apache\Python is that accessing your py page returns a 500+ web server error. One possibility is that Apache does not identify the right Python interpreter to interpret your Python page. In [3] ScriptInterpreterSource Registry, you inform Apache to use windows registry to identify the right Python interpreter and it may not be set correctly. One solution is to remove the line (or comment) it:

#ScriptInterpreterSource Registry

After restarting Apache, you can try your Python page, but only after you explicitly add the she-bang command as the very first line of your Python page, which specifies the location of the Python interpreter. E.g.:

#!C:\Python35-32\Python.exe

Test it with a Web page such as test.py (from HOWTO Use Python in the web):

#!"c:\python35-32\python.exe"
# enable debugging
import cgitb
cgitb.enable()

print("Content-Type: text/plain;charset=utf-8")
print()
print("Hello World!")
 

You need to set the shebang line to point to your Python interpreter, which is likely elsewhere as your version may be more recent.

2. Python-CGI

• Common Gateway Interface (CGI) is a specification for communications between a Web server and an application program.
• There are many ways to develop Python's Web applications.
• In this course, we focus on the most fundamental (and primitive) one, Python CGI: https://docs.python.org/3.5/library/cgi.html#using-the-cgi-module.
• Realistic Web application development should use a more sophisticated platform such as Django.

In the example above,

1. print("Content-Type: text/plain;charset=utf-8") will print to the standard output, such as the command line prompt.
2. Under CGI, when the Web server invoke the Python program, printing to the standard output will send the output to the Web Server.

Steps in static Web pages:

1. A user agent makes a request to Web server with an URL: HTTP Request to the Web server in the URL.
2. The URL is mapped by the Web server to a local resource: URL Mapping maps URL to a data file, usually HTML.
3. The Web server fetches (reads) the file.
4. The Web server prepares a HTTP Response, the body of which is the fetched file.

HTTP Request:

1. HTTP Request header: storing meta data in name-value pairs
2. An empty line
3. HTTP Request body (optional)

HTTP Response

1. HTTP Response header in name-value pairs.
2. An empty line
3. HTTP Response body (optional)

There are many tools that you can use to look at HTTP request and response headers, for example, HTTP Spy extension for Chrome, or using the built-in inspector of the browser.

Example:

Using Chrome to access http://dcm.uhcl.edu/yue/:

HTTP Request header may be:

GET /yue/ HTTP/1.1
Host: dcm.uhcl.edu
Accept: text/html,application/xhtml+xml,application/xml;q=0.9,image/webp,*/*;q=0.8
Accept-Encoding: gzip, deflate, sdch
Accept-Language: en-US,en;q=0.8
Cookie: ASPSESSIONIDSQCQRSBD=INENHEEBLFGOGHKFBKIBLAEO; ASPSESSIONIDSQBRRTAC=BHCPCNOBKEBCIGBIAPPGKCCC; ASPSESSIONIDSSCTRSAD=MICBCLJCAEMDBIBIBNBGGIIE; ASPSESSIONIDSSCRSSAC=GKGLGNDDFIMKDKBHLKPDPCHO; ASPSESSIONIDSQBTTRAC=CBABGKODOJHMDCNONKOCPDHN; ASPSESSIONIDSQARTSBC=IGNDLEDDOJEGAGCMBLEICEEI; ASPSESSIONIDQSARTSAD=PKLPPBDBNCHJPBLALOPCJIGN; ASPSESSIONIDSSARRRDD=CMPNGIHAKBPNAOKKGOGLHPFK; ASPSESSIONIDQSARSTAD=PCADGENBBDKFJEMAMDIKEHOB; ASPSESSIONIDQSDTTQBD=JKHNECHCBMCLEHFBGHFMFELC; ASPSESSIONIDSSARRRCC=BAJNJNGAPJOOBMIDAPOIHJHN; ASPSESSIONIDACSSCTQR=CAJBMBBDMCGDNOLIJBGHEEIO; ASPSESSIONIDCARSAQTQ=KFCHGJABODHAJJCJBPNKMICK; ASPSESSIONIDCQRACTCB=POPHJGADHNGFECOGGEOIOFBA; ASPSESSIONIDCQTCARBD=FOBNDOABCKNJBGPMMFFCHFJJ; ASPSESSIONIDASTCARBC=KDHLNLKBDLAPGEIMILDNOFCB; ASPSESSIONIDCQSDCTCB=HPOBPEECPBNPPBIFPMKPHKAL; ASPSESSIONIDCSQTARAS=BDHFPJDCNCAEPFFMFPABAMKM; ASPSESSIONIDQSSSRRTA=CCPDFCHBILLMAFIMLLNBPOAB; ASPSESSIONIDSQSRRQTA=CMDFCOGDGBHDFAHIIADBABJO; ASPSESSIONIDQSSQSQSA=LJOBGGBAEBNGBODOMKDPFLCF; ASPSESSIONIDQSSRQTTB=GFHPPMMAJMDOCAOBKNKHFLPP; ASPSESSIONIDSSQRTRCS=HKOBDDLAABMCJPGNIGFNKMIJ; ASPSESSIONIDSSTTSTBT=DMMLKGKANJPNILMPOAPMHLID; ASPSESSIONIDCARTQCAS=MJMFMKEDNCKKINPHLCIOGNPA; ASPSESSIONIDAATQTDBS=GPGPOCPDJGFLGEGKGHCLJABE; ASPSESSIONIDACRSQCAT=EDPJEGEBEGAHOFNKLLJCLKBJ; ASPSESSIONIDAAAATTBA=OFJJHHJCONLIOFGCCIHACKCG; ASPSESSIONIDAACBSSAA=BEMNHODDCMIHGABJKDONDLBC; ASPSESSIONIDAADASTBB=ECONFFOBCGANIFAOBCPJDIDH; ASPSESSIONIDAADCRSAA=NEIBBLICBGHFINHGPPFCJAKN; ASPSESSIONIDCCDBTRCA=OKFJCGDDMALOAABLJCKMHBBM; ASPSESSIONIDQQRTSRCQ=IKBJHDODEOFFFANBAHFKAAPI; ASPSESSIONIDSSSTQSDR=CGIJNOHCDGBILOOMDOFGLACH; ASPSESSIONIDQQSRTTDQ=IGLDFICDELFIENPMDINFCLJC; ASPSESSIONIDQCDDTTAQ=IPDJJHNDIHKHJLLPOMHGGLCF; ASPSESSIONIDQCDCRQDQ=ONCNOFBBKPGLDEIGIALKHKPO; ASPSESSIONIDQCAAQSDQ=LEHFKPLDJNMEDOCIHBKDMMEH; ASPSESSIONIDQAADRRDQ=LMNNECGAPNGBLONGIMFFOEAB; ASPSESSIONIDACRCRTQD=BPOHFIKBGEBMEEGOBKKBJAGD; ASPSESSIONIDAATDSRRD=LMGJCJPCJLCBJOOIPLFLNCAA; ASPSESSIONIDSQACSRDR=DGNJNLJBLOMAHDEAAOEFCAMM; ASPSESSIONIDQQADQSDQ=PJJFHIOAAGKDJDAFKDIGOMJN; ASPSESSIONIDQSCDQTCQ=JKCHLINCGEHJDFEGHNIBKMIM; ASPSESSIONIDSSBCSSDR=MCGPECIDJNGIEFBMLEHKNOKJ; ASPSESSIONIDSSDCQTDR=COIDIPBAMMBLPGFKCKDEPMNG; ASPSESSIONIDSACSBBTR=DIKJFGMAEDOKGKBFFGANFLNF; __utmc=61056616; ASPSESSIONIDCATSDDST=HIBNGHNDHJLCEKFOFAEDHNPA; ASPSESSIONIDAAQSCDTS=FADLJFICPIFBOGEPFAPEGNIE; ASPSESSIONIDASQRTTAQ=FPHHDCPAGFFKAKCCKEABAJBJ; ASPSESSIONIDAQRTQQDQ=PCPHCJDCOCCGMJOIMDBOKGHG; ASPSESSIONIDASQSTSAQ=KHFDEONCEBMMCACPPGDMNKDB; has_js=1; ASPSESSIONIDQAADDRQA=DPCFGJLALPHKCEFJLCMAIMNJ; ASPSESSIONIDSCCQASAR=CLKFPHBDNCPCDAPGFHOBJEMA; ASPSESSIONIDQAATATBR=NNEHOJMDJCFNKGNFPMDHPJNM; ASPSESSIONIDSQTTCDTA=HKJDFDAABHHHOPGFFDOHDFDO; ASPSESSIONIDSSSQADSC=DDNBIEOBDFJCLODCMNFHINHA; __utma=66415163.264850463.1309620985.1410922342.1428450784.123; __utmc=66415163; __utmz=66415163.1428450784.123.1.utmccn=(direct)|utmcsr=(direct)|utmcmd=(none); .UHCLAuthNet=89A9D736D3B96923E9BCB6E6C53E80E0DDC8DDD4BD0D724749FD14CFF04168C3FBBCDEF2182008646D15199DA8CDF39711261C2BC52AA1EE77D35E00B6FF5C66B3C0BBEE170AC1AB582A889BAA0419EFB36052C406E4372450A65406A8D11844E4828C4802058711568C15831E272DE827ED0C3E; WT_FPC=id=172.29.1.35-3959882976.30274169:lv=1438888300784:ss=1438888300784; _ga=GA1.2.903118926.1438958881
Upgrade-Insecure-Requests: 1
User-Agent: Mozilla/5.0 (Windows NT 6.1; WOW64) AppleWebKit/537.36 (KHTML, like Gecko) Chrome/44.0.2403.155 Safari/537.36
 

HTTP Response header:

HTTP/1.1 200 OK
Accept-Ranges: bytes
Content-Encoding: gzip
Content-Length: 1970
Content-Type: text/html
Date: Wed, 12 Aug 2015 19:50:09 GMT
ETag: "3be2b1c6662fd01:0"
Last-Modified: Tue, 13 Jan 2015 19:26:06 GMT
Server: Microsoft-IIS/7.5
Vary: Accept-Encoding
X-Powered-By: ASP.NET

Dynamic Web Page Generation

1. A user agent makes a request to Web server with an URL: HTTP Request to the Web server in the URL.
2. The URL is mapped by the Web server to a local resource: URL Mapping maps URL to a program file, such as .aspx, .php, .cgi, .pl, .py, etc.
3. The Web server invokes the appropriate interpreter to execute the program.
4. Communications between the Web server and the program uses the CGI standard.
5. Standard output from the program is sent to the Web server.
6. The Web server prepares a HTTP Response accordingly.

Example:

In the program test.py:

#!"c:\python35-32\python.exe"
# enable debugging
import cgitb
cgitb.enable()

print("Content-Type: text/plain;charset=utf-8") # HTTP Response Header
print() # Blank Line
print("Hello World!") # HTTP Response Body

the Python CGI program print out:

1. A part of the HTTP Response header: "Content-Type: text/plain;charset=utf-8"
2. An empty line
3. THe HTTP Response body: "Hello World"

The Web Server will 'dress up' the HTTP Response Header. It may become something likes:

HTTP/1.1 200 OK
alt-svc: quic=":443"; p="1"; ma=604800
alternate-protocol: 443:quic,p=1
cache-control: no-cache, no-store, max-age=0, must-revalidate
content-type: text/plain; charset=utf-8
date: Thu, 13 Aug 2015 01:43:36 GMT
expires: Fri, 01 Jan 1990 00:00:00 GMT
pragma: no-cache
server: GSE
status: 200
x-content-type-options: nosniff
x-xss-protection: 1; mode=block
 

• The Python program is used to implement the logic of the Web application, including database access.

Example

cgi2.py:

#!"c:\python35-32\python.exe"
import cgi
import cgitb
cgitb.enable()
import mysql.connector

#   cgi: Support module for Common Gateway Interface (CGI) scripts.
#   cgitb: Traceback manager for CGI scripts
#   cgitb.enable(): enable trace back feature

#   Minimal HTTP Response Header information for
#   delivering HTML content
print("Content-Type: text/html;charset=utf-8")

#   A blank line as the separator of HTTP Response header and body.
print()

print ('''<html>
<head></head>
<body>
''')

cnx = mysql.connector.connect(user='...', password='...',
    host='localhost', -- because the Web server and MySQL server are in the same server computer
    database='c4333f15')
cursor = cnx.cursor()

query = '''
SELECT FirstName, LastName, Phone, AmtLastYear
FROM Donor
WHERE AmtLastYear > 0
ORDER BY AmtLastYear DESC;
'''

cursor.execute(query)

#   Read data and generate code for a HTML table.
print('''
<table border='1'>
<tr><th>Name</th><th>Phone</th><th>Amount donated last year</th></tr>
''')

for (first_name, last_name, phone, amount) in cursor:
    print("<tr><td>{} {}</td><td>{}</td><td>${:,.0f}</td></tr>".format(first_name, last_name, phone, amount))

cursor.close()
cnx.close()

print ('''
</table>
</body>
</html>
''')

The output sent to the Web server:

Content-Type: text/html;charset=utf-8

<html>
<head></head>
<body>


<table border='1'>
<tr><th>Name</th><th>Phone</th><th>Amount donated last year</th></tr>

<tr><td>John Smith</td><td>303-222-2002</td><td>$1,500</td></tr>
<tr><td>Linda Smith</td><td>303-222-2002</td><td>$1,000</td></tr>
<tr><td>John Doe</td><td>134-800-1134</td><td>$255</td></tr>
<tr><td>Paul Carter</td><td>212-121-2203</td><td>$200</td></tr>
<tr><td>Jack Clinton</td><td>134-200-1134</td><td>$55</td></tr>
<tr><td>Jane Doe</td><td>134-800-1134</td><td>$55</td></tr>

</table>
</body>
</html>
 

Note that the example above does not use dbconfig.py and dbconfig.ini. If you want to use them in the dcm server, you will need to use the followings:

dbconfig.py:

import configparser
import os

#  simplistic and no error handling.
def get_mysql_param(
      filename=os.path.dirname(os.environ['PATH_TRANSLATED']) + "\\dbconfig.ini",
        section='mysql'):
    config = configparser.ConfigParser()
    config.read(filename)

    return config['mysql']

This is needed because the relative filename in Python script in IIS web server is not relative to the script directory.

dbconfig.ini:

[mysql]
host = localhost
database = ...
user = ...
password = ...

• HTTP provides various methods for the user agents to submit data to the Web servers.
• Data are submitted as name-value paired parameters.
• The main ones are GET and POST:
• GET:
  1. The parameters are a part of the URL after '?', known as the query string.
  2. The HTTP request body is empty.
• POST:
  1. The parameters are not a part of the URL.
  2. The HTTP request body stores the parameters.
• In Python CGI module, no matter whether GET or POST are used, HTTP parameters can be obtained through the dictionary cgi.FieldStorage().

Example:

cgi1.py:

#!"c:\python35-32\python.exe"
import cgi
import cgitb
cgitb.enable()

print("Content-Type: text/html;charset=utf-8")
print()

print ('''<html>
<head></head>
<body>
''')

form = cgi.FieldStorage()
if "name" not in form or "addr" not in form:
    print("<H1>Error</H1>")
    print("Please fill in the name and addr fields.")
    quit()
print("<p>name:", form["name"].value)
print("<p>addr:", form["addr"].value)

print ('''</body>
</html>
''')
 

For: http://localhost/python/tinker/cgi1.py

or

http://localhost/python/tinker/cgi1.py?name=Yue

or

http://localhost/python/tinker/cgi1.py?Addr=UHCL

The data sent by the program to the Web server:

Content-Type: text/html;charset=utf-8

<html>
<head></head>
<body>

<H1>Error</H1>
Please fill in the name and addr fields.
 

For: http://localhost/python/tinker/cgi1.py?name=Bun&addr=UHCL:

The data sent by the program to the Web server:

Content-Type: text/html;charset=utf-8

<html>
<head></head>
<body>

<p>name: Yue
<p>addr: UHCL
</body>
</html>

The actual HTTP Response sent by the Web Server may be:

HTTP/1.1 200 OK
Connection: Keep-Alive
Content-Type: text/html;charset=utf-8
Date: Thu, 13 Aug 2015 15:45:23 GMT
Keep-Alive: timeout=5, max=100
Server: Apache/2.2.21 (Win32) mod_ssl/2.2.21 OpenSSL/1.0.0e PHP/5.3.8 mod_perl/2.0.4 Perl/v5.10.1
Transfer-Encoding: chunked

<html>
<head></head>
<body>

<p>name: Yue
<p>addr: UHCL
</body>
</html>
 

• FieldStorage provides other useful utilities. For example:
  • getfirst(name[, default]): get the first value of a multi-valued parameter.
  • getlist(name): get the entire list of values of a parameter.

Basic steps in Web DB development:

1. Identify the input parameters.
2. Develop the desirable Web page for typical input parameters.
3. Analyze the code of (2) and identify which parts are constants and which parts (dynamic data) are generated from database.
4. Construct and test he SQL to fetch the dynamic data.
5. Write the Python code for (3) accordingly.

Example:

Write a Python CGI program, toyu_1.py, to accept a HTTP Get parameter major and display the students majoring in major and their number of enrolled courses.

For example, for http://.../toyu_1.py?major=CSCI:

There is no need for error checking of the user input parameter major. A skeleton for t2.py is provided for you.

Solution:

[1] Identify the input parameters.

major: typical values: 'CSCI' and 'ITEC'.

[2] Develop the desirable Web page for typical input parameters.

For CSCI:

<html>
<head></head>
<body>

<p>Students and their numbers of classes enrolled in the major CSCI:</p>
<table border='1'>
<tr><th>Student Name</th><th>Number of classes</th></tr>

<tr><td>David Hawk</td><td>3</td></tr>
<tr><td>Mary Hawk</td><td>2</td></tr>
<tr><td>Tony Hawk</td><td>6</td></tr>
</body>
</html>
 

For ITEC:

<html>
<head></head>
<body>

<p>Students and their numbers of classes enrolled in the major ITEC:</p>
<table border='1'>
<tr><th>Student Name</th><th>Number of classes</th></tr>

<tr><td>Larry Johnson</td><td>2</td></tr>
<tr><td>Catherine Lim</td><td>0</td></tr>
</body>
</html>
 

[3] Identify dynamic data: Analyze the code of (2) and identify which parts are constants and which parts (dynamic data) are generated from database.

For CSCI:

<html>
<head></head>
<body>

<p>Students and their numbers of classes enrolled in the major CSCI:</p>
<table border='1'>
<tr><th>Student Name</th><th>Number of classes</th></tr>

<tr><td>David Hawk</td><td>3</td></tr>
<tr><td>Mary Hawk</td><td>2</td></tr>
<tr><td>Tony Hawk</td><td>6</td></tr>
</body>
</html>
 

For ITEC:

<html>
<head></head>
<body>

<p>Students and their numbers of classes enrolled in the major ITEC:</p>
<table border='1'>
<tr><th>Student Name</th><th>Number of classes</th></tr>

<tr><td>Larry Johnson</td><td>2</td></tr>
<tr><td>Catherine Lim</td><td>0</td></tr>
</body>
</html>

Dynamic Data:

• For each student, student.lname, student.fname, count of classId in the enroll table of the student.

[4] Construct and test the SQL to fetch the dynamic data.

for CSCI:

SELECT CONCAT(s.fName, ' ', s.lName) AS student,
   COUNT(e.classId) as num_classes
FROM student AS s LEFT JOIN enroll AS e ON (s.stuId = e.stuId)
WHERE s.major = 'CSCI'
GROUP BY s.stuId, student
ORDER BY s.lName, s.fName;

for ITEC:

SELECT CONCAT(s.fName, ' ', s.lName) AS student,
   COUNT(e.classId) as num_classes
FROM student AS s LEFT JOIN enroll AS e ON (s.stuId = e.stuId)
WHERE s.major = 'ITEC'
GROUP BY s.stuId, student
ORDER BY s.lName, s.fName;

The typical input will be replaced by a parameter in the Python/SQL code.

[5] Write the Python code for (3) accordingly.

#!"c:\python35-32\python.exe"
from toyu_config import *
import pymysql
import cgi
import cgitb
cgitb.enable()

print("Content-Type: text/html;charset=utf-8")
print()

print ('''<html>
<head></head>
<body>
''')

#  
db = get_mysql_param()
cnx = pymysql.connect(user=db['user'],
                      password=db['password'],
                      host=db['host'],
                      # port needed only if it is not the default number, 3306.
                      port = int(db['port']),
                      database=db['database'])
cursor = cnx.cursor()                
                             
form = cgi.FieldStorage()

major = form.getfirst('major')
if major is None:
    print ('Please enter a valid major code in the URL.')
    print ('</body></html>')
    quit()



query = '''
SELECT CONCAT(s.fName, ' ', s.lName) AS student,
   COUNT(e.classId) as num_classes
FROM student AS s LEFT JOIN enroll AS e ON (s.stuId = e.stuId)
WHERE s.major = %s
GROUP BY s.stuId, student
ORDER BY s.lName, s.fName;
'''

cursor.execute(query,(major,))
row = cursor.fetchone()

if row is None:
    print ('Sorry, no major with code {}. Please enter a valid major code in the URL.'.format(major))
    print ('</body></html>')
    quit()

print('''<p>Studnts and their numbers of classes enrolled in the major {}:</p>
<table border='1'>
<tr><th>Student Name</th><th>Number of classes</th></tr>
'''.format(major))
while row is not None:
    (student, count) = row
    print("<tr><td>{}</td><td>{}</td></tr>"
        .format(student, count))
    row = cursor.fetchone()
cursor.close()
cnx.close()     
                
print ('''</body>
</html>''')