Web Interfaces for Database Servers

We consider a web interface to our database with Python scripts.

CGI, pymysql, and Sockets

Our Goal: to build a web interface to a MySQL database.

The client/server interaction has two components:

  1. The server is a Python script which uses pymysql (or MySQLdb).
  2. The client is a CGI script which defines the web interface.

To illustrate the building of an interface, we consider OurPyFiles as the example databse, with the data in its table scripts.

Steps in incremental development:

  1. Our test script counts the number of records (a simple query).
  2. The server listens to one connection and sends to the client the number of records.
  3. We run the client script first at the command line.
  4. The second version of the client script writes plain text on web page.

The scripts_count.py prints the number of records in the table scripts of the MySQL database OurPyFiles. For a successful run of this script, the MySQL server must be started and the table scripts must have been defined in the database OurPyFiles. Our test script is short:

import pymysql

OURDB = pymysql.connect(db='OurPyFiles')
CRS = OURDB.cursor()
QRY = 'select count(*) from scripts'
CRS.execute(QRY)
RES = CRS.fetchone()
NBR = int(RES[0])
print('the number of scripts : %d' % NBR)

To demonstrate the client/server interaction, consider the terminal window where the server script was launched:

$ python scripts_server.py
server waits for connection
server accepted connection from  ('127.0.0.1', 54814)
server connects to database
server sends #scripts to client
count sent, closing off
$

In another terminal, the client script is executed:

$ python scripts_client.py
client is connected
client received "96"
$

The server script has the following structure:

import pymysql # import MySQLdb
from socket import socket as Socket

def connect():
    """
    Returns client and server socket
    to communicate with one client.
    """

def count():
    """
    Returns the number of scripts.
    """

def main():
    """
    Accepts connection and sends #scripts.
    """

The network connections in the server script are defined as follows:

HOSTNAME = ''      # use any address
PORTNUMBER = 11267 # number for the port
BUFFER = 80        # size of the buffer

def connect():
    """
    Returns client and server socket
    to communicate with one client.
    """
    from socket import AF_INET, SOCK_STREAM
    server_address = (HOSTNAME, PORTNUMBER)
    server = Socket(AF_INET, SOCK_STREAM)
    server.bind(server_address)
    server.listen(1)
    print('server waits for connection')
    client, client_address = server.accept()
    print('server accepted connection from ',\
        client_address)
    return client, server

The counting the number of records is defined by the function count() below.

def count():
    """
    Returns the number of scripts.
    """
    # ourdb = MySQLdb.connect(db='OurPyFiles')
    ourdb = pymysql.connect(db='OurPyFiles')
    crs = ourdb.cursor()
    qry = 'select count(*) from scripts'
    crs.execute(qry)
    res = crs.fetchone()
    nbr = int(res[0])
    return nbr

The function main() is defined below.

def main():
    """
    Accepts connection and sends result.
    """
    client, server = connect()
    print('server connects to database')
    nbr = count()
    print('server sends #scripts to client')
    data = str(nbr)
    client.send(data.encode())
    print('count sent, closing off')
    server.close()

Observe the top down design, with two functions. The function connect() encapsulates the network connections while the function count() encapsulates the MySQL query.

The first version of the client is in scripts_client.py below.

from socket import socket as Socket
from socket import AF_INET, SOCK_STREAM

HOSTNAME = 'localhost'  # on same host
PORTNUMBER = 11267      # same port number
BUFFER = 80             # size of the buffer

SERVER_ADDRESS = (HOSTNAME, PORTNUMBER)
CLIENT = Socket(AF_INET, SOCK_STREAM)
CLIENT.connect(SERVER_ADDRESS)

print('client is connected')
DATA = CLIENT.recv(BUFFER).decode()
print('client received \"' + DATA + '\"')

CLIENT.close()

The above client script runs in a terminal window. The second version of the script will be called by a browser, as shown in Fig. 86.

_images/figscriptsweb.png

Fig. 86 Showing the number of scripts in a browser window.

For this web interface to work, the following must be up and running.

  1. The mysql deamon must have been started.

    $ sudo mysqld_safe

  2. The database server script runs.

    $ python scripts_server.py
server waits for connection

  3. The CGI server script runs.

    $ python myserver.py
welcome to our cgi server
press ctrl c to shut down the server

Then we point the broswer to localhost:8000/scripts_web.py. The second client is defined by scripts_web.py, with the code as listed below.

print("Content-Type: text/plain\n")

from socket import socket as Socket
from socket import AF_INET, SOCK_STREAM

HOSTNAME = 'localhost' # on same host
PORTNUMBER = 11267     # same port number
BUFFER = 80            # size of the buffer

SERVER_ADDRESS = (HOSTNAME, PORTNUMBER)
CLIENT = Socket(AF_INET, SOCK_STREAM)
CLIENT.connect(SERVER_ADDRESS)

