INTRODUCTION

Databases are exceptionally important in today's world. Their main purpose is to store information in a structured manner that one can easily retrieve it with search and link criteria. Due to the interconnectedness of the Internet and the widespread use of social networks, there is a gigantic amount of information stored at millions of database hosts. Therefore, it is important that you learn how data is handled in a database. This will also help you to better assess the risks associated with the use of database-based systems.

In most computerized databases, information is saved in the form of tables which are interrelated. They are thus called relational databases. In order to deal with the tables efficiently, a program bundle called the relational database management system (RDBMS) must be available. Databases should therefore be understood as the collection of data that they contain, together with the corresponding management tools.

Simple databases can be located locally on a PC and operated by a single person, for example for the management of CDs or books. But most databases are hosted on the Internet and are therefore accessible to many users simultanously (online databases). These client-server systems include a computer that acts as database server (also called a host) and multiple distributed clients. The data communication works similarly to a web server using the TCP protocol over a TCP port (e.g. 3306 for MySQL or 1527 for Derby). A specific application program that is written in any high-level programming language such as Python runs on the client and connects to the host.

Direct connection		Indirect connection

Often the client is not directly connected to the database server, but rather indirectly through a web server. The client then only runs one of the known web browsers. In this case, the specific program for the exchange of data with the database servers is located on the web server and must also be written in a suitable programming language (often PHP). The procedures are similar in both cases and demand good programming knowledge in terms of databases, which you will acquire here [more... The database server is typically over the Internet to the Web server connected to both servers but can also be installed on the same machine].

YOUR OWN DATABASE SERVER

Access to existing database servers on the Internet is subject to strong security restrictions since you want to avoid unauthorized data from being saved or changed. For these reasons, you install your own database server on your PC that you can either use directly from there, or from other clients within a LAN/WLAN connection. You usually have to authenticate a database with a username and password in order to access it [more... Internet access is usually blocked by firewalls. From your home you can but the firewall the necessary permissions give themselves. For this purpose, it is necessary to port the database server, e.g. 1527 to open for the IP address of your PC].

In the following example you will instal and use Derby, a free product of the Apache organization developed by the world famous Apache web server. (You could also use any other database software, e.g. MySQL.) To install Derby, follow the instructions listed below

As an alternative you can start the server by using the files startderby.bat (for Windows) or startderby (for Linux/Mac) located in the directory Lib. Create a link for these files so that they are just a click away. The server is only visible on the local PC (localhost). To open it to the outside, you need to start it by setting the IP address of your PC. If your IP addres is 10.1.1.123, start it with

java -jar derbynet.jar start -h 10.1.1.123

or with

startderby 10.1.1.123.

The database server can manage several databases simultaneously. You can gain access to a database with the database name and a username and password. You need to connect to the server using connect() in order to create and use a database. If the database does not already exist, it will then be created. In Python you put all database operations into a with-block, because the connection to the database is automatically closed when the block terminates, even if an error occurs.

After the successful establishment of a connection, you receive a connection object con with which you can request an access key (cursor) to the database by calling the method cursor(). Finally, all doors to the database casino are open to you.
You can consider the database as an object to which you can send commands and which executes them accordingly. The commands are written in a somewhat standardized database language SQL (Structured Query Language) that leans heavily on English slang, so that pretty much anyone can figure it out. You pack a SQL command into a string and execute it with the method execute(SQL). To make it a bit more clear, you write the SQL language elements in capital letters. However, it also works with lower case letters or a mix between the two.

As a first step, create a database table with the SQL command CREATE TABLE. As usual, the table consists of rows and columns. The columns, also called fields, specify what information you want to store in the table. You need to give the field a name and a data type for this. The most important types are listed in the following table:

To administer the seat reservations in the casino hall for a certain concert, you create a table reservation. As other fields you choose seat number seat, booked with the letters N or Y, and a customer number cust that identifies the person who reserved a seat (not used for the moment).

from dbapi import *

serverURL = "derbyserver:localhost"
#serverURL = "derbyserver:10.1.1.123"
dbname = "casino"
username = "admin"
password = "solar"
tbl = "reservation"

with connect(serverURL, dbname, username, password) as con:
    cursor = con.cursor()
    sql = "CREATE TABLE " + tbl + "(seat INTEGER, booked CHAR(1), cust INTEGER)" 
    cursor.execute(sql)
    con.commit()
print "Table created"

MEMO

INSERTING DATA INTO THE TABLE

In the next phase you will want to fill the table with initialization data, so label all seats as available and enter the customer number 0. To do this you use the SQL INSERT command, for example for the set with the number 1:

INSERT INTO reservation VALUES (1, 'N', 0)

With this command you add a single row to the table. A single row of the table is also called a dataset or a record.

from dbapi import *

serverURL = "derbyserver:localhost"
#serverURL = "derbyserver:10.1.1.123"
dbname = "casino"
username = "admin"
password = "solar"
tbl = "reservation"

with connect(serverURL, dbname, username, password) as con:
    cursor = con.cursor()
    for seatNb in range(1, 31):
        sql = "INSERT INTO " + tbl + " VALUES (" + str(seatNb) + ",'N',0)"
        cursor.execute(sql)
    con.commit()
print "Table initialized"

MEMO

READING DATA FROM A TABLE

By now you are probably curious to see if the data are actually located in the table. In any case, today's software systems are so stable that you can assume that if there is no error message, the database transaction was indeed successful. In order to read a table, you should use the most famous SQL command:

SELECT * FROM reservation

By using an asterisk (wildcard) you are asking for all records to be read. After executing with execute(), you can retrieve the obtained records with the cursor method fetchall(). A list is returned in which the individual records are contained as tuples (an unalterable list). You can read the individual fields in it using indices.

The total number of records provided by the SELECT command is important. You get the number of records from the variable rowcount.

from dbapi import *

serverURL = "derbyserver:localhost"
#serverURL = "derbyserver:10.1.1.123"
dbname = "casino"
username = "admin"
password = "solar"
tbl = "reservation"

with connect(serverURL, dbname, username, password) as con:
    cursor = con.cursor()
    sql = "SELECT * FROM " + tbl
    cursor.execute(sql)
    result = cursor.fetchall() # list of tuples
    for record in result:
        print "seatNb:", record[0], " booked:", record[1]," cust:", record[2]

MEMO

DELETING TABLES

You can finish the exercise by deleting the table that you just created. In the database language this is called a drop operation. The corresponding SQL command is:

DROP TABLE reservation

After this, your database casino no longer has any user-specified tables.

from dbapi import *

serverURL = "derbyserver:localhost"
#serverURL = "derbyserver:10.1.1.123"
dbname = "casino"
username = "admin"
password = "solar"
tbl = "reservation"

with connect(serverURL, dbname, username, password) as con:
    cursor = con.cursor()
    sql = "DROP TABLE " + tbl
    cursor.execute(sql)
    con.commit()
print "Table removed"

MEMO

EXERCISES