Interfacing with MySQL

We consider a graphical user interface to navigate the records in the database and then to modify the database.

A GUI to browse a MySQL table

Consider the database of Python scripts of the previous lecture. We made one table scripts in the database OurPyFiles, storing data about our Python scripts. For every Python script we have 4 fields: its type, number, date, and file name. An example of a header is below.

# L-26 MCS 275 Fri 10 Mar 2017 : guidb1.py

The corresponding data tuple is

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

The goal of the GUI is to get a better overview of the records in the table. We will sort the records in various ways.

The GUI will have the following widgets:

• A message field to write status information.
• Buttons to
  • connect to the database,
  • retrieve all records,
  • see next 10 or previous 10 records.
• Radio buttons to determine
  • which field to use for sorting,
  • to sort in ascending or descending order.
• Listboxes to display the columns in the table.

The layout of the GUI is displayed in Fig. 73.

Fig. 73 A GUI to browse the content of a table.

The code in __init__ to define the message label is

self.message = StringVar()
self.message.set("welcome to our database")
self.messageLabel = Label(wdw, \
    textvariable = self.message)
self.messageLabel.grid(row=0, column=0, columnspan=4)

The labels for the three fields in the table are defined as

self.tt = Label(wdw, text='type')
self.tt.grid(row=2, column=0)
self.dd = Label(wdw, text='date')
self.dd.grid(row=2, column=1)
self.ff = Label(wdw, text='file')
self.ff.grid(row=2, column=2, columnspan=2)

And then we have the labels to sort:

self.sL = Label(wdw, text='sort by')
self.sL.grid(row=4, column=0)

The constructor contains a button to connect to the database:

self.bc = Button(wdw, text='connect', \
   command=self.connect)
self.bc.grid(row=1, column=0)
self.cursor = 0

and the callback function associated to the button is defined by the method connect defined below.

def connect(self):
    """
    Connects to the database OurPyFiles.
    """
    try:
        # db = MySQLdb.connect(db="OurPyFiles")
        db = pymysql.connect(db="OurPyFiles")
        self.message.set("connected to \"OurPyFiles\"")
        self.cursor = db.cursor()
    except:
        self.message.set("failed to connect to \"OurPyFiles\"")

We can sort on type, date, or file. Which sort is selected by a Radiobutton, stored in the attributed RadioSort of the constructor.

self.RadioSort = IntVar()
self.st = Radiobutton(wdw, text='type', \
    variable=self.RadioSort, value = 1)
self.st.grid(row=4, column=1)
self.sd = Radiobutton(wdw, text='date', \
    variable=self.RadioSort, value = 2)
self.sd.grid(row=4, column=2)
self.sf = Radiobutton(wdw, text='file', \
    variable=self.RadioSort, value = 3)
self.sf.grid(row=4, column=3)
self.RadioSort.set(1)

The default sort is set to type. The user has choice between ascending or descending order, which is defined by another Radiobutton, with code in the __init__:

self.RadioOrder = IntVar()
self.asc = Radiobutton(wdw, text='ascending order', \
    variable = self.RadioOrder, value = 1)
self.asc.grid(row=5, column=0, columnspan=2)
self.dsc = Radiobutton(wdw, text='descending order', \
    variable = self.RadioOrder, value = 2)
self.dsc.grid(row=5, column=2, columnspan=2)
self.RadioOrder.set(1)

The default order is set to ascending.

Depending on the choices selected by the user, the formulation of the query is defined in the method query.

def query(self):
    """
    Returns the query to select all records,
    taking input from the radio buttons.
    """
    q = 'select * from scripts'
    ord = ''
    if self.RadioOrder.get() == 1:
        ord = 'asc'
    elif self.RadioOrder.get() == 2:
        ord = 'desc'
    if self.RadioSort.get() == 1:
        q = q + ' order by t, n ' + ord
    elif self.RadioSort.get() == 2:
        q = q + ' order by d ' + ord
    elif self.RadioSort.get() == 2:
        q = q + ' order by f ' + ord
    return q

The callback command for the second button, to retrieve records is defined in the method retrieve.

def retrieve(self):
    """
    Retrieves all records from the scripts table.
    """
    if self.cursor == 0:
        self.message.set("please connect first")
    else:
        q = self.query()
        lc = self.cursor.execute(q)
        m = 'retrieved %d records' % int(lc)
        self.message.set(m)
        R = self.cursor.fetchall()
        self.clear()
        for i in range(len(R)):
            if i >= 10:
                break
            self.insert(R[i])
        self.records = R
        self.pos = 10

All records in the table are stored in the data attribute records. The self.pos holds the current position in the of records.

To display the data, we use Listbox widgets. We will use three listboxes (defined in __init__):

1. for type and number, e.g.: L-26

   self.Lt = Listbox(wdw, width=4, height=10)
   self.Lt.grid(row=3, column=0)
   