print('client is connected')
DATA = CLIENT.recv(BUFFER).decode()
print('Number of scripts : ' + DATA)

CLIENT.close()

Displaying all Records in an HTML Table

In extending the web interface, we want to see all records in the table scripts displayed in a browser window.

  1. The server sends the number of records to the client.
  2. The client receives the number of records.
  3. The server sends all records to the client.
  4. The client receives all records and makes an HTML table to display.

The synchronization is very important: for every send of the server, there must be a matching recv by the client!

The listing of the scripts in a browser is shown in Fig. 87.

_images/figscriptsservdb.png

Fig. 87 Listing the scripts in a table in a browser window.

The folowing functions are defined in scripts_servdb.py which define the actions of the server:

def connect_client():
    """
    Returns client and server socket.
    """
def count_records(crs):
    """
    Given cursor crs, returns the number of scripts.
    """
def retrieve_records(crs):
    """
    Given cursor crs, returns all records.
    """
def pack_tuple(tup):
    """
    Packs the data tuple as a string.
    """

The above functions are then called in the main() of scripts_servdb.py as follows:

def main():
    """
    Accepts connection and sends records.
    """
    ourdb = pymysql.connect(db='OurPyFiles')
    crs = ourdb.cursor()
    nbr = count_records(crs)
    client, server = connect_client()
    client.send(str(nbr).encode())
    records = retrieve_records(crs)
    for record in records:
        client.send(pack_tuple(record))
    server.close()

The counting of the records is encapsulated in the function count_records(crs) defined next:

def count_records(crs):
    """
    Returns the number of scripts,
    given the cursor crs.
    """
    qry = 'select count(*) from scripts'
    crs.execute(qry)
    result = crs.fetchone()
    nbr = int(result[0])
    return nbr

and the retrieval of all records is encoded in the following function:

def retrieve_records(crs):
    """
    Given cursor crs, returns all records.
    """
    qry = 'select * from scripts'
    crs.execute(qry)
    return crs.fetchall()

A record is returned as a data tuple

('L', '26', '2017-03-10', 'guidb1.py')

To send the data to the client, we pack it as 'L-26:2017-03-10:guidb1.py'

def pack_tuple(tup):
    """
    Packs the tuple as string with items
    separated by colons.  Notice the padding!
    """
    result = tup[0] + '-' + str(int(tup[1])) + ':' \
        + str(tup[2]) + ':' + tup[3] + ':'
    result += (BUFFER - len(result))*' '
    return result

The BUFFER is a global variable and holds the size of the messages passed between client and server.

The code in the client to display the HTML table in scripts_showall.py is listed below.

#!/usr/bin/python

from socket import socket as Socket
from socket import AF_INET, SOCK_STREAM

HOSTNAME = 'localhost' # on same host
PORTNUMBER = 11267     # same port number
BUFFER = 80            # size of the buffer

def print_header(title):
    """
    writes title and header of page
    """
    print("""Content-type: text/html

<html>
<head>
<title>%s</title>
</head>
<body>""" % title)

The main function in the client follows.

def main():
    """
    Connects and prints data of server.
    """
    print_header('showing all scripts')
    server_address = (HOSTNAME, PORTNUMBER)
    client = Socket(AF_INET, SOCK_STREAM)
    client.connect(server_address)
    data = client.recv(BUFFER).decode()
    nbr = int(data)
    print("<B>Number of scripts : %d</B>" % nbr)
    retrieve_table(client, nbr)
    client.close()

The retrieval of the data and the formatting of the records in an HTML table is defined by the function below.

def retrieve_table(sock, nbr):
    """
    Retrieves table of nbr records,
    using socket sock to communicate.
    """
    print("<table>")
    for i in range(nbr):
        data = sock.recv(BUFFER).decode()
        record = data.split(':')
        print("<tr>")
        print("<td>%d</td>" % i)
        print("<td>%s</td>" % record[0])
        print("<td>%s</td>" % record[1])
        print("<td>%s</td>" % record[2])
        print("</tr>")
    print("</table>")

Displaying Sorted Records in Order

To have the user select the sort order, radio buttons are used as illustrated in Fig. 88.

_images/figsortform0.png

Fig. 88 Radio button determined the sort order.

This HTML form is stored in

  • users public_html directory on Unix; or
  • users Sites directory on Mac OS X.

A CGI script confirms the choice, as illustrated in Fig. 89.

_images/figsortform1.png

Fig. 89 A script confirms the selected sort order.

This CGI script is stored in

  • /var/www/cgi-bin on Unix; or
  • /Library/WebServer/CGI-Executables on Mac OS X.

The HTML code in the body of sort_order.html is listed below.

<h1> determine sort order </h1>
<form action="http://localhost/cgi-bin/sort_order.py">
<p>
sort by
<input type="radio" name="sort"
       value = 0 checked> type
<input type="radio" name="sort"
       value = 1> date
