{"id":2566,"date":"2025-10-12T17:18:18","date_gmt":"2025-10-12T09:18:18","guid":{"rendered":"http:\/\/blue.yn.cn\/?p=2566"},"modified":"2025-10-21T16:54:11","modified_gmt":"2025-10-21T08:54:11","slug":"python-and-databases","status":"publish","type":"post","link":"http:\/\/blue.yn.cn\/?p=2566","title":{"rendered":"Python and Databases"},"content":{"rendered":"

Using Databases and SQL<\/strong><\/p>\n

What is a database?<\/h3>\n

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.<\/p>\n

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.<\/p>\n

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.<\/p>\n

http:\/\/sqlite.org\/<\/a><\/p>\n

SQLite is well suited to some of the data manipulation problems that we see in Informatics.<\/p>\n

Database concepts<\/h3>\n

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.<\/p>\n

\"file\"
\nRelational Databases<\/p>\n

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.<\/p>\n

Database Browser For SQLite<\/h3>\n

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:<\/p>\n

http:\/\/sqlitebrowser.org\/<\/a><\/p>\n

Using the browser you can easily create tables, insert data, edit data, or run simple SQL queries on the data in the database.<\/p>\n

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.<\/p>\n

Creating a database table<\/h3>\n

Databases require more defined structure than Python lists or dictionaries.<\/p>\n

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.<\/p>\n

You can look at the various data types supported by SQLite at the following url:<\/p>\n

http:\/\/www.sqlite.org\/datatypes.html<\/a><\/p>\n

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.<\/p>\n

The code to create a database file and a table named Track<\/code> with two coluumns in the database is as follows:<\/p>\n

import sqlite3\n\nconn = sqlite3.connect('music.sqlite')\ncur = conn.cursor()\n\ncur.execute('DROP TABLE IF EXISTS Track')\ncur.execute('CREATE TABLE Track (title TEXT, plays INTEGER)')\n\nconn.close()<\/code><\/pre>\n
The connect<\/code> operation makes a "connection" to the database stored in the file music.sqlite<\/code> 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.<\/p>\n
A curson is like a fiile handle that we can use to perform operations on the data stored in the database. Calling curson()<\/code> is very similar conceptually to calling open()<\/code> when dealing with text files.<\/p>\n
\"file\"
\nA Database Cursor<\/p>\n
Once we have the cursor, we can begin to execute commands on the contents of the database using the execute()<\/code> method.<\/p>\n
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.<\/p>\n
https:\/\/en.wikipedia.org\/wiki\/SQL<\/a><\/p>\n
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.<\/p>\n
The first SQL command removes the Track<\/code> table from the database if it exists. This pattern is simply to allow us to run the same program to create the Track<\/code> table over and over again without causing an error. Note that the DROP TABLE<\/code> command deletes the table and all of its contents from the database (i.e., there is no "undo").<\/p>\n
cur.execute('DROP TABLE IF EXISTS Track ')<\/code><\/pre>\n
The second command creates a table named Track<\/code> with a text column name title<\/code> and an integer column named plays<\/code>.<\/p>\n
cur.execute('CREATE TABLE Track (title TEXT, plays INTEGER)')<\/code><\/pre>\n
Now that we have created a table named Track<\/code>, we can put some data into that table using the SQL INSERT<\/code> operation. Again, we begin by making a connection to the database and obtaining the cursor<\/code>. We can then execute SQL commands using the cursor.<\/p>\n
The SQL INSERT<\/code> command indicates which table we are using and thehn defines a new row by listing the fields we want to include (title, plays)<\/code> followed by the VALUES<\/code> we want placed in the new row. We specify the values as question marks (?, ?)<\/code> to indicate that the actual values are passed in as a tuple ('My Way', 15)<\/code> as the second parameter to the execute()<\/code> call.<\/p>\n
import sqlite3\n\nconn = sqlite3.connect('music.sqlite')\ncur = conn.cursor()\n\ncur.execute('INSERT INTO Track (title, plays) VALUES (?, ?)',\n    ('Thunderstruck', 20))\ncur.execute('INSERT INTO Track (title, plays) VALUES (?, ?)',\n    ('My Way', 15))\nconn.commit()\n\nprint('Track:')\ncur.execute('SELECT title, plays FROM Track')\nfor row in cur:\n     print(row)\n\ncur.execute('DELETE FROM Track WHERE plays < 100')\nconn.commit()\n\ncur.close()<\/code><\/pre>\n
First we INSERT<\/code> two rows into our table and use commit()<\/code> to force the data to be written to the database file.<\/p>\n
\"file\"
\nRows in a Table<\/p>\n
Then we use the SELECT<\/code> command to retrieve the rows we just inserted form the table. On the SELECT<\/code> command, we indicate which columns we would like (title, plays)<\/code> and indicate which table we want to retrieve the data from. After execute the SELECT<\/code> statement, the cursor is something we can loop through in a for<\/code> statement. For efficiency, the cursor dose not read all of the data from the database when we execute the SELECT<\/code> statement. Instead, the data is read on demand as we loop through the rows in the for<\/code> statement.<\/p>\n
The output of the program is as follows:<\/p>\n
Track:\n('Thunderstruck', 20)\n('My Way', 15)<\/code><\/pre>\n
Our for<\/code> loop finds two rows, and each row is a Python tuple with the first value as the title<\/code> and the second value as the number of plays<\/code>.<\/p>\n
At the very end of the program, we execute an SQL command to DELETE<\/code> the rows we have just created so we can run the program over and over. The DELETE<\/code> command shows the use of a WHERE<\/code> 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<\/code> is performed, we also call commit()<\/code> to force the data to be removed from the database.<\/p>\n
Structured Query Language summary<\/h3>\n
So far, we have been using the Structured Query Language in our Python examples and have covered many of the basics of the SQL commands. In this section, we look at the SQL language in paticular and give an overview of SQL syntax.<\/p>\n
Since there are so many different database vendors, the Structured Query Language (SQL) was standardized so we could communicate in a portable manner to database systems from multiple vendors.<\/p>\n
A relational database is made up of tabels, rows and columns. The columns generally have a type such as text, numeric, or date data. When we create a table, we indicate the names and types of the columns:<\/p>\n
CREATE TABLE Track (title TEXT, plays INTEGER)<\/code><\/pre>\n
To insert a row into a table, we use the SQL INSERT<\/code> command:<\/p>\n
INSERT INTO Track (title, plays) VALUES ('My Way', 15)<\/code><\/pre>\n
The INSERT<\/code> statement specifies the table name, then a list of the fields\/columns that you would like to set in the new row, and then the keyword VALUE<\/code> and a list of corresponding values for each of the fields.<\/p>\n
The SQL SELECT<\/code> command is used to retrieve rows and columns from a database. The SELECT<\/code> statement lets you specify which columns you would like to retrieve as well as a WHERE<\/code> clause to select which rows you would like to see. It also allows an optional ORDER BY<\/code> clause to control the sorting of the returned rows.<\/p>\n
SELECT * FROM Track WHERE title = 'My Way'<\/code><\/pre>\n
Using *<\/code> indicates that you want the database to return all of the columns for each row that matches the WHERE<\/code> clause.<\/p>\n
Note, unlike in Python, in a SQL WHERE<\/code> clause we use a single equal sign to indicate a test for equanlity rather than a double equal sign. Other logical operations allowed in a WHERE<\/code> clause include <<\/code>, ><\/code>, <=<\/code>, >=<\/code>, !=<\/code>, as well as AND<\/code> and OR<\/code> and parentheses to buil your logical expressions.<\/p>\n
You can request that the returned rows be sorted by one of the fields as follows:<\/p>\n
SELECT title,plays FROM Track ORDER BY title<\/code><\/pre>\n
It is possible to UPDATE<\/code> a column or columns within one or more rows in a table using the SQL UPDATE<\/code> statement as follows:<\/p>\n
UPDATE Track SET plays = 16 WHERE title = 'My Way'<\/code><\/pre>\n
The UPDATE<\/code> statement specifies a table and then a list of fields and values to change after the SET<\/code> keyword and then an optional WHERE<\/code> clause to select the rows that are to be updated. A single UPDATE<\/code> statement will change all of  the rows that match the WHERE<\/code> clause. If a WHERE<\/code> clause is not specified, it performs the UPDATE<\/code> on all of the rows in the table.<\/p>\n
To remove a row, you need a WHERE<\/code> clause on an SQL DELETE<\/code> statement. The WHERE<\/code> clause determines which rows are to be deleted:<\/p>\n
DELETE FROM Track WHERE title = 'My Way'<\/code><\/pre>\n
These four basic SQL commands (INSERT, SELECT, UPDATE, DELETE) allow the four basic operations needed to create and maintain data. We use "CRUD" (Create, Read, Update, and Delete) to capture all these concepts in a single term.<\/p>\n
Multiple table and basic data modeling<\/h3>\n
The real power of a relational database is when we create multiple tables and make links between those tables. The act of deciding how to break up your application data into multiple tables and establshing the relationships between the tables is called data modeling. The design document that shows the tables and their relationships is called a data model.<\/p>\n
Data modeling is a relatively sophisticated skill and we will only introduce the most basic concepts of relational data modeling in this section. For more detail on data modeling you can start with:<\/p>\n
https:\/\/en.wikipedia.org\/wiki\/Relational_model<\/a><\/p>\n
Lets say for our tracks database we wanted to track the name of the artist<\/code> for each track in addition to the title<\/code> and number of plays for each track. A simple approach might be to simply add another column to the database called artist<\/code> and put the name of the artist in the column as follows:<\/p>\n
DROP TABLE IF EXISTS Track;\nCREATE TABLE Track (title TEXT, plays INTEGER, artist TEXT);<\/code><\/pre>\n
Then we could insert a few tracks into our tabel.<\/p>\n
INSERT INTO Track (title, plays, artist)\n    VALUES ('My Way', 15, 'Frank Sinatra');\nINSERT INTO Track (title, plays, artist)\n    VALUES ('New York', 25, 'Frank Sinatra');<\/code><\/pre>\n
If we were to look at our data with a SELECT * FROM Track<\/code> statement, it looks like we have done a fine job.<\/p>\n
sqlite> SELECT * FROM Track;\nMy Way|15|Frank Sinatra\nNew York|25|Frank Sinatra\nsqlite><\/code><\/pre>\n
We have made a very bad error in our data modeling. We have violated the rules of database normalization.<\/p>\n
https:\/\/en.wikipedia.org\/wiki\/Database_normalization<\/a><\/p>\n
While database normalization seems very complex on the surface and contains a lot of mathematical justifications, for now we can reduce it all into one simple rule that we will follow.<\/p>\n
We should never put the same string data in a column more than once. If we need the data more than once, we create a numeric key for the data and reference the actual data using this key. Especially if the multiple entries refer to the same object.<\/p>\n
To demonstrate the slippery slope we are going down by assigning string columns to out database model, think about how we would change the data model if we wanted to keep track of the eye color of our artist? would we do this?<\/p>\n
DROP TABLE IF EXISTS Track;\nCREATE TABLE Track (title TEXT, plays INTEGER,\n    artist TEXT, eyes TEXT);\nINSERT INTO Track (title, plays, artist, eyes)\n    VALUES ('My Way', 15, 'Frank Sinatra', 'Blue');\nINSERT INTO Track (title, plays, artist, eyes)\n    VALUES ('New York', 25, 'Frank Sinatra', 'Blue');<\/code><\/pre>\n
Since Frank Sinatra recorded over 1200 songs, are we really going to put the string 'Blue' in 1200 rows in our Track<\/code> table. And what would happen if we decided his eye color was 'Light Blue'? Something does not fell right.<\/p>\n
The correct solution is to create a table for the each Artist<\/code> and store all the data about the artist in that table. And then somehow we need to make a connection between a row in the Track<\/code> table to a row in the Artist<\/code> table. Perhaps we could call this "link" between two "tables" a "relationship" between two tables. And that is exactly what database experts decided to all these links.<\/p>\n
Lets make an Artist<\/code> table as follows:<\/p>\n
DROP TABLE IF EXISTS Artist;\nCREATE TABLE Artist (name TEXT, eyes TEXT);\nINSERT INTO Artist (name, eyes)\n   VALUES ('Frank Sinatra', 'blue');<\/code><\/pre>\n
Now we have a row in the table for 'Frank Sinatra' (and his eye color) and a primary key of '42' to use to link our tracks to him. So we alter our Track tabel as follows:<\/p>\n
DROP TABLE IF EXISTS Track;\nCREATE TABLE Track (title TEXT, plays INTEGER,\n    artist_id INTEGER);\nINSERT INTO Track (title, plays, artist_id)\n    VALUES ('My Way', 15, 42);\nINSERT INTO Track (title, plays, artist_id)\n    VALUES ('New York', 25, 42);<\/code><\/pre>\n
The atrist_id<\/code> column is an integer, and by naming convention is a foreign key pointing at a primary key in the Artist<\/code> table. We call it foreign key because it is pointing to a row in a different table.<\/p>\n
Now we are following the rules of database normalization, but when we want to get data out of our database, we don't want to see the 42, we want to see the name and eye color of the artist. To do this we use the JOIN<\/code> keyword in our SELECT statement.<\/p>\n
SELECT title, plays, name, eyes\nFROM Track JOIN Artist\nON Track.artist_id = Artist.id;<\/code><\/pre>\n
The JOIN<\/code> clause includes an ON<\/code> condition that defines how the rows are to to be connected. For each row in Track<\/code> add the data from Artist<\/code> from the row where artist_id<\/code> Track<\/code> table matches the id<\/code> from the Artist<\/code> table.<\/p>\n
The output would be:<\/p>\n
My Way|15|Frank Sinatra|blue\nNew York|25|Frank Sinatra|blue<\/code><\/pre>\n
While it might seem a little clunky and your instincts might tell you that it would be faster just to keep the data in one table, it turns out the the limit on database performance is how much data needs to be scanned when retrieving a query. While the details are very complex, integers are a lot smaller than strings (especially Unicode) and far quicker to to move and compare.<\/p>\n
Data model diagrams<\/h3>\n
While our Track<\/code> and Artist<\/code> database design is simple with just two tables and a single one-to-many relationship, these data model can get complicated quickly and are easier to understand if we can make a graphical representation of our data model.<\/p>\n
\"file\"
\nA Verbose One-to-Many Data Model<\/p>\n
While there are many graphical representations of data models, we will use one of the "classic" approaches, called "Crow's Foot Diagrams" as shown in Figure. Each table is shown as a box with the name of the table and its columns. Then where there is a relationship between two tablse a line is drawn connecting the tables with a notation added to the end of each line indicating the nature of the relationship.<\/p>\n
https:\/\/en.wikipedia.org\/wiki\/Entity-relationship_model<\/a><\/p>\n
In this case, "many" tracks can be associated with each artist. So the track end is shown with the crow's foot spread out indicating it is the "many" end. The artist end is shown with a vertical like that indicates "one". There will be "many" artists in general, but the important aspect is that for each artist there will be many tracks. And each of those artists may be associated with multiple tracks.<\/p>\n
You will note that the column that holds the foreign_key like artist_id<\/code> is on the "many" end and the primary key is at the "one" end.<\/p>\n
Since the pattern of foreign and primary key placement is so consistent and follows the "many" and "one" ends of the lines, we never include either the primary or foreign key columns in our diagram of the data model as shown in the second diagram as shown in Figure. The columns are thought of as "implementation details" to capture the nature of the relationship details and not an essential part of the data being modeled.<\/p>\n
\"file\"
\nA Succinct One-to-Many Data Model<\/p>\n
Automatically creating primary keys<\/h3>\n
In the above example, we arbitrarily assigned Frank the primary key of 42. However when we are inserting millions or rows, it is nice to have the database automatically generate the values for the id column. We do this by declaring the id<\/code> column as a PRIMARY KEY<\/code> and leave out the id<\/code> value when inserting the row:<\/p>\n
DROP TABLE IF EXISTS Artist;\nCREATE TABLE Artist (id INTEGER PRIMARY KEY,\n    name TEXT, eyes TEXT);\nINSERT INTO Artist (name, eyes)\n   VALUES ('Frank Sinatra', 'blue');<\/code><\/pre>\n
Now we have instructed the database to auto-assign us a unique value to the Frank Sinatra row. But we then need a way to have the database tell us the id<\/code> value for the recently inserted row. One way is to use a SELECT<\/code> statement to retrieve data from an SQLite built-in-function called last_insert_rowid()<\/code>.<\/p>\n
sqlite> DROP TABLE IF EXISTS Artist;\nsqlite> CREATE TABLE Artist (id INTEGER PRIMARY KEY,\n   ...>     name TEXT, eyes TEXT);\nsqlite> INSERT INTO Artist (name, eyes)\n   ...>    VALUES ('Frank Sinatra', 'blue');\nsqlite> select last_insert_rowid();\n1\nsqlite> SELECT * FROM Artist;\n1|Frank Sinatra|blue\nsqlite><\/code><\/pre>\n
Once we know the id<\/code> of our 'Frank Sinatra' row, we can use it when we INSERT<\/code> the tracks into the Track<\/code> table. As a general strategy, we add these id<\/code> columns to any table we create:<\/p>\n
sqlite> DROP TABLE IF EXISTS Track;\nsqlite> CREATE TABLE Track (id INTEGER PRIMARY KEY,\n   ...>     title TEXT, plays INTEGER, artist_id INTEGER);<\/code><\/pre>\n
Note that the artist_id<\/code> value is the new auto-assigned row in the Artist<\/code> table and that while we added an INTEGER PRIMARY KEY<\/code> to the the Track<\/code> table, we did not include id<\/code> in the list of fields on the INSERT<\/code> statements into the Track<\/code> table. Again this tells the database to choose a unique value for us for the id<\/code> column.<\/p>\n
sqlite> INSERT INTO Track (title, plays, artist_id)\n   ...>     VALUES ('My Way', 15, 1);\nsqlite> select last_insert_rowid();\n1\nsqlite> INSERT INTO Track (title, plays, artist_id)\n   ...>     VALUES ('New York', 25, 1);\nsqlite> select last_insert_rowid();\n2\nsqlite><\/code><\/pre>\n
You can call SELECT last_insert_rowid();<\/code> after each of the inserts to retrieve the value that the database assigned to the id<\/code> of each newly created row. Later when we are coding in Python, we can ask for the id<\/code> value in our code and store it in a variable for later use.<\/p>\n
Logical keys for fast lookup<\/h3>\n
If we had a table full of artist and a table full of tracks, each with a foreign key link to a row in a table full of artists and we wanted to list all the tracks that were sung by 'Frank Sinatra' as follows:<\/p>\n
SELECT title, plays, name, eyes\nFROM Track JOIN Artist\nON Track.artist_id = Artist.id\nWHERE Artist.name = 'Frank Sinatra';<\/code><\/pre>\n
Since we have two tables and a foreign key between the two tables, our data is well-modeled, but if we are going to have millions of records in the Artist<\/code> table and going to do a lot of lookups by artist name, we would benefit if we gave the database a hint about our intended use of the name<\/code> column.<\/p>\n
We do this by adding an "index" to a text column that we intend to use in WHERE<\/code> clauses:<\/p>\n
CREATE INDEX artist_name ON Artist(name);<\/code><\/pre>\n
When the database has been told that an index is needed on a column in a table, it stores extra information to make it possible to look up a row more quickly using the indexed field (name<\/code> in this example). Once you request that an index be created, there is nothing special that is needed in the SQL to access the table. The database keeps the index up to data as data is inserted, deleted, and updated, and uses it aotomatically if it will increase the performance of a database query.<\/p>\n
These text columns that are used to find rows based on some information in the "real world" like the name of an artist are called Logical keys.<\/p>\n
Adding constraints to the data database<\/h3>\n
We can also use an index to enforce a constraint (i.e. rules) on our database operations. The most common constraint is a uniqueness constraint which insists that all of the values in a column are unique. We can add the optional UNIQUE<\/code> keyword, to the CREATE INDEX<\/code> statement to tell the database that we would like it to enforce the constraint on our SQL. We can drop and re-create the artist_name<\/code> index with a UNIQUE<\/code> constraint as follows.<\/p>\n
DROP INDEX artist_name;\nCREATE UNIQUE INDEX artist_name ON Artist(name);<\/code><\/pre>\n
If we try to insert 'Frank Sinatra' a second time, it will fail with an error.<\/p>\n
sqlite> SELECT * FROM Artist;\n1|Frank Sinatra|blue\nsqlite> INSERT INTO Artist (name, eyes)\n   ...>    VALUES ('Frank Sinatra', 'blue');\nRuntime error: UNIQUE constraint failed: Artist.name (19)\nsqlite><\/code><\/pre>\n
We can tell the database to ignore any duplicate key errors by adding the IGNOR<\/code> keyword to the INSERT<\/code> statement as follows:<\/p>\n
sqlite> INSERT OR IGNORE INTO Artist (name, eyes)\n   ...>     VALUES ('Frank Sinatra', 'blue');\nsqlite> SELECT id FROM Artist WHERE name='Frank Sinatra';\n1\nsqlite><\/code><\/pre>\n
By combining an INSERT OR IGNORE<\/code> and a SELECT<\/code> we can insert a new record if the name is not already there and whether or not the record is already there, retrieve the primary key of the record.<\/p>\n
sqlite> INSERT OR IGNORE INTO Artist (name, eyes)\n   ...>      VALUES ('Elvis', 'blue');\nsqlite> SELECT id FROM Artist WHERE name='Elvis';\n2\nsqlite> SELECT * FROM Artist;\n1|Frank Sinatra|blue\n2|Elvis|blue\nsqlite><\/code><\/pre>\n
Since we have not added a uniqueness constraint to the eye color column, there is no problem having multiple 'Blue' values in the eye<\/code> column.<\/p>\n
\"file\"
\nTracks, Albums, and Artist<\/p>\n
Sample multi-table application<\/h3>\n
A sample application called tracks_csv.py<\/code> shows how these ideas can be combined to parse textual data and load it into several tables using a proper data model with relational connections between the tables.<\/p>\n
This application reads and pases a comma-separated file track.csv<\/code> based on an export from Dr.Chunk's iTunes library.<\/p>\n
Another One Bites The Dust,Queen,Greatest Hits,55,100,217103\nAsche Zu Asche,Rammstein,Herzeleid,79,100,231810\nBeauty School Dropout,Various,Grease,48,100,239960\nBlack Dog,Led Zeppelin,IV,109,100,296620\n...<\/code><\/pre>\n
The columns in this file are: title, artist, album, number of plays, rating(0-100) and length in milliseconds.<\/p>\n
Our data model is shown in Figure and described in SQL as follows:<\/p>\n
DROP TABLE IF EXISTS Artist;\nDROP TABLE IF EXISTS Album;\nDROP TABLE IF EXISTS Track;\n\nCREATE TABLE Artist (\n    id INTEGER PRIMARY KEY,\n    name TEXT UNIQUE\n);\n\nCREATE TABLE Album (\n    id INTEGER PRIMARY KEY,\n    artist_id  INTEGER,\n    title TEXT UNIQUE\n);\n\nCREATE TABLE Track (\n    id INTEGER PRIMARY KEY,\n    title TEXT UNIQUE,\n    album_id INTEGER,\n    len INTEGER, rating INTEGER, count INTEGER\n);<\/code><\/pre>\n
We are adding the UNIQUE<\/code> keyword to TEXT<\/code> columns that we would like to have a uniqueness constraint that we will use in INSERT IGNORE<\/code> statements. This is more succinct that separate CREATE INDEX<\/code> statements but has the same effect.<\/p>\n
import sqlite3\n\n# \u8fde\u63a5\u6570\u636e\u5e93\uff08\u82e5\u4e0d\u5b58\u5728\u5219\u521b\u5efa trackdb.sqlite \u6587\u4ef6\uff09\nconn = sqlite3.connect('trackdb.sqlite')\n# \u521b\u5efa\u6e38\u6807\u5bf9\u8c61\uff08\u7528\u4e8e\u6267\u884c SQL \u8bed\u53e5\uff09\ncur = conn.cursor()\n\n# \u5b9a\u4e49\u8981\u6267\u884c\u7684 SQL \u8bed\u53e5\uff08\u521b\u5efa\u8868\u7ed3\u6784\uff09\nsql_statements = """\nDROP TABLE IF EXISTS Artist;\nDROP TABLE IF EXISTS Album;\nDROP TABLE IF EXISTS Track;\n\nCREATE TABLE Artist (\n    id INTEGER PRIMARY KEY,\n    name TEXT UNIQUE\n);\n\nCREATE TABLE Album (\n    id INTEGER PRIMARY KEY,\n    artist_id  INTEGER,\n    title TEXT UNIQUE\n);\n\nCREATE TABLE Track (\n    id INTEGER PRIMARY KEY,\n    title TEXT UNIQUE,\n    album_id INTEGER,\n    len INTEGER, rating INTEGER, count INTEGER\n);\n"""\n\n# \u6267\u884c SQL \u8bed\u53e5\uff08\u4f7f\u7528 executescript \u4e00\u6b21\u6027\u6267\u884c\u591a\u6761\u8bed\u53e5\uff09\ncur.executescript(sql_statements)\n\n# \u63d0\u4ea4\u4e8b\u52a1\uff08\u786e\u4fdd\u6240\u6709\u64cd\u4f5c\u88ab\u5199\u5165\u6570\u636e\u5e93\u6587\u4ef6\uff09\nconn.commit()\n\n# \u5173\u95ed\u6e38\u6807\u548c\u8fde\u63a5\ncur.close()\nconn.close()\n\nprint("\u6570\u636e\u5e93\u8868\u7ed3\u6784\u5df2\u521b\u5efa\u5e76\u4fdd\u5b58\u5230 trackdb.sqlite")<\/code><\/pre>\n
With these tables in place, we write the following code tracks_csv.py<\/code> to parse the data and insert it into the tables:<\/p>\n
import sqlite3\n\nconn = sqlite3.connect('trackdb.sqlite')\ncur = conn.cursor()\n\nhandle = open('tracks.csv')\n\nfor line in handle:\n    line = line.strip();\n    pieces = line.split(',')\n    if len(pieces) != 6 : continue\n\n    name = pieces[0]\n    artist = pieces[1]\n    album = pieces[2]\n    count = pieces[3]\n    rating = pieces[4]\n    length = pieces[5]\n\n    print(name, artist, album, count, rating, length)\n\n    cur.execute('''INSERT OR IGNORE INTO Artist (name)\n        VALUES ( ? )''', ( artist, ) )\n    cur.execute('SELECT id FROM Artist WHERE name = ? ', (artist, ))\n    artist_id = cur.fetchone()[0]\n\n    cur.execute('''INSERT OR IGNORE INTO Album (title, artist_id)\n        VALUES ( ?, ? )''', ( album, artist_id ) )\n    cur.execute('SELECT id FROM Album WHERE title = ? ', (album, ))\n    album_id = cur.fetchone()[0]\n\n    cur.execute('''INSERT OR REPLACE INTO Track\n        (title, album_id, len, rating, count)\n        VALUES ( ?, ?, ?, ?, ? )''',\n        ( name, album_id, length, rating, count ) )\n\n    conn.commit()<\/code><\/pre>\n
You can see that we are repeating the pattern of INSERT OR IGNORE<\/code> followed by a SELECT<\/code> to get the appropriate artist_id<\/code> and album_id<\/code> for use in later INSERT<\/code> statement. We start from Artist<\/code> because we need artist_id<\/code> to insert the Album<\/code> and need the album_id<\/code> to insert the Track<\/code>.<\/p>\n
If we look at the Album<\/code> table, we can see that the entries were added and assigned a primary key as necessary as the data was parsed. We can also see the foreign key pointing to a row in the Artist<\/code> table for each Album<\/code> row.<\/p>\n
sqlite> .mode column\nsqlite> SELECT * FROM Album LIMIT 5;\nid  artist_id  title\n--  ---------  -----------------\n1   1          Greatest Hits\n2   2          Herzeleid\n3   3          Grease\n4   4          IV\n5   5          The Wall [Disc 2]<\/code><\/pre>\n
We can reconstruct all of the Track<\/code> data, following all the relations using JOIN \/ ON<\/code> clauses. You can see both ends of each of the (2) relational connections in each row in the output below:<\/p>\n
sqlite> .mode line\nsqlite> SELECT * FROM Track\n   ...> JOIN Album ON Track.album_id = Album.id\n   ...> JOIN Artist ON Album.artist_id = Artist.id\n   ...> LIMIT 2;\n       id = 1\n    title = Another One Bites The Dust\n album_id = 1\n      len = 217103\n   rating = 100\n    count = 55\n       id = 1\nartist_id = 1\n    title = Greatest Hits\n       id = 1\n     name = Queen\n\n       id = 2\n    title = Asche Zu Asche\n album_id = 2\n      len = 231810\n   rating = 100\n    count = 79\n       id = 2\nartist_id = 2\n    title = Herzeleid\n       id = 2\n     name = Rammstein<\/code><\/pre>\n
This example shows three tables and two one-to-many relationships between the tables. It also shows how to use indexes and uniqueness constraints to programmatically construct the tables and their relationships.<\/p>\n
https:\/\/en.wikipedia.org\/wiki\/One-to-many_(data_model)<\/a><\/p>\n
Up next we will look at the many-to-many relationships in data models.<\/p>\n
Many to many relationships in database<\/h3>\n
Some data relationships cannot be modeled by a simple on-to-many relationship. For example, lets say we are going to build a data model for a course management system. There will be courses, users, and rosters. A user can be on the roster for many courses and a course will have many users on its roster.<\/p>\n
It is pretty simple to draw a manyj-to-many relationship as shown if Figure. We simply draw two tables and connect them with a line that has the "many" indicator on both ends of the lines. The problem is how to implement the relationship using primary keys and foreign keys.<\/p>\n
Before we explore how we implement many-to-many relationships, let's see if we could hack something up by extending a one-to-many relationship.<\/p>\n
\"file\"
\nA Many to Many Relationship<\/p>\n
If SQL supported the notion of arrays, we might try to define this:<\/p>\n
CREATE TABLE Course (\n    id     INTEGER PRIMARY KEY,\n    title  TEXT UNIQUE\n    student_ids ARRAY OF INTEGER;\n);<\/code><\/pre>\n
Sadly, while this is a tempting idea, SQL does not support arrays.<\/p>\n
Or we could just make long string and concatenate all the User<\/code> primary keys into a long string separated by commas.<\/p>\n
CREATE TABLE Course (\n    id     INTEGER PRIMARY KEY,\n    title  TEXT UNIQUE\n    student_ids ARRAY OF INTEGER;\n);\n\nINSERT INTO Course (title, student_ids)\nVALUES( 'si311', '1,3,4,5,6,9,14');<\/code><\/pre>\n
This would be very inefficient because as the course roster grows in size and the number of courses increases it becomes quite expensive to figure out which courses have student 14 on their roster.<\/p>\n
\"file\"
\nA Many to Many Connector Table<\/p>\n
Instead of either of these approches, we model a many-to-many relationship using an additional table that we call a "junction table", "through table", "connector table", or "join table" as shown in Figure. The purpose of this table is to capture the connection between a course and a student.<\/p>\n
In a sense the table sits between the Course<\/code> and User<\/code> table and has a one-to-many relationship to both tables. By using an intermediate table we break a many-to-many relationship into two one-to-many relationships. Databases are very good at modeling and processing one-to-many relationships.<\/p>\n
An example Member<\/code> table would be as follows:<\/p>\n
CREATE TABLE User (\n    id     INTEGER PRIMARY KEY,\n    name   TEXT UNIQUE\n);\n\nCREATE TABLE Course (\n    id     INTEGER PRIMARY KEY,\n    title  TEXT UNIQUE\n);\n\nCREATE TABLE Member (\n    user_id     INTEGER,\n    course_id   INTEGER,\n    PRIMARY KEY (user_id, course_id)\n);<\/code><\/pre>\n
Following our naming convention, Member.user_id<\/code> and Member.course_id<\/code> are foreign keys pointing at the corresponding rows in the User<\/code> and Course<\/code> tables. Each entry in the member table links a row in the User<\/code> table to a row in the Course<\/code> table by going through the Member<\/code> table.<\/p>\n
We indicate that the combination of course_id<\/code> and user_id<\/code> is the PRIMARY KEY<\/code> for the Member<\/code> table, also creating an uniqueness constraint for a course_id<\/code> \/ user_id<\/code> combination.<\/p>\n
Now lets say we need to insert a number of students into the rosters of a number of courses. Lets assume the data comes to us in a JSON-formatted file with records like this:<\/p>\n
[\n  [ "Charley", "si110"],\n  [ "Mea", "si110"],\n  [ "Hattie", "si110"],\n  [ "Keziah", "si110"],\n  [ "Rosa", "si106"],\n  [ "Mea", "si106"],\n  [ "Mairin", "si106"],\n  [ "Zendel", "si106"],\n  [ "Honie", "si106"],\n  [ "Rosa", "si106"],\n...\n]<\/code><\/pre>\n
We could write code as follows to read the JSON file and insert the members of each course roster into the database using the following code:<\/p>\n
import json\nimport sqlite3\n\nconn = sqlite3.connect('rosterdb.sqlite')\ncur = conn.cursor()\n\nstr_data = open('roster_data_sample.json').read()\njson_data = json.loads(str_data)\n\nfor entry in json_data:\n\n    name = entry[0]\n    title = entry[1]\n\n    print((name, title))\n\n    cur.execute('''INSERT OR IGNORE INTO User (name)\n        VALUES ( ? )''', ( name, ) )\n    cur.execute('SELECT id FROM User WHERE name = ? ', (name, ))\n    user_id = cur.fetchone()[0]\n\n    cur.execute('''INSERT OR IGNORE INTO Course (title)\n        VALUES ( ? )''', ( title, ) )\n    cur.execute('SELECT id FROM Course WHERE title = ? ', (title, ))\n    course_id = cur.fetchone()[0]\n\n    cur.execute('''INSERT OR REPLACE INTO Member\n        (user_id, course_id) VALUES ( ?, ? )''',\n        ( user_id, course_id ) )\n\n    conn.commit()<\/code><\/pre>\n
Like in a previous example, we first make sure that we have an entry in the User<\/code> table and know the primary key of the entry as well as an entry in the Course<\/code> table and know its primary key. We use the 'INSERT OR IGNORE' and 'SELECT' pattern so our code works regardless of whether the record is in the table or not.<\/p>\n
Our insert into the Member<\/code> table is simply inserting the two integers as a new or existing row depending on the constraint to make sure wo do not end up with duplicate entries in the Member<\/code> table for a particular user_id<\/code> \/ course_id<\/code> combination.<\/p>\n
To reconstruct our data across all three tables, we again use JOIN<\/code> \/ ON<\/code> to construct a SELECT<\/code> query;<\/p>\n
sqlite> SELECT * FROM Course\n   ...> JOIN Member ON Course.id = Member.course_id\n   ...> JOIN User ON Member.user_id = User.id;\n+----+-------+---------+-----------+----+---------+\n| id | title | user_id | course_id | id |  name   |\n+----+-------+---------+-----------+----+---------+\n| 1  | si110 | 1       | 1         | 1  | Charley |\n| 1  | si110 | 2       | 1         | 2  | Mea     |\n| 1  | si110 | 3       | 1         | 3  | Hattie  |\n| 1  | si110 | 4       | 1         | 4  | Lyena   |\n| 1  | si110 | 5       | 1         | 5  | Keziah  |\n| 1  | si110 | 6       | 1         | 6  | Ellyce  |\n| 1  | si110 | 7       | 1         | 7  | Thalia  |\n| 1  | si110 | 8       | 1         | 8  | Meabh   |\n| 2  | si106 | 2       | 2         | 2  | Mea     |\n| 2  | si106 | 10      | 2         | 10 | Mairin  |\n| 2  | si106 | 11      | 2         | 11 | Zendel  |\n| 2  | si106 | 12      | 2         | 12 | Honie   |\n| 2  | si106 | 9       | 2         | 9  | Rosa    |\n+----+-------+---------+-----------+----+---------+\nsqlite><\/code><\/pre>\n
You can see the three tables from left to right - Course<\/code>, Member<\/code>, and User<\/code> and you can see the connections between the primary keys and foreign keys in each row of output.<\/p>\n
Modeling data at the many-to-many connection<\/h3>\n
While we have presented the "join table" as having two foreign keys making a connection between rows in two tables, this is the simplest form of a join table. It is quite common to want to add some data to the connection itself.<\/p>\n
Continuing with our example of users, courses, and rosters to model a simple learning management system, we will also need to understand the role that each user is assigned in each course.<\/p>\n
If we first try to solve this by adding a "instructor" flag to the User<\/code> table, we will find that this does not work because a user can be a instructor in one course and a student in another course. If we add an instructor_id<\/code> to the Course<\/code> table it will not work because a course can have multiple instructors. And there is no one-to-many hack that can deal with the fact that the number of roles will expand into roles like Teaching Assistant or Parent.<\/p>\n
But if we simply add a role<\/code> column to the Member<\/code> table - we can represent a wide range of roles, role combinations, etc.<\/p>\n
Lets change our member table as follows:<\/p>\n
DROP TABLE Member;\n\nCREATE TABLE Member (\n    user_id     INTEGER,\n    course_id   INTEGER,\n    role        INTEGER,\n    PRIMARY KEY (user_id, course_id)\n);<\/code><\/pre>\n
For simplicity, we will decide that zero in the role means "student" and one in the role means instructor. Lets assume our JSON data is augmented with the role as follows:<\/p>\n
[\n  [ "Charley", "si110", 1],\n  [ "Mea", "si110", 0],\n  [ "Hattie", "si110", 0],\n  [ "Keziah", "si110", 0],\n  [ "Rosa", "si106", 0],\n  [ "Mea", "si106", 1],\n  [ "Mairin", "si106", 0],\n  [ "Zendel", "si106", 0],\n  [ "Honie", "si106", 0],\n  [ "Rosa", "si106", 0],\n...\n]<\/code><\/pre>\n
We could alter the roster.py<\/code> program above to incorporate role as follows:<\/p>\n
for entry in json_data:\n\n    name = entry[0]\n    title = entry[1]\n    role = entry[2]\n\n    ...\n\n    cur.execute('''INSERT OR REPLACE INTO Member\n        (user_id, course_id, role) VALUES ( ?, ?, ? )''',\n        ( user_id, course_id, role ) )<\/code><\/pre>\n
In a real system, we would probably build a Role<\/code> table and make the role<\/code> column in Member<\/code> a foreign key into the Role table as follows:<\/p>\n
DROP TABLE Member;\n\nCREATE TABLE Member (\n    user_id     INTEGER,\n    course_id   INTEGER,\n    role_id     INTEGER,\n    PRIMARY KEY (user_id, course_id, role_id)\n);\n\nCREATE TABLE Role (\n    id          INTEGER PRIMARY KEY,\n    name        TEXT UNIQUE\n);\n\nINSERT INTO Role (id, name) VALUES (0, 'Student');\nINSERT INTO Role (id, name) VALUES (1, 'Instructor');<\/code><\/pre>\n
Notice that because we declared the id<\/code> column in the Role<\/code> table as a PRIMARY KEY<\/code>, we could omit it in the INSERT<\/code> statement. But we can also choose the id<\/code> value as long as the value is not already in the id<\/code> column and does not vialate the implied UNIQUE<\/code> constraint on primary keys.<\/p>\n
Summary<\/h3>\n
This chapter has covered a lot of ground to give you an overview of the basics of using a database in Python. It is more complicated to write the code to use a database to store data than Python dictionaries or flat files so there is little reason to use a database unless your application truly needs the capabilities of a database. The situations where a database can be quite usefull are:<\/p>\n
    \n
  1. \n
    when your application needs to make many small random updates within a large data set<\/p>\n<\/li>\n
  2. \n
    when your data is so large it cannot fit in a dictionary and you need to look up information repeatedly, or<\/p>\n<\/li>\n
  3. \n
    when you have a long-running process that you want to be able to stop and restart and retain the data from one run to the next.<\/p>\n<\/li>\n<\/ol>\n
    You can build a simple database with a single table to suit many application needs, but most problems will require several tables and links\/relationships between rows in different tables. When you start making links between tables, it is important to do some thoughtful design and follow the rules of database normalization to make the best use of the database's capabilities. Since the primary motivation for using a database is that you have a large amount of data to deal withi, it is important to model your data efficiently so your programs run as fast as possible.<\/p>\n
    Debugging<\/h3>\n
    One common pattern when you are developing a Python program to connect to an SQLite database will be to run a Python program and check the results using the Database Browser for SQLite. The browser allows you to quickly check to see if your program is working properly.<\/p>\n
    You must be careful because SQLite takes care to keep two programs from changing the same data at the same time. For example, if you open a database in the browser and make a change to the database and have not yet pressed the "save" button in the browser, the browser "locks" the database file and keeps any other program from accessing the file. In particular, your Python program will not be able to access the file if it is locked.<\/p>\n
    So a solution is to make sure to either close the database browser or use the file menu to close the database in the browser before you attempt to access the database from Python to avoid the problem of your Python code failing because the database is locked.<\/p>\n
    Glossary<\/h3>\n
    attribute<\/strong>
    \nOne of the values within a tuple. More commonly called a "column" or "field".<\/p>\n
    constraint<\/strong>
    \nWhen we tell the database to enforce a rule on a field or a row in a table. A common constraint is to insist that there can be no duplicate values in a particular field (i.e., all the values must be unique).<\/p>\n
    cursor<\/strong>
    \nA cursor allows you to execute SQL commands in a database and retrieve data from the database. A cursor is similar to a socket or file handle for network connections and files, respectively.<\/p>\n
    database browser<\/strong>
    \nA piece of software that allows you to directly connect to a database and manipulate the database directly without writing a program.<\/p>\n
    foreign key<\/strong>
    \nA numeric key that points to the primary key of a row in another table. Foreign keys establish relationships between rows stored in different tables.<\/p>\n
    index<\/strong>
    \nAdditional data that the database software maintains as rows and inserts into a table to make lookups very fast.<\/p>\n
    logical key<\/strong>
    \nA key that the "outside world" uses to look up a particular row. For example in a table of user accounts, a person's email address might be a good candidate as the logical key for the user's data.<\/p>\n
    normalization<\/strong>
    \nDesigning a data model so that no data is replicated. We store each item of data at one place in the database and reference it elsewhere using a foreign key.<\/p>\n
    primary key<\/strong>
    \nA numeric key assigned to each row that is used to refer to one row in a table from another table. Often the database is configured to automatically assign primary keys as rows are inserted.<\/p>\n
    relation<\/strong>
    \nAn area within a database that contains tuples and attributes. More typically called a "table".<\/p>\n
    tuple<\/strong>
    \nA single entry in a database table that is a set of attributes. More typically called "row".<\/p>\n","protected":false},"excerpt":{"rendered":"
    Using Databases and SQL What is a database? A database … » \u9605\u8bfb\u5168\u6587<\/a><\/p>\n","protected":false},"author":1,"featured_media":0,"comment_status":"open","ping_status":"open","sticky":false,"template":"","format":"standard","meta":{"footnotes":""},"categories":[24],"tags":[],"class_list":["post-2566","post","type-post","status-publish","format-standard","hentry","category-python"],"_links":{"self":[{"href":"http:\/\/blue.yn.cn\/index.php?rest_route=\/wp\/v2\/posts\/2566","targetHints":{"allow":["GET"]}}],"collection":[{"href":"http:\/\/blue.yn.cn\/index.php?rest_route=\/wp\/v2\/posts"}],"about":[{"href":"http:\/\/blue.yn.cn\/index.php?rest_route=\/wp\/v2\/types\/post"}],"author":[{"embeddable":true,"href":"http:\/\/blue.yn.cn\/index.php?rest_route=\/wp\/v2\/users\/1"}],"replies":[{"embeddable":true,"href":"http:\/\/blue.yn.cn\/index.php?rest_route=%2Fwp%2Fv2%2Fcomments&post=2566"}],"version-history":[{"count":11,"href":"http:\/\/blue.yn.cn\/index.php?rest_route=\/wp\/v2\/posts\/2566\/revisions"}],"predecessor-version":[{"id":2586,"href":"http:\/\/blue.yn.cn\/index.php?rest_route=\/wp\/v2\/posts\/2566\/revisions\/2586"}],"wp:attachment":[{"href":"http:\/\/blue.yn.cn\/index.php?rest_route=%2Fwp%2Fv2%2Fmedia&parent=2566"}],"wp:term":[{"taxonomy":"category","embeddable":true,"href":"http:\/\/blue.yn.cn\/index.php?rest_route=%2Fwp%2Fv2%2Fcategories&post=2566"},{"taxonomy":"post_tag","embeddable":true,"href":"http:\/\/blue.yn.cn\/index.php?rest_route=%2Fwp%2Fv2%2Ftags&post=2566"}],"curies":[{"name":"wp","href":"https:\/\/api.w.org\/{rel}","templated":true}]}}