Using Databases and SQL
What is a database?
A database is a file that is organized for storing data. Most databases are organized like a dictionary in the sense that they map from keks to values. The biggest difference is that the database is on disk (or other permanent storage), so it persists after the program ends. Because a database is stored on permanent storage, it can store far more data then a dictionary, which is limited to the size of the memory in the computer.
Like a dictionary, database software is designed to keep the inserting and accessing of data very fast, even for large amounts of data. Database software maintains its performance by building indexes as data is added to the database to allow the computer to jump quickly to a particular entry.
There are many different database systems which are used for a wide variety of purposes including: Oracle, MySQL, Microsoft SQL Server, PostgreSQL, and SQLite. We focus on SQLite in this book because it is a very common database and its already built into Python. SQLite is designed to be embedded into other applications to provide database support within the application. For example, the Firefox browser also uses the SQLite database internally as do many other products.
SQLite is well suited to some of the data manipulation problems that we see in Informatics.
Database concepts
When you first look at a database it looks like a spreadsheet with multiple sheets. The primary data structures in a database are: tables, rows, columns.
Relational Databases
In technical descriptions of relational databases the concepts of table, row, and column are more formally referred to as relation, tuple, and attribute, respectively. We will use the less formal terms in this chapter.
Database Browser For SQLite
While this chapter will focus on using Python to work with data in SQLite database files, many operations can be done more conveniently using software called the Database Browser for SQLite which is freely available from:
Using the browser you can easily create tables, insert data, edit data, or run simple SQL queries on the data in the database.
In a sense, the database browser is similar to a text editor when working with text files. When you want to do one or very few operations on a text file, you can just open it in a text editor and make the changes you want. When you have many changes that you need to do to a text file, often you will write a simple Python program. You will find the same pattern when working with databases. You will do simple operations in the database manager and more complex operations will be most conveniently done in Python.
Creating a database table
Databases require more defined structure than Python lists or dictionaries.
When we create a database table we must tell the database in advance the name 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 lookup the data based on the type of data.
You can look at the various data types supported by SQLite at the following url:
http://www.sqlite.org/datatypes.html
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 Track with two coluumns in the database is as follows:
import sqlite3
conn = sqlite3.connect('music.sqlite')
cur = conn.cursor()
cur.execute('DROP TABLE IF EXISTS Track')
cur.execute('CREATE TABLE Track (title TEXT, plays INTEGER)')
conn.close()The connect operation makes a "connection" to the database stored in the file music.sqlite 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 databases is stored on a sparate "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 curson is like a fiile handle that we can use to perform operations on the data stored in the database. Calling curson() is very similar conceptually to calling open() when dealing with text files.
A Database Cursor
Once we have the cursor, we can begin to execute commands on the contents of the database using the execute() method.
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.
https://en.wikipedia.org/wiki/SQL
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 Track table from the database if it exists. This pattern is simply to allow us to run the same program to create the Track 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 Track ')The second command creates a table named Track with a text column name title and an integer column named plays.
cur.execute('CREATE TABLE Track (title TEXT, plays INTEGER)')Now that we have created a table named Track, we can put some data into that table using the SQL INSERT operation. Again, we begin by making a connection to the database and obtaining the cursor. We can then execute SQL commands using the cursor.
The SQL INSERT command indicates which table we are using and thehn 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 Track (title, plays) VALUES (?, ?)',
('Thunderstruck', 20))
cur.execute('INSERT INTO Track (title, plays) VALUES (?, ?)',
('My Way', 15))
conn.commit()
print('Track:')
cur.execute('SELECT title, plays FROM Track')
for row in cur:
print(row)
cur.execute('DELETE FROM Track 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.
Rows in a Table
Then we use the SELECT command to retrieve the rows we just inserted form 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 execute the SELECT statement, the cursor is something we can loop through in a for statement. For efficiency, the cursor dose 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:
Track:
('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.
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.
留言