{"id":574,"date":"2021-07-16T07:00:11","date_gmt":"2021-07-16T12:00:11","guid":{"rendered":"https:\/\/wollen.org\/blog\/?p=574"},"modified":"2024-08-17T07:49:33","modified_gmt":"2024-08-17T12:49:33","slug":"working-with-sql-databases","status":"publish","type":"post","link":"https:\/\/wollen.org\/blog\/2021\/07\/working-with-sql-databases\/","title":{"rendered":"SQL databases aren’t actually that scary"},"content":{"rendered":"

Today let’s dip our toes into the SQL world. We’ve spent a lot of time working with pandas<\/em> and CSV files but data won’t always be packaged that way. It’s a valuable skill to be comfortable with data in all its forms.<\/p>\n

We won’t spend too much time studying database queries\u2014in fact there’s only one basic query in the code below. Instead we’ll walk through a quick example of retrieving and visualizing a table.<\/p>\n

\n

1. The data.<\/h4>\n

This post uses the chinook.db<\/em> example database that’s found all over the internet. You can learn more about it here<\/a>. The database contains 11 separate tables but we’ll just focus on one: invoices<\/em>. In a more complex SQL query you might bring multiple tables together with a UNION<\/em> or JOIN<\/em> operation.<\/p>\n

The invoices<\/em> table contains 9 columns:<\/p>\n

InvoiceId\r\nCustomerId\r\nInvoiceDate\r\nBillingAddress\r\nBillingCity\r\nBillingState\r\nBillingCountry\r\nBillingPostalCode\r\nTotal<\/pre>\n
Let’s analyze how invoices vary between countries. We can find the number of invoices sent to each country and their average bill.<\/p>\n
Begin with the imports. sqlite3<\/em> is built into the Python standard library so there’s no excuse not to take advantage! contextlib<\/em> provides a convenient way to manage context when querying databases. The concept is similar to opening a text file by writing with open(\"file.txt\", \"r\") as f<\/code>. Whatever you place within the block only exists while the block is executed. The idea is to place a database connection in a context, then close it when you’re done.<\/p>\n
import sqlite3\r\nfrom contextlib import closing\r\nimport matplotlib.pyplot as plt\r\nfrom matplotlib import ticker\r\nimport seaborn as sns<\/pre>\n
Before digging in let’s take a closer look at the table’s structure. There are a few different ways to retrieve column info but I find a PRAGMA<\/em> operation to be the most readable. It also works independently from the cursor so you don’t have to retrieve a view before calling cursor.description<\/code>.<\/p>\n
Notice how contextlib.closing<\/code> is used below. Both the cursor and the database connection only exist in the context of this code block.<\/p>\n
with closing(sqlite3.connect(\"chinook.db\")) as connection:\r\n    with closing(connection.cursor()) as cursor:\r\n        column_info = cursor.execute(\"PRAGMA table_info(invoices)\").fetchall()\r\n        for col in column_info:\r\n            print(col[1])<\/pre>\n
At this point we’ve printed the names of all columns within the table. But notice we only printed the 1-indexed element of each item within column_info<\/code>. That’s because elements include more information than just the column’s name. Below you can see all the information available:<\/p>\n
(0, 'InvoiceId', 'INTEGER', 1, None, 1)\r\n(1, 'CustomerId', 'INTEGER', 1, None, 0)\r\n(2, 'InvoiceDate', 'DATETIME', 1, None, 0)\r\n(3, 'BillingAddress', 'NVARCHAR(70)', 0, None, 0)\r\n(4, 'BillingCity', 'NVARCHAR(40)', 0, None, 0)\r\n(5, 'BillingState', 'NVARCHAR(40)', 0, None, 0)\r\n(6, 'BillingCountry', 'NVARCHAR(40)', 0, None, 0)\r\n(7, 'BillingPostalCode', 'NVARCHAR(10)', 0, None, 0)\r\n(8, 'Total', 'NUMERIC(10,2)', 1, None, 0)<\/pre>\n
It’s often helpful to know a column’s datatype, for example.<\/p>\n
\n
2. Get the data from the database.<\/h4>\n
For this exercise we’ll focus on BillingCountry<\/em> and Total<\/em> columns. That’s all we need to calculate the number of invoices from each country and each country’s average bill. The query begins by selecting<\/strong> three columns from<\/strong> a particular table. For two of the columns it does an operation on them and assigns an alias.<\/p>\n
The query groups by<\/strong> country, which works very similarly to pandas.groupby<\/code>. All rows that represent a German buyer, for example, are combined into a single row. We do this because we’re interested in describing each country’s buyers as a group.<\/p>\n
There are 24 unique countries within the table but we’ll limit the query to 10 for visualization purposes. That’s what LIMIT 10<\/code> means. The query specifies descending order<\/strong> to return the top-10 countries ranked by invoice count<\/strong>.<\/p>\n
The fetchall<\/code> method returns a list of tuples with as many elements as the query returns. Since we’re grouping by country and limiting the response to 10, rows<\/code> will be a list of ten 3-tuples.<\/p>\n
rows = cursor.execute(\r\n    \"SELECT BillingCountry, COUNT(BillingCountry) AS count, AVG(Total) AS avg \"\r\n    \"FROM invoices \"\r\n    \"GROUP BY BillingCountry \"\r\n    \"ORDER BY count DESC \"\r\n    \"LIMIT 10\"\r\n).fetchall()<\/pre>\n
The response:<\/p>\n
[\r\n ('USA', 91, 5.747912087912091),\r\n ('Canada', 56, 5.427857142857142),\r\n ('France', 35, 5.574285714285712),\r\n ...\r\n]<\/pre>\n
As you can see, USA is the most common customer country with Canada coming in second. USA customers spend slightly more per invoice than Canada and France.<\/p>\n
Now that we’ve moved data from an SQL table to a Python list, the world is our oyster. We could turn it into a DataFrame, plot it right now, or anything else. Let’s plot it with Seaborn<\/em>.<\/p>\n
\n
3. Prepare the data for Seaborn.<\/h4>\n
The cleanest way to unpack rows<\/code> into 3 variables is to use zip<\/code> along with the *<\/code>\u00a0 operator.<\/p>\n
country, count, average = list(zip(*rows))<\/pre>\n
The only caveat is that these variables will be tuples, not lists, which Seaborn<\/em> expects. However that’s easily addressed by using list()<\/code> in the plotting methods later.<\/p>\n
As an aside, if you aren’t familiar with the *<\/code> operator, an asterisk is used to “unpack” iterables. Below is a more obvious demonstration:<\/p>\n
>>> rows = [(\"John\", 1, 10), (\"Paul\", 2, 20), (\"George\", 3, 30)]\r\n>>> print(*rows)\r\n('John', 1, 10) ('Paul', 2, 20) ('George', 3, 30)\r\n>>> list(zip(*rows))\r\n[('John', 'Paul', 'George'), (1, 2, 3), (10, 20, 30)]<\/pre>\n
So just like when you iterate through a zipped pair of lists…<\/p>\n
for x, y in zip(x_values, y_values):\r\n    print(x, y)<\/pre>\n
… The first elements go together, then the second elements, and so on. We use the same principle to separate rows<\/code> into 3 iterables that we can plot.<\/p>\n
\n
4. Plot the data.<\/h4>\n
Now we’ll create 2 barplots<\/em> to visualize the data. We’re going to place the plots side-by-side so create a 1×2 subplot grid and make the figure size extra wide. Specify an axis for each plot, tweak the style and formatting as desired, and we’re ready to go.<\/p>\n
A few quick notes about the code:<\/p>\n