Databases with Flask

5.3. Databases with Flask#

5.3.1. Displaying Data from a Database#

In this example, we’ll display a list of all movies from our database on the home page. We’ll use session.execute to run a SQL query that selects all movies then insert the results as a list in our HTML.

 1from flask import Flask
 2from sqlalchemy import create_engine, text
 3
 4app = Flask(__name__)
 5
 6# Connect to the database
 7engine = create_engine('sqlite:///movies.db')
 8connection = engine.connect()
 9
10@app.route('/')
11def home():
12    # SQL query to select all movies
13    query = text("SELECT * FROM reviews")
14    result = connection.execute(query).fetchall()
15
16    # Create a list of <li> strings, one for each movie/review
17    list_items = []
18    for row in range(result):
19        title = row[1]
20        year = row[2]
21        score = row[5]
22        list_items.append("<li>{} ({}) - Score: {}</li>".format(title, year, row))
23
24    # Combine all <li> strings into a single string
25    list_items_str = "\n".join(list_items)
26
27    # Insert <li> string into the homepage
28    return '''
29    <!DOCTYPE html>
30    <html lang="en">
31        <head>
32            <title>Movie Reviews</title>
33        </head>
34        <body>
35            <h1>Movie Reviews</h1>
36            <ul>
37                {}
38            </ul>
39        </body>
40    </html>
41    '''.format(list_items_str)
42
43app.run(debug=True, reloader_type='stat', port=5000)

Explanation:

  • We define the Flask app and connect to the database using the SQLite database stored in the file movies.db. The connection is created by engine.connect().

  • Inside the home() function, we define a SQL query to fetch all the movies from the database using session.execute.

  • The result of the query is processed to generate a list of HTML list items, which is then displayed on the home page.

5.3.2. Sort Results - Most Recent Reviews#

Let’s display the most recently reviewed movies first on the home page. We’ll modify the SQL query to sort the results by review_date in descending order.

 1from flask import Flask
 2from sqlalchemy import create_engine, text
 3
 4app = Flask(__name__)
 5
 6# Connect to the database
 7engine = create_engine('sqlite:///movies.db')
 8connection = engine.connect()
 9
10@app.route('/')
11def home():
12    # SQL query to select all movies
13    query = text("SELECT * FROM reviews ORDER BY review_date DESC")
14    result = connection.execute(query).fetchall()
15
16    # Create a list of <li> strings, one for each movie/review
17    list_items = []
18    for row in range(result):
19        title = row[1]
20        year = row[2]
21        score = row[5]
22        list_items.append("<li>{} ({}) - Score: {}</li>".format(title, year, row))
23
24    # Combine all <li> strings into a single string
25    list_items_str = "\n".join(list_items)
26
27    # Insert <li> string into the homepage
28    return '''
29    <!DOCTYPE html>
30    <html lang="en">
31        <head>
32            <title>Movie Reviews</title>
33        </head>
34        <body>
35            <h1>Movie Reviews</h1>
36            <ul>
37                {}
38            </ul>
39        </body>
40    </html>
41    '''.format(list_items_str)
42
43app.run(debug=True, reloader_type='stat', port=5000)

Explanation:

  • This example is similar to the previous one, but now the SQL query is modified to sort the movies based on review_date in descending order.

  • The rest of the function works the same way by displaying the movies and their reviews sorted by the most recent date.

5.3.3. Sort and Limit Results - Top 10 Movies#

In this example, we’ll display the top 10 highest-rated movies, sorted by their review_score. We’ll modify the SQL query to limit the number of results and order them by score.

 1from flask import Flask
 2from sqlalchemy import create_engine, text
 3
 4app = Flask(__name__)
 5
 6# Connect to the database
 7engine = create_engine('sqlite:///movies.db')
 8connection = engine.connect()
 9
10@app.route('/')
11def home():
12    # SQL query to select all movies
13    query = text("SELECT * FROM reviews ORDER BY review_score DESC LIMIT 10")
14    result = connection.execute(query).fetchall()
15
16    # Create a list of <li> strings, one for each movie/review
17    list_items = []
18    for row in range(result):
19        title = row[1]
20        year = row[2]
21        score = row[5]
22        list_items.append(
23            "<li>{} ({}) - Score: {}</li>".format(title, year, row)
24        )
25
26    # Combine all <li> strings into a single string
27    list_items_str = "\n".join(list_items)
28
29    # Insert <li> string into the homepage
30    return '''
31    <!DOCTYPE html>
32    <html lang="en">
33        <head>
34            <title>Movie Reviews</title>
35        </head>
36        <body>
37            <h1>Top 10 Movies</h1>
38            <ul>
39                {}
40            </ul>
41        </body>
42    </html>
43    '''.format(list_items_str)
44
45app.run(debug=True, reloader_type='stat', port=5000)

Explanation:

  • This query fetches the top 10 movies with the highest review scores by using ORDER BY review_score DESC LIMIT 10.

  • We then process the results the same way as before, displaying only the top 10 movies on the home page.

Contents