Pre/post processing (classical mapping)

Question

How to perform pre and post-processing to ensure the domain model is uploaded correctly using SQLAlchemy?

The domain model:

@dataclass
class Posting:
    id: uuid.UUID
    date: datetime.date
    description: str
    debit: float
    credit: float

class Account:
    def __init__(self, id, name, parent, postings):
        self.id: uuid.UUID = id
        self.name: str = name
        self.parent: "Account" = parent
        self.postings: list[Posting] = postings

    def add_posting(self, posting: Posting):
        self.postings.append(posting)

The mapping (based on SQLAlchemy documentation):

mapper_registry = registry()

account = Table(
    'account',
    mapper_registry.metadata,
    Column('id', String(255), primary_key=True),
    Column('name', String(255)),
    Column('parent_id', String(255), ForeignKey('account.id')))

posting = Table(
    'posting',
    mapper_registry.metadata,
    Column('id', String(255), primary_key=True),
    Column('date', Date),
    Column('description', String(1023)),
    Column('account_id', String(255), ForeignKey('account.id')),
    Column('debit', Float),
    Column('credit', Float))

mapper_registry.map_imperatively(Account, account, properties={
    'parent_account': relationship(Account, primaryjoin=account.c.id == account.c.parent_id)})

mapper_registry.map_imperatively(Posting, posting, properties={
    'posting_account': relationship(Account, primaryjoin=posting.c.id == account.c.id)})

engine = create_engine("sqlite:///path_to_db.db")
mapper_registry.metadata.create_all(engine)

The calls I'd like to make to insert data into the database:

with Session(engine) as session:
    parent_account = Account(uuid.uuid4(), "Assets", None, None)
    child_account = Account(uuid.uuid4(), "Current Assets", parent_account, None)
    child_account.add_posting(Posting(uuid.uuid4(), datetime.date(2023, 9, 24), "Add current asset", 60, 0))
    child_account.add_posting(Posting(uuid.uuid4(), datetime.date(2023, 9, 25), "Subtract current asset", 0, 20))

    session.add_all([child_account, parent_account])
    session.add_all(child_account.postings)
    session.commit()

There are two issues:

With account referencing itself, it can't get the id of the parent class. I thought you could use some pre-processing to get the id of the parent class and then insert that into the database. Post-processing would do the reverse of that.
There would also have to be something done so that when an account is queried, all the postings would be loaded as well.

How to go about this?

Pre/post processing (classical mapping)

0 Answers