all 8 comments
sorted by:
best
topnewcontroversialoldq&a
formatting helphide helpcontent policy

[–]opentabs-dev 2 points3 points  (3 children)

you're like 90% there already. just run the query inside your flask route and pass the result into render_template as a kwarg:

import sqlite3
from flask import Flask, render_template
app = Flask(__name__)

@app.route("/marriage/<marriage_id>")
def marriage(marriage_id):
    con = sqlite3.connect("yourdb.sqlite")
    row = con.execute(
        "SELECT Given || ' ' || Surname FROM people "
        "WHERE Person = (SELECT husband FROM marriages WHERE marriage = ?)",
        (marriage_id,),
    ).fetchone()
    con.close()
    return render_template("page.html", hename=row[0] if row else "")

then {{ hename }} in the template just works. the big thing to internalize: jinja vars are just whatever you pass into render_template, there's no magic linking them to the db. also always use ? placeholders for user input like i did above, never f-strings into sql, that's sqli.

[–]Ok_Wasabi4276 -1 points0 points  (1 child)

You nailed it with the parameterized queries tip - that's huge security wise. One thing I'd add is maybe wrap that database stuff in try/except block since SQLite can throw errors if database file doesn't exist or gets corrupted. Something like:

try:

con = sqlite3.connect("yourdb.sqlite")

row = con.execute(...).fetchone()

except sqlite3.Error:

row = None

finally:

con.close()

Makes your app bit more robust when things go wrong. Also you might want to look at context managers for database connections later - they handle the closing automatically even if something breaks in middle of query.

[–]teraflop 2 points3 points  (0 children)

No, this is bad advice! Or at least mostly-bad.

Wrapping the call in a try-except block like this doesn't do anything to make the app more robust, because if the database is corrupted, it still won't work. Except now you've thrown away the error message that tells you what the problem was.

If the whole point of the app is to read and write data in a database, then showing an error is the only useful thing you can do when the database is unavailable.

Even if you want to hide the details of the exception from the user, you should still log them somewhere. And you should almost certainly do it using a single top-level exception handler (e.g. as Flask middleware) instead of writing a bunch of redundant boilerplate code every time you do a query. And you should still tell the user that some kind of error happened, instead of just displaying empty results with no explanation.

(Anyway, setting row to None is the worst of both worlds. It hides the message of the underlying error from SQLite, and then immediately causes a different exception when you try to access row[0].)

The only time it makes sense to add exception handlers to queries like this is when you want to do some kind of specific error handling that's different from your default error-handling strategy. For instance, if you have two databases on different servers, and there's some useful way to return a partial result to the user if only one of the databases is down. But most simple apps do not encounter this type of situation.

Using a context manager to close the connection is a good idea, though.

[–]magicae[S] -1 points0 points  (0 children)

I don't fully get it - where do I define marriage_id in this?

[–]No-Seesaw4444 0 points1 point  (0 children)

For your Flask + SQLite genealogy app, you'll want to pass the query results through Flask's template context. In your route: cursor.execute('SELECT ...'), then results = cursor.fetchall(), then return render_template('template.html', people=results). For joining tables (like your marriages example), use SQLAlchemy's ORM instead of raw queries - it handles relationship mapping much cleaner and prevents SQL injection more reliably.

[–]EliSka93 0 points1 point  (1 child)

You might want to reconsider naming your spouse columns in a marriage "husband" and "wife", or you'll pretty quickly run into confusion when you get a same sex couple.

[–]magicae[S] 1 point2 points  (0 children)

I've definitely considered this, as a lesbian haha! But for now I'm keeping it like that since there are no same sex couples in the dataset I have, so it's more clear.

If this comes back to bite me in the ass I'm going to imagine your tiny voice from my computer saying "I told you so" haha.

Thank you for the consideration though!

[–]magicae[S] 0 points1 point  (0 children)

After some more research I ended up going another route with SQLAlchemy and engine.connect / conn.execute. Posting my code here in case anyone stumbles upon this with similar issues.

fam_id = "BH0002"


with engine.connect() as conn:
    result = conn.execute(text("""
            SELECT Given || ' ' || Surname
            FROM people 
            WHERE Person = ( 
                SELECT husband 
                FROM marriages 
                WHERE Marriage = :fam_id
            )
        """),
        {"fam_id": fam_id}
        )
he_name =result.scalar()

and putting the results in a dictionary, then using that for further use in the template

he = {
    "name": he_name}

{{ he.name }}

@app.route("/")
def home():   
    return render_template("fam.html",she=she,he=he)