Databases¶

Database information systems manage data better than file oriented programs.

Relational Model and Structured Query Language¶

Consider the management of a library with a file oriented information system, schematically shown in Fig. 70.

Fig. 70 A file oriented library manager.

The information system consists of 3 files:

records of people (librarians and patrons);
data catalog of books in the library; and
who has checked out what books.

Database Information System

A database is a collection of data organized and managed by a specific software system: the Database Management System (DBMS).

The library manager with a database is schematically represented in Fig. 71.

Fig. 71 Managing a library with a database.

To design databases we consider the relational model. In the relational model, a relation is a table with a fixed number of columns. The columns are called attributes, taking values belonging to a domain (similar to types). The rows are called tuples.

The schema of a relation describes the structure of the relation. A subschema describes only that portion of the database relevant for the user. For example: a librarian can see which books any patron has checked out, whereas the view of a patron is limited. The instance of a relation is the set of tuples of the relation present at the database at any given moment.

Consider the schema of our library datatase. We have three relations: Person, Book, and Check.

Attributes of Person are identification number (id), name, (email) address, and status (librarian or patron).
Attributes of Book are identification number, author, title, and availability status (in or out).
Check relates identification numbers of people to the identification numbers of books checked out. The two attributes in Check are person_id and book_id.

Structured Query Language (SQL) is a standard supported on all relational databases. For example, suppose we want to see the titles of all books checked out by a person with identification number equal to nb. A query in SQL could then be formulated as

SELECT title
FROM BOOK, CHECK
WHERE CHECK.person_id = nb
  AND CHECK.book_id = BOOK.id

The result of this query is a new table, with one attribute: title.

For large, multiuser, and distributed databases (such as banking), losing data can be very costly. To ensure database integrity, the information to perform the updated is backed up, stored in a log file. The Commit/Rollback protocol regulates the update of a database in two stages:

commit At the commit point, all data to perform the update in the database has been stored in the log. In case of malfunction during the update, the data is retrieved from the log.
rollback If problem should arise during the actual update, the update can be made undone, using the information from the log.

In the classification of programming languages, SQL ranks as a 4GL, a fourth generation language:

native machine code for the CPU
assembler language
high level programming languages, such as Ada, C/C++, Fortran, Java, Python, etc...
languages closer to being like a human language, SQL statements read like sentences.

MySQL¶

MySQL is an open source database, developed by the company MySQL AB. In February 2008, Sun Microsystems acquired MySQL~AB and the expertise of the GPL software for $1 billion. In January 2010, Oracle acquired Sun for $7.38 billion. MySQL can be downloaded for free from http://www.mysql.com/downloads. Following the instructions, install MySQL first.

MySQLdb is an interface to connect Python to MySQL. An alternative to MySQLdb is pymysql.

The interaction with MySQL runs via a client, which makes request to a server, as represented in Fig. 72.

Fig. 72 Data flow in a client/server computation.

The server may be started at boot time, otherwise – as root user – we have to start the daemon as follows:

$ sudo mysqld_safe

Starting mysqld daemon with databases
from /usr/local/mysql/data

Shutting the MySQL server down is done as:

$ sudo mysqladmin shutdown

STOPPING server from pid file
/usr/local/mysql/data/ambiorix.local.pid
080304 21:33:10  mysqld ended

To work with MySQL, we first must have a database. The creation and deletion of databases is done with the command mysqladmin, executed by the superuser.

We need to use it to create first a database. As our first example of MySQL server administration, on a Mac OS X, at the prompt, consider

$ sudo mysqladmin create Book

We have created a database with name Book. To delete the database Book:

$ sudo mysqladmin drop Book

We dropped the Book because Book will be table in the database Library.

$ sudo mysqladmin create Library
$ sudo mysql
Welcome to the MySQL monitor.  Commands end with ; or \g.
Your MySQL connection id is 4
Server version: 5.0.45 MySQL Community Server (GPL)
Type 'help;' or '\h' for help. Type '\c' to clear the buffer.
mysql> use Library;
Database changed
mysql> create table Book
    -> (id INT, title CHAR(80), available SMALLINT);
Query OK, 0 rows affected (0.00 sec)

We created a table Book with attributes

id of domain INT;
title of domain CHAR, 80 characters wide; and
available of domain SMALLINT.

Two other tables in the database Library are the tables Person and Checked.

mysql> create table Person
    -> (id INT, name CHAR(20), status SMALLINT);
Query OK, 0 rows affected (0.00 sec)

mysql> create table Checked
    -> (idbook INT, idname INT);
Query OK, 0 rows affected (0.00 sec)

mysql> show tables;
+-------------------+
| Tables_in_library |
+-------------------+
| Book              |
| Checked           |
| Person            |
+-------------------+
3 rows in set (0.01 sec)

Before entering data, let us look at the structure of the table Book.

mysql> explain Book;
+-----------+-------------+------+-----+---------+-------+
| Field     | Type        | Null | Key | Default | Extra |
+-----------+-------------+------+-----+---------+-------+
| id        | int(11)     | YES  |     | NULL    |       |
| title     | char(80)    | YES  |     | NULL    |       |
| available | smallint(6) | YES  |     | NULL    |       |
+-----------+-------------+------+-----+---------+-------+

mysql> insert into Book values
    -> (1,"The Art & Craft of Computing",1);
mysql> insert into Book values
    -> (2,"Making Use of Python",1);

To check whether the additions of the tuples into Book succeeded, we select everything from the table Book.

mysql> select * from Book;
+------+------------------------------+-----------+
| id   | title                        | available |
+------+------------------------------+-----------+
|    1 | The Art & Craft of Computing |         1 |
|    2 | Making Use of Python         |         1 |
+------+------------------------------+-----------+

