Search code examples
pythonsqlsqlitesqlalchemyrdbms

Handle multiple relations between two rows/objects in SQLAlchemy

I found this problem in my application using Python3 with a SQLAlchemy connected sqlite-database. I designed the data structure from the view of an object-orientated developer. I think this is one of my problems here. ;)

Simple description: Entity a (from table/class A) can have multible references to entity b (from table/class B).

Sample:

CREATE TABLE "B" (
        oid INTEGER NOT NULL,
        val INTEGER,
        PRIMARY KEY (oid)
);
CREATE TABLE "A" (
        oid INTEGER NOT NULL,
        PRIMARY KEY (oid)
);
CREATE TABLE a_b_relation (
        a_oid INTEGER,
        b_oid INTEGER,
        FOREIGN KEY(a_oid) REFERENCES "A" (oid),
        FOREIGN KEY(b_oid) REFERENCES "B" (oid)

sqlite> SELECT oid FROM A;
1
sqlite> SELECT oid, val FROM B;
1|0
2|1
sqlite> SELECT a_oid, b_oid FROM a_b_relation;
1|1
1|1

You see here that a has two references to the same entity/row b. In my data structure this make sense. But maybe it break a SQL-/RDBMS-rule?

When I do this in Python3 with the object it causes an error because SQLAlchemy try to execute an DELETE statement for that.

a._bbb.clear()

The error

DELETE FROM a_b_relation WHERE a_b_relation.a_oid = ? AND a_b_relation.b_oid = ?
(1, 1)
ROLLBACK
sqlalchemy.orm.exc.StaleDataError: DELETE statement on table 'a_b_relation' expected to delete 1 row(s); Only 2 were matched.

The error make sense for me in that case. But I don't know how I could handle that.

From my view it looks like a "bug" in SQLAlchemy because the constructed DELETE statement doesn't take care of my use case. But of course I know that especially the SQLA-developers think about what they do and there is a good design reason for that behaviour.

Here is code that create and fill a sample-database to present this problem:

#!/usr/bin/env python3

import os.path
import os
import sqlalchemy as sa 
import sqlalchemy.orm as sao
import sqlalchemy.ext.declarative as sad
from sqlalchemy_utils import create_database

_Base = sad.declarative_base()
session = None

a_b_relation= sa.Table('a_b_relation', _Base.metadata,
    sa.Column('a_oid', sa.Integer, sa.ForeignKey('A.oid')),
    sa.Column('b_oid', sa.Integer, sa.ForeignKey('B.oid'))
)


class A(_Base):
    __tablename__ = 'A'

    _oid = sa.Column('oid', sa.Integer, primary_key=True)
    _bbb = sao.relationship('B', secondary=a_b_relation)

    def __str__(self):
        s = '{}.{} oid={}'.format(type(self), id(self), self._oid)
        for b in self._bbb:
            s += '\n\t{}'.format(b)
        return s


class B(_Base):
    __tablename__ = 'B'

    _oid = sa.Column('oid', sa.Integer, primary_key=True)
    _val = sa.Column('val', sa.Integer)

    def __str__(self):
        return '{}.{} oid={} val={}'.format(type(self), id(self), self._oid, self._val)


dbfile = 'set.db'

def _create_database():
    if os.path.exists(dbfile):
        os.remove(dbfile)

    engine = sa.create_engine('sqlite:///{}'.format(dbfile), echo=True)
    create_database(engine.url)
    _Base.metadata.create_all(engine)
    return sao.sessionmaker(bind=engine)()

def _fill_database():
    a = A()
    session.add(a)

    for v in range(2):
        b = B()
        b._val = v
        session.add(b)
        if v == 0:
            # THIS CAUSE THE PROBLEM I THINK
            a._bbb += [b]
            a._bbb += [b]

    session.commit()


if __name__ == '__main__':
    session = _create_database()
    _fill_database()

    a = session.query(A).first()
    a._bbb.clear()
    session.commit()

Maybe it goes a bit to far but I will describe the original data-structure, too. It is about making usage statistic for a gym. :)

You walk to a machine and lift some kilograms a couple of times - that is called TrainingUnit (what is equivalent to A in the example). For warming up you make 12 repetitions and lift up 35 kg in each of them - that is called a SetSet (equivalent to B in the example; keep in mind that Set is a reserved SQL-keyword). Then you make a one-minute-break and do 12 repetitions with 40 kg - the second set. And you do a third again(!) with 12 repetitions and 40 kg again. So the TrainingUnit instance/row need three relations to instances/rows of SetSet. Because the second and third set have the same values/settings I would use the same instance/row of SetSet here.

Most of the time the sportsman does TrainingUnits each day in the same configuration - which means each day the sets have the same values. That is why I want to reuse that instances/rows of SetSet. And the number of sets per TrainingUnit are not fixed - it depends on the sportsman how many sets he/she will make.

Solution

  • My solution depends on that resources. Thanks for helping there!

    I use the sample code to describe the solution.

    The schema in SQL...

    CREATE TABLE "A" (
        oid INTEGER NOT NULL,
        PRIMARY KEY (oid)
);
CREATE TABLE "B_Val" (
        oid INTEGER NOT NULL,
        val INTEGER,
        PRIMARY KEY (oid)
);
CREATE TABLE "B" (
        oid INTEGER NOT NULL,
        b_val_fk INTEGER,
        PRIMARY KEY (oid),
        FOREIGN KEY(b_val_fk) REFERENCES "B_Val" (oid)
);
CREATE TABLE a_b_relation (
        a_oid INTEGER,
        b_oid INTEGER,
        FOREIGN KEY(a_oid) REFERENCES "A" (oid),
        FOREIGN KEY(b_oid) REFERENCES "B" (oid)
);

    ...and SQLAlchemy

    a_b_relation= sa.Table('a_b_relation', _Base.metadata,
    sa.Column('a_oid', sa.Integer, sa.ForeignKey('A.oid')),
    sa.Column('b_oid', sa.Integer, sa.ForeignKey('B.oid'))
)


class A(_Base):
    __tablename__ = 'A'

    _oid = sa.Column('oid', sa.Integer, primary_key=True)
    _bbb = sao.relationship('B', secondary=a_b_relation)


class B_Val(_Base):
    __tablename__ = 'B_Val'
    _oid = sa.Column('oid', sa.Integer, primary_key=True)
    _val = sa.Column('val', sa.Integer)

    def __init__(self, val):
        self._val = val


class B(_Base):
    __tablename__ = 'B'

    _oid = sa.Column('oid', sa.Integer, primary_key=True)
    _b_val_fk = sa.Column('b_val_fk', sa.Integer, sa.ForeignKey('B_Val.oid'))
    _b_val = sao.relationship('B_Val')

    def __init__(self, b_val):
        self._b_val = b_val