Randomly inserting in the wrong order.
When trying to insert sequentially a model (team in the example) then another model with a foreign key pointing to the first model (user in the example) I was expecting sqlalchemy to either insert in the order I called db.session.add
or resolve the correct insertion order. It looks like it randomly resolve to an incorrect insertion order.
It look like the insertion order is computed in the sort_as_subsets
method from the topological.py
module.
Sometimes the insertion is in the correct order:
#!
INFO:sqlalchemy.engine.base.Engine:BEGIN (implicit)
INFO:sqlalchemy.engine.base.Engine:INSERT INTO team (team_id) VALUES (%s)
INFO:sqlalchemy.engine.base.Engine:(1,)
INFO:sqlalchemy.engine.base.Engine:INSERT INTO user (user_id, team_id) VALUES (%s, %s)
INFO:sqlalchemy.engine.base.Engine:((1, 1), (2, 1), (3, 1))
INFO:sqlalchemy.engine.base.Engine:COMMIT
And sometimes it starts by inserting the users instead of inserting the team:
#!
INFO:sqlalchemy.engine.base.Engine:COMMIT
INFO:sqlalchemy.engine.base.Engine:BEGIN (implicit)
INFO:sqlalchemy.engine.base.Engine:INSERT INTO user (user_id, team_id) VALUES (%s, %s)
INFO:sqlalchemy.engine.base.Engine:((1, 1), (2, 1), (3, 1))
INFO:sqlalchemy.engine.base.Engine:ROLLBACK
Error
Traceback (most recent call last):
File "/home/tim/Workspace/dota_memories/website/tests/test_sqla.py", line 45, in test_method
db.session.commit()
File "/home/tim/Workspace/dota_memories/website/env/local/lib/python2.7/site-packages/sqlalchemy/orm/scoping.py", line 157, in do
return getattr(self.registry(), name)(*args, **kwargs)
File "/home/tim/Workspace/dota_memories/website/env/local/lib/python2.7/site-packages/sqlalchemy/orm/session.py", line 874, in commit
self.transaction.commit()
File "/home/tim/Workspace/dota_memories/website/env/local/lib/python2.7/site-packages/sqlalchemy/orm/session.py", line 461, in commit
self._prepare_impl()
File "/home/tim/Workspace/dota_memories/website/env/local/lib/python2.7/site-packages/sqlalchemy/orm/session.py", line 441, in _prepare_impl
self.session.flush()
File "/home/tim/Workspace/dota_memories/website/env/local/lib/python2.7/site-packages/sqlalchemy/orm/session.py", line 2139, in flush
self._flush(objects)
File "/home/tim/Workspace/dota_memories/website/env/local/lib/python2.7/site-packages/sqlalchemy/orm/session.py", line 2259, in _flush
transaction.rollback(_capture_exception=True)
File "/home/tim/Workspace/dota_memories/website/env/local/lib/python2.7/site-packages/sqlalchemy/util/langhelpers.py", line 60, in __exit__
compat.reraise(exc_type, exc_value, exc_tb)
File "/home/tim/Workspace/dota_memories/website/env/local/lib/python2.7/site-packages/sqlalchemy/orm/session.py", line 2223, in _flush
flush_context.execute()
File "/home/tim/Workspace/dota_memories/website/env/local/lib/python2.7/site-packages/sqlalchemy/orm/unitofwork.py", line 389, in execute
rec.execute(self)
File "/home/tim/Workspace/dota_memories/website/env/local/lib/python2.7/site-packages/sqlalchemy/orm/unitofwork.py", line 548, in execute
uow
File "/home/tim/Workspace/dota_memories/website/env/local/lib/python2.7/site-packages/sqlalchemy/orm/persistence.py", line 181, in save_obj
mapper, table, insert)
File "/home/tim/Workspace/dota_memories/website/env/local/lib/python2.7/site-packages/sqlalchemy/orm/persistence.py", line 799, in _emit_insert_statements
execute(statement, multiparams)
File "/home/tim/Workspace/dota_memories/website/env/local/lib/python2.7/site-packages/sqlalchemy/engine/base.py", line 945, in execute
return meth(self, multiparams, params)
File "/home/tim/Workspace/dota_memories/website/env/local/lib/python2.7/site-packages/sqlalchemy/sql/elements.py", line 263, in _execute_on_connection
return connection._execute_clauseelement(self, multiparams, params)
File "/home/tim/Workspace/dota_memories/website/env/local/lib/python2.7/site-packages/sqlalchemy/engine/base.py", line 1053, in _execute_clauseelement
compiled_sql, distilled_params
File "/home/tim/Workspace/dota_memories/website/env/local/lib/python2.7/site-packages/sqlalchemy/engine/base.py", line 1189, in _execute_context
context)
File "/home/tim/Workspace/dota_memories/website/env/local/lib/python2.7/site-packages/sqlalchemy/engine/base.py", line 1393, in _handle_dbapi_exception
exc_info
File "/home/tim/Workspace/dota_memories/website/env/local/lib/python2.7/site-packages/sqlalchemy/util/compat.py", line 203, in raise_from_cause
reraise(type(exception), exception, tb=exc_tb, cause=cause)
File "/home/tim/Workspace/dota_memories/website/env/local/lib/python2.7/site-packages/sqlalchemy/engine/base.py", line 1159, in _execute_context
context)
File "/home/tim/Workspace/dota_memories/website/env/local/lib/python2.7/site-packages/sqlalchemy/dialects/mysql/mysqldb.py", line 113, in do_executemany
rowcount = cursor.executemany(statement, parameters)
File "/home/tim/Workspace/dota_memories/website/env/local/lib/python2.7/site-packages/MySQLdb/cursors.py", line 262, in executemany
r = self._query('\n'.join([query[:p], ',\n'.join(q), query[e:]]))
File "/home/tim/Workspace/dota_memories/website/env/local/lib/python2.7/site-packages/MySQLdb/cursors.py", line 354, in _query
rowcount = self._do_query(q)
File "/home/tim/Workspace/dota_memories/website/env/local/lib/python2.7/site-packages/MySQLdb/cursors.py", line 318, in _do_query
db.query(q)
IntegrityError: (_mysql_exceptions.IntegrityError) (1452, 'Cannot add or update a child row: a foreign key constraint fails (`memories_tests`.`user`, CONSTRAINT `user_ibfk_1` FOREIGN KEY (`team_id`) REFERENCES `team` (`team_id`))') [SQL: u'INSERT INTO user (user_id, team_id) VALUES (%s, %s)'] [parameters: ((1, 1), (2, 1), (3, 1))]
I created a simple example that runs 100 times the same test to demonstrate that it is randomly failing.
import logging
from unittest import TestCase
from flask import Flask
from flask.ext.sqlalchemy import SQLAlchemy
logging.basicConfig()
logging.getLogger('sqlalchemy.engine').setLevel(logging.INFO)
db = SQLAlchemy()
app = Flask(__name__)
app.config['SQLALCHEMY_DATABASE_URI'] = 'mysql://root:toor@localhost:3306/memories_tests'
db.init_app(app)
class User(db.Model):
user_id = db.Column(db.BigInteger, primary_key=True, autoincrement=False)
team_id = db.Column(db.BigInteger, db.ForeignKey('team.team_id'), nullable=True)
class Team(db.Model):
team_id = db.Column(db.BigInteger, primary_key=True, autoincrement=False)
class TeamTests(TestCase):
def setUp(self):
self.context = app.test_request_context()
self.context.push()
db.drop_all()
db.create_all()
def tearDown(self):
db.session.remove()
db.drop_all()
self.context.pop()
def add_test_method(index):
def test_method(self):
db.session.add(Team(team_id=1))
db.session.add(User(user_id=1, team_id=1))
db.session.add(User(user_id=2, team_id=1))
db.session.add(User(user_id=3, team_id=1))
db.session.commit()
test_method.__name__ = 'test_method_' + str(index)
setattr(TeamTests, test_method.__name__, test_method)
for i in range(100):
add_test_method(i)
Here is what I use :
- mysql 5.7.17
- Ubuntu 16.04
- SQLAlchemy 1.1.6
- Flask 0.11.1
- Flask-SQLAlchemy 2.1
- MySQL-python 1.2.5
Comments (3)
-
repo owner -
reporter Hi,
I didn't realize it is mandatory to specify the relationship on the model. It is working now, thank you for your quick answer.
SqlAlchemy really is great !
Tim
-
reporter - changed status to closed
- Log in to comment
Hello -
Thanks for the clear test case.
Session.add() does not preserve the order of INSERT between entirely different models, only those rows within a single table. So the .add() calls for User are maintained in order.
In this case, you have a dependency between Team and User, but you have not informed the SQLAlchemy ORM that such a dependency exists. The normal way to do this would be to indicate a relationship between the two objects:
no bug is illustrated here and the above fix illustrates the correct use of the API so let me know that's working. Thanks!