IntegrityError when inserting data in an association table using SQLAlchemy

by

The line that causes the autoflush is

t.users.append(a)

You’ve not defined your relationship loading strategies, so they default to "select". Since you’re accessing the Team.users relationship attribute for the first time, it will emit a SELECT in order to fetch the related objects.

You’ve also made changes to the session indirectly in

a.user = User.query.filter_by(id=current_user.id).first()

which due to save-update cascade, on by default, places the new Association instance to the session as well.

In order to keep the DB’s and session’s state consistent SQLAlchemy has to flush your changes to the DB before the query is emitted, and so the half initialized Association is sent to the DB before being associated with the Team instance.

Possible solutions:

  1. Temporarily disable the autoflush feature for that particular append because you know you’re adding a new Association:

    with db.session.no_autoflush:
    t.users.append(a)

    The SELECT will still be emitted, though.

  2. Reverse the operation. Instead of appending the Association instance to the Team.users collection, assign the Team to the Association.team:

    # Instead of t.users.append(a):
a.team = t  # Mr.

  3. A less obvious solution is to eager load the contents of the relationship when you fetch the Team instance, so that no SELECT is necessary:

    t = db.session.query(Team).options(db.joinedload(Team.users)).first()

    or not load them at all using the db.noload(Team.users) option.

Note that

db.session.add(t)

is redundant. The fetched Team instance is already in the session – otherwise the lazy load of Team.users could not have proceeded in the first place.

More Related Contents:

  1. Difference between filter and filter_by in SQLAlchemy
  2. Sqlite / SQLAlchemy: how to enforce Foreign Keys?
  3. SQLAlchemy IN clause
  4. Flask SQLAlchemy query, specify column names
  5. How can I bind a list to a parameter in a custom query in SQLAlchemy?
  6. How to disable SQLAlchemy caching?
  7. memory-efficient built-in SqlAlchemy iterator/generator?
  8. sqlalchemy unique across multiple columns
  9. How to build a flask application around an already existing database?
  10. How to create a new database using SQLAlchemy?
  11. How can I use psycopg2.extras in sqlalchemy?
  12. Multi-threaded use of SQLAlchemy
  13. How to get column names from SQLAlchemy result (declarative syntax)
  14. How to discover table properties from SQLAlchemy mapped object
  15. AttributeError: ‘int’ object has no attribute ‘_sa_instance_state’
  16. sqlalchemy: create relations but without foreign key constraint in db?
  17. Dynamic Class Creation in SQLAlchemy
  18. Strange SQLAlchemy error message: TypeError: ‘dict’ object does not support indexing
  19. SQLAlchemy equivalent to SQL “LIKE” statement
  20. How do I connect to SQL Server via sqlalchemy using Windows Authentication?
  21. How to get rows which match a list of 3-tuples conditions with SQLAlchemy
  22. Best way to do enum in Sqlalchemy?
  23. How do I INSERT INTO t1 (SELECT * FROM t2) in SQLAlchemy?
  24. Creating partial unique index with sqlalchemy on Postgres
  25. SQLAlchemy Joinedload filter column
  26. SQLAlchemy func.count on boolean column
  27. SQLAlchemy: get Model from table name. This may imply appending some function to a metaclass constructor as far as I can see
  28. pandas to_sql all columns as nvarchar
  29. How to make mysql connection that requires CA-CERT with sqlalchemy or SQLObject
  30. add column to SQLAlchemy Table

Leave a Comment