https://nl1.outband.net/fossil/query/file?name=query.py&ci=t...

In short you have your query in file sql/dept_personal.sql and you call it like

    for row in q.dept_personal(db_cursor, department='manpower'):

Your pattern of re-interpreting __doc__ is kinda weird though. Why not just add a `return` statement?

with engine: fn(args)

I would rather have

with engine: fn(args, engine)

This makes testing way easier.

So I made a very simple module that takes those sql files and turns them into SQLAlchemy text objects with variables in them.

Would it be possible to add something like this to the project or does it require many sql parsing libraries etc. to ensure sql validity to find variables in the sql file?

I feel the same, hence why I prefer a Django-like ORM to SQLAlchemy in spite of all the praises it gets. The author says "SQLAlchemy is the best. I don't like the API or codebase of the others", but actually what he describes feels like the Django ORM (or Tortoise, or many others).

Also, sometimes just a thin layer above SQL is fine. For small personal projects I use my own wrapper above sqlite like so:

    import oora
    from dataclasses import dataclass

    db = oora.DB(
        db_path=":memory:",  # or /path/to/your/db.sqlite3
        # migrations are just pairs of key=>val where key is an arbitrary (but unique) label and val is a SQL script or a callable.
        # If val is a callable, it must take a sqlite3.Cursor as first parameter.
        # migrations are executed in order
        migrations={
            # here's an initial migration:
            "0000": "CREATE TABLE IF NOT EXISTS user(id INTEGER PRIMARY KEY, name TEXT UNIQUE NOT NULL);",
            # simulating a schema evolution, let's add a field:
            "0001": "ALTER TABLE user ADD COLUMN email TEXT NULL;",
        },
    )
    db.migrate()

    db.insert("user", {"name": "John"})
    db.insert("user", {"name": "Jack"})
    db.insert("user", {"name": "Jill"})


    # dataclasses are perfect to represent rows
    # while still allowing custom behaviour
    @dataclass
    class User:
        id: int
        name: str
        email: str

        def __str__(self):
            return self.name


    # fetch a random instance
    user = db.hydrate(User, db.execute("select * from user ORDER BY RANDOM() limit 1").fetchone())
    print(f"User(id {user.id}), original name: {user}")

    # change name and email
    user.name = "Richard"
    user.email = "richard@acme.tld"
    db.save(user) # name of table is infered from the dataclass name
    print(f"User(id {user.id}), updated name: {user} <{user.email}>")

    # persist changes
    db.commit()import oora
    from dataclasses import dataclass

    db = oora.DB(
        db_path=":memory:",  # or /path/to/your/db.sqlite3
        # migrations are just pairs of key=>val where key is an arbitrary (but unique) label and val is a SQL script or a callable.
        # If val is a callable, it must take a sqlite3.Cursor as first parameter.
        # migrations are executed in order
        migrations={
            # here's an initial migration:
            "0000": "CREATE TABLE IF NOT EXISTS user(id INTEGER PRIMARY KEY, name TEXT UNIQUE NOT NULL);",
            # simulating a schema evolution, let's add a field:
            "0001": "ALTER TABLE user ADD COLUMN email TEXT NULL;",
        },
    )
    db.migrate()

    db.insert("user", {"name": "John"})
    db.insert("user", {"name": "Jack"})
    db.insert("user", {"name": "Jill"})


    # dataclasses are perfect to represent rows
    # while still allowing custom behaviour
    @dataclass
    class User:
        id: int
        name: str
        email: str

        def __str__(self):
            return self.name


    # fetch a random instance
    user = db.hydrate(User, db.execute("select * from user ORDER BY RANDOM() limit 1").fetchone())
    print(f"User(id {user.id}), original name: {user}")

    # change name and email
    user.name = "Richard"
    user.email = "richard@acme.tld"
    db.save(user) # name of table is infered from the dataclass name
    print(f"User(id {user.id}), updated name: {user} <{user.email}>")

    # persist changes
    db.commit()