2. for the date, e.g.: 2017-03-10

   self.Ld = Listbox(wdw, width=10, height=10)
   self.Ld.grid(row=3, column=1)
   

3. for the file name, e.g.: guidb1.py

   self.Ln = Listbox(wdw, width=15, height=10)
   self.Ln.grid(row=3, column=2, columnspan=2)
   

The basic methods to use the listboxes are defined below.

def clear(self):
    """
    Clears all listboxes.
    """
    self.Lt.delete(0, END)
    self.Ld.delete(0, END)
    self.Ln.delete(0, END)

def insert(self, item):
    """
    Inserts one record item to the listboxes.
    """
    t = item[0] + '-' + str(int(item[1]))
    self.Lt.insert(END, t)
    self.Ld.insert(END, str(item[2]))
    self.Ln.insert(END, item[3])

Because our listboxes are only 10 in height, we cannot display everything all at once. Two navigation buttons are defined in __init__:

1. to show the next 10 records:

   self.bn = Button(wdw, text='next 10', command=self.next10)
   self.bn.grid(row=1, column=2)
   

2. to show the previous 10 records:

   self.bp = Button(wdw, text='previous 10', command=self.prev10)
   self.bp.grid(row=1, column=3)
   

Storing the current position in the retrieved records is done in the data attribute pos initialized to zero.

self.pos = 0

The display of the next 10 records is defined by the method next10 listed below.

def next10(self):
    """
    Displays the next 10 records in listboxes.
    """
    if self.records == 0:
        self.message.set("no records to show")
    else:
        self.clear()
        for i in range(self.pos, self.pos+10):
            if i >= len(self.records):
                break
            self.insert(self.records[i])
        self.pos = self.pos + 10
        if self.pos >= len(self.records):
            self.pos = len(self.records) - 1

Displaying the previous 10 records is similar, but notice the difference in adjusting the data attributed pos to set the current position in the list of records.

def prev10(self):
    """
    Displays the previous 10 records in listboxes.
    """
    if self.records == 0:
        self.message.set("no records to show")
    else:
        self.clear()
        self.pos = self.pos - 20
        if self.pos < 0:
            self.pos = 0
        for i in range(self.pos, self.pos+10):
            if i >= len(self.records):
                break
            self.insert(self.records[i])
        self.pos = self.pos + 10

Normalization

To make the database more useful, we have to split the table scripts in two.

The four fields were copied from the headers. There are two drawbacks to working with one table:

1. only the file name is unique for every record and not so convenient to use as key; and
2. as most lectures featured more than one script, there is a lot of redundant data in the table.

We normalize, using scripts to create two new tables: one with file names and one with type and dates. Because it is tedious to do manually with the mysql monitor, we develop a script for the normalization.

The top down design is realized by main(), defined below.

def main():
    """
    Splits scripts in two tables.
    """
    ourdb = pymysql.connect(db="OurPyFiles")
    crs = ourdb.cursor()
    qry = 'select * from scripts order by d'
    nbr = crs.execute(qry)
    print('found %d records' % int(nbr))
    ans = input('Make the tables ? (y/n) ')
    if ans == 'y':
        make_tables(crs)
    data = crs.fetchall()
    (types, files) = split_records(data)
    print('T = ', types)
    print('F = ', files)
    if ans == 'y':
        ans = input('Do the inserts ? (y/n) ')
        insert_type_date(crs, types, ans=='y')
        insert_file_data(crs, files, ans=='y')
        date_files(crs)
        ourdb.commit()
    else:
        print('showing the insert queries')
        insert_type_date(crs, types)
        insert_file_data(crs, files)

The making the tables is defined by make_tables below.

def make_tables(crs):
    """
    Executes the MySQL commands to create
    the tables typedate and filedata.
    The input parameter crs is the cursor.
    """
    make_type_date(crs)
    make_file_data(crs)

The structure of the table typedate is encoded in the function below.

def make_type_date(crs):
    """
    The table typedate stores type and date,
    e.g.: L-26 and 2017-03-10, repesented by
    four fields: id, type, number, and date.
    The input parameter crs is the cursor.
    """
    try:
        qry = 'create table typedate ' + \
          '( i INT, t CHAR(1), n INT, d DATE )'
        crs.execute(qry)
        print(qry + ' succeeded')
    except:
        print(qry + ' went wrong...')

The structure of the table filedata is defined in the function below.

def make_file_data(crs):
    """
    The table filedata stores file names,
    represented by three fields: id, name,
    and id of the corresponding entry in
    the typedate table, crs is the cursor.
    """
    try:
        qry = 'create table filedata ' + \
          '( i INT, f CHAR(20), t INT )'
        crs.execute(qry)
        print(qry + ' succeeded')
    except:
        print(qry + ' went wrong...')

In moving the data into the tables, we first split the records with a Python script. The relevant function is split_records(), listed below.

def split_records(R):
    """
    Returns two lists: records that go
    in typedate and in filedata.
    """
    L = []; T = []; F = []; cnt = -1
    for i in range(len(R)):
        d = R[i]
        p = (d[0], d[1], d[2])
        if p in L:
            ind = L.index(p)
        else:
            cnt = cnt + 1
            L.append((d[0], d[1], d[2]))
            T.append((cnt, d[0], d[1], d[2]))
            ind = cnt
        F.append((i, d[3], ind))
    return (T, F)

The query to insert the type and date in the table typedate is encoded in the function listed below.

def insert_type_date(crs, T, doit=False):
    """
    Given the cursor and a list of values for
    the table typedate, all records are added.
    """
    for i in range(len(T)):
        p = T[i]
        qry = 'insert into typedate values ' + \
            '(\"%s\", \"%s\", \"%s\", \"%s\") ' \
            % (str(p[0]), str(p[1]), str(p[2]), \
               str(p[3]))
        if doit:
            crs.execute(qry)
        else:
            print('query = ', qry)

The query to insert the names of the files into the table filedata is encoded in the function listed below.

def insert_file_data(crs, F, doit=False):
    """
    Given the cursor and a list of values for
    the table filedata, all records are added.
    """
    for i in range(len(F)):
        n = F[i]
        qry = 'insert into filedata values ' + \
            '(\"%s\", \"%s\", \"%s\") ' \
          % (str(n[0]), n[1], str(n[2]))
        if doit:
            crs.execute(qry)
        else:
            print('query = ', qry)

The check to see whether we have not lost any data is to run a query which show all original records in the table scripts, but now using the tables filedata and typedate. This check is defined in the function below.

def date_files(crs):
    """
    As a final check, selects file name
    and corresonding date from the newly
    created tables, crs is the cursor.
    """
    qry = 'select f,d from filedata, typedate' \
        + ' where filedata.t = typedate.i'
    crs.execute(qry)
    res = crs.fetchall()
    print(res)
    print('#records :', len(res))

Retrieving Specific Records

After the normalization, every script has a unique key. This key is in the i field of the table filedata. Accessing the data via the key allows us to select records directly; and to insert, delete, and update records.

Fig. 74 A GUI to retrieve a specific record.

The query to retrieve a specific record is encoded in the following function.

def query(self, key):
    """
    Returns the query for all the information
    of the script with the given key.
    """
    q = 'select typedate.t, n, d, f ' + \
        'from typedate, filedata ' + \
        'where filedata.t = typedate.i ' + \
        'and filedata.i = %d' % key
    return q

To insert a new record in the table, we have to assign a key. Therefore we count the number of files we have already stored in the table. The query for this count is encapsulated in the function below.

def count_files(self):
    """
    Returns the number of files.
    """
    qry = 'select count(*) from filedata'
    ret = self.cursor.execute(qry)
    nbr = self.cursor.fetchone()
    return int(nbr[0])

The GUI to enter the data for a new record is illustrated in Fig. 75.

Fig. 75 Inserting a new record in the database of scripts.

With the message label we confirm the data and then ask for the user to confirm, as shown in Fig. 76.

Fig. 76 Asking to confirm the data for a new record.

Then with a message label, the insertion is confirmed, as shown in Fig. 77.

Fig. 77 Confirming the insert of a new record.

The insertion of the data for a new record in the database is initiated by the method below.

def insert_data(self):
    """
    Inserts a new record into the database.
    """
    t = self.tp.get()
    d = self.dt.get()
    f = self.nm.get()
    r = (t, d, f)
    if not self.startinsert:
        m = 'inserting %s,' % str(r)
        m = m + 'press to confirm'
        self.message.set(m)
        self.startinsert = True
    else:
        self.insert_record(r)
        m = 'inserted %s' % str(r)
        self.startinsert = False

The method insert_record, used above in the method insert_data, is defined below.

def insert_record(self, data):
    """
    Prepares data to insert a record.
    """
    L = data[0].split('-')
    (t, n) = (L[0], L[1])
    (d, f) = (data[1], data[2])
    nf = self.count_files()
    nt = self.count_types()
    v = '(' + '\"' + str(nf) + '\"' + \
        ',' + '\"' + f + '\"' + ',' + \
        '\"' + str(nt) + '\"' + ')'
    self.insert_file_values(v)
    m = 'inserted %s' % v
    self.message.set(m)

To insert the values, the MySQL command

insert into <table> values <tuple>

is applied in the method below.

def insert_file_values(self, vals):
    """
    Inserts values in filedata table.
    """
    qry = 'insert into filedata values '
    qry = qry + vals
    self.cursor.execute(qry)

Visit http://www.mysqltutorial.org/ for more about mysql.

Exercises

1. Create the equivalent to guidb1.py, our first GUI, writing a CGI script.
2. The method in our second GUI to insert is not yet complete. Also provide functions to add the corresponding values in the table typedate.
3. Provide functions to delete records.
4. Provide functions to update records.