Creating the Database

D ^aTabase h ^anDling

C

o

:

• You will learn how to interact with a database from within Python by creating database files, database tables, and building table structures.

• You will learn how to insert and update records in database tables.

• You will learn how to query database data by retrieving records and using their values for various operations in Python.

• You will learn how to delete existing table records.

11.1 C

D

Python, like many other computer languages, is able to interact with a database, which is a collection of information organized to provide efficient access by computer programs. Python can access a database through its specialized libraries. While there

are a few libraries that enable the interaction between Python and databases, in this book we will work with the SQLite3 library. SQLite3 is a set of Python scripts that contain

already-made functions which enable the interaction with a database. This library must have been automatically installed along with Eclipse, so you don’t have to install it separately.

The first thing we will do in Python is create an empty database file. This is a very easy task accomplished by the following code:

import SQLite3

db = SQLite3.connect('database1.db')

First, we need to import the SQLite3 library in Python. This is done using the import command along with the name of the library. Once we import it inside our program, we can use the library and its

built-in functions as many times as we want. In the second lbuilt-ine we make a connection to a database. In this example, the name of the database is database1.db. If this database

This chapter uses:

SQLite3

import

file exists in your system, a connection will be established. Otherwise, a new empty database file named database1.db will be created and simultaneously a connection with it will be established. After you run this code, try to locate the newly created database file on the PyDev package explorer panel by refreshing it first:

Figure 11.1: Database file created using the SQLite3 library.

All we did so far was create an empty database file and established a connection with it. Now, we are going to create a table inside the created database that is constructed by fields. To do that, we need to write some code inside Python, using the SQL language.

SQL is a programming language designed to manage data held in databases. Through the slqlite3 library, Python allows SQL code to be written inside its environment.

Here is the complete code that creates two tables with three fields each:

import SQLite3

db = SQLite3.connect('database.db')

text, secondname text, age int)')

db.execute('create table book (title text, author text, genre text)')

Notice that the first two lines remain the same. Then, in the second and the third lines we create the tables person and book respectively. The key tool to create the tables here is the execute() method which is attached after the database object (db) we created earlier. The execute() method executes SQL code inside Python. Therefore, all the code

we write inside the parenthesis following execute() is SQL code.

Let’s take a closer look at the first SQL statement contained within the execute() method:

execute()

11.1 Creating the Database 167167

secondname text, age int)'

Here we tell the program to create a table named person. Then we create three fields, one named firstname that will hold text values, one named secondname that will again hold text values, and another one named age that holds integer values. This way, the table and its structure is created.

Similarly, the other execute() method creates the table called book. After you run the whole code, try to open the “database1.db” file inside eclipse to see how it looks.

Tip: If you do not have any database system installed on your computer, you might not see any meaningful result when you open

the database file. However, the database is stored in the correct structure.

The code we just wrote runs without problem if the database file is empty (free of tables), but if this is not the case, you may run into overwriting problems. To avoid this, we should add two other lines inside our code.

Therefore a safer code to create the tables would be this one:

import SQLite3

db = SQLite3.connect('database1.db')

db.execute('drop table if exists person') text, secondname text, age int)')

db.execute('drop table if exists book') text, secondname text, age int)')

The lines we added will overwrite the existing tables within the database file with the new ones as created by the code. In the next section, we will populate the tables with sample data.

Questions for Review

1. What does the import SQLite3 command do?

a. Imports tables inside Python.

b. Imports SQL methods inside Python.

c. Imports a library that is able to handle interactions with a database.

d. Imports a library that creates a database system.

2. What argument does the execute() method take?

a. Python executable commands.

b. Table fields.

c. SQL data types.

d. SQL code.

Lab Activity

Create a new table within the existing database. Name the table

“triangle”, and add three fields, “type”, “area” and “rightTriangle”. Try to assign the appropriate types for each of the fields.

l

s

import SQLite3

db = SQLite3.connect('database1.db') db.execute('create table triangle (type

Questions for Review

Lab Activity

169