[sqlalchemy] IntegrityError on Association Proxy

[Erkan Özgür Yılmaz]

Hi,

I've encountered a situation where I don't know if this is a bug or it is
the correct behavior. It is about association_proxy. Please see the
User.__init__ and Department.__init__ methods.

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


Base = declarative_base()

DBSession = scoped_session(
sessionmaker(
extension=None
)
)


class Entity(Base):
__tablename__ = 'Entities'

id = Column(Integer, primary_key=True)
entity_type = Column(String(128), nullable=False)

__mapper_args__ = {
polymorphic_on: entity_type,
polymorphic_identity: SimpleEntity
}


class User(Entity):
__tablename__ = 'Users'
__mapper_args__ = {polymorphic_identity: User}
user_id = Column('id', Integer, ForeignKey('Entities.id'),
 primary_key=True)

departments = association_proxy(
'department_role',
'department',
creator=lambda d: DepartmentUsers(department=d)
)

department_role = relationship(
'DepartmentUser',
back_populates='user',
cascade='all, delete-orphan',
primaryjoin='Users.c.id==Department_Users.c.uid'
)

def __init__(self, departments=None):
super(User, self).__init__()
if departments is None:
departments = []

# using this creates an auto flush and raises an IntegrityError
# self.departments = departments

# where as using this is OK
for department in departments:
self.department_role.append(
DepartmentUser(user=self, department=department)
)


class Department(Entity):
__tablename__ = 'Departments'
__mapper_args__ = {polymorphic_identity: Department}
department_id = Column('id', Integer, ForeignKey('Entities.id'),
   primary_key=True)

users = association_proxy(
'user_role',
'user',
creator=lambda u: DepartmentUser(user=u)
)

user_role = relationship(
DepartmentUser,
back_populates=department,
cascade='all, delete-orphan',
primaryjoin='Departments.c.id==Department_Users.c.did'
)

@validates('user_role')
def _validate_user_role(self, key, user_role):
validates the given user_role variable

return user_role

def __init__(self, users=None):
if users is None:
users = []
# again using this creates an auto flush and raises an IntegrityError
# self.users = users

# where as using this is OK
for user in users:
self.user_role.append(
DepartmentUser(department=self, user=user)
)


class Role(Entity):
__tablename__ = 'Roles'
__mapper_args__ = {polymorphic_identity: Role}
role_id = Column('id', Integer, ForeignKey('Entities.id'), primary_key=True)


class DepartmentUser(Base):
__tablename__ = 'Department_Users'

user_id = Column(
'uid',
Integer,
ForeignKey('Users.id'),
primary_key=True
)

user = relationship(
'User',
back_populates='department_role',
primaryjoin='DepartmentUser.user_id==User.user_id'
)

department_id = Column(
'did',
Integer,
ForeignKey('Departments.id'),
primary_key=True
)

department = relationship(
'Department',
back_populates='user_role',
primaryjoin='DepartmentUser.department_id==Department.department_id'
)

role_id = Column(
'rid',
Integer,
ForeignKey('Roles.id'),
nullable=True
)

role = relationship(
'Role',
primaryjoin='DepartmentUser.role_id==Role.role_id'
)

def __init__(self, department=None, user=None, role=None):
self.department = department
self.user = user
self.role = role


if __name__ == __main__:
database_engine_settings = {
sqlalchemy.url: sqlite:///:memory:,
sqlalchemy.echo: False,
}

engine = engine_from_config(database_engine_settings, 'sqlalchemy.')
DBSession.configure(
bind=engine,
extension=None
)
Base.metadata.create_all(engine)

user1 = User()
dep1 = Department()
DBSession.add(user1)
DBSession.add(dep1)
DBSession.commit()

dep1.users.append(user1)

assert user1 in dep1.users
assert dep1 in user1.departments

dep_users = DBSession.query(DepartmentUser).all()
assert len(dep_users) == 1

user2 = User()
dep1.users.append(user2)
DBSession.add(user2)
DBSession.commit()

So, I get an IntegrityError if I set the **self.departments** or
**self.users** directly, so apparently creating the association objects
triggers an auto-flush but if I create the association objects instead of
directly setting the attributes there is no error. 

[sqlalchemy] Re: IntegrityError on Association Proxy

[Erkan Özgür Yılmaz]

Ok my test case was not reproducing the error as in my previous post, but
consider this one it is generating the IntegrityError

if __name__ == __main__:
database_engine_settings = {
sqlalchemy.url: sqlite:///:memory:,
sqlalchemy.echo: False,
}

engine = engine_from_config(database_engine_settings, 'sqlalchemy.')
DBSession.configure(
bind=engine,
extension=None
)
Base.metadata.create_all(engine)

user1 = User()
dep1 = Department(users=[user1])
DBSession.add(user1)
DBSession.add(dep1)
DBSession.commit()

assert user1 in dep1.users
assert dep1 in user1.departments

dep_users = DBSession.query(DepartmentUser).all()
assert len(dep_users) == 1

# user2 = User(departments=[dep1])
user2 = User()
DBSession.add(user2)
DBSession.commit()

dep1.users.append(user2)



Erkan Ozgur Yilmaz