<input type="radio" name="sort"
       value = 2> name
<br>
order is
<input type="radio" name="order"
       value = True checked> ascending
<input type="radio" name="order"
       value = False> descending
</p>
<p> <input type="submit"> </p>

The script sort_order.py which confirms the selection made by the user is listed below.

#!/usr/bin/python
import cgi
import cgitb
cgitb.enable()
FORM = cgi.FieldStorage()
SORTBY = FORM['sort'].value
ORDERIS = FORM['order'].value
if SORTBY == '0':
    PRT = 'sort by type and number'
elif SORTBY == '1':
    PRT = 'sort by date'
else:
    PRT = 'sort by name'
if ORDERIS == 'True':
    PRT = PRT + ' in ascending order'
else:
    PRT = PRT + ' in descending order'
print("Content-Type: text/plain\n")
print(PRT)

In processing forms, we have two different way of working. Either we apply the integrated approach in which Python scripts print the HTML forms and process the forms Or, we separate the HTML code from the Python code:

  • f.html holds the form, the action refers to f.py
  • f.py defines the CGI script, and is invoked by submit.

This second approach is good for testing purposes.

In our example application, The database server listens to two clients:

  • The first client displays number of records, prints the form for the sort order, and activates the second client.
  • The second client processes the form, sends the sort order to the server, and retrieves and displays the sorted records.

Both clients after connection receive the number of records in the table.

The server listens to two clients. Its main function is below.

def main():
    """
    Accepts connection and sends records.
    """
    ourdb = pymysql.consnect(db='OurPyFiles')
    crs = ourdb.cursor()
    nbr = count_records(crs)
    sortclient, server = connect_client()
    sortclient.send(str(nb).encode())
    print('wait for submit client')
    submitclient, adr = server.accept()
    print('submit client is connected')
    submitclient.send(str(nbr).encode())
    sortorder = submitclient.recv(BUFFER).decode()
    print('received sort order \"' + sortorder + '\"')
    records = retrieve_records(crs, sortorder)
    print('sending records ...')
    for record in records:
        submitclient.send(pack_tuple(record).encode())
    print('closing connection')
    server.close()

The main function in scripts_sort.py is below.

def main():
    """
    Connects and prints data of server.
    """
    print_header('sorting all scripts')
    server_address = (HOSTNAME, PORTNUMBER)
    client = Socket(AF_INET, SOCK_STREAM)
    client.connect(server_address)
    data = client.recv(BUFFER).decode()
    nbr = int(data)
    print("<B>Number of scripts : %d</B>" % nbr)
    prompt_sort_order()
    client.close()

The main function in scripts_sortall.py is below.

def main():
    """
    Connects and prints data of server.
    """
    print_header('showing all scripts')
    server_address = (HOSTNAME, PORTNUMBER)
    client = Socket(AF_INET, SOCK_STREAM)
    client.connect(server_address)
    data = client.recv(BUFFER).decode()
    nbr = int(data)
    print("<b>Number of scripts : %d</b>" % nbr)
    send_sort_order(client)
    retrieve_table(client, nbr)
    client.close()

The first client in scripts_sort.py prompts the sort order.

def prompt_sort_order():
    """
    Displays a form to ask the user for
    the field to sort on and the order.
    """
    print("""
<form action="http://localhost/cgi-bin/scripts_sortall.py">
<p>
     ... rest of html code ...
"""

Note: if myserver.py is used, the action is defined as

<form action="http://localhost:8000/scripts_sortall.py">

The second client in scripts_sortall.py calls the following function.

def send_sort_order(sock):
    """
    Sends sort order to server
    using the client socket sock.
    """
    form = cgi.FieldStorage()
    sortby = form['sort'].value
    if bool(form['order'].value):
        sortby = sortby + '+'
    else:
        sortby = sortby + '-'
    sock.send(sortby.encode())

The query in the server script scripts_sortdb.py is encoded in the function below.

def retrieve_records(crs, sortorder):
    """
    Given cursor crs, returns all records,
    taking sortorder into account.
    """
    qry = 'select * from scripts'
    if sortorder[0] == '0':
        qry = qry + ' order by t,n'
    elif sortorder[0] == '1':
        qry = qry + ' order by d'
    else:
        qry = qry + ' order by f'
    if sortorder[1] == '+':
        qry = qry + ' asc'
    else:
        qry = qry + ' desc'
    crs.execute(qry)
    return crs.fetchall()

Python glues CGI, MySQLdb, and Sockets.

Exercises

  1. Provide a web interface to enter data in the table scripts. Use an HTML page to enter all data where the submit will activate a client of the database server. The client sends the user data to the server, the server adds it and sends feedback to the client.
  2. Use tables typedate and filedata of Lecture 23 to make a web interface to retrieve records based on keys. Start at an HTML page with an input element to enter a key. The action in the form launches a client of the database server. The server retrieves the record and sends the data to the client for display.