1_satest.py

from sqlalchemy import *
from sqlalchemy.ext.declarative import declarative_base
from sqlalchemy.orm import *

Base = declarative_base()


class Photo(Base):
    __tablename__ = 'photos'

    id = Column(Integer, primary_key=True)
    gallery_id = Column(ForeignKey('galleries.id'), nullable=True)

    gallery = relationship('Gallery', foreign_keys=gallery_id, back_populates='photos')
    cover_gallery = relationship('Gallery', foreign_keys=lambda: Gallery.cover_id, viewonly=True,
                                 back_populates='cover')

    def __repr__(self):
        return '<Photo {}>'.format(self.id)


class Gallery(Base):
    __tablename__ = 'galleries'

    id = Column(Integer, primary_key=True)
    cover_id = Column(ForeignKey('photos.id'), nullable=False)

    cover = relationship('Photo', foreign_keys=cover_id, back_populates='cover_gallery')
    photos = relationship('Photo', foreign_keys=Photo.gallery_id, back_populates='gallery')

    def __repr__(self):
        return '<Gallery {}>'.format(self.id)


e = create_engine('sqlite://', echo=True)
Base.metadata.create_all(e)
sess = Session(e, autoflush=False)


p = Photo()
sess.add(p)
sess.commit()

photos = sess.query(Photo).all()
gal = Gallery()
sess.add(gal)
gal.cover = p
sess.flush()
# sess.expire(gal, ['cover'])  # if i uncomment this it works
p.gallery = gal
sess.commit()

2_output.txt

2017-05-15 03:05:33,015 INFO sqlalchemy.engine.base.Engine SELECT CAST('test plain returns' AS VARCHAR(60)) AS anon_1
2017-05-15 03:05:33,015 INFO sqlalchemy.engine.base.Engine ()
2017-05-15 03:05:33,016 INFO sqlalchemy.engine.base.Engine SELECT CAST('test unicode returns' AS VARCHAR(60)) AS anon_1
2017-05-15 03:05:33,016 INFO sqlalchemy.engine.base.Engine ()
2017-05-15 03:05:33,016 INFO sqlalchemy.engine.base.Engine PRAGMA table_info("photos")
2017-05-15 03:05:33,016 INFO sqlalchemy.engine.base.Engine ()
2017-05-15 03:05:33,017 INFO sqlalchemy.engine.base.Engine PRAGMA table_info("galleries")
2017-05-15 03:05:33,017 INFO sqlalchemy.engine.base.Engine ()
2017-05-15 03:05:33,017 INFO sqlalchemy.engine.base.Engine
CREATE TABLE photos (
        id INTEGER NOT NULL,
        gallery_id INTEGER,
        PRIMARY KEY (id),
        FOREIGN KEY(gallery_id) REFERENCES galleries (id)
)


2017-05-15 03:05:33,017 INFO sqlalchemy.engine.base.Engine ()
2017-05-15 03:05:33,018 INFO sqlalchemy.engine.base.Engine COMMIT
2017-05-15 03:05:33,018 INFO sqlalchemy.engine.base.Engine
CREATE TABLE galleries (
        id INTEGER NOT NULL,
        cover_id INTEGER NOT NULL,
        PRIMARY KEY (id),
        FOREIGN KEY(cover_id) REFERENCES photos (id)
)


2017-05-15 03:05:33,018 INFO sqlalchemy.engine.base.Engine ()
2017-05-15 03:05:33,018 INFO sqlalchemy.engine.base.Engine COMMIT
2017-05-15 03:05:33,023 INFO sqlalchemy.engine.base.Engine BEGIN (implicit)
2017-05-15 03:05:33,024 INFO sqlalchemy.engine.base.Engine INSERT INTO photos (gallery_id) VALUES (?)
2017-05-15 03:05:33,024 INFO sqlalchemy.engine.base.Engine (None,)
2017-05-15 03:05:33,024 INFO sqlalchemy.engine.base.Engine COMMIT
2017-05-15 03:05:33,025 INFO sqlalchemy.engine.base.Engine BEGIN (implicit)
2017-05-15 03:05:33,025 INFO sqlalchemy.engine.base.Engine SELECT photos.id AS photos_id, photos.gallery_id AS photos_gallery_id
FROM photos
2017-05-15 03:05:33,025 INFO sqlalchemy.engine.base.Engine ()
2017-05-15 03:05:33,026 INFO sqlalchemy.engine.base.Engine INSERT INTO galleries (cover_id) VALUES (?)
2017-05-15 03:05:33,026 INFO sqlalchemy.engine.base.Engine (1,)
2017-05-15 03:05:33,027 INFO sqlalchemy.engine.base.Engine ROLLBACK
Traceback (most recent call last):
  File "satest.py", line 51, in <module>
    sess.commit()
  File "/home/adrian/dev/photosite/env/lib/python2.7/site-packages/sqlalchemy/orm/session.py", line 874, in commit
    self.transaction.commit()
  File "/home/adrian/dev/photosite/env/lib/python2.7/site-packages/sqlalchemy/orm/session.py", line 461, in commit
    self._prepare_impl()
  File "/home/adrian/dev/photosite/env/lib/python2.7/site-packages/sqlalchemy/orm/session.py", line 441, in _prepare_impl
    self.session.flush()
  File "/home/adrian/dev/photosite/env/lib/python2.7/site-packages/sqlalchemy/orm/session.py", line 2139, in flush
    self._flush(objects)
  File "/home/adrian/dev/photosite/env/lib/python2.7/site-packages/sqlalchemy/orm/session.py", line 2259, in _flush
    transaction.rollback(_capture_exception=True)
  File "/home/adrian/dev/photosite/env/lib/python2.7/site-packages/sqlalchemy/util/langhelpers.py", line 66, in __exit__
    compat.reraise(exc_type, exc_value, exc_tb)
  File "/home/adrian/dev/photosite/env/lib/python2.7/site-packages/sqlalchemy/orm/session.py", line 2223, in _flush
    flush_context.execute()
  File "/home/adrian/dev/photosite/env/lib/python2.7/site-packages/sqlalchemy/orm/unitofwork.py", line 381, in execute
    postsort_actions):
  File "/home/adrian/dev/photosite/env/lib/python2.7/site-packages/sqlalchemy/util/topological.py", line 36, in sort_as_subsets
    _gen_edges(edges)
sqlalchemy.exc.CircularDependencyError: Circular dependency detected. (SaveUpdateState(<Photo at 0x7fbe1534bbd0>), SaveUpdateState(<Gallery at 0x7fbe1534b310>), ProcessState(ManyToOneDP(Photo.gallery), <Photo at 0x7fbe1534bbd0>, delete=False), ProcessState(ManyToOneDP(Gallery.cover), <Gallery at 0x7fbe1534b310>, delete=False))