On Thu, Nov 27, 2014 at 12:39 AM, Erkan Özgür Yılmaz eoyil...@gmail.com
wrote:

 Hi,

 I've encountered a situation where I don't know if this is a bug or it is
 the correct behavior. It is about association_proxy. Please see the
 User.__init__ and Department.__init__ methods.

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


 Base = declarative_base()

 DBSession = scoped_session(
 sessionmaker(
 extension=None
 )
 )


 class Entity(Base):
 __tablename__ = 'Entities'

 id = Column(Integer, primary_key=True)
 entity_type = Column(String(128), nullable=False)

 __mapper_args__ = {
 polymorphic_on: entity_type,
 polymorphic_identity: SimpleEntity
 }


 class User(Entity):
 __tablename__ = 'Users'
 __mapper_args__ = {polymorphic_identity: User}
 user_id = Column('id', Integer, ForeignKey('Entities.id'),
  primary_key=True)

 departments = association_proxy(
 'department_role',
 'department',
 creator=lambda d: DepartmentUsers(department=d)
 )

 department_role = relationship(
 'DepartmentUser',
 back_populates='user',
 cascade='all, delete-orphan',
 primaryjoin='Users.c.id==Department_Users.c.uid'
 )

 def __init__(self, departments=None):
 super(User, self).__init__()
 if departments is None:
 departments = []

 # using this creates an auto flush and raises an IntegrityError
 # self.departments = departments

 # where as using this is OK
 for department in departments:
 self.department_role.append(
 DepartmentUser(user=self, department=department)
 )


 class Department(Entity):
 __tablename__ = 'Departments'
 __mapper_args__ = {polymorphic_identity: Department}
 department_id = Column('id', Integer, ForeignKey('Entities.id'),
primary_key=True)

 users = association_proxy(
 'user_role',
 'user',
 creator=lambda u: DepartmentUser(user=u)
 )

 user_role = relationship(
 DepartmentUser,
 back_populates=department,
 cascade='all, delete-orphan',
 primaryjoin='Departments.c.id==Department_Users.c.did'
 )

 @validates('user_role')
 def _validate_user_role(self, key, user_role):
 validates the given user_role variable
 
 return user_role

 def __init__(self, users=None):
 if users is None:
 users = []
 # again using this creates an auto flush and raises an IntegrityError
 # self.users = users

 # where as using this is OK
 for user in users:
 self.user_role.append(
 DepartmentUser(department=self, user=user)
 )


 class Role(Entity):
 __tablename__ = 'Roles'
 __mapper_args__ = {polymorphic_identity: Role}
 role_id = Column('id', Integer, ForeignKey('Entities.id'), 
 primary_key=True)


 class DepartmentUser(Base):
 __tablename__ = 'Department_Users'

 user_id = Column(
 'uid',
 Integer,
 ForeignKey('Users.id'),
 primary_key=True
 )

 user = relationship(
 'User',
 back_populates='department_role',
 primaryjoin='DepartmentUser.user_id==User.user_id'
 )

 department_id = Column(
 'did',
 Integer,
 ForeignKey('Departments.id'),
 primary_key=True
 )

 department = relationship(
 'Department',
 back_populates='user_role',
 primaryjoin='DepartmentUser.department_id==Department.department_id'
 )

 role_id = Column(
 'rid',
 Integer,
 ForeignKey('Roles.id'),
 nullable=True
 )

 role = relationship(
 'Role',
 primaryjoin='DepartmentUser.role_id==Role.role_id'
 )

 def __init__(self, department=None

Re: [sqlalchemy] IntegrityError on Association Proxy

[Erkan Özgür Yılmaz]

Yes it recommends using ``no_autoflush`` and:

with DBSession.no_autoflush:
dep1.users.append(user2)

fixes it.

So is this the correct way appending to the collection, and should I wrap
all code that is manipulating ``Department.users`` with a ``no_autoflush``?

Erkan Ozgur Yilmaz

On Thu, Nov 27, 2014 at 2:13 AM, Michael Bayer mike...@zzzcomputing.com
wrote:

 take a look at the stack trace (or send it along).  Do you see autoflush
 in it?  If so, you should use “with session.no_autoflush” around the area
 where you are likely triggering a lazy load of an attribute or collection.


 http://docs.sqlalchemy.org/en/rel_0_9/orm/session.html#sqlalchemy.orm.session.Session.no_autoflush


 On Nov 26, 2014, at 6:00 PM, Erkan Özgür Yılmaz eoyil...@gmail.com
 wrote:

 As I understand it is not related with the __init__() methods or how I
 initialize the attributes

 Erkan Ozgur Yilmaz

 On Thu, Nov 27, 2014 at 12:58 AM, Erkan Özgür Yılmaz eoyil...@gmail.com
 wrote:

 Ok my test case was not reproducing the error as in my previous post, but
 consider this one it is generating the IntegrityError

 if __name__ == __main__:
 database_engine_settings = {
 sqlalchemy.url: sqlite:///:memory:,
 sqlalchemy.echo: False,
 }

 engine = engine_from_config(database_engine_settings, 'sqlalchemy.')
 DBSession.configure(
 bind=engine,
 extension=None
 )
 Base.metadata.create_all(engine)

 user1 = User()
 dep1 = Department(users=[user1])
 DBSession.add(user1)
 DBSession.add(dep1)
 DBSession.commit()

 assert user1 in dep1.users
 assert dep1 in user1.departments

 dep_users = DBSession.query(DepartmentUser).all()
 assert len(dep_users) == 1

 # user2 = User(departments=[dep1])
 user2 = User()
 DBSession.add(user2)
 DBSession.commit()

 dep1.users.append(user2)



 Erkan Ozgur Yilmaz

 On Thu, Nov 27, 2014 at 12:39 AM, Erkan Özgür Yılmaz eoyil...@gmail.com
 wrote:

 Hi,

 I've encountered a situation where I don't know if this is a bug or it
 is the correct behavior. It is about association_proxy. Please see the
 User.__init__ and Department.__init__ methods.

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


 Base = declarative_base()

 DBSession = scoped_session(
 sessionmaker(
 extension=None
 )
 )


 class Entity(Base):
 __tablename__ = 'Entities'

 id = Column(Integer, primary_key=True)
 entity_type = Column(String(128), nullable=False)

 __mapper_args__ = {
 polymorphic_on: entity_type,
 polymorphic_identity: SimpleEntity
 }


 class User(Entity):
 __tablename__ = 'Users'
 __mapper_args__ = {polymorphic_identity: User}
 user_id = Column('id', Integer, ForeignKey('Entities.id'),
  primary_key=True)

 departments = association_proxy(
 'department_role',
 'department',
 creator=lambda d: DepartmentUsers(department=d)
 )

 department_role = relationship(
 'DepartmentUser',
 back_populates='user',
 cascade='all, delete-orphan',
 primaryjoin='Users.c.id 
 http://users.c.id/==Department_Users.c.uid'
 )

 def __init__(self, departments=None):
 super(User, self).__init__()
 if departments is None:
 departments = []

 # using this creates an auto flush and raises an IntegrityError
 # self.departments = departments

 # where as using this is OK
 for department in departments:
 self.department_role.append(
 DepartmentUser(user=self, department=department)
 )


 class Department(Entity):
 __tablename__ = 'Departments'
 __mapper_args__ = {polymorphic_identity: Department}
 department_id = Column('id', Integer, ForeignKey('Entities.id'),
primary_key=True)

 users = association_proxy(
 'user_role',
 'user',
 creator=lambda u: DepartmentUser(user=u)
 )

 user_role = relationship(
 DepartmentUser,
 back_populates=department,
 cascade='all, delete-orphan',
 primaryjoin='Departments.c.id 
 http://departments.c.id/==Department_Users.c.did'
 )

 @validates('user_role')
 def _validate_user_role(self, key, user_role):
 validates the given user_role variable
 
 return user_role

 def __init__(self, users=None):
 if users is None:
 users = []
 # again using this creates an auto flush and raises an 
 IntegrityError
 # self.users = users

 # where as using this is OK
 for user in users:
 self.user_role.append(
 DepartmentUser(department=self, user=user)
 )


 class Role(Entity):
 __tablename__ = 'Roles

[sqlalchemy] Association Proxy with Events (working very nice but there is a problem)

[Erkan Özgür Yılmaz]

Hi everybody,

I'm trying to use Association Proxy in one of my classes relation to
itself. I've got a Task class and Tasks can be dependent to each other, and
I've also wanted to store the dependency type ('start-to-start',
'start-to-end' etc.), so AssociationProxy was the way I've gone. Any way my
problem is with events.

