Databases with Flask

6.3. Databases with Flask#

6.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
11@app.route("/")
12def home():
13    # SQL query to select all movies
14    query = text("SELECT * FROM reviews")
15    result = connection.execute(query).fetchall()
16
17    # Create a list of <li> strings, one for each movie/review
18    list_items = []
19    for row in range(result):
20        title = row[1]
21        year = row[2]
22        score = row[5]
23        list_items.append(
24            "<li>{} ({}) - Score: {}</li>".format(
25                title,
26                year,
27                row,
28            )
29        )
30
31    # Combine all <li> strings into a single string
32    list_items_str = "\n".join(list_items)
33
34    # Insert <li> string into the homepage
35    return """
36    <!DOCTYPE html>
37    <html lang="en">
38        <head>
39            <title>Movie Reviews</title>
40        </head>
41        <body>
42            <h1>Movie Reviews</h1>
43            <ul>
44                {}
45            </ul>
46        </body>
47    </html>
48    """.format(
49        list_items_str
50    )
51
52
53app.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.

6.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
11@app.route("/")
12def home():
13    # SQL query to select all movies
14    query = text("SELECT * FROM reviews ORDER BY review_date DESC")
15    result = connection.execute(query).fetchall()
16
17    # Create a list of <li> strings, one for each movie/review
18    list_items = []
19    for row in range(result):
20        title = row[1]
21        year = row[2]
22        score = row[5]
23        list_items.append(
24            "<li>{} ({}) - Score: {}</li>".format(
25                title,
26                year,
27                row,
28            )
29        )
30
31    # Combine all <li> strings into a single string
32    list_items_str = "\n".join(list_items)
33
34    # Insert <li> string into the homepage
35    return """
36    <!DOCTYPE html>
37    <html lang="en">
38        <head>
39            <title>Movie Reviews</title>
40        </head>
41        <body>
42            <h1>Movie Reviews</h1>
43            <ul>
44                {}
45            </ul>
46        </body>
47    </html>
48    """.format(
49        list_items_str
50    )
51
52
53app.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.

6.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
11@app.route("/")
12def home():
13    # SQL query to select all movies
14    query = text("SELECT * FROM reviews ORDER BY review_score DESC LIMIT 10")
15    result = connection.execute(query).fetchall()
16
17    # Create a list of <li> strings, one for each movie/review
18    list_items = []
19    for row in range(result):
20        title = row[1]
21        year = row[2]
22        score = row[5]
23        list_items.append(
24            "<li>{} ({}) - Score: {}</li>".format(
25                title,
26                year,
27                row,
28            )
29        )
30
31    # Combine all <li> strings into a single string
32    list_items_str = "\n".join(list_items)
33
34    # Insert <li> string into the homepage
35    return """
36    <!DOCTYPE html>
37    <html lang="en">
38        <head>
39            <title>Movie Reviews</title>
40        </head>
41        <body>
42            <h1>Top 10 Movies</h1>
43            <ul>
44                {}
45            </ul>
46        </body>
47    </html>
48    """.format(
49        list_items_str
50    )
51
52
53app.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.