So I am working on my first project using SQLite and SQLModel as ORM. Creating and handling the first table in my database was easily structured: A function for each CRUD-Operation.

But now I have added a second table to my DB and am starting to think about structure a bit. Essentially I want to do the same thing for all of my tables: Add an entry, edit it, get one or all entries and delete the entry. So do I add those functions for the CRUD operations again for each entry? Or do I create "general-purpose" functions for those operations? I could do this something like this:

def create_entry(table: SQLModel, _id: int, **kwargs):
    with Session(engine) as session:

I don't like the use of **kwargs though. So maybe I would create extra dataclasses something like this:

from dataclasses import dataclass

class Update():
    id: int

class HeroUpdate(Update):
    name: Optional[str]
    age: Optional[str]

class Teamupdate(Update):
    teamname: Optional[str]

def update_entry(table: SQLModel, values: Update):
    with Session(engine) as session:

Or should I maybe create a class which handles the transactions (while still using the dataclasses to model the field?)?

class BaseTransactions:
    def __init__(table: SQLModel):
        self.table = table
    def create(values: Create)
        with Session(engine) as session:

So to sum up my question: Is there a standard, pythonic way to structure code for database transactions when using an ORM?

1 Answer 1


Is there a ... pythonic way to structure code for database transactions when using an ORM?

Yes, definitely. I have written lots of code that sends queries and updates via sqlalchemy. It doesn't look much like OP's example code.

Your create_entry() verb makes perfect sense. I tend to write that as a method, so I can use self.engine, or a self.get_session() helper, but the OP code is fine as-is.

class Create():
    id: int

What?!? The cure is definitely worse than the symptom, if there even were any symptoms.

Prefer to use a noun when naming the class. And besides, what do I care about the ID at this point? Typically I let the DB worry about rolling a fresh one.

Each of those FooUpdate classes could maybe make sense, taking "an update" as a noun rather than the verb you seem to intend. But I don't find much value add there, over using traditional **kwargs. I guess you want type safety so mypy will reveal bugs prior to a runtime stack trace? Ok, to each his own, maybe that approach saves you development time. I figure my automated tests are going to uncover type mismatches when they run, so I'm not worried about that detail.

should I maybe create a class which handles the transactions (while still using the dataclasses to model the field)?

Yes. I agree with that. If your signature becomes def create_hero(self, name: str, age: int) then your proposal looks pretty sane to me.

Certainly there are situations where a method should accept a Hero object. But I would typically expect to obtain such an object from the DB, which has assigned it a unique ID.

Workaday code will often move the bits back and forth between DB and some (json) API endpoint. So converting a query result row to dict, and vice versa, will be pretty common. A deep inheritance hierarchy for persisted objects would tend to distract from the simplicity of {row, dict} representations. Maybe inheritance and type safety can be a big win. In my experience they matter more for what's going up and down the call stack than they do when serializing out to the DB or external API.

When writing a class (or module) that contains business logic, be sure to give the class a docstring explaining its responsibility. Then you won't be tempted to cram other logic into the class.

  • Thanks for your reply! So to summarize, you would use the **kwargs solution, right? Do you have a piece of code which would demonstrate a "best practice"? And at what point would you add classes to the mix?
    – Jan
    Commented Nov 24, 2023 at 19:01
  • To explain my dataclass approach: First of all, I made some typos, I'll fix them in a second. I would only use the id-int in the parent class for Updates, not for creation. My idea is, that I could write somewhere else in the code: create_entry(table=HeroTable, values=HeroUpdate(name="", ...)). This would make it easier to get all the entries right in the function call (bc of autocompletion/intellisense etc.). Maybe I am leaning to much on those kinds of features though?
    – Jan
    Commented Nov 24, 2023 at 19:02
  • 1
    Yes, that's right. And no, I don't, as my employer owns the code. When to add classes? When I start to see "same old parameters" being passed around repeatedly, so I turn them into object attributes, accessed via self.foo. Often the first such foo will be a DB engine. (Other folks prefer to store the engine in a module-level global, which I'm fine with, until we have connections open to different database vendors on different servers.) I don't understand the IDE autocomplete motivation. JetBrains PyCharm gives me all the autocomplete I want, even for dict keys. CI/CD lint + unittest works!
    – J_H
    Commented Nov 24, 2023 at 19:53
  • 1
    If I could critique the OP question for a moment, I'd say the thing I chiefly find wanting is that it "lacks review context". That is, I'm looking for code which actually accomplishes something of interest to the business, something folks care about, maybe adding an additional widget request in an existing pending order. And we have implemented an automated unit or integration test which exercises that feature. With that in hand, we're in a better position to critique the pluses / minuses of this or that approach. Feel free to tag me with such a submission over on stackexchange Code Review.
    – J_H
    Commented Nov 24, 2023 at 19:58
  • Critique is always welcome :) I didn't know about stackexchange code review, thank you! I'll take some time next week to think about my design a bit and maybe I'll post the actual example over there.
    – Jan
    Commented Nov 24, 2023 at 20:21

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge you have read our privacy policy.

Not the answer you're looking for? Browse other questions tagged or ask your own question.