Error in SQLAlchemy failure after DB exception?

Question

In my SQLAlchemy application (working on MariaDB) there are two models MyModelA and MyModelB , where the latter is a child of the former:

 class MyModelA(db.Model): a_id = db.Column(db.Integer, nullable=False, primary_key=True) my_field1 = db.Column(db.String(1024), nullable=True) class MyModelB(db.Model): b_id = db.Column(db.Integer, nullable=False, primary_key=True) a_id = db.Column(db.Integer, db.ForeignKey(MyModelA.a_id), nullable=False) my_field2 = db.Column(db.String(1024), nullable=True) 

These are the instances of MyModelA and MyModelB that I create:

 >>> my_a = MyModelA(my_field1="A1") >>> my_a.aid 1 >>> MyModelB(a_id=my_a.aid, my_field2="B1") 

I have the following code that removes an instance of MyModelA , where a_id==1 :

 db.session.commit() try: my_a = MyModelA.query.get(a_id=1) assert my_a is not None print "#1) Number of MyModelAs: %s\n" % MyModelA.query.count() db.session.delete(my_a) db.session.commit() except IntegrityError: print "#2) Cannot delete instance of MyModelA because it has child record(s)!" db.session.rollback() print "#3) Number of MyModelAs: %s\n" % MyModelA.query.count() 

When I ran this code, look at the unexpected results:

 #1) Number of MyModelAs: 1 #2) Cannot delete instance of MyModelA because it has child record(s)! #3) Number of MyModelAs: 0 

The deletion allegedly fails, and the database throws an exception that causes a rollback. However, even after the rollback, the number of rows in the table indicates that the row that was not supposedly deleted has actually disappeared.

Why is this happening? How can i fix this? This seems like a bug in SQLAlchemy.

Answer 1

TL; DR Your problem may be due to a lack of declaration of an explicit relationship.

For example, there is an example of the relationship of objects. In addition to using the ForeignKey field, the class explicitly uses the relationship directive to define this connection. The following text appears in the session API documentation :

object references should be built at the object level, and not at the foreign key level

What might mean the SQLAlchemy way to manage relationships. I am not very good at basic mechanisms, but it is possible that this happens. Your session includes only the MyModelA object. Since you did not use the relationship() directive in the definition of MyModelB , objects of type MyModelA do not know that any other object can refer to them via ForeignKey . Therefore, when a session is about to be completed, it is not aware of the fact that deleting an object affects some other MyModelB object, and its transaction mechanism does not take this into account. I suggest that explicitly adding relationships can interfere with this behavior.