[sqlalchemy] Re: sqlalchemy.exc.InvalidRequestError: Object 'Foo at 0x23b0690' is already attached to session '29012560' (this is '30924944')
On Sun, May 31, 2009 at 16:50, Michael Bayer mike...@zzzcomputing.com wrote: On May 30, 2009, at 1:56 AM, ericthompso...@gmail.com wrote: Upon reaching the instantiation of Foo, my code blows up with the error message in the subject. Now I realize this is often caused by threading issues - multiple threads, each with their own Session, trying to touch an entity without first merging that entity into the thread's local session. But my application is single-threaded and to the best of my knowledge I'm not creating any new sessions other than the ScopedSession that was created when I imported elixir. Even if I had attempted to create several sessions via elixir_session(), my understanding is that I would have been returned the same session because I haven't changed threads and elixir by default uses a ScopedSession. that is all correct. So why is Foo winding up in a new session (and how did it already get attached to my current session)? I haven't passed any options regarding the session in my entity definitions. I can't see anything here which would cause this. A frequent cause of this confusion is when a library makes use of the ScopedSession.mapper method which has the effect of objects being automatically added to a session without the need to say session.add(object) - but again confusion would only occur here if there is more han one session in use. FWIW, this is the case with Elixir by default. You can turn that behavior off if you want though. That explains why your new object gets attached to the current session as soon as you instanciate your class. I have no idea why it is using a different session though. -- Gaëtan de Menten http://openhex.org --~--~-~--~~~---~--~~ 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 -~--~~~~--~~--~--~---
[sqlalchemy] Re: Relation spanning multiple tables
This looks like an association proxy. Have you tried that? http://www.sqlalchemy.org/docs/05/reference/ext/associationproxy.html -- Mike Conley On Mon, Jun 1, 2009 at 11:46 PM, George Sakkis george.sak...@gmail.comwrote: I can't for the life of me figure out how to specify a relation spanning 3 tables. I think I've tried all combinations of primaryjoin, secondaryjoin, viewonly, foreign_keys, remote_dest and all that jazz, to no avail so far. If this is possible at all, there should be a sample usage at the docs or at least a recipe on the wiki since apparently it's not obvious at all. Below are some of my failed attempts; any help will be very much appreciated. George # from sqlalchemy.orm import relation, sessionmaker from sqlalchemy.ext.declarative import declarative_base from sqlalchemy import create_engine, Column, Integer, String, ForeignKey, and_ TEST = 5 def main(): c = Company() u = User() a = Address() c.employees.append(u) u.addresses.append(a) session = sessionmaker()() session.add(c) session.flush() print a.company Base = declarative_base(bind=create_engine('sqlite:///:memory:', echo=True)) class Company(Base): __tablename__ = 'company' id = Column(Integer, primary_key=True) name = Column(String(50)) employees = relation('User') class User(Base): __tablename__ = 'users' id = Column(Integer, primary_key=True) name = Column(String(50)) company_id = Column(Integer, ForeignKey(Company.id)) addresses = relation('Address') class Address(Base): __tablename__ = 'addresses' id = Column(Integer, primary_key=True) email = Column(String(50)) user_id = Column(Integer, ForeignKey(User.id)) # 1st attempt: just a primary join. Fails with # sqlalchemy.exc.ArgumentError: Could not locate any equated, locally # mapped column pairs for primaryjoin condition 'addresses.user_id = users.id # AND users.company_id = company.id' on relation Address.company. For more # relaxed rules on join conditions, the relation may be marked as viewonly=True. if TEST == 1: company = relation(Company, primaryjoin=and_(user_id==User.id, User.company_id==Company.id)) # 2nd attempt: add viewonly=True. Fails with # sqlalchemy.exc.ArgumentError: Could not determine relation direction for # primaryjoin condition 'addresses.user_id = users.id AND users.company_id = # company.id', on relation Address.company. Specify the 'foreign_keys' # argument to indicate which columns on the relation are foreign. if TEST == 2: company = relation(Company, primaryjoin=and_(user_id==User.id, User.company_id==Company.id), viewonly=True) # 3rd attempt: add foreign_keys=[user_id]. Fails with #sqlalchemy.exc.ArgumentError: Remote column 'users.id' is not part of # mapping Mapper|Company|company. Specify remote_side argument to indicate # which column lazy join condition should bind. if TEST == 3: company = relation(Company, primaryjoin=and_(user_id==User.id, User.company_id==Company.id), viewonly=True, foreign_keys=[user_id]) # 4th attempt: add remote_side=[Company.id]. Fails with # sqlalchemy.orm.exc.UnmappedColumnError: No column users.company_id is # configured on mapper Mapper|Address|addresses... if TEST == 4: company = relation(Company, primaryjoin=and_(user_id==User.id, User.company_id==Company.id), viewonly=True, foreign_keys=[user_id], remote_side=[Company.id]) # 5th attempt: try secondary table/join. Fails with: # sqlalchemy.orm.exc.UnmappedColumnError: Column 'users.id' is not available, # due to conflicting property 'id':sqlalchemy.orm.properties.ColumnProperty # object at 0x8f73bac if TEST == 5: company = relation(Company, User.__table__, primaryjoin=user_id==User.id, secondaryjoin=User.company_id==Company.id) # give up :/ if __name__ == '__main__': Base.metadata.create_all() main() --~--~-~--~~~---~--~~ 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 -~--~~~~--~~--~--~---
[sqlalchemy] Re: many-to-many relation, 'secondaryjoin' is needed as well
Oh I did it Thanks! --~--~-~--~~~---~--~~ 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 -~--~~~~--~~--~--~---
[sqlalchemy] Re: Relation spanning multiple tables
On Jun 2, 7:00 am, Mike Conley mconl...@gmail.com wrote: This looks like an association proxy. Have you tried that? http://www.sqlalchemy.org/docs/05/reference/ext/associationproxy.html I don't see how this is related. From a quick read, it seems that's a simplified way to handle many-to-many relations by hiding the association object and it requires a regular relation to be defined anyway. In my example there are two independent one-to-many relationships and the intermediate table (User) is not just an association object, it's a standalone object. George On Mon, Jun 1, 2009 at 11:46 PM, George Sakkis george.sak...@gmail.comwrote: I can't for the life of me figure out how to specify a relation spanning 3 tables. I think I've tried all combinations of primaryjoin, secondaryjoin, viewonly, foreign_keys, remote_dest and all that jazz, to no avail so far. If this is possible at all, there should be a sample usage at the docs or at least a recipe on the wiki since apparently it's not obvious at all. Below are some of my failed attempts; any help will be very much appreciated. George # from sqlalchemy.orm import relation, sessionmaker from sqlalchemy.ext.declarative import declarative_base from sqlalchemy import create_engine, Column, Integer, String, ForeignKey, and_ TEST = 5 def main(): c = Company() u = User() a = Address() c.employees.append(u) u.addresses.append(a) session = sessionmaker()() session.add(c) session.flush() print a.company Base = declarative_base(bind=create_engine('sqlite:///:memory:', echo=True)) class Company(Base): __tablename__ = 'company' id = Column(Integer, primary_key=True) name = Column(String(50)) employees = relation('User') class User(Base): __tablename__ = 'users' id = Column(Integer, primary_key=True) name = Column(String(50)) company_id = Column(Integer, ForeignKey(Company.id)) addresses = relation('Address') class Address(Base): __tablename__ = 'addresses' id = Column(Integer, primary_key=True) email = Column(String(50)) user_id = Column(Integer, ForeignKey(User.id)) # 1st attempt: just a primary join. Fails with # sqlalchemy.exc.ArgumentError: Could not locate any equated, locally # mapped column pairs for primaryjoin condition 'addresses.user_id = users.id # AND users.company_id = company.id' on relation Address.company. For more # relaxed rules on join conditions, the relation may be marked as viewonly=True. if TEST == 1: company = relation(Company, primaryjoin=and_(user_id==User.id, User.company_id==Company.id)) # 2nd attempt: add viewonly=True. Fails with # sqlalchemy.exc.ArgumentError: Could not determine relation direction for # primaryjoin condition 'addresses.user_id = users.id AND users.company_id = # company.id', on relation Address.company. Specify the 'foreign_keys' # argument to indicate which columns on the relation are foreign. if TEST == 2: company = relation(Company, primaryjoin=and_(user_id==User.id, User.company_id==Company.id), viewonly=True) # 3rd attempt: add foreign_keys=[user_id]. Fails with #sqlalchemy.exc.ArgumentError: Remote column 'users.id' is not part of # mapping Mapper|Company|company. Specify remote_side argument to indicate # which column lazy join condition should bind. if TEST == 3: company = relation(Company, primaryjoin=and_(user_id==User.id, User.company_id==Company.id), viewonly=True, foreign_keys=[user_id]) # 4th attempt: add remote_side=[Company.id]. Fails with # sqlalchemy.orm.exc.UnmappedColumnError: No column users.company_id is # configured on mapper Mapper|Address|addresses... if TEST == 4: company = relation(Company, primaryjoin=and_(user_id==User.id, User.company_id==Company.id), viewonly=True, foreign_keys=[user_id], remote_side=[Company.id]) # 5th attempt: try secondary table/join. Fails with: # sqlalchemy.orm.exc.UnmappedColumnError: Column 'users.id' is not available, # due to conflicting property 'id':sqlalchemy.orm.properties.ColumnProperty # object at 0x8f73bac if TEST == 5: company = relation(Company, User.__table__, primaryjoin=user_id==User.id, secondaryjoin=User.company_id==Company.id) # give up :/ if __name__ == '__main__': Base.metadata.create_all() main() --~--~-~--~~~---~--~~ You received this
[sqlalchemy] declarative defer error
Hello, error in declarative query with defer class ClassDefaults(dec.DeclarativeMeta): def __init__(cls, classname, bases, dict_): dict_['id'] = Fdb.Unicode(Adb.Length(55), Adb.Primary_Key(True), Adb.Unique(True), Adb.Nullable(False)) dict_['status'] = Fdb.Unicode(Adb.Length(10), Adb.Index(True), Adb.Nullable(False), Adb.Default(u'ACTIVO')) dict_['_created_at']= Fdb.DateTime(Adb.Index(True), Adb.Nullable(False), Adb.Default(datetime.now)) dict_['_updated_at']= Fdb.DateTime(Adb.Index(True), Adb.Nullable(False), Adb.Default(datetime.now), Adb.Onupdate (datetime.now)) dict_['_unit_code'] = Fdb.Unicode(Adb.Length(20), Adb.Index(True), Adb.Nullable(False), Adb.Default(u'*')) dict_['_organization_code'] = Fdb.Unicode(Adb.Length(20), Adb.Index(True), Adb.Nullable(False), Adb.Default(u'*')) return dec.DeclarativeMeta.__init__(cls, classname, bases, dict_) BaseDefaults = declarative_base(metaclass=ClassDefaults) BaseDefaults.metadata.bind = dataClass_active_mail.engine Base = dec.declarative_base() Base.metadata.bind = dataClass_active_mail.engine class Usuario(BaseDefaults): __tablename__ = 'd_users' __ID__ = id __classname__ = 'Usuario' __dataName__ = 'datos de usuarios' __dataType__ = 'APLICACION' __mapper_args__ = {'extension': [defaultValueTracker(), cacheValueTracker()]} code = Fdb.Unicode(Adb.Length(55), Adb.Index(True), Adb.Nullable (False)) name = Fdb.Unicode(Adb.Length(120), Adb.Nullable(False)) password = Fdb.Unicode(Adb.Length(20), Adb.Nullable(False)) email= Fdb.Unicode(Adb.Length(50)) role_id = Fdb.Unicode(Adb.Length(55), Adb.Index(True), Adb.Nullable (False), Adb.Default(u'')) sitio_id = Fdb.Unicode(Adb.Length(55), Adb.Index(True), Adb.Nullable (False), Adb.Default(u'')) area_id = Fdb.Unicode(Adb.Length(55), Adb.Index(True), Adb.Nullable (False), Adb.Default(u''), Adb.ForeignKey('d_seccion.id')) group_id = Fdb.Unicode(Adb.Length(120), Adb.Index(True), Adb.Nullable(False), Adb.Default(u'')) Index('UsuarioUnique', Usuario.__table__.c.code, Usuario.__table__.c._unit_code, Usuario.__table__.c._organization_code, unique=True) session = dySession() q = session.query(Usuario) q = q.options(sqlalchemy.orm.defer('name')) print q print q.all() --- print result --- SELECT d_users.code AS d_users_code, d_users.password AS d_users_password, d_use rs.email AS d_users_email, d_users.role_id AS d_users_role_id, d_users.sitio_id AS d_users_sitio_id, d_users.area_id AS d_users_area_id, d_users.group_id AS d_u sers_group_id, d_users.id AS d_users_id, d_users.status AS d_users_status, d_use rs._created_at AS d_users__created_at, d_users._updated_at AS d_users__updated_a t, d_users._unit_code AS d_users__unit_code, d_users._organization_code AS d_use rs__organization_code FROM d_users Traceback (most recent call last): File start.py, line 31, in module globalStart.defServer('activedocument') File C:\Acappella_2Zero\libs\utils\globalStart.py, line 56, in defServer run = __import__(v + '.run') File C:\Acappella_2Zero\projects\activemail\run.py, line 629, in module print q.all() File C:\Acappella_2Zero\thirdparty\sqlalchemy\orm\query.py, line 1193, in al l return list(self) File C:\Acappella_2Zero\thirdparty\sqlalchemy\orm\query.py, line 1346, in in stances process[0](context, row, rows) File C:\Acappella_2Zero\thirdparty\sqlalchemy\orm\query.py, line 1947, in ma in _instance(row, result) File C:\Acappella_2Zero\thirdparty\sqlalchemy\orm\mapper.py, line 1612, in _ instance identitykey = identity_key(row) File C:\Acappella_2Zero\thirdparty\sqlalchemy\orm\mapper.py, line 1553, in i dentity_key return (identity_class, tuple(row[column] for column in pk_cols)) File C:\Acappella_2Zero\thirdparty\sqlalchemy\orm\mapper.py, line 1553, in genexpr return (identity_class, tuple(row[column] for column in pk_cols)) File C:\Acappella_2Zero\thirdparty\sqlalchemy\engine\base.py, line 1348, in __getitem__ return self.__parent._get_col(self.__row, key) File C:\Acappella_2Zero\thirdparty\sqlalchemy\engine\base.py, line 1609, in _get_col type_, processor, index = self._props[key] File C:\Acappella_2Zero\thirdparty\sqlalchemy\util.py, line 68, in __missing __ self[key] = val = self.creator(key) File C:\Acappella_2Zero\thirdparty\sqlalchemy\engine\base.py, line 1507, in fallback raise exc.NoSuchColumnError(Could not locate column in row for column '%s' % (str(key))) sqlalchemy.exc.NoSuchColumnError: Could not locate column in row for column 'd_ users.name' Thank´s --~--~-~--~~~---~--~~ 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
[sqlalchemy] Re: declarative defer error
quirogaco wrote: File C:\Acappella_2Zero\thirdparty\sqlalchemy\orm\mapper.py, line 1612, in _ instance identitykey = identity_key(row) File C:\Acappella_2Zero\thirdparty\sqlalchemy\orm\mapper.py, line 1553, in i dentity_key return (identity_class, tuple(row[column] for column in pk_cols)) File C:\Acappella_2Zero\thirdparty\sqlalchemy\orm\mapper.py, line 1553, in genexpr return (identity_class, tuple(row[column] for column in pk_cols)) File C:\Acappella_2Zero\thirdparty\sqlalchemy\engine\base.py, line 1348, in __getitem__ return self.__parent._get_col(self.__row, key) File C:\Acappella_2Zero\thirdparty\sqlalchemy\engine\base.py, line 1609, in _get_col type_, processor, index = self._props[key] File C:\Acappella_2Zero\thirdparty\sqlalchemy\util.py, line 68, in __missing __ self[key] = val = self.creator(key) File C:\Acappella_2Zero\thirdparty\sqlalchemy\engine\base.py, line 1507, in fallback raise exc.NoSuchColumnError(Could not locate column in row for column '%s' % (str(key))) sqlalchemy.exc.NoSuchColumnError: Could not locate column in row for column 'd_ users.name' your code example doesn't illustrate this (and is also hard to understand since it uses all kinds of methods to which nobody outside of your organization could be familiar with, such as Fdb.Unicode(...Adb.Primary_Key(True))) but the stack trace reveals that name is considered to be a primary key by the mapper. Primary key columns can't be deferred. --~--~-~--~~~---~--~~ 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 -~--~~~~--~~--~--~---
[sqlalchemy] deletes using association_proxy
I have a many-to-many schema using an association object and the association proxy. I'm able to add data via the ORM, but trying to delete from the association (but not delete the left or right tables) throws a AssertionError: Dependency rule tried to blank-out primary key column 'keyphrase_action.keyphrase_id' on instance 'KeyphraseAction at 0x9da7d8c' error. My test code is below. Any advice is appreciated! import os, sys from sqlalchemy import Column, Integer, String, Table, create_engine, schema, types from sqlalchemy import orm, MetaData, Column, ForeignKey from sqlalchemy.orm import relation, mapper, sessionmaker from sqlalchemy.ext.associationproxy import association_proxy engine = create_engine('sqlite:home/aw/desktop/test.db', echo=True) meta = MetaData(bind=engine) # schema keyphrase_table = schema.Table('keyphrase', meta.metadata, schema.Column('id', types.Integer, primary_key = True, autoincrement = True), schema.Column('phrase', types.String(160), nullable = False), ) action_table = schema.Table('action', meta.metadata, schema.Column('id', types.Integer, primary_key = True, autoincrement = True), schema.Column('action_name', types.Text, nullable = False), ) keyphrase_action_table = schema.Table('keyphrase_action', meta.metadata, schema.Column('keyphrase_id', types.Integer, schema.ForeignKey ('keyphrase.id'), primary_key = True), schema.Column('action_id', types.Integer, schema.ForeignKey ('action.id'), primary_key = True), schema.Column('is_deferred', types.Boolean, nullable = False), ) meta.create_all() # classes class Keyphrase(object): def __init__(self, phrase): self.phrase = phrase # creator function def _getKeyphraseAction(d): return KeyphraseAction(action = d['action'], isDeferred = d ['isDeferred']) actions = association_proxy('keyphrase_action', 'action', creator = _getKeyphraseAction) class Action(object): def __init__(self, name): self.action_name = name class KeyphraseAction(object): def __init__(self, keyphrase = None, action = None, isDeferred = False): self.keyphrase = keyphrase self.action = action self.is_deferred = isDeferred # mappers mapper(Keyphrase, keyphrase_table) mapper(Action, action_table) mapper(KeyphraseAction, keyphrase_action_table, properties={ 'keyphrase': relation(Keyphrase, backref = 'keyphrase_action'), 'action': relation(Action), }) # test code Session = sessionmaker(bind=engine) s = Session() # add some data kp = Keyphrase('fast') a = Action('capture_email') s.add(kp) s.add(a) s.commit() # assciate the keyphrase to the action kp.actions.append({'action':a,'isDeferred':True}) s.commit() #remove the newly created association, leaving the keyphrase and actions kp = s.query(Keyphrase).get(kp.id) for i, action in enumerate(kp.actions): print action.action_name kp.actions.remove(action) # this fails! s.commit() --~--~-~--~~~---~--~~ 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 -~--~~~~--~~--~--~---