Before you keep reading...
Runestone Academy can only continue if we get support from individuals like you. As a student you are well aware of the high cost of textbooks. Our mission is to provide great books to you for free, but we ask that you consider a $10 donation, more if you can or less if $10 is a burden.
Before you keep reading...
Making great stuff takes time and $$. If you appreciate the book you are reading now and want to keep quality materials free for other students please consider a donation to Runestone Academy. We ask that you consider a $10 donation, but if you can give more thats great, if $10 is too much for your budget we would be happy with whatever you can afford as a show of support.
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.
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
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()
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.
Once we have the cursor, we can begin to execute commands on the
contents of the database using the
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.
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
title and an integer column named
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
Again, we begin by making a connection to the database and getting a
cursor object. We can then execute SQL commands using the cursor.
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
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()
INSERT two rows into our table and use
commit() to force the data to be written to the database
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
The output of the program is as follows:
Tracks: ('Thunderstruck', 20) ('My Way', 15)
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
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
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
commit() to force the data to be removed from the
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