Lets assume we have an SQLite Pizza database called pizza.db with the following tables: Pizza, Topping and an association (joining) table called PizzaTopping - because one pizza can have many toppings and one topping can be used on many pizzas - which makes it a many-to-many relationship between Pizza and Topping - see the SQL section for more details on that (also: see Mr Dunford and ask him why he hasn't created the SQL section yet).

Our Pizza table has id, name and description columns.  Using Python to query a single row from this table will return a tuple (which in Python is much like a list) - and so we can access the individual parts (name and description) using indexing - just like we would with a list.

Any single database query result for the Pizza table will be in the form (id, name, description) - so result[0] is the id (an INT), result[1] is the name (a STRING) and result[2] is the description (also a STRING - because TEXT is the same thing as a STRING (most of the time)). 

We'll modify our dynamic route made in the previous lesson to print the details of whichever pizza is requested in the URL.  The changes from the previous code are in bold:

from flask import Flask

import sqlite3  # needed to make database connections and queries

app = Flask(__name__)

@app.route("/")

def hello():

   return "Hello World!"

@app.route("/help")

def help():

   return "help!"

@app.route("/pizza/<string:pizza_name>")

def pizza(pizza_name):

   connection = sqlite3.connect('pizza.db')

   cursor = connection.cursor()

   cursor.execute("SELECT * FROM Pizza where name = ?", (pizza_name,))

   pizza = cursor.fetchone()

   connection.close()

   return "<h1>{}</h1><p>{}</p>".format(pizza[1], pizza[2])

if __name__ == "__main__":

app.run(debug=True)

So now we can look up pizzas based on their name (although, see the dragons below).

Most of the new code is boilerplate that could (and should) be moved to its own function since we'll eventually most likely need to connect, query and disconnect for multiple different routes in this application - but that's a job for another iteration. Lets have a look at it though:

   connection = sqlite3.connect('pizza.db')

   cursor = connection.cursor()

   cursor.execute("SELECT * FROM Pizza where name = ?", (pizza_name,))

   pizza = cursor.fetchone()

The code connects to the database (stored as the object called connection), creates a cursor to do the work (cursor), executes a query (the SELECT), and then finally fetches one result (only the first, if there are multiple matches) of that query, returned as a tuple containing all columns from that table, and storing it as pizza.  It seems a little long-winded but that's the process.  

We used a fetchone() to get the results because we only care about one pizza here - if we were listing all the pizzas then we would use a generic query (select * from Pizza;) and a fetchall().  There is also a .fetchmany(n) if you wanted a specific number (n) of results.

The pizza = cursor.fetchone() line returns a tuple (which is somewhat like a list).  Assuming the URL from the browser was http://localhost/pizza/Meatlovers it might return something like: (3, "Meatlovers", "Ok I guess") - because we said earlier that the Pizza table only has an id, name and description.  So we can ignore pizza[0], the id, because in this instance we don't need it.  But we'll use the other two: pizza[1] has the name of the pizza and pizza[2] the description.

You probably also noticed we added some very simple HTML to format the output - the return string for a Flask route function can be a full HTML website if you want - but there is a better way, and we'll look at that a bit later on.

For now the output should be something like this:

Python's built in string functions are simply NOT the right way to assemble a query string for an SQL database - but there are times, with more complex queries, when the right way doesn't work.  The important thing here is that there are single ticks around the {} to ensure that if someone tries an SQL injection attack it will hopefully (?!) just be treated as a string, not potentially dangerous queries.  There is some information on this kind of attack, and mitigation, here.