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