deutsch     english    français     Print

 

9.2 ONLINE DATABASES

 

 

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].

PROGRAMMING CONCEPTS: Database, table, database server

 

 

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

1. Download the file tjderby.zip from here.
2. Unpack it and copy the files into the subdirectory Lib of the directory where tigerjython2.jar is located.
3. Go into the directory Lib with a command shell and start the server with
java -jar derbynet.jar start
Heads up! You have to have administrator privileges [more...In a protected pool room do you use the best one specially computer].

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:

SQL data type (for Derby) Description
VARCHAR String, appropriate length (<=255)
CHAR(M) String, M characters, fixed length M <= 255
INTEGER Whole number (4 bytes integer)
FLOAT Decimal number (64 bit double precision)
DATE Date (java.sql.date)

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"
Highlight program code (Ctrl+C copy, Ctrl+V paste)

 

 

MEMO

 

If the program finishes without problems, you will know that you set up the database server successfully. If you get an error message carefully read through the chapter once more and try to repeat each step.

With databases, it is typical that SQL commands only affect the database after commit() is called. It is thus ensured that multi-state database transactions can be considered as entities that are always executed as a whole or upon an error message not at all.

If you run the program when the table already exists, it aborts with an error message.

 

 

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"
Highlight program code (Ctrl+C copy, Ctrl+V paste)

 

 

MEMO

 

If you execute the program twice the record is inserted twice.

 

 

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]
Highlight program code (Ctrl+C copy, Ctrl+V paste)

 

 

MEMO

 

In addition to fetchall() you can also use fetchmany(n) and fetchone() to read the data. Each time you call one of the fetch methods, the cursor is, so to say, pushed forward as a pointer to the number of returned records (hence the name cursor) and the next call of fetchall(), fetchmany() or fetchone() delivers the records from the new position on. If the cursor reaches the end of the table this method returns the value None.

 

 

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"
Highlight program code (Ctrl+C copy, Ctrl+V paste)

 

 

MEMO

 

he database named pythondb exists even after you delete the table. There are multiple files in the subdirectory pythondb of the directory where you copied the Derby software (in this case to Lib/pythondb). If you want to remove all traces of the exercise, you can simply delete the directory pythondb.

 

 

EXERCISES

 
1.

You would like to reserve seat 6 for customer number 33. You can do this by adding an update query to the above program before calling showAll().

sql = "UPDATE " + tbl + " SET booked='Y', cust=33 WHERE seat=6"

Reserve seat number 5 for the same customer as well. Also reserve seats 10, 11, and 12 for customer number 34. Check the reservations by displaying the content of the table.

2.

You can display all reserved seats using the SQL query

sql = "SELECT * FROM " + tbl + " WHERE booked='Y'"           
          

a. Write a program that displays only empty seats
b. Change it to display only the seat reservations of customer number 34.

3.

Customer number 34 cancelled all of their reservations. Implement a corresponding update query.

4.

After a renovation of the hall, seats 24 - 30 are no longer available. Delete these records from the table.

To do this, you can use a delete query according to the following model

SQL = "DELETE * FROM " + table + " WHERE ..."