Relational Databases and Pandas:

  • Sometimes you might have a single data table in a spreadsheet or a flat file, but often you might be working with a relational database that hold multiple tables.
  • Each of these individual tables in a database is similar to a Pandas DataFrame, but then there’s additional logic that connects them together as part of the database.

SQL Alchemy:

  • The SQL Alchemy package helps facilitate connections to different types of SQL-based databases including an example that we’ll look at here.
python
1import pandas as pd
2from sqlalchemy import create_engine
3engine = create_engine('sqlite:///data/product_data.sqlite')
4sales = pd.read_sql_query("SELECT * FROM sales", engine)
5print(sales)

Output:

1 | Month | Pens | Pencils | Erasers | Paper |
2 |-------|------|---------|---------|-------|
3 | Jan   | 400  | 550     | 80      | 480   |
4 | Feb   | 370  | 420     | 55      | 450   |
5 | Mar   | 255  | 302     | 25      | 280   |
6 | Apr   | 150  | 225     | 20      | 200   |
7 | May   | 200  | 275     | 41      | 225   |
8 | Jun   | 125  | 170     | 25      | 184   |
9 | Jul   | 50   | 80      | 10      | 100   |
10 | Aug   | 425  | 600     | 90      | 505   |
11 | Sep   | 423  | 580     | 95      | 525   |
12 | Oct   | 200  | 225     | 60      | 400   |
13 | Nov   | 105  | 106     | 12      | 203   |
14 | Dec   | 78   | 69      | 15      | 99    |
  • We might have not just sales data but also orders, and so we can pass a different SQL query, SELECT * FROM orders to the database engine and then this would then produce a separate DataFrame showing in this case not monthly sales, but quarterly orders

FROM, ORDER BY and WHERE Clauses:

  • We might want to identify or detect when inventory’s getting low by asking, FROM our inventory table the numbers of pens or pencils or erasers or papers get below some threshold.
  • The ORDER BY clause can be used to sort the result set in ascending, ASC, or descending, DESC, order.
python
1import pandas as pd
2from sqlalchemy import create_engine
3engine = create_engine('sqlite:///data/product_data.sqlite')
4low_inventory = pd.read_sql_query("SELECT * FROM inventory WHERE (Pencils < 200 or Pens < 200 or Erasers < 50 or Paper < 300)", engine)
5print(low_inventory)

Output:

1| Month | Pens | Pencils | Erasers | Paper |
2|-------|------|---------|---------|-------|
3| Mar   | 175  | 228     | 240     | 190   |
4| Jun   | 200  | 58      | 254     | 181   |
5| Sep   | 302  | 198     | 459     | 251   |
6| Nov   | 497  | 367     | 487     | 248   |
7| Dec   | 419  | 298     | 472     | 149   |