CS1116/CS5018

Web Development 2

Dr Derek Bridge

School of Computer Science & Information Technology

University College Cork

Relational databases

Database of past and future gigs

CREATE TABLE gigs 
(
    num INT AUTO_INCREMENT,
    band VARCHAR(50),
    gig_date DATE,
    PRIMARY KEY (num)
);
INSERT INTO gigs (band, gig_date)
    VALUES ('Decaying Shroom', DATE('2018-01-12')),
           ('Belated Tonic', DATE('2018-01-14'));

Database of past and future gigs

gigs
numbandgig_date
1Decaying Shroom2018-01-12
2Belated Tonic2018-01-14
3Dumpy Tension of the Divided Unicorn2018-02-10
4Belated Tonic2018-02-20
5Missing Roller and the Earl2018-02-26
6Glam Blizzard2018-03-07
7Piscatory Classroom2018-03-12
8Prickly Muse2018-03-20
9Interactive Children of the Phony Filth2018-03-29

Querying the database from Python: the essentials

#!/usr/local/bin/python3
            
import pymysql as db
            
print('Content-Type: text/html')
print()

connection = db.connect('localhost', 'userid', 'password', 'database_name')

cursor = connection.cursor(db.cursors.DictCursor)

cursor.execute("""SELECT band, gig_date FROM gigs
                  WHERE gig_date >= CURDATE()""")

for row in cursor.fetchall():
    print(row['band'], row['gig_date'])
  
cursor.close()  
connection.close()

Notes

  1. Import the database module for your database server:
    • Different modules for different DBMS (mySQL, Oracle, …)
    • But many of these modules provide the same set of functions, which makes your code portable
  2. Create a connection:
    • localhost: the machine on which the DBMS runs
    • userid: your usual CompSci user id, e.g. db12
    • password: the database password you used in CS1106/CS5021, not your usual Linux password
    • database_name: the database name you used in CS1106/CS5021, e.g. 2020_db12
  3. Create a cursor for executing SQL statements and traversing their results.
    • Several different types of cursor, e.g. DictCursor

Notes

  1. Use the cursor to ask the DBMS to execute an SQL statement.
  2. Use the cursor to fetch various results:
    • cursor.rowcount: the number of rows in the result
    • cursor.fetchone(): returns the next row, or None if no more rows are available
    • cursor.fetchall(): returns a list of all rows (or all remaining rows), or the empty list if there are no remaining rows
    Note: Because we created a DictCursor, each row is a dictionary
    • Q: What will the keys of the dictionary be?
    • Q: What will the values of the dictionary be?
  3. Close the cursor and the connection

Improving the program

#!/usr/local/bin/python3

from cgitb import enable 
enable()

import pymysql as db

print('Content-Type: text/html')
print()

result = ''
try:
    connection = db.connect('localhost', 'userid', 'password', 'database_name')
    cursor = connection.cursor(db.cursors.DictCursor)
    cursor.execute("""SELECT band, gig_date FROM gigs
                      WHERE gig_date >= CURDATE()""")
    result = """<table>
                <tr><th colspan="2">Upcoming Gigs</th></tr>
                <tr><th>Band</th><th>Date</th></tr>""" 
    for row in cursor.fetchall():
        result += '<tr><td>%s</td><td>%s</td></tr>' % (row['band'], row['gig_date'])
    result += '</table>'
    cursor.close()  
    connection.close()
except db.Error:
    result = '<p>Sorry! We are experiencing problems at the moment. Please call back later.</p>'
    
print("""
    <!DOCTYPE html>
    <html lang="en">
        <head>
            <meta charset="utf-8" />
            <title>Upcoming Gigs</title>
        </head>
        <body>
            %s
        </body>
    </html>""" % (result))

Notes

A different program

#!/usr/local/bin/python3

from cgitb import enable 
enable()

from cgi import FieldStorage, escape
import pymysql as db
            
print('Content-Type: text/html')
print()

form_data = FieldStorage()
bandname = ''
result = ''
if len(form_data) != 0:
    try:    
        bandname = escape(form_data.getfirst('bandname'))
        connection = db.connect('localhost', 'userid', 'password', 'database_name')
        cursor = connection.cursor(db.cursors.DictCursor)
        cursor.execute("""SELECT gig_date FROM gigs
                          WHERE band = '%s'""" % (bandname))
        result = """<table>
                    <tr><th>Gig Dates</th></tr>"""
        for row in cursor.fetchall():
            result += '<tr><td>%s</td></tr>' % row['gig_date']
        result += '</table>'
        cursor.close()  
        connection.close()
    except db.Error:
        result = '<p>Sorry! We are experiencing problems at the moment. Please call back later.</p>'
        
print("""
    <!DOCTYPE html>
    <html lang="en">
        <head>
            <meta charset="utf-8" />
            <title>Gigs by band</title>
        </head>
        <body>
            <form action="band_gigs.py" method="get">
                <label for="bandname">Band: </label>
                <input type="text" name="bandname" vale="%s" size="50" maxlength="50" id="bandname" />
                <input type="submit" value="Search for gigs" />
            </form>
            %s
        </body>
    </html>""" % (bandname, result))

Notes