Skip to main content

Python for Everyone: The PreTeXt Interactive Edition

Section 21.4 Creating a database table

Databases require more defined structure than Python lists or dictionaries  1 .
When we create a database table we must tell the database in advance the names of each of the columns in the table and the type of data which we are planning to store in each column. When the database software knows the type of data in each column, it can choose the most efficient way to store and look up the data based on the type of data.

Checkpoint 21.4.1.

    Q-1: True or False? Python dictionaries require less defined structure than databases.
  • True
  • Databases require more defined structure than Python lists or dictionaries.
  • False
  • Try again!
You can look at the various data types supported by SQLite at the following url:
Defining structure for your data up front may seem inconvenient at the beginning, but the payoff is fast access to your data even when the database contains a large amount of data.
The code to create a database file and a table named Tracks with two columns in the database is as follows:
import sqlite3

conn = sqlite3.connect('music.sqlite')
cur = conn.cursor()

cur.execute('DROP TABLE IF EXISTS Tracks')
cur.execute('CREATE TABLE Tracks (title TEXT, plays INTEGER)')

conn.close()
The connect operation makes a “connection” to the database stored in the file music.sqlite3 in the current directory. If the file does not exist, it will be created. The reason this is called a “connection” is that sometimes the database is stored on a separate “database server” from the server on which we are running our application. In our simple examples the database will just be a local file in the same directory as the Python code we are running.
A cursor is like a file handle that we can use to perform operations on the data stored in the database. Calling cursor() is very similar conceptually to calling open() when dealing with text files.
Figure 21.4.2.

Checkpoint 21.4.3.

    Q-2: A cursor that we create to perform operations on the data within the database is very similar to which of the following?
  • Socket
  • A network socket is a software structure within a network node of a computer network that serves as an endpoint for sending and receiving data across the network.
  • File Handle
  • A cursor is like a file handle. Calling cursor() is similar to calling open().
  • Script
  • A script is a file containing code written in Python.
  • Module Object
  • A value created by an import statement that provides access to the data and code defined in a module.
Once we have the cursor, we can begin to execute commands on the contents of the database using the execute() method.

Checkpoint 21.4.4.

Database commands are expressed in a special language that has been standardized across many different database vendors to allow us to learn a single database language. The database language is called Structured Query Language or SQL for short.

Checkpoint 21.4.5.

    Q-4: SQL stands for ___________.
  • Structured Query Language
  • SQL is the acronym for Structured Query Language.
  • Simple Query Language
  • Not quite.
  • Synchronized Query Language
  • Try again.
  • Simple Query Lingua
  • Not quite.
In our example, we are executing two SQL commands in our database. As a convention, we will show the SQL keywords in uppercase and the parts of the command that we are adding (such as the table and column names) will be shown in lowercase.
The first SQL command removes the Tracks table from the database if it exists. This pattern is simply to allow us to run the same program to create the Tracks table over and over again without causing an error. Note that the DROP TABLE command deletes the table and all of its contents from the database (i.e., there is no “undo”).
cur.execute('DROP TABLE IF EXISTS Tracks ')
The second command creates a table named Tracks with a text column named title and an integer column named plays.
cur.execute('CREATE TABLE Tracks (title TEXT, plays INTEGER)')
Now that we have created a table named Tracks, we can put some data into that table using the SQL INSERT operation. Again, we begin by making a connection to the database and getting a cursor object. We can then execute SQL commands using the cursor.
The SQL INSERT command indicates which table we are using and then defines a new row by listing the fields we want to include (title, plays) followed by the VALUES we want placed in the new row. We specify the values as question marks (?, ?) to indicate that the actual values are passed in as a tuple ( 'My Way', 15 ) as the second parameter to the execute() call.
import sqlite3

conn = sqlite3.connect('music.sqlite')
cur = conn.cursor()

cur.execute('INSERT INTO Tracks (title, plays) VALUES (?, ?)',
    ('Thunderstruck', 20))
cur.execute('INSERT INTO Tracks (title, plays) VALUES (?, ?)',
    ('My Way', 15))
conn.commit()

print('Tracks:')
cur.execute('SELECT title, plays FROM Tracks')
for row in cur:
     print(row)

cur.execute('DELETE FROM Tracks WHERE plays < 100')
conn.commit()

cur.close()
First we INSERT two rows into our table and use commit() to force the data to be written to the database file.
Figure 21.4.6.
Then we use the SELECT command to retrieve the rows we just inserted from the table. On the SELECT command, we indicate which columns we would like (title, plays) and indicate which table we want to retrieve the data from. After we execute the SELECT statement, the cursor is something we can loop through in a for statement. For efficiency, the cursor does not read all of the data from the database when we execute the SELECT statement. Instead, the data is read on demand as we loop through the rows in the for statement.
The output of the program is as follows:
Tracks:
('Thunderstruck', 20)
('My Way', 15)
Our for loop finds two rows, and each row is a Python tuple with the first value as the title and the second value as the number of plays.

Note 21.4.7.

Note: You may see strings starting with u' in other books or on the Internet. This was an indication in Python 2 that the strings are Unicode strings that are capable of storing non-Latin character sets. In Python 3, all strings are unicode strings by default.
At the very end of the program, we execute an SQL command to DELETE the rows we have just created so we can run the program over and over. The DELETE command shows the use of a WHERE clause that allows us to express a selection criterion so that we can ask the database to apply the command to only the rows that match the criterion. In this example the criterion happens to apply to all the rows so we empty the table out so we can run the program repeatedly. After the DELETE is performed, we also call commit() to force the data to be removed from the database.

Checkpoint 21.4.8.

Put the following code in order to create a cursor, make a table called “Cats” with two text columns (“Name” and “Breed”). Then add the rows for Whiskers, Ruby, and Milo in the table and commit that change. Finally select all the rows and print them before closing the cursor.
SQLite actually does allow some flexibility in the type of data stored in a column, but we will keep our data types strict in this chapter so the concepts apply equally to other database systems such as MySQL
http://www.sqlite.org/datatypes.html
http://en.wikipedia.org/wiki/SQL
<Prev^TopNext>
Authored in PreTeXt
Powered by Runestone