Here is an example illustrating the problem:

import logging
from sqlalchemy import event, Column, Integer, String, ForeignKey
from sqlalchemy.ext.associationproxy import association_proxy
from sqlalchemy.ext.declarative import declarative_base
from sqlalchemy.orm import relationship

logging.basicConfig()
logger = logging.getLogger(__name__)
logger.setLevel(logging.DEBUG)

Base = declarative_base()


class Task(Base):

__tablename__ = 'Tasks'
id = Column(Integer, primary_key=True)
name = Column(String)

status = Column(String)

depends = association_proxy(
'task_depends_to',  # outgoing_edges
'to_node',  # to_node
creator=lambda n: TaskDependent(to_node=n)
)

dependent_of = association_proxy(
'task_dependent_of',  # incoming_edges
'from_node',  # from_node
creator=lambda n: TaskDependent(from_node=n)
)

task_depends_to = relationship(
'TaskDependent',
cascade=all, delete-orphan,
primaryjoin='Tasks.c.id==Task_Dependencies.c.from_node_id'
)

task_dependent_of = relationship(
'TaskDependent',
cascade=all, delete-orphan,
primaryjoin='Tasks.c.id==Task_Dependencies.c.to_node_id'
)

def __init__(self, name='', depends=None, status='WFD'):
self.name = name
self.status = status
if depends is None:
depends = []
self.depends = depends

def __repr__(self):
return %s (Task) % self.name

def update_status_with_dependent_statuses(self):
if not self.depends:
self.status = 'RTS'


class TaskDependent(Base):

__tablename__ = Task_Dependencies
from_node_id = Column(
Integer,
ForeignKey(Tasks.id),
primary_key=True
)

from_node = relationship(
Task,
back_populates='task_depends_to',
primaryjoin='Task.id==TaskDependent.from_node_id',
)

to_node_id = Column(
Integer,
ForeignKey(Tasks.id),
primary_key=True
)

to_node = relationship(
Task,
back_populates='task_dependent_of',
primaryjoin=TaskDependent.to_node_id==Task.id,
)

gap = Column(Integer)

def __init__(self, from_node=None, to_node=None, dependency_type=None,
 gap=0, gap_model='length'):
self.to_node = to_node
self.from_node = from_node
self.dependency_type = dependency_type
self.gap = gap


@event.listens_for(Task.task_depends_to, 'remove', propagate=True)
def removed_a_dependency(task, task_dependent, initiator):
Runs when a task is removed from another tasks dependency list.

# update task status with dependencies
logger.debug('inside removed_a_dependency')
logger.debug('task: %s' % task)
logger.debug('task.depends: %s' % task.depends)
logger.debug('task_dependent  : %s' % task_dependent)
logger.debug('task_dependent.to_node  : %s' % task_dependent.to_node)
logger.debug('task_dependent.from_node: %s' % task_dependent.from_node)
logger.debug('initiator   : %s' % initiator)
task.update_status_with_dependent_statuses()


if __name__ == '__main__':
taskA = Task(name='TaskA')
taskB = Task(name='TaskB')
taskC = Task(name='TaskC', depends=[taskA, taskB])

assert taskC.status == 'WFD'
taskC.depends = []
assert taskC.depends == []
assert taskC.status == 'RTS'

Outputs:

