Hello, SQL

SQL is a universal language used for querying databases. We're going to dip into SQL and database design, and build our own Pinterest database.

We'll start with SQLite, a lightweight SQL database that writes to a single file on disk. However, it's worth knowing that most production web apps use more fully-featured database engines, such as MySQL and PostgreSQL. We'll learn more about these next week.

SQLite comes installed with Python. The main command to access it is

sqlite3 test.db

where test.db is the filename of your database.

If that command didn't work for you, you may need to download SQLite. For Windows, download the command-line SQLite program (zip) and move the sqlite3.exe file to C:\Python27\Scripts (or equivalent depending where your Python is installed).

The Four Horsemen

First, let's look at the main operations you might want to do with a database.

You start out with an empty database, so you want to create a database table and create (insert) data inside those tables.

Then, you probably want to be able to get that data back out. That's called a select in SQL.

What if you want to change an existing piece of data? Update.

Finally, let's clear out something entirely with a delete.

You'll sometimes see these four operations referred to as "CRUD". Not because they are cruddy (I hope not!) but because they can be collectively called Create, Read, Update, Delete.

Bootstrapping

We'll start by using a file to get our database going. You could type everything from the file in, but it's quicker to load it up directly from the file.

Download bar_ingredients.sql -- note -O is capital O, not zero :)

$ curl -O http://labs.bewd.co/public/data/bar_ingredients.sql

Now load it up into bar.db:

$ sqlite3 bar.db < bar_ingredients.sql

Open up bar.db and take a look:

$ sqlite3 bar.db
SQLite version 3.7.11 2012-03-20 11:35:50
Enter ".help" for instructions
Enter SQL statements terminated with a ";"
sqlite> .tables
ingredients

The .tables command shows the database tables. Each table is like a tab in a spreadsheet and usually maps to a "type" of data (e.g. people, drinks, orders, etc).

Now let's take a closer look at the ingredients table:

sqlite> .schema ingredients

This prints out the SQL statement used to create that table. You can see that we defined an id which is an int (integer), name which is text, and price and stock which are also both int.

sqlite> .schema ingredients
CREATE TABLE ingredients (
    id int primary_key not null,
    name text not null,
    price int,
    stock int
);

The primary_key means that this column (id) uniquely identifies each separate row in the database, like the row number in a spreadsheet. not null is what it sounds like - this value can't be empty.

Now let's use the select command to show us the contents.

sqlite> select * from ingredients;

This prints out each row with a pipe (|) joining each different "cell" of data in a row. You can type .mode columns to make this a bit more readable.

sqlite> select * from ingredients;
1|glug of rum|4|3
2|slug of whisky|3|4
3|splash of gin|3|7
4|olive on a stick|2|5
5|salt-dusted rim|1|20
6|rasher of bacon|3|8

The * command just means "everything", a convention you may have seen before in Terminal.

If we want to select specific fields, we can.

sqlite> select name, price from ingredients;

And if we want to restrict by specific criteria we can use a where to do that, too.

sqlite> select name, price from ingredients where price=3;

Let's update the stock level on our bacon item. This has an ID of 6, so we can use that unique identifier to make the update easy (and make sure it only affects the bacon!).

sqlite> update ingredients set stock=0 where id=6;

Finally, let's add some new items. The insert syntax needs to know which fields you are updating, too:

sqlite> insert into ingredients (id, name, price, stock) values (7, 'shake of bitters', 2, 12);

What happens if you leave fields out, or try to insert something that is null where the schema says "not null"?

Getting Connected

When dealing with a database in Python, we always have to do two things. We have to open a connection to the database and create a cursor, which is a way of interacting with the database through the connection.

This maps directly on to what you do in Terminal:

$ sqlite3 mydatabase.db     # open a connection
sqlite> SELECT * FROM data; # this is the cursor

To do this in Python, we first import the sqlite3 library, so we can use the relevant functions, and then connect. Try this in a new file or in the interpreter:

import sqlite3

conn = sqlite3.connect('bar.db')
db = conn.cursor()

conn.close() # Always do this after you are done with everything else.

To run any SQL command, we use db.execute() on it.

db.execute("SELECT * FROM ingredients;")

You may be surprised to hear that this function doesn't have a result we can use. After calling execute, we have to go ask the cursor to get us the rows which matched:

results = db.fetchall()

Now results has a list of all the rows which matched the last thing executed. We can print them to see what they look like:

print results

[(1, u'glug of rum', 4, 3), (2, u'slug of whisky', 3, 4), (3, u'splash of gin', 3, 7), (4, u'olive on a stick', 2, 5), (5, u'salt-dusted rim', 1, 20), (6, u'rasher of bacon', 3, 8)]

This is a list, and each row is a tuple (a tuple is just a list whose values you can't change, and has round instead of square brackets to denote this). You can clean the display up with a for loop:

for row in results:
    print row[0], row[1]

And that's the essentials of using SQL in Python!

Challenges

Can you use the Python-database code above (reference here) to print out the current stock levels of the bar?

Try making an UPDATE statement. You will need to do some string formatting using the {} and format operators:

my_command = "UPDATE ingredients SET stock={} WHERE id={}".format(new_stock_level, item_id)

You also need to commit these changes so they persist outside the current database session.

conn.commit()

The goal is to make a web app that can replicate the functionality of our pirate bar from week 1, but using the database (and stock levels) to serve up one drink at a time until an item is out of stock.

Break down the individual tasks that this app will have to do.

What is the simplest possible thing it could do? Start by just building that. (Perhaps it's just listing out all the ingredients in a list, or a drop-down menu.)

What pages and routes do you need?

Ordering a drink

How can you represent the combinations of ingredients? Should it be in the database or a dictionary?

If you want to dynamically change it, you can store it in the database, but using a dictionary is an easier way to start out. You could add another field to the ingredients for 'sweet', 'salty' etc. - or define a new table and use a relation between tables!

What input do you need from the customer? Build that page.

What logic do you need to put in when the customer orders a drink?

What do you think is the right thing to do when an ingredient runs out?

Interactive Practice (optional)

Go to the SQL Intro Git repository and copy the URL on the right hand side of the page to clone it locally:

$ git clone https://YOU_FIND_THIS_URL.git

Go into the directory you just cloned and run the sql_exercise.py. This is an interactive tutorial which requires you to read carefully -- there are links throughout to SQLZoo pages which explain more about the different statements.

$ cd sql_intro
$ python sql_exercise.py

Note The practice questions can be very, very specific about what they want. If you think you got it right but you're frustrated because it's not accepting your answer, there could be one tiny thing that's not quite the same as the reference answer. You can skip any question if this becomes too infuriating.