Hour 19. Using SQL to Get More out of Databases
What You’ll Learn in This Hour:
How to filter queries with WHERE
How to sort data with ORDER BY
How to get unique items with DISTINCT
How to update records with UPDATE
How to delete records with DELETE
When to use advanced SQL statements in the real world
In the last hour, you learned how to use databases to store information. That’s not all there is to databases, though! You know how to create a database and store information in it. In this hour, we’ll cover how to use more SQL statements to get information out of databases, how to update the information in a database, and how to delete items.
So far, you know how to make a simple query:
from tablename select [column or *]
This is a perfectly fine SQL statement, if you really do want all the items in a certain column (or all the information in a table). Many times, though, that’s too much information. We usually just want a selection of items, or we want them in a certain order. We want the database to do the heavy lifting when it comes to searching, not Python.
For the following examples, we’re going to use a sample database that has been populated by a random selection of video games. Table 19.1 has all the data in this database.
Filtering with WHERE
The WHERE clause is a special statement you can tack on to the end of your SQL statement. It allows you to filter when you make a call to your database, so only a subset of items is returned. An SQL statement with a WHERE clause looks like this:
FROM tablename SELECT [column / *] WHERE Boolean statement
When you make a query that has a WHERE statement, only statements where the Boolean statement is true are returned. WHERE can be used to check for equality and inequality, find items that are similar, find items that do not fit a certain criteria, and check for greater than or less than.
Checking for Equality
Say you want to make a query against the games database. If you want to get only the games where the rating is E, the SQL statement would look like this:
SELECT *
FROM games
WHERE rating="E"
Note that we’re using only a single equals sign rather than the double equals signs we’re used to in Python expressions. That’s because SQL has a slightly different syntax than Python.
In our shell, let’s try out the preceding statement. In theory, we should get three games: Forza Motorsport 4, ZhuZhu Pets, and Sonic Generations. Naturally, we’ll have to open our database and create a cursor first:
>>> import sqlite3
>>> conn = sqlite3.connect('games.db')
>>> cursor = conn.cursor()
>>> sql = '''SELECT *
... FROM games
... WHERE rating="E"'''
>>> e_games = cursor.execute(sql)
>>> e_games = results.fetchall()
>>> e_games
[(u'Forza Motorsport 4', u'E', u'360', 2011), (u'Sonic Generations', u'E',
u'360', 2012), (u'ZhuZhu Pets', u'E', u'Wii', 2010)]
As predicted, we got our three games that were rated E.
Checking for Inequality
What if we wanted all the games that weren’t rated E? In this case, we can use the “does not equal” operator (!=). Let’s make a new query, this time to get all the games that aren’t rated E:
>>> sql = '''SELECT *
... FROM games
... WHERE rating != "E"
... '''
>>> results = cursor.execute(sql)
>>> not_e_games = results.fetchall()
>>> print not_e_games
[(u'Tales of the Abyss', u'T', u'3DS', 2011), (u'Adventure Time', u'E10+', u'DS',
2012), (u'Hollywood Crimes', u'T', u'3DS', 2011), (u'Forza Horizon', u'T', u'360',
2012)]
This time, we got all the games in our database that were not rated E. Note, though, that we got one game that is rated E10+. Maybe that’s fine, and we just wanted to exclude games that were rated E.
Using LIKE to Find Similar Items
What if we wanted to exclude or get games that were rated in any of the E categories? When you want to find items that contain another string, you can use LIKE. LIKE allows you to look for items that begin with, end with, or contain a string. Here is what an SQL statement with LIKE looks like:
SELECT * FROM table WHERE column LIKE '%string'
A LIKE statement uses a percent sign (%) to create the pattern that the database will try to match to. The percent sign indicates where SQLite is allowed to fill in any other characters, from none to many. The percent sign can go on the beginning of a substring, at the end, or on both ends. Table 19.2 shows some examples of what a LIKE statement would look like, and what it would select.
Let’s select all the games in our database whose rating begins with E, assuming we already have the database open and have a cursor set up:
>>> sql = '''
... SELECT *
... FROM games
... WHERE rating LIKE "E%"
... '''
>>> results = cursor.execute(sql)
>>> all_e_games = results.fetchall()
>>> all_e_games
[(u'Adventure Time', u'E10+', u'DS', 2012), (u'Forza Motorsport 4', u'E', u'360',
2011), (u'Sonic Generations', u'E', u'360', 2012), (u'ZhuZhu Pets', u'E', u'Wii',
2012)]
This time, we got all of our E games, as well as our E10+ game.
Using NOT LIKE to Find Nonsimilar Items
But what if we wanted to get all games except the games in the various E categories? In this case, we would want to use the NOT keyword with our LIKE statement. A SELECT statement that uses NOT looks something like this:
SELECT * FROM table WHERE column NOT LIKE "%pattern%"
When you use NOT, the database will return every item that doesn’t match that pattern. Let’s get all the games that aren’t rated in any of the E categories:
>>> sql = '''
... SELECT *
... FROM games
... WHERE rating NOT LIKE "E%"
... '''
>>> results = cursor.execute(sql)
>>> non_e_games = results.fetchall()
>>> non_e_games
[(u'Tales of the Abyss', u'T', u'3DS', 2011), (u'Hollywood Crimes', u'T', u'3DS',
2011), (u'Forza Horizon', u'T', u'360', 2012)]
This time, our one E10+ game, Adventure Time, wasn’t returned because its rating starts with a capital E. Only games with ratings that don’t start with a capital E were returned.
Querying with Greater Than and Less Than
Along with filtering by matches, we can also filter based on values being greater than or less than each other. A query using the greater than or less than operator looks like this:
SELECT * FROM table WHERE column > value
In our database, let’s look for games that were published after 2011:
>>> sql = '''
... SELECT title FROM games
... WHERE year > 2011
... '''
>>> results = cursor.execute(sql)
>>> games = results.fetchall()
>>> games
[(u'Adventure Time',), (u'Sonic Generations',), (u'Forza Horizon',)]
Our database returns three games, all of them published in 2012 or 2013. What if we wanted to search for all games published in 2011 or before? Then, just like with Python, we can use the “less than or equal to” operator:
>>> sql = '''
... SELECT title FROM games
... WHERE year <= 2011
... '''
>>> results = cursor.execute(sql)
>>> games = results.fetchall()
>>> games
[(u'Tales of the Abyss',), (u'Forza Motorsport 4',), (u'ZhuZhu Pets',),
(u'Hollywood Crimes',)]
This time, we’re given the games published in 2011 and 2010.
Sorting with ORDER BY
So far, when we make a query to our database, we get our results in no particular order. We can add an ORDER BY clause that tells the database to return our results in a certain order. An SQL statement that has an ORDER BY clause looks something like this:
SELECT * FROM table ORDER BY column
For example, let’s say we want to get all our titles from the database, sorted by their titles. It would look like this:
>>> sql = '''
... SELECT title
... FROM games
... ORDER BY title
... '''
>>> results = cursor.execute(sql)
>>> titles = results.fetchall()
>>> print titles
[(u'Adventure Time',), (u'Forza Horizon',), (u'Forza Motorsport 4',),
(u'Hollywood Crimes',), (u'Sonic Generations',), (u'Tales of the Abyss',),
(u'ZhuZhu Pets',)]
Our database returned all our titles in alphabetical order. But what if we wanted them in reverse alphabetical order? For this, we can use the DESC operator. A statement with a DESC operator looks like this:
SELECT * FROM table ORDER BY column DESC
Note that DESC goes after the item you want to sort by. Let’s put our titles in reverse alphabetical order:
>>> sql = '''
... SELECT title
... FROM games
... ORDER BY title DESC
... '''
>>> results = cursor.execute(sql)
>>> titles = results.fetchall()
>>> titles
[(u'ZhuZhu Pets',), (u'Tales of the Abyss',), (u'Sonic Generations',),
(u'Hollywood Crimes',), (u'Forza Motorsport 4',), (u'Forza Horizon',),
(u'Adventure Time',)]
Now, we have all our titles, but this time in reverse order.
Getting Unique Items with DISTINCT
A very useful operator in SQL is DISTINCT. When you use DISTINCT with a SELECT statement, your database returns only unique entries for that column. A statement using DISTINCT looks something like this:
SELECT DISTINCT column FROM table
Let’s get a list of our gaming systems from the database:
>>> sql = '''
... SELECT DISTINCT system
... FROM games
... '''
>>> results = cursor.execute(sql)
>>> systems = results.fetchall()
>>> systems
[(u'360',), (u'3DS',), (u'DS',), (u'Wii',)]
According to the results of this query, we have games for the Xbox 360 and the Nintendo DS, 3DS, and Wii.
Updating Records with UPDATE
Being able to store data and add records is great, but we’re going to eventually have to change records. To change a record, we have to create an UPDATE statement. An UPDATE statement looks like this:
UPDATE table SET column="some value" WHERE column="some value"
For example, let’s say we add another game to the database:
>>> sql = '''
... INSERT INTO games
... (title, rating, system, year)
... VALUES ("Luigi's Mansion", "E", "3DS", 2013)
... '''
>>> cursor.execute(sql)
<sqlite3.Cursor object at 0x10dd28c00>
>>> conn.commit()
We later learn that the new game is actually called Luigi’s Mansion, Dark Moon. We’ll have to change the title so that our database is accurate. We can use an UPDATE to change the title:
>>> sql = '''
... UPDATE games
... SET title="Luigi's Mansion, Dark Moon"
... WHERE title="Luigi's Mansion"
... '''
>>> cursor.execute(sql)
<sqlite3.Cursor object at 0x10dd28c00>
>>> conn.commit()
Note that we have to commit changes using conn.commit() after using UPDATE. Otherwise, the changes wouldn’t be saved.
If you want to make sure the changes went through, it’s safest to use a new cursor. If you use the same cursor, you could get back inaccurate results.
>>> sql = '''
... SELECT * FROM games WHERE title LIKE "Luigi%"
... '''
>>> cursor2 = conn.cursor()
>>> results = cursor2.execute(sql)
>>> print results.fetchall()
[(u"Luigi's Mansion, Dark Moon", u'E', u'3DS', 2013)]
The title is now correct, and our database has been updated.
Deleting Records with DELETE
Now we can add records and change records. What if we need to remove a record completely? For this, we create a DELETE statement. A DELETE statement looks something like this:
DELETE FROM table WHERE column=value
Let’s say that Hollywood Crimes needs to be removed completely from our database. We would use DELETE FROM as follows:
>>> sql = '''
... DELETE FROM games
... WHERE title="Hollywood Crimes"
... '''
>>> cursor.execute(sql)
<sqlite3.Cursor object at 0x10dd28c00>
>>> conn.commit()
We then check the database to make sure the game is really gone:
>>> sql = '''
... SELECT * FROM games WHERE title="Hollywood Crimes"
... '''
>>> cursor2 = conn.cursor()
>>> results = cursor2.execute(sql)
>>> print results.fetchall()
[]
The game was successfully removed from the database!
DELETE will remove any records that match your pattern, so be careful not to be too overzealous when using it. Carefully consider whether any of your other records might be accidentally culled because you were too liberal with your wildcards.
Using SQL in the Real World
Let’s return to the cook in our restaurant example. In our previous hour, she had written a program that allowed her to add items in her inventory to a database. She’s able to list all the current items, add items, and search items. Here’s her current code:
import sqlite3
def open_database(db_name):
conn = sqlite3.connect(db_name)
return conn
def add_ingredient(cursor):
ingredient = raw_input("Name of ingredient: ")
num = raw_input("Number in storage: ")
description = raw_input("description: ")
sql = '''insert into ingredients
(title, amount, description)
values
("{title}", {amount}, "{description}")'''
sql = sql.format(title=ingredient, amount=num, description=description)
cursor.execute(sql)
print "Added!"
def find_ingredient(cursor, item):
sql = '''SELECT title, amount FROM ingredients WHERE title="{item}"'''
sql = sql.format(item=item)
results = cursor.execute(sql)
items = cursor.fetchall()
if len(items) == 0:
print "Sorry, that ingredient wasn't found"
else:
for item in items:
print item[0], "-", item[1]
def list_ingredients(cursor):
sql = '''SELECT title, amount FROM ingredients'''
results = cursor.execute(sql)
items = results.fetchall()
print "Items in the inventory"
for item in items:
print item[0], '-', item[1]
def menu():
print
print "What do you want to do?"
print "A - Add an ingredient"
print "S - Search for an ingredient"
print "L - List all ingredients"
print "Q - Quit"
choice = raw_input("Choice [A/S/L/Q]: ")
return choice[0].lower()
def main():
conn = open_database('inventory.db')
cursor = conn.cursor()
while True:
choice = menu()
if choice == 'a':
add_ingredient(cursor)
elif choice == 's':
item = raw_input("What ingredient? ")
find_ingredient(cursor=cursor, ingredient=item)
elif choice =='l':
list_ingredients(cursor)
elif choice == 'q':
print "Goodbye"
break
else:
print "Sorry, that's not valid"
conn.commit()
conn.close()
if __name__ == '__main__':
main()
Though her code does many things, it’s missing something: the ability to update the items in her inventory. After all, that’s main reason for having an inventory program!
She realizes that she’s going to have to add a function that has an UPDATE statement. She also realizes that the user may want to update what the ingredient is called, how much the user has of it, or what its description is. With that in mind, she writes the following function:
def update_ingredient(cursor):
item = raw_input("Which item? ")
column = raw_input("What column (title, amount, description)? ")
value = raw_input("To what value? ")
if column[0].lower() == 't':
sql = '''UPDATE ingredients
SET title="{value}"
WHERE title="{title}"'''
elif column[0].lower() == 'a':
sql = '''UPDATE ingredients
SET amount={value}
WHERE title="{title}"'''
elif column[0].lower() == 'd':
sql = '''UPDATE ingredients
SET description="{value}"
WHERE title="{title}"'''
else:
print "Sorry, that's not valid."
return
sql = sql.format(value=value, title=item)
cursor.execute(sql)
She also has to update her menu and main function, but when she’s done, her program runs like this:
$ python inventory_main.py
What do you want to do?
A - Add an ingredient
S - Search for an ingredient
L - List all ingredients
U - Update an ingredient
Q - Quit
Choice [A/S/L/U/Q]: l
Items in the inventory
flour - 40.0
sugar (lb) - 20.0
Egg - 100.0
What do you want to do?
A - Add an ingredient
S - Search for an ingredient
L - List all ingredients
U - Update an ingredient
Q - Quit
Choice [A/S/L/U/Q]: u
Which item? flour
What column (title, amount, description)? amount
To what value? 100
What do you want to do?
A - Add an ingredient
S - Search for an ingredient
L - List all ingredients
U - Update an ingredient
Q - Quit
Choice [A/S/L/U/Q]: l
Items in the inventory
flour - 100.0
sugar (lb) - 20.0
Egg - 100.0
What do you want to do?
A - Add an ingredient
S - Search for an ingredient
L - List all ingredients
U - Update an ingredient
Q - Quit
Choice [A/S/L/U/Q]: q
Goodbye
Now she can add items to her inventory as well as update them so she can keep track of how much she has available.
Summary
During this hour, you learned how to narrow the results of your SELECT queries. You also learned how to order the results from your SELECT queries by columns. Finally, you learned how to update and delete records in your database.
Q&A
Q. Does Python work with other databases?
A. It does, but you’ll have to install a module or library to work with it. A popular toolkit is SQLAlchemy, which works with many popular databases, such as MySQL and Oracle. You can find SQLAlchemy at http://www.sqlalchemy.org/.
If SQLAlchemy doesn’t cover your database, the next best option is to go to PyPi (https://pypi.python.org/pypi) and search for your particular database.
Q. Is this all there is to SQL?
A. Not by a long shot! There are ways to perform queries that combine different tables, ways of setting up your database to run faster, and ways to work with multiple databases at once. After all, some people’s entire jobs revolve around just working with databases.
If you want to learn more about databases, a number of online tutorials are available, as well as many books geared toward beginners. The W3Schools website has a good tutorial to get you started (http://www.w3schools.com/sql/default.asp). There is also the book Sams Teach Yourself SQL in 24 Hours, by Ryan Stephens, Ron Plew, and Arie D. Jones.
Workshop
The Workshop contains quiz questions and exercises to help you solidify your understanding of the material covered. Try to answer all questions before looking at the answers that follow.
Quiz
1. What is the percent sign (%) character used for in queries?
2. What happens if more than one item matches the WHERE clause in an UPDATE statement?
3. How do you order the results of a SELECT query?
Answers
1. The percent sign is used as a wildcard. It allows you to find substring matches when making a query.
2. All the items that have been selected will be updated.
3. The ORDER BY clause lets you order by any column or even more than one column.
Exercise
The inventory program needs one more feature: the ability to delete items. Write a function that allows the cook to delete an item from her inventory. She should be able to select the item via its title, and the item should be immediately deleted and the changes committed.