DEBUG:__main__:inside removed_a_dependency
DEBUG:__main__:task: TaskC (Task)
DEBUG:__main__:task.depends: [TaskA (Task), TaskB (Task)]
DEBUG:__main__:task_dependent  : __main__.TaskDependent object at
0x1fa3c10
DEBUG:__main__:task_dependent.to_node  : TaskA (Task)
DEBUG:__main__:task_dependent.from_node: None
DEBUG:__main__:initiator   : sqlalchemy.orm.attributes.Event
object at 0x1fa3f90
DEBUG:__main__:inside removed_a_dependency
DEBUG:__main__:task: TaskC (Task)
DEBUG:__main__:task.depends: [TaskA (Task), TaskB (Task)]
DEBUG:__main__:task_dependent  : __main__.TaskDependent object at
0x1fa3cd0
DEBUG:__main__:task_dependent.to_node  : TaskB (Task)
DEBUG:__main__:task_dependent.from_node: None
DEBUG:__main__:initiator   : sqlalchemy.orm.attributes.Event
object at 0x1fa3f90
Traceback (most recent call last):
  File /home/eoyilmaz/association_proxy_remove_event.py, line 121, in
module
assert taskC.status == 'RTS'
AssertionError

So, TaskC depends both to TaskA and TaskB. When I set taskC.depends to []
the remove event successfully triggered, and the

[sqlalchemy] CircularDependencyError, why?

[Erkan Özgür Yılmaz]

The following gives CircularDependencyError

where I think there isn't, but you know I don't know that much the internals
of SQLAlchemy and maybe (and it is a strong possibility) I'm wrong. I've
ripped of the classes causing the error (copy/paste will re-produce the
error).

from sqlalchemy import orm, create_engine
from sqlalchemy.orm import relationship, synonym, validates
from sqlalchemy import (Table, Column, ForeignKey, Boolean, Integer, String)
from sqlalchemy.ext.declarative import (declarative_base, synonym_for,
declared_attr)


Base = declarative_base()



class SimpleEntity(Base):
__tablename__ = SimpleEntities
id = Column(id, Integer, primary_key=True)

entity_type = Column(db_entity_type, String(128), nullable=False)
__mapper_args__ = {
polymorphic_on: entity_type,
polymorphic_identity: SimpleEntity,
}


class Entity(SimpleEntity):
__tablename__ = Entities
__mapper_args__ = {polymorphic_identity: Entity}
entity_id = Column(id, Integer, ForeignKey(SimpleEntities.id),
   primary_key=True)


class Task(Entity):
__tablename__ = Tasks
__mapper_args__ = {polymorphic_identity: Task}
task_id = Column(id, Integer, ForeignKey(Entities.id),
 primary_key=True)
task_of_id = Column(Integer, ForeignKey(TaskableEntities.id))


class ProjectMixin(object):
#--
@declared_attr
def project_id(cls):
return Column(
project_id,
Integer,
ForeignKey(Projects.id),
)

#--
@declared_attr
def project(cls):
return relationship(
Project,
primaryjoin=\
cls.__name__ + .project_id==Project.project_id_local,
post_update=True, # for project itself
uselist=False
)

#--
def __init__(self,
 project=None,
 **kwargs):

self.project = project


class TaskableEntity(Entity, ProjectMixin):
__tablename__ = TaskableEntities
__mapper_args__ = {polymorphic_identity: TaskableEntity}
taskableEntity_id = Column(id, Integer, ForeignKey(Entities.id),
   primary_key=True)
tasks = relationship(
Task,
primaryjoin=taskableEntity_id==Task.task_of_id,
backref=task_of,
post_update=True,
)


class Project(TaskableEntity):
__tablename__ = Projects
project_id_local = Column(id, Integer,
ForeignKey(TaskableEntities.id),
  primary_key=True)
__mapper_args__ = {polymorphic_identity: Project,
   inherit_condition:
   project_id_local==TaskableEntity.taskableEntity_id}


