[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
-~--~~~~--~~--~--~---
