Now that you've gotten a brief introduction to SQL, its time to get some hands-on practice connecting to a database via Python and executing some queries.
You will be able to:
- Connect to a SQL database using Python
- Retrieve all information from a SQL table
- Retrieve a subset of records from a table using a
WHERE
clause - Write SQL queries to filter and order results
- Retrieve a subset of columns from a table
First, let's connect to our database by importing sqlite3 and running the following cell in our notebook. You'll need a cursor object (cur
) to fetch results. Cursor objects allow you to keep track of which result set is which since it's possible to run multiple queries before you're done fetching the results of the first.
import sqlite3
conn = sqlite3.connect('data.sqlite')
cur = conn.cursor()
# connect database and create cursor here
import sqlite3
conn = sqlite3.connect('data.sqlite')
cur = conn.cursor()
The database that you've just connected to is the same database from the previous introduction. Here's an overview of the database:
Now that you're connected to the database, let's take a look at how you can query the data within.
With your cursor object, you can execute queries
cur.execute("""SELECT * FROM employees LIMIT 5;""")
<sqlite3.Cursor at 0x111deb420>
The execute command itself only returns the cursor object. To see the results, you must use the fetchall method afterwards.
cur.fetchall()
[('1002',
'Murphy',
'Diane',
'x5800',
'dmurphy@classicmodelcars.com',
'1',
'',
'President'),
('1056',
'Patterson',
'Mary',
'x4611',
'mpatterso@classicmodelcars.com',
'1',
'1002',
'VP Sales'),
('1076',
'Firrelli',
'Jeff',
'x9273',
'jfirrelli@classicmodelcars.com',
'1',
'1002',
'VP Marketing'),
('1088',
'Patterson',
'William',
'x4871',
'wpatterson@classicmodelcars.com',
'6',
'1056',
'Sales Manager (APAC)'),
('1102',
'Bondur',
'Gerard',
'x5408',
'gbondur@classicmodelcars.com',
'4',
'1056',
'Sale Manager (EMEA)')]
It's also possible to combine the previous two cells into one line, like so:
## Uncomment cell to display contents:
# cur.execute("""SELECT * FROM employees LIMIT 5;""").fetchall()
When working with strings, you may have previously seen a 'string'
, a "string"
, a '''string'''
, or a """string"""
. While all of these are strings, the triple quotes have the added functionality of being able to use multiple lines within the same string. Sometimes, SQL queries can be much longer than others, in which case it's helpful to use new lines for readability. Here's a short example:
## Uncomment cell to display contents:
# cur.execute("""SELECT *
# FROM employees
# LIMIT 5;""").fetchall()
Often, a more convenient output will be to turn these results into pandas DataFrames. To do this, you simply wrap the c.fetchall()
output with a pandas DataFrame constructor:
import pandas as pd
cur.execute("""SELECT * FROM employees LIMIT 5;""")
df = pd.DataFrame(cur.fetchall())
df.head()
.dataframe tbody tr th {
vertical-align: top;
}
.dataframe thead th {
text-align: right;
}
0 | 1 | 2 | 3 | 4 | 5 | 6 | 7 | |
---|---|---|---|---|---|---|---|---|
0 | 1002 | Murphy | Diane | x5800 | dmurphy@classicmodelcars.com | 1 | President | |
1 | 1056 | Patterson | Mary | x4611 | mpatterso@classicmodelcars.com | 1 | 1002 | VP Sales |
2 | 1076 | Firrelli | Jeff | x9273 | jfirrelli@classicmodelcars.com | 1 | 1002 | VP Marketing |
3 | 1088 | Patterson | William | x4871 | wpatterson@classicmodelcars.com | 6 | 1056 | Sales Manager (APAC) |
4 | 1102 | Bondur | Gerard | x5408 | gbondur@classicmodelcars.com | 4 | 1056 | Sale Manager (EMEA) |
Sadly as you can see this is slightly clunky as we do not have the column names.
We can access the column names by calling cur.description
, like so:
cur.execute("""SELECT * FROM employees LIMIT 5;""")
df = pd.DataFrame(cur.fetchall())
df.columns = [x[0] for x in cur.description]
df.head()
.dataframe tbody tr th {
vertical-align: top;
}
.dataframe thead th {
text-align: right;
}
employeeNumber | lastName | firstName | extension | officeCode | reportsTo | jobTitle | ||
---|---|---|---|---|---|---|---|---|
0 | 1002 | Murphy | Diane | x5800 | dmurphy@classicmodelcars.com | 1 | President | |
1 | 1056 | Patterson | Mary | x4611 | mpatterso@classicmodelcars.com | 1 | 1002 | VP Sales |
2 | 1076 | Firrelli | Jeff | x9273 | jfirrelli@classicmodelcars.com | 1 | 1002 | VP Marketing |
3 | 1088 | Patterson | William | x4871 | wpatterson@classicmodelcars.com | 6 | 1056 | Sales Manager (APAC) |
4 | 1102 | Bondur | Gerard | x5408 | gbondur@classicmodelcars.com | 4 | 1056 | Sale Manager (EMEA) |
- Check out the documentation for more info on cursor methods and attributes here.
In general, the WHERE
clause filters query results by some condition. As you are starting to see, you can also combine multiple conditions.
cur.execute("""SELECT * FROM customers WHERE city = 'Boston';""")
df = pd.DataFrame(cur.fetchall())
df.columns = [x[0] for x in cur.description]
df
.dataframe tbody tr th {
vertical-align: top;
}
.dataframe thead th {
text-align: right;
}
customerNumber | customerName | contactLastName | contactFirstName | phone | addressLine1 | addressLine2 | city | state | postalCode | country | salesRepEmployeeNumber | creditLimit | |
---|---|---|---|---|---|---|---|---|---|---|---|---|---|
0 | 362 | Gifts4AllAges.com | Yoshido | Juri | 6175559555 | 8616 Spinnaker Dr. | Boston | MA | 51003 | USA | 1216 | 41900.00 | |
1 | 495 | Diecast Collectables | Franco | Valarie | 6175552555 | 6251 Ingle Ln. | Boston | MA | 51003 | USA | 1188 | 85100.00 |
cur.execute("""SELECT * FROM customers WHERE city = 'Boston' OR city = 'Madrid';""")
df = pd.DataFrame(cur.fetchall())
df.columns = [x[0] for x in cur.description]
df
.dataframe tbody tr th {
vertical-align: top;
}
.dataframe thead th {
text-align: right;
}
customerNumber | customerName | contactLastName | contactFirstName | phone | addressLine1 | addressLine2 | city | state | postalCode | country | salesRepEmployeeNumber | creditLimit | |
---|---|---|---|---|---|---|---|---|---|---|---|---|---|
0 | 141 | Euro+ Shopping Channel | Freyre | Diego | (91) 555 94 44 | C/ Moralzarzal, 86 | Madrid | 28034 | Spain | 1370 | 227600.00 | ||
1 | 237 | ANG Resellers | Camino | Alejandra | (91) 745 6555 | Gran Vía, 1 | Madrid | 28001 | Spain | 0.00 | |||
2 | 344 | CAF Imports | Fernandez | Jesus | +34 913 728 555 | Merchants House | 27-30 Merchant's Quay | Madrid | 28023 | Spain | 1702 | 59600.00 | |
3 | 362 | Gifts4AllAges.com | Yoshido | Juri | 6175559555 | 8616 Spinnaker Dr. | Boston | MA | 51003 | USA | 1216 | 41900.00 | |
4 | 458 | Corrida Auto Replicas, Ltd | Sommer | Martín | (91) 555 22 82 | C/ Araquil, 67 | Madrid | 28023 | Spain | 1702 | 104600.00 | ||
5 | 465 | Anton Designs, Ltd. | Anton | Carmen | +34 913 728555 | c/ Gobelas, 19-1 Urb. La Florida | Madrid | 28023 | Spain | 0.00 | |||
6 | 495 | Diecast Collectables | Franco | Valarie | 6175552555 | 6251 Ingle Ln. | Boston | MA | 51003 | USA | 1188 | 85100.00 |
Two additional keywords that you can use to refine your searches are the ORDER BY
and LIMIT
clauses. The order by clause allows you to sort the results by a particular feature. For example, you could sort by the customerName
column if you wished to get results in alphabetical order. By default, ORDER BY
is ascending. So, as with the above example, if you want the opposite, use the additional parameter DESC
. Finally, the limit clause is typically the last argument in a SQL query and simply limits the output to a set number of results.
cur.execute("""SELECT customerNumber, customerName, city, creditLimit
FROM customers
WHERE (city = 'Boston' OR city = 'Madrid') AND (creditLimit >= 50000.00)
ORDER BY creditLimit DESC
LIMIT 15
;""")
df = pd.DataFrame(cur.fetchall())
df.columns = [x[0] for x in cur.description]
df
.dataframe tbody tr th {
vertical-align: top;
}
.dataframe thead th {
text-align: right;
}
customerNumber | customerName | city | creditLimit | |
---|---|---|---|---|
0 | 495 | Diecast Collectables | Boston | 85100.00 |
1 | 344 | CAF Imports | Madrid | 59600.00 |
2 | 362 | Gifts4AllAges.com | Boston | 41900.00 |
3 | 141 | Euro+ Shopping Channel | Madrid | 227600.00 |
4 | 458 | Corrida Auto Replicas, Ltd | Madrid | 104600.00 |
5 | 237 | ANG Resellers | Madrid | 0.00 |
6 | 465 | Anton Designs, Ltd. | Madrid | 0.00 |
You might notice that the output of this query doesn't seem to respect our credit limit criterion. A little investigation shows that this is because the number is actually stored as a string!
type(df.creditLimit.iloc[0])
str
This is an annoying problem to encounter and also underlines the importance of setting up a database in an appropriate manner at the get-go. For now, it's time to practice some of your SQL querying skills!
In this lesson, you saw how to connect to a SQL database via Python and how to subsequently execute queries against that database. Going forward, you'll continue to learn additional keywords for specifying your query parameters!