engine = create_engine(sqlite:///:memory:)
Base.metadata.create_all(engine)
Session = sqlalchemy.orm.sessionmaker(bind=dengine)

session = Session()




The Error message:
sqlalchemy.exc.CircularDependencyError: Circular dependency detected:
cycles:
set([
Table('TaskableEntities', MetaData(None),
  Column('id', Integer(), ForeignKey('Entities.id'),
table=TaskableEntities, primary_key=True, nullable=False),
  Column('project_id', Integer(), ForeignKey('Projects.id'),
table=TaskableEntities), schema=None),
Table('Projects', MetaData(None),
  Column('id', Integer(), ForeignKey('TaskableEntities.id'),
table=Projects, primary_key=True, nullable=False), schema=None)]
) all edges:
set(
[
(Table('TaskableEntities', MetaData(None),
   Column('id', Integer(), ForeignKey('Entities.id'),
table=TaskableEntities, primary_key=True, nullable=False),
   Column('project_id', Integer(), ForeignKey('Projects.id'),
table=TaskableEntities), schema=None),
 Table('Projects', MetaData(None),
   Column('id', Integer(), ForeignKey('TaskableEntities.id'),
table=Projects, primary_key=True, nullable=False), schema=None)),
(Table('SimpleEntities', MetaData(None),
   Column('id', Integer(), table=SimpleEntities,
primary_key=True, nullable=False),
   Column('db_entity_type', String(length=128),
table=SimpleEntities, nullable=False), schema=None),
 Table('Entities', MetaData(None),
   Column('id', Integer(), ForeignKey('SimpleEntities.id'),
table=Entities, 

Re: [sqlalchemy] CircularDependencyError, why?

[Erkan Özgür Yılmaz]

Oh, that worked very well, thank you very much...

E.Ozgur Yilmaz
Lead Technical Director
eoyilmaz.blogspot.com



On Wed, Sep 7, 2011 at 8:04 PM, Michael Bayer mike...@zzzcomputing.comwrote:


 On Sep 7, 2011, at 12:36 PM, Erkan Özgür Yılmaz wrote:

 The following gives CircularDependencyError

 where I think there isn't, but you know I don't know that much the
 internals of SQLAlchemy and maybe (and it is a strong possibility) I'm
 wrong. I've ripped of the classes causing the error (copy/paste will
 re-produce the error).


 According to the error message, TaskableEntity has a foreign key to
 Projects and Projects has a foreign key to TaskableEntity, thus creating a
 cycle.  TaskableEntity would appear to have this Project foreign key via the
 ProjectMixin class.So I'd add a use_alter=True to one of those
 ForeignKey objects.






 from sqlalchemy import orm, create_engine
 from sqlalchemy.orm import relationship, synonym, validates
 from sqlalchemy import (Table, Column, ForeignKey, Boolean, Integer,
 String)
 from sqlalchemy.ext.declarative import (declarative_base, synonym_for,
 declared_attr)


 Base = declarative_base()


 
 class SimpleEntity(Base):
 __tablename__ = SimpleEntities
 id = Column(id, Integer, primary_key=True)

 entity_type = Column(db_entity_type, String(128), nullable=False)
 __mapper_args__ = {
 polymorphic_on: entity_type,
 polymorphic_identity: SimpleEntity,
 }

 
 class Entity(SimpleEntity):
 __tablename__ = Entities
 __mapper_args__ = {polymorphic_identity: Entity}
 entity_id = Column(id, Integer, ForeignKey(SimpleEntities.id),
primary_key=True)

 
 class Task(Entity):
 __tablename__ = Tasks
 __mapper_args__ = {polymorphic_identity: Task}
 task_id = Column(id, Integer, ForeignKey(Entities.id),
  primary_key=True)
 task_of_id = Column(Integer, ForeignKey(TaskableEntities.id))

 
 class ProjectMixin(object):
 #--
 @declared_attr
 def project_id(cls):
 return Column(
 project_id,
 Integer,
 ForeignKey(Projects.id),
 )

 #--
 @declared_attr
 def project(cls):
 return relationship(
 Project,
 primaryjoin=\
 cls.__name__ + .project_id==Project.project_id_local,
 post_update=True, # for project itself
 uselist=False
 )

 #--
 def __init__(self,
  project=None,
  **kwargs):

 self.project = project

 
 class TaskableEntity(Entity, ProjectMixin):
 __tablename__ = TaskableEntities
 __mapper_args__ = {polymorphic_identity: TaskableEntity}
 taskableEntity_id = Column(id, Integer, ForeignKey(Entities.id),
primary_key=True)
 tasks = relationship(
 Task,
 primaryjoin=taskableEntity_id==Task.task_of_id,
 backref=task_of,
 post_update=True,
 )

 
 class Project(TaskableEntity):
 __tablename__ = Projects
 project_id_local = Column(id, Integer,
 ForeignKey(TaskableEntities.id),
   primary_key=True)
 __mapper_args__ = {polymorphic_identity: Project,
inherit_condition:
project_id_local==TaskableEntity.taskableEntity_id}


 engine = create_engine(sqlite:///:memory:)
 Base.metadata.create_all(engine)
 Session = sqlalchemy.orm.sessionmaker(bind=dengine)

 session = Session()




 The Error message:
 sqlalchemy.exc.CircularDependencyError: Circular dependency detected:
 cycles:
 set([
 Table('TaskableEntities', MetaData(None),
   Column('id', Integer(), ForeignKey('Entities.id'),
 table=TaskableEntities, primary_key=True, nullable=False),
   Column('project_id', Integer(), ForeignKey('Projects.id'),
 table=TaskableEntities), schema=None),
 Table('Projects', MetaData(None),
   Column('id', Integer(), ForeignKey('TaskableEntities.id'),
 table=Projects, primary_key=True, nullable=False), schema=None)]
 ) all edges:
 set(
 [
 (Table('TaskableEntities', MetaData(None),
Column('id', Integer(), ForeignKey('Entities.id'),
 table=TaskableEntities, primary_key=True, nullable=False),
Column

Re: [sqlalchemy] one-to-many with mixins in inherited classes

[Erkan Özgür Yılmaz]

I forgot to say thank you, I've solved it as you suggested and created a
TaskableEntity for task related entities...

E.Ozgur Yilmaz
Lead Technical Director
eoyilmaz.blogspot.com



On Sun, Sep 4, 2011 at 11:35 PM, Michael Bayer mike...@zzzcomputing.comwrote:

 TaskMixin.tasks places a relationship named task_of onto the Task class
 via the backref directive.   Only one relationship of this name can exist on
 the parent class, and a relationship is only configurable against a single
 class.   This single class can be of course a common base for many
 subclasses, but in this case you're attempting to create two task_of
 relationships, one hardwired to TaskableClassA and the other hardwired to
 TaskableClassB.

 It seems likely you'd like task_of to link to both classes and load
 polymorphically - so TaskableClassA and TaskableClassB should both inherit
 from a common base, with no __tablename__, such as TaskableClassBase - that
 class then uses TaskMixin to establish behavior common to both.

 Of course you don't really need the TaskMixin here since it can only be
 used once with that backref on it.

 you can try the modified versions attached - the first uses TaskMixin the
 second removes it.


 --
 You received this message because you are subscribed to the Google Groups
 sqlalchemy group.
 To post to this group, send email to sqlalchemy@googlegroups.com.
 To unsubscribe from this group, send email to
 sqlalchemy+unsubscr...@googlegroups.com.
 For more options, visit this group at
 http://groups.google.com/group/sqlalchemy?hl=en.





 On Sep 4, 2011, at 3:18 PM, Erkan Özgür Yılmaz wrote:

 from sqlalchemy import Column, ForeignKey, Integer, String
 from sqlalchemy.orm import relationship, validates
 from sqlalchemy.ext import declarative
 from sqlalchemy.ext.declarative import declarative_base, declared_attr

 Base = declarative_base()

 # The Base Class
 
 class SimpleEntity(Base):
 __tablename__ = SimpleEntities
 id = Column(id, Integer, primary_key=True)

 entity_type = Column(db_entity_type, String(128), nullable=False)
 __mapper_args__ = {
 polymorphic_on: entity_type,
 polymorphic_identity: SimpleEntity,
 }

 name = Column(String(256), nullable=False)

 #--
 def __init__(self, name=None, **kwargs):

 self.name = name

 
 class Task(SimpleEntity):
 __tablename__ = Tasks
 task_id = Column(id, Integer, ForeignKey(SimpleEntities.id),
  primary_key=True)
 __mapper_args__ = {polymorphic_identity: Task,
inherit_condition: task_id==SimpleEntity.id}

 task_of_id = Column(Integer,
 ForeignKey(SimpleEntities.id)
 )

 #--
 def __init__(self, task_of=None, **kwargs):
 super(Task, self).__init__(**kwargs)
 self.task_of = task_of

 
 class TaskMixin(object): # The mixin
 def __init__(self, tasks=None, **kwargs):
 if tasks is None:
 tasks = []
 self.tasks = tasks

 #--
 @declared_attr
 def tasks(cls):
 return relationship(
 Task,
 primaryjoin=Tasks.c.task_of_id==SimpleEntities.c.id,
 backref=task_of,
 )


 
 # example class 1 - defining only one class with TaskMixin doesn't create
 # any problem
 class TaskableClassA(SimpleEntity, TaskMixin):
 __tablename__ = TaskableAs
 __mapper_args__ = {polymorphic_identity: TaskableClassA}
 taskableClass_id = Column(id, Integer,
 ForeignKey(SimpleEntities.id),
   primary_key=True)

 #--
 def __init__(self, **kwargs):
 super(SimpleEntity, self).__init__(**kwargs)
 TaskMixin.__init__(self, **kwargs)

 ###
 # example class 2 - which creates the problem
 class TaskableClassB(SimpleEntity, TaskMixin):
 __tablename__ = TaskableBs
 __mapper_args__ = {polymorphic_identity: TaskableClassB}
 taskableClass_id = Column(id, Integer,
 ForeignKey(SimpleEntities.id),
   primary_key=True)

 #--
 def __init__(self, **kwargs):
 super(SimpleEntity, self).__init__(**kwargs)
 TaskMixin.__init__(self, **kwargs)

 a_taskable_object = TaskableClassA(name=taskable test object)
 task1 = Task(name=Test Task1, task_of

[sqlalchemy] class_variable as polymorphic_identity

[Erkan Özgür Yılmaz]

Hi everybody,

(fast start :) )
I have a class hierarchy and I'm using joined table inheritance, but my
classes are not always going to be used with SQLAlchemy (or I plan to give
the freedom to people to use these classes anywhere they want).

Anyway I was using the entity_type column in my base table to map the
polymorphic_identity. It was nice to have the entity_type variable *after*
mapping with SQLAlchemy. Now, I decided to add this variable to all my
classes as a new class variable. So my problem is when I map with SQLAlchemy
the variable becomes an sqlalchemy.orm.attributes.InstrumentedAttribute and
prints out None before committing the objects, so I created another column
for the discriminator with the name db_entity_type now I'm not able to use
the default value for entity_type from the class. Is there a solution for
that?

Thanks...

E.Ozgur Yilmaz
Lead Technical Director
eoyilmaz.blogspot.com
www.ozgurfx.com

-- 
You received this message because you are subscribed to the Google Groups 
sqlalchemy group.
To post to this group, send email to sqlalchemy@googlegroups.com.
To unsubscribe from this group, send email to 
sqlalchemy+unsubscr...@googlegroups.com.
For more options, visit this group at 
http://groups.google.com/group/sqlalchemy?hl=en.

Re: [sqlalchemy] class_variable as polymorphic_identity

[Erkan Özgür Yılmaz]

thanks Michael,

because I edited my question a couple of times, and also trying to solve it
while editing the text, it went to a slightly different way then I want to
point to,

Let me explain it in a different way, may be I am asking the wrong question:

Right now I'm developing an Open Source Production Asset Management System
(ProdAM). Lets say I have Project, Sequence, Shot, Asset, Task etc. classes,
each class should have a status attribute:

A Project can have these statuses:
- Waiting To Start
- On Hold
- In Progress
- Complete

A Shot can have these statuses:
- Waiting To Start
- Waiting Offline
- In Progress
- On Hold
- Pending Review
- Approved
- Complete

A Sequence can have these statuses:
- Waiting To Start
- On Hold
- In Progress
- Pending Review
- Approved
- Complete

etc.

As you see most of the statuses are going to be common, so I decided to have
a StatusList object holding Status objects. And let the user create all the
statuses he needs in his studio and then group them in StatusList objects.

But this introduces another problem, when the user creates a new Project
object he needs to specify which StatusList he wants to use, and serving all
the StatusLists and letting him to choose is not a good idea (then he can
choose a wrong StatusList which is not designed for that kind of objects).

So instead of doing that, and to automate this part, I wanted to have
another object holding the available StatusList objects for specific object
types, lets say an EntityTypeStatusListOption table/object which has a
column for entity_type and another for statusList_id. (all right we are
there finally). Then, I wanted to use the entity_type attribute of the
polymorphic_identity (just because it was showing the type of the object)
and the id of the StatusList to hold the StatusList for specific kind of
objects.

And because I'm using plain Python objects in my class hierarchy, I didn't
want to use something (the entity_type attribute) which is only introduced
when the user uses SQLAlchemy (after mapping). Then, I decided to add the
entity_type as a class attribute and try to persist it and also use it as
the polymorphic_identity, but it didn't work like that, I did what you
suggest (another column with the name db_entity_type for the
polymorphic_identity) but then I couldn't able to store the value of the
class attribute entity_type in the base table (where as the db_entity_type
was holding the correct value (db_entity_type=MyClass.entity_type) ) I
believe I need to store it to be able to use it in secondary join with the
EntityTypeStatusListOption table (or should I use the db_entity_type for the
join, may be, anyway, I'm not sure). So this is my first question, do you
have any suggestion?

My second question is about the validity of my idea, just because I have
never designed a system which uses a database, I'm not sure about all this
setup, and may be I'm making it complex than it needs to be, or there are
other simple ways of doing the same thing (I think I need to read about the
design patterns in relational databases). So anyway is there anything you
can suggest me with this setup.

and sorry about asking something which is not directly related with
SQLAlchemy itself but the design of the database.

Thank you again...

E.Ozgur Yilmaz
Lead Technical Director
eoyilmaz.blogspot.com
www.ozgurfx.com


On Tue, Jan 18, 2011 at 5:56 PM, Michael Bayer mike...@zzzcomputing.comwrote:


 On Jan 18, 2011, at 9:42 AM, Erkan Özgür Yılmaz wrote:

 Hi everybody,

 (fast start :) )
 I have a class hierarchy and I'm using joined table inheritance, but my
 classes are not always going to be used with SQLAlchemy (or I plan to give
 the freedom to people to use these classes anywhere they want).

 Anyway I was using the entity_type column in my base table to map the
 polymorphic_identity. It was nice to have the entity_type variable *after*
 mapping with SQLAlchemy. Now, I decided to add this variable to all my
 classes as a new class variable. So my problem is when I map with SQLAlchemy
 the variable becomes an sqlalchemy.orm.attributes.InstrumentedAttribute and
 prints out None before committing the objects, so I created another column
 for the discriminator with the name db_entity_type now I'm not able to use
 the default value for entity_type from the class. Is there a solution for
 that?


 0.7 has the polymorphic discriminator column auto-populated when you first
 create an object, so you wouldn't have this issue then.

 For now keep your db_entity_type as the mapped column, keep entity_type as
 class based, and use it in the mapping:

 class MyClass(MyParent):
entity_type = 'myclass'

 mapper(MyClass, mytable, inherits=MyParent,
 polymorphic_identity=MyClass.entity_type)




 Thanks...

 E.Ozgur Yilmaz
 Lead Technical Director
 eoyilmaz.blogspot.com
 www.ozgurfx.com

 --
 You received this message because you are subscribed to the Google

[sqlalchemy] ObjectDeletedError

[Erkan Özgür Yılmaz]

Hi all,

This is my first question regarding to SQLAlchemy, you can count me as a
newbie. Here is my problem:

I've this inheritance between my Python classes, everything were working
fine until last night when I've added another class to my inheritance
hierarchy. I've tried to find the cause of that pretty much read all the
documentation about session, but couldn't find any solution.

I'm going to try to reduce the things I'm writing here by not including all
the attributes/methods below, so warn me if you spot something:

This is the basic inheritance to let you figure out what is inheriting from
what:

class SimpleEntity(object):
pass

class Entity(SimpleEntity):
pass

class TypeEntity(Entity):
pass

class LinkType(TypeEntity):
pass



Here are the Tables (just the ones used with these classes, there are a lot
other tables too, I can paste them here if you ask for):

# SIMPLE ENTITY
simpleEntities = Table(
simpleEntities, metadata,
Column(id, Integer, primary_key=True),
Column(name, String(256), nullable=False),
Column(description, String),

Column(
created_by_id,
Integer,
ForeignKey(users.id, use_alter=True, name=x)
),

Column(
updated_by_id,
Integer,
ForeignKey(users.id, use_alter=True, name=x)
),

Column(date_created, DateTime),
Column(date_updated, DateTime),
Column(entity_type, String(128), nullable=False),
UniqueConstraint('name', 'entity_type')

)


# ENTITY
entities = Table(
entities, metadata,
Column(
id,
ForeignKey(simpleEntities.id),
primary_key=True
),
)



# TYPEENTITIES
typeEntities = Table(
typeEntities, metadata,
Column(
id,
Integer,
ForeignKey(entities.id),
primary_key=True,
),
)


# LINKTYPES
linkTypes = Table(
linkTypes, metadata,
Column(
id,
Integer,
ForeignKey(typeEntities.id),
),
)



Mappers:

# SimpleEntity
mapper(
entity.SimpleEntity,
tables.simpleEntities,
properties={
_name: tables.simpleEntities.c.name,
name: synonym(_name),
_description: tables.simpleEntities.c.description,
description: synonym(_description),
_created_by: relationship(
user.User,
backref=_entities_created,
primaryjoin=tables.simpleEntities.c.created_by_id== \
tables.users.c.id,
post_update=True,
uselist=False
),
created_by: synonym(_created_by),
_updated_by: relationship(
user.User,
backref=_entities_updated,
primaryjoin=tables.simpleEntities.c.updated_by_id== \
tables.users.c.id,
post_update=True,
uselist=False
),
updated_by: synonym(_updated_by),
_date_created: tables.simpleEntities.c.date_created,
date_created: synonym(_date_created),
_date_updated: tables.simpleEntities.c.date_updated,
date_updated: synonym(_date_updated)
},
polymorphic_on=tables.simpleEntities.c.entity_type,
polymorphic_identity=SimpleEntity
)


# Entity
mapper(
entity.Entity,
tables.entities,
inherits=entity.SimpleEntity,
inherit_condition=tables.entities.c.id==tables.simpleEntities.c.id,
polymorphic_identity=Entity,
properties={
_tags: relationship(
tag.Tag,
secondary=tables.entity_tags,
backref=_entities
),
tags: synonym(_tags)
}
)


# TypeEntity
mapper(
entity.TypeEntity,
tables.typeEntities,
inherits=entity.Entity,
inherit_condition=tables.typeEntities.c.id==tables.entities.c.id,
polymorphic_identity=TypeEntity,
)



# LinkType
mapper(
types.LinkType,
tables.linkTypes,
inherits=entity.TypeEntity,
inherit_condition=tables.linkTypes.c.id==tables.typeEntities.c.id,
polymorphic_identity=LinkType,
)



Alright here it comes slowly, my session object is set up in another module
(like the classes, tables and mappers are all in seperate modules), but I'm
going to try to merge them here:

 engine = create_engine(sqlite:///:memory:, echo:True)

here are calls for the mappers shown above:
 mappers.setup()
 create_all(engine)
 Session = sessionmaker(bind=engine)
 session = Session()

 sound_link_type = LinkType(name=Sound)
 sound_linkt_type.__dict__
{'_created_by': None, '_sa_instance_state':
sqlalchemy.orm.state.InstanceState object at 0x19866d0, '_description':
'', '_date_updated': datetime.datetime(2011, 1, 11, 15, 44, 27, 954088),
'_date_created': datetime.datetime(2011, 1, 11, 15, 44, 27, 954074),
'_tags': [], '_name': 'Sound', 

Re: [sqlalchemy] ObjectDeletedError

[Erkan Özgür Yılmaz]

Great, adding the primary_key=True to the linkTypes table solved my problem,
thank you very much...

Cheers...

E.Ozgur Yilmaz
Lead Technical Director
eoyilmaz.blogspot.com
www.ozgurfx.com


On Tue, Jan 11, 2011 at 5:24 PM, Michael Bayer mike...@zzzcomputing.comwrote:


 On Jan 11, 2011, at 8:51 AM, Erkan Özgür Yılmaz wrote:

 Hi all,

 This is my first question regarding to SQLAlchemy, you can count me as a
 newbie. Here is my problem:

 I've this inheritance between my Python classes, everything were working
 fine until last night when I've added another class to my inheritance
 hierarchy. I've tried to find the cause of that pretty much read all the
 documentation about session, but couldn't find any solution.

 I'm going to try to reduce the things I'm writing here by not including all
 the attributes/methods below, so warn me if you spot something:

 This is the basic inheritance to let you figure out what is inheriting from
 what:



 There's a subtle configuration problem here that is causing the mapper to
 create the entity in the database in a way that throws it off when it goes
 to get it back.Ticket #2019 is added for a warning to be emitted
 corresponding to this condition.   linkTypes has no primary key column, so
 that when the mapper inserts the rows for LinkType, it doesn't insert into
 the final linkTypes table.Later, when you access .name,
 sound_link_type has been expired, which is the default behavior after a
 commit, and the mapper goes to load its data from all of its tables.No
 row is present in linkTypes, so the load returns nothing and the mapper
 assumes the identity key has been deleted.

 I'd also note that you probably don't need all those inherit_condition
 parameters, those are figured out automatically based on foreign keys - only
 if there are multiple ways to join between parent and child tables, and the
 mapper throws an error, do you need to use that param.


 class SimpleEntity(object):
 pass

 class Entity(SimpleEntity):
 pass

 class TypeEntity(Entity):
 pass

 class LinkType(TypeEntity):
 pass



 Here are the Tables (just the ones used with these classes, there are a lot
 other tables too, I can paste them here if you ask for):

 # SIMPLE ENTITY
 simpleEntities = Table(
 simpleEntities, metadata,
 Column(id, Integer, primary_key=True),
 Column(name, String(256), nullable=False),
 Column(description, String),

 Column(
 created_by_id,
 Integer,
 ForeignKey(users.id, use_alter=True, name=x)
 ),

 Column(
 updated_by_id,
 Integer,
 ForeignKey(users.id, use_alter=True, name=x)
 ),

 Column(date_created, DateTime),
 Column(date_updated, DateTime),
 Column(entity_type, String(128), nullable=False),
 UniqueConstraint('name', 'entity_type')

 )


 # ENTITY
 entities = Table(
 entities, metadata,
 Column(
 id,
 ForeignKey(simpleEntities.id),
 primary_key=True
 ),
 )



 # TYPEENTITIES
 typeEntities = Table(
 typeEntities, metadata,
 Column(
 id,
 Integer,
 ForeignKey(entities.id),
 primary_key=True,
 ),
 )


 # LINKTYPES
 linkTypes = Table(
 linkTypes, metadata,
 Column(
 id,
 Integer,
 ForeignKey(typeEntities.id),
 ),
 )



 Mappers:

 # SimpleEntity
 mapper(
 entity.SimpleEntity,
 tables.simpleEntities,
 properties={
 _name: tables.simpleEntities.c.name,
 name: synonym(_name),
 _description: tables.simpleEntities.c.description,
 description: synonym(_description),
 _created_by: relationship(
 user.User,
 backref=_entities_created,
 primaryjoin=tables.simpleEntities.c.created_by_id== \
 tables.users.c.id,
 post_update=True,
 uselist=False
 ),
 created_by: synonym(_created_by),
 _updated_by: relationship(
 user.User,
 backref=_entities_updated,
 primaryjoin=tables.simpleEntities.c.updated_by_id== \
 tables.users.c.id,
 post_update=True,
 uselist=False
 ),
 updated_by: synonym(_updated_by),
 _date_created: tables.simpleEntities.c.date_created,
 date_created: synonym(_date_created),
 _date_updated: tables.simpleEntities.c.date_updated,
 date_updated: synonym(_date_updated)
 },
 polymorphic_on=tables.simpleEntities.c.entity_type,
 polymorphic_identity=SimpleEntity
 )


 # Entity
 mapper(
 entity.Entity,
 tables.entities,
 inherits=entity.SimpleEntity,
 inherit_condition=tables.entities.c.id==tables.simpleEntities.c.id
 ,
 polymorphic_identity=Entity