sqlite3 und parametrisierte Abfragen

import sqlite3


def find_by_author(con: sqlite3.Connection, author: str) -> list[str]:
    """Titles by exactly this author, in title order."""
    sql = f"SELECT title FROM book WHERE author = '{author}' ORDER BY title"
    return [row[0] for row in con.execute(sql)]


def search_titles(con: sqlite3.Connection, word: str) -> list[str]:
    """Titles that contain word anywhere, in title order."""
    sql = f"SELECT title FROM book WHERE title LIKE '%{word}%' ORDER BY title"
    return [row[0] for row in con.execute(sql)]

import sqlite3

from main import find_by_author, search_titles


def make_db():
    con = sqlite3.connect(":memory:")
    con.execute("CREATE TABLE book(title TEXT, author TEXT)")
    con.executemany(
        "INSERT INTO book VALUES (?, ?)",
        [
            ("Emma", "Jane Austen"),
            ("Persuasion", "Jane Austen"),
            ("Dune", "Frank Herbert"),
            ("At Swim-Two-Birds", "Flann O'Brien"),
        ],
    )
    return con


def test_author():
    """find_by_author findet beide Titel von Austen"""
    got = find_by_author(make_db(), "Jane Austen")
    assert got == ["Emma", "Persuasion"], f"find_by_author lieferte {got!r}"


def test_apostrophe():
    """Ein Name mit Apostroph funktioniert"""
    got = find_by_author(make_db(), "Flann O'Brien")
    assert got == ["At Swim-Two-Birds"], f"find_by_author lieferte {got!r}"


def test_author_injection():
    """Ein Injection-Versuch ist nur ein Name, der nichts findet"""
    got = find_by_author(make_db(), "x' OR '1'='1")
    assert got == [], f"find_by_author lieferte {got!r}: binden Sie den Wert mit ?"


def test_search():
    """search_titles findet Titel, die das Wort enthalten"""
    got = search_titles(make_db(), "u")
    assert got == ["Dune", "Persuasion"], f"search_titles lieferte {got!r}"


def test_search_injection():
    """Anführungszeichen im Suchwort können die Abfrage nicht ändern"""
    got = search_titles(make_db(), "' OR 1=1 OR title = '")
    assert got == [], f"search_titles lieferte {got!r}: die %-Zeichen gehören in den gebundenen Wert"

import sqlite3


def transfer(con: sqlite3.Connection, src: str, dst: str, amount: int) -> None:
    """Move amount from src to dst: both updates happen, or neither does."""
    # Nothing is committed, and nothing stops an overdraft or an unknown account.
    con.execute("UPDATE account SET balance = balance - ? WHERE name = ?", (amount, src))
    con.execute("UPDATE account SET balance = balance + ? WHERE name = ?", (amount, dst))

import sqlite3

from main import transfer


def make_db():
    con = sqlite3.connect(":memory:")
    con.execute("CREATE TABLE account(name TEXT PRIMARY KEY, balance INTEGER)")
    con.executemany("INSERT INTO account VALUES (?, ?)", [("ada", 100), ("bob", 50)])
    con.commit()
    return con


def balances(con):
    return dict(con.execute("SELECT name, balance FROM account"))


def raises_value_error(con, *args):
    try:
        transfer(con, *args)
    except ValueError:
        return True
    return False


def test_moves_money():
    """30 gehen von ada an bob"""
    con = make_db()
    transfer(con, "ada", "bob", 30)
    got = balances(con)
    assert got == {"ada": 70, "bob": 80}, f"die Kontostände sind {got!r}"


def test_committed():
    """Die Überweisung wird committet"""
    con = make_db()
    transfer(con, "ada", "bob", 30)
    assert not con.in_transaction, "eine Transaktion ist noch offen: mit with con: wird sie committet"


def test_overdraft():
    """Zu wenig Geld löst ValueError aus und ändert nichts"""
    con = make_db()
    raised = raises_value_error(con, "ada", "bob", 500)
    got = balances(con)
    assert raised and got == {"ada": 100, "bob": 50}, f"ValueError ausgelöst: {raised}, Kontostände {got!r}"


def test_unknown_account():
    """Ein unbekannter Empfänger löst ValueError aus, und ada behält ihr Geld"""
    con = make_db()
    raised = raises_value_error(con, "ada", "eve", 10)
    got = balances(con)
    assert raised and got == {"ada": 100, "bob": 50}, f"ValueError ausgelöst: {raised}, Kontostände {got!r}: die erste Änderung zurückrollen"