Chapter 15, Intermediate

From pure SQLite to ORM

Before SQLAlchemy, it's worth understanding what it automates. The `sqlite3` comes with Python, writes to a file, and makes clear what a database is, and also shows the biggest danger of writing SQL by hand.

A database in a file

SQLite stores everything in a file. The data survives server restarts, unlike an in-memory list. The module sqlite3 is part of Python, without installing anything:

intermediario/cap15_sqlite.pylinhas 10 a 27

import sqlite3

conn = sqlite3.connect("loja.db")
conn.row_factory = sqlite3.Row
conn.execute(
    """CREATE TABLE IF NOT EXISTS tarefas (
        id INTEGER PRIMARY KEY,
        titulo TEXT NOT NULL,
        concluida INTEGER NOT NULL DEFAULT 0
    )"""
)
conn.execute("DELETE FROM tarefas")
conn.execute("INSERT INTO tarefas (titulo) VALUES (?)", ("Estudar SQL",))
conn.execute("INSERT INTO tarefas (titulo, concluida) VALUES (?, ?)", ("Escrever testes", 1))
conn.commit()

for linha in conn.execute("SELECT id, titulo, concluida FROM tarefas ORDER BY id"):
    print(dict(linha))

Output

{'id': 1, 'titulo': 'Estudar SQL', 'concluida': 0}
{'id': 2, 'titulo': 'Escrever testes', 'concluida': 1}

The commit() commits the changes permanently. Without it, the INSERT remains pending and disappears if the connection closes. You can inspect the file loja.db with a visual tool, like the "SQLite Viewer" extension for VS Code.

The danger: SQL built with text

The easiest way to write a query is to build a string with the value inside. It is also the most dangerous way, because the value can change the query. This is the SQL injection, and you can see it happen:

intermediario/cap15_sqlite.pylinhas 32 a 43

conn2 = sqlite3.connect(":memory:")
conn2.execute("CREATE TABLE usuarios (nome TEXT, senha TEXT)")
conn2.execute("INSERT INTO usuarios VALUES ('ana', 's3gredo')")
conn2.execute("INSERT INTO usuarios VALUES ('bia', 'outra-senha')")

entrada_maliciosa = "x' OR '1'='1"

inseguro = f"SELECT nome FROM usuarios WHERE nome = '{entrada_maliciosa}'"
print("montando texto:", conn2.execute(inseguro).fetchall())

seguro = "SELECT nome FROM usuarios WHERE nome = ?"
print("com parâmetro: ", conn2.execute(seguro, (entrada_maliciosa,)).fetchall())

Output

montando texto: [('ana',), ('bia',)]
com parâmetro:  []

In the first query, the input closed the quote and added OR '1'='1', which is always true: the query returned all the users. In the second, the ? sends the value separate from the SQL, and it is always treated as data, never as code. Rule without exception: never build SQL with f-string or + external data. Always use parameters. SQLAlchemy does this for you.

Using `sqlite3` in FastAPI

Each request should have its own connection, opened and closed with a dependency on yield. This avoids check_same_thread=False and sharing a single connection between threads, which is a common and dangerous shortcut:

intermediario/cap15_sqlite.pylinhas 48 a 83

from typing import Annotated

from fastapi import Depends, FastAPI
from fastapi.testclient import TestClient

app = FastAPI()


def obter_conexao():
    conexao = sqlite3.connect("loja.db")
    conexao.row_factory = sqlite3.Row
    try:
        yield conexao
    finally:
        conexao.close()


Conexao = Annotated[sqlite3.Connection, Depends(obter_conexao)]


@app.get("/tarefas")
def listar(conexao: Conexao):
    linhas = conexao.execute("SELECT id, titulo, concluida FROM tarefas ORDER BY id").fetchall()
    return [dict(linha) for linha in linhas]


@app.post("/tarefas", status_code=201)
def criar(titulo: str, conexao: Conexao):
    cursor = conexao.execute("INSERT INTO tarefas (titulo) VALUES (?)", (titulo,))
    conexao.commit()
    return {"id": cursor.lastrowid, "titulo": titulo}


cliente = TestClient(app)
print(cliente.post("/tarefas", params={"titulo": "Revisar o capítulo"}).json())
print(len(cliente.get("/tarefas").json()))

Output

{'id': 3, 'titulo': 'Revisar o capítulo'}
3

Pure SQL or ORM

	Pure SQLite (`sqlite3`)	ORM (SQLAlchemy)
How to write	SQL Strings	Python classes and objects
Protection against injection	Only if you use `?` always	Embedded
Switching databases	Rewriting SQL	Change the connection URL
Initial effort	None, comes with Python	Install and learn
Does it grow well?	It turns into a mess of strings	Yes, with models and relationships

For a script or a tiny application, the sqlite3 is enough. For an API that will grow, I use the ORM, and that is the subject of the next chapters.

Exercise 1

Search securely

Write buscar_por_titulo(conexao, termo), which returns the titles that contain the term (with LIKE), using a parameter. Check that malicious input "' OR '1'='1" returns nothing.

A database in a file

The danger: SQL built with text

Using `sqlite3` in FastAPI

Pure SQL or ORM

View solution