The table Person is created in a similar fashion as the table Book. Its structure is explained below.

mysql> explain Person;
+--------+-------------+------+-----+---------+-------+
| Field  | Type        | Null | Key | Default | Extra |
+--------+-------------+------+-----+---------+-------+
| id     | int(11)     | YES  |     | NULL    |       |
| name   | char(20)    | YES  |     | NULL    |       |
| status | smallint(6) | YES  |     | NULL    |       |
+--------+-------------+------+-----+---------+-------+

Now we insert two tuples into the table Person as follows.

mysql> insert into Person values
    -> (1,"Rashi Gupta",1);
mysql> insert into Person values
    -> (2,"Guido van Rossum",0);

To check the additions, we select everything from the table Person.

mysql> select * from Person;
+------+------------------+--------+
| id   | name             | status |
+------+------------------+--------+
|    1 | Rashi Gupta      |      1 |
|    2 | Guido van Rossum |      0 |
+------+------------------+--------+

The table Checked has two fields and relates the identification numbers of the books that are checked out to the identification numbers of the people who borrowed the books.

mysql> explain Checked;
+--------+---------+------+-----+---------+-------+
| Field  | Type    | Null | Key | Default | Extra |
+--------+---------+------+-----+---------+-------+
| idbook | int(11) | YES  |     | NULL    |       |
| idname | int(11) | YES  |     | NULL    |       |
+--------+---------+------+-----+---------+-------+

Storing that book 1 is checked out by person 2 happens as follows.

mysql> insert into Checked values (1,2);
mysql> select * from Checked;
+--------+--------+
| idbook | idname |
+--------+--------+
|      1 |      2 |
+--------+--------+

To illustrate the modification of records in a table, let us change the status the book with id = 1, because it has been checked out.

mysql> update Book set available=0 where id = 1;
Query OK, 1 row affected (0.00 sec)
Rows matched: 1  Changed: 1  Warnings: 0

To verify the success of the modification, we can do:

mysql> select * from Book;
+------+------------------------------+-----------+
| id   | title                        | available |
+------+------------------------------+-----------+
|    1 | The Art & Craft of Computing |         0 |
|    2 | Making Use of Python         |         1 |
+------+------------------------------+-----------+

A more involved Query has clauses. Let us select the tiles of all books Guido van Rossum has checked out:

mysql> select title
    -> from Book, Person, Checked
    -> where Person.name = "Guido van Rossum"
    ->   and Checked.idname = Person.id
    ->   and Checked.idbook = Book.id;

The result is displayed below:

+------------------------------+
| title                        |
+------------------------------+
| The Art & Craft of Computing |
+------------------------------+
mysql> exit;
Bye

MySQLdb: MySQL with Python¶

Instead of typing in the MySQL queries in a client, a Python script will transmit the query to MySQL. MySQLdb is an interface to use MySQL from within a Python session. At the command prompt, we launch the python interpreter and then type the following:

$ sudo python
Python 2.5.1 (r251:54869, Apr 18 2007, 22:08:04)
[GCC 4.0.1 (Apple Computer, Inc. build 5367)] on darwin
Type "help", "copyright", "credits" or "license" for more information.
>>> import MySQLdb
>>> L = MySQLdb.connect(db="Library")
>>> c = L.cursor()

which assumes that the server is running. The sudo should not be necessary if permissions were set to the user running the python interpreter.

Observe the following:

with connect(), we identify the database Library;
L.cursor() returns a new object to represent a database cursor used to manage all operations.

For Python 3.5, pymysql works just as well:

$ python
Python 3.5.0 (v3.5.0:374f501f4567, Sep 12 2015, 11:00:19)
[GCC 4.2.1 (Apple Inc. build 5666) (dot 3)] on darwin
Type "help", "copyright", "credits" or "license" for more information.
>>> import pymysql
>>> L = pymysql.connect(db="Library")
>>> c = L.cursor()
>>> c.execute("show tables")
1
>>> c.fetchone()
('Book',)
>>> c.execute("select * from Book")
1
>>> c.fetchall()
((1, 'primer on scientific programming', 1),)

With a cursor, c in the session above, we pass MySQL commands as strings to the execute() method, applied to the cursor c, as c.execute(cmd). For example:

>>> c.execute("show tables")
3L

The 3L indicates there are 3 lines of output. To retrieve the output line by line, we use fetchone:

>>> c.fetchone()
('Book',)
>>> c.fetchone()
('Checked',)
>>> c.fetchone()
('Person',)
>>> c.fetchone()

With fetchall() we obtain all results at once.

>>> c.execute("select * from Book")
2L
>>> c.fetchall()
((1L, 'The Art & Craft of Computing', 0),\
 (2L, 'Making Use of Python', 1))

A more involved query, to display all titles of books checked out by Guido van Rossum, is formulated in a string as follows:

>>> s = "select title " + \
... " from Book, Person, Checked" + \
... " where Person.Name = \"Guido van Rossum\"" + \
... "   and Checked.idname = Person.id" + \
... "   and Checked.idbook = Book.id"
>>> r = c.execute(s)
>>> c.fetchall()
(('The Art & Craft of Computing',),)

To learn more about MySQL, visit http://www.mysqltutorial.org/.

Exercises¶

Design a simple relational database to manage bank accounts, and to perform financial transactions.
Download and install MySQL and MySQLdb, or pymysql.
Write a Python function that takes on input the name of a person and returns the query to select all titles of the books that person has checked out.
Use MySQL and MySQLdb (or pymysql) to extend the login_form with a database that stores user names and passwords, used to grant or deny access.

Databases¶

Relational Model and Structured Query Language¶

MySQL¶

MySQLdb: MySQL with Python¶

Exercises¶

Table Of Contents

Previous topic

Next topic

This Page