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 :numref:`figguidb1`.

.. _figguidb1:

.. figure:: ./figguidb1.png
    :align: center

    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.

.. _figguidb2ret0:

.. figure:: ./figguidb2ret0.png
    :align: center

    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 :numref:`figguidb2insert1`.

.. _figguidb2insert1:

.. figure:: ./figguidb2insert1.png
    :align: center

    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 :numref:`figguidb2insert2`.

.. _figguidb2insert2:

.. figure:: ./figguidb2insert2.png
    :align: center

    Asking to confirm the data for a new record.

Then with a message label, the insertion is confirmed,
as shown in :numref:`figguidb2insert3`.

.. _figguidb2insert3:

.. figure:: ./figguidb2insert3.png
    :align: center

    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.