On 9/29/15 7:20 AM, c.bu...@posteo.jp wrote:
This is related to this post
<http://stackoverflow.com/questions/32825381/handle-multiple-relations-between-two-rows-objects-in-sqlalchemy>
I found this problem in my application using Python3 with a SQLAlchemy
connected sqlite-database.
Simple description:
Entity `a` (from table/class `A`) can have multible references to
entity `b` (from table/class `B`).
Sample:
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?
it does, it breaks first normal form in that there is no primary key
(http://www.tutorialspoint.com/sql/first-normal-form.htm).
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.
The table here is not storing data that SQLAlchemy could have persisted
with the ORM, since the ORM would never make a duplicate row like that
in an association table. if you want to use the ORM to modify these
rows then you'd need to use it on the INSERT as well as the DELETE side.
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.
OK but those are not the same SetSet, they happened at two different
times. They should be separate entities of data. if TrainingUnit
would like SetSet objects to be unique on their reps/weight, then the
association table itself would become an entity in which case you'd need
to add an additional column to it as a primary key.
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.
go through the normalization articles either at
https://en.wikipedia.org/wiki/Database_normalization#List_of_Normal_Forms up
to 3NF or at this tutorialspoint site which seems a lot simpler
("Database Normalization" at the bottom of
http://www.tutorialspoint.com/sql/sql-rdbms-concepts.htm), design your
schema to conform to that, and everything will work great.
--
You received this message because you are subscribed to the Google Groups
"sqlalchemy" group.
To unsubscribe from this group and stop receiving emails from it, send an email
to sqlalchemy+unsubscr...@googlegroups.com.
To post to this group, send email to sqlalchemy@googlegroups.com.
Visit this group at http://groups.google.com/group/sqlalchemy.
For more options, visit https://groups.google.com/d/optout.
