Skip to content
Tutorials Center
My learning

Topics

Lesson 26 of 72 ยท Databases

Using sqlite3 with Flask

Use sqlite3 in a Flask app: one connection per request with g, closing it in teardown_appcontext, dict-like rows and SQL injection prevention.

Most real apps need to remember things after a restart, which means a database. Python ships with SQLite in the standard library (sqlite3), so you can add a real SQL database to a Flask app with no extra install. It is a fine choice for small sites, prototypes and tests, and everything you learn here (connections, parameters, rows) carries over to bigger databases.

The interesting part is not the SQL, it is where the connection lives. A web app handles many requests, and each one should open a connection, use it, and close it, without leaking connections or sharing one between users by accident. Flask's g object and the teardown_appcontext hook solve exactly that.

One connection per request with g

flask.g is a scratch space that lives for the duration of one application context, which in practice means one request. The standard pattern has two functions:

  • get_db() creates the connection the first time it is needed in a request, stores it on g, and returns the same one on later calls.
  • A function registered with @app.teardown_appcontext closes it when the request ends, even if the view raised an error.
sequenceDiagram
    participant R as Request
    participant V as View
    participant G as g
    participant T as Teardown
    R->>V: call view
    V->>G: get_db() (first call)
    G-->>V: opens connection, stores g.db
    V->>G: get_db() (second call)
    G-->>V: returns the same g.db
    V-->>R: response
    R->>T: app context ends
    T->>G: g.pop("db"), close()

Connections are created lazily and always closed at the end of the request.

The lazy creation matters: a view that never touches the database never opens a connection. This example counts openings and closings so you can see the lifecycle:

Python get_db() and teardown_appcontext

Press Run (or Ctrl + Enter) to see the result here.

Program input ยท what you'd type while the program runs

Type the answers before you press Run, one per line. Each time the program reads a line (with input()), it gets the next line from this box.

Two requests used the database, so two connections were opened and both were closed. The /none view cost nothing. The teardown function receives the exception that ended the request (or None), which you can ignore here.

g is not shared between requests or users. Do not use it as a cache for data that should outlive a request; use a module-level cache or the database for that. See Hooks, Application Context and g.

In-memory databases and why this demo shares one

A file database such as sqlite3.connect("app.db") keeps its data on disk, so a new connection per request sees the same tables. An in-memory database (":memory:") lives only as long as its connection: every connect(":memory:") gives a brand-new, empty database.

That is perfect for tests, but it means a per-request connection would see nothing. The browser sandbox has no real files either, so the next example keeps one shared in-memory connection and lets get_db() hand it out. Teardown skips closing it. With a real file you would use the else branch, which opens and closes a connection per request.

Rows, queries and a small API

By default fetchone() returns plain tuples, which are hard to read (row[2]). Setting connection.row_factory = sqlite3.Row makes rows behave like read-only dictionaries: access by column name (row["title"]) or convert with dict(row) for JSON.

Python A books API on sqlite3

Press Run (or Ctrl + Enter) to see the result here.

Program input ยท what you'd type while the program runs

Type the answers before you press Run, one per line. Each time the program reads a line (with input()), it gets the next line from this box.

A few things to notice:

  • init_db() runs inside app.app_context() because g only exists inside one. In a real project you would register it as a CLI command (flask init-db) or call it from the app factory.
  • Writes need db.commit(). Without it the insert is rolled back when the connection closes. Reads do not need it.
  • cursor.lastrowid gives the id SQLite assigned to the new row.
  • Returning a dict from a view makes Flask send JSON (see Responses).
  • abort(404) hands the error to Flask's error handling, so a missing book gives a proper 404.

SQL injection and parameters

SQL injection happens when user input is pasted into a SQL string, so the input can change the meaning of the query. The cure is never to build SQL with f-strings, % or +. Put a ? placeholder where each value goes and pass the values as a separate tuple; the driver sends them as data, never as SQL.

Python Unsafe versus parameterized queries

Press Run (or Ctrl + Enter) to see the result here.

Program input ยท what you'd type while the program runs

Type the answers before you press Run, one per line. Each time the program reads a line (with input()), it gets the next line from this box.

The attacker's second input turns the WHERE clause into name = 'nobody' OR '1'='1', which is true for every row, so the unsafe query leaks everyone. The parameterized version simply looks for a user literally named nobody' OR '1'='1 and finds none.

Rules to remember:

  • Use ? placeholders for every value that did not come from your own code, including numbers and ids.
  • Placeholders cannot stand in for table or column names. If a sort column comes from the user, check it against an allow-list ({"title", "year"}) before putting it in the query.
  • Treat executescript() as trusted-input only: it runs several statements and takes no parameters.
  • The same idea applies to every database library, including the ORM you meet in the next lesson. See also Web security essentials.

When sqlite3 is enough

sqlite3 with g is a good fit when the schema is small and you like writing SQL. It has no models, no automatic relationship loading and no schema migrations, so larger apps usually move to an ORM. SQLite also allows only one writer at a time, which is fine for modest traffic but not for a busy multi-server site. The next lesson introduces Flask-SQLAlchemy for those cases.

Check your understanding

Quick check ยท 5 questions

of 5 answered You scored / 5 ยท perfect!

  1. 1. What is the job of @app.teardown_appcontext in the get_db()/close_db() pattern?

    Teardown functions run when the application context is popped, whether the view succeeded or raised, so connections are not leaked.
  2. 2. What does connection.row_factory = sqlite3.Row change?

    sqlite3.Row supports row["title"] as well as index access, which makes JSON conversion easy.
  3. 3. Which line is safe from SQL injection?

    Only the placeholder form sends the value separately from the SQL text.
  4. 4. An INSERT works in the view, but the row is gone on the next request. What did the view probably forget?

    Writes happen inside a transaction. Without commit() the changes are discarded when the connection closes.
  5. 5. Why does a per-request sqlite3.connect(":memory:") seem to lose your tables?

    An in-memory database belongs to the one connection that created it. File databases are shared by all connections.

Summary

  • Store one connection per request on g, create it lazily in get_db(), and close it in a @app.teardown_appcontext function.
  • sqlite3.Row as row_factory gives name-based access and easy dict(row) conversion.
  • Call commit() after writes; lastrowid returns the new id.
  • Always pass values with ? placeholders. Placeholders cannot replace table or column names, so validate those against an allow-list.
  • An in-memory SQLite database exists only while its connection stays open, so use a file (in app.instance_path) for real apps.
  • Outgrowing raw SQL points to an ORM.

Next: Flask-SQLAlchemy Models, mapping tables to Python classes.