[sqlalchemy] Re: problem with backref
Michael Bayer ha scritto:
the original mapping essentially expressed this relationship:
A -- cascade=all, delete-orphan -- B
in both directions. What that means is, there cannot be an A with no B
in the database, and there cannot be a B with no A in the database,
i.e. its either A-B or both will be deleted. its like an oxygen
molecule, or something.
what you need to do is decide which of A and B can exist on its own
without a parent relationship. since you want to delete rows from B
and not A, that would indicate that the mapping should be:
mapper(A, a)
mapper(
B, b,
properties={
'a': relation(
A, backref=backref('b', lazy=False, uselist=False,
cascade='all, delete-orphan'),
uselist=False
)
}
)
i.e. the delete-orphan cascade is only in the direction from A-B.
the cascade from B-A is left at its default value of save-update, so
delete operations dont propigate from B's to A's.
Sorry, I still have problems.
Here is the code I'm using:
from sqlalchemy import *
db = create_engine('postgres://manlio:[EMAIL PROTECTED]/test', echo=True)
metadata = BoundMetaData(db)
a = Table(
'a', metadata,
Column('x', String),
Column('y', String),
PrimaryKeyConstraint('x', 'y')
)
b = Table(
'b', metadata,
Column('x', String),
Column('y', String),
Column('z', String),
PrimaryKeyConstraint('x', 'y'),
ForeignKeyConstraint(['x', 'y'], ['a.x', 'a.y'], ondelete='CASCADE')
)
metadata.create_all()
class A(object):
def __init__(self, x, y):
self.x = x
self.y = y
class B(object):
def __init__(self, x, y, z):
self.x = x
self.y = y
self.z = z
mapper(A, a)
mapper(
B, b,
properties={
'a': relation(
A, backref=backref('b', lazy=False, uselist=False,
cascade='all, delete-orphan'),
uselist=False
)
}
)
try:
1
conn = db.connect()
trans = conn.begin()
sess = create_session(bind_to=conn)
o = A('1', '2')
o.b = B(o.x, o.y, '3')
sess.save(o)
sess.flush()
sess.close()
trans.commit()
conn.close()
2
conn = db.connect()
trans = conn.begin()
sess = create_session(bind_to=conn)
sess.update(o)
print o.b.z
del o.b
#sess.delete(o.b)
#o.b = None
sess.flush()
sess.close()
trans.commit()
conn.close()
3
print o.b
s = a.select()
print conn.execute(s).fetchall()
finally:
metadata.drop_all(tables=[b])
metadata.drop_all(tables=[a])
The error is:
Traceback (most recent call last):
File alchemy.py, line 82, in ?
print o.b
File /usr/lib/python2.4/site-packages/sqlalchemy/orm/attributes.py,
line 48, in __get__
return self.get(obj)
File /usr/lib/python2.4/site-packages/sqlalchemy/orm/attributes.py,
line 205, in get
value = callable_()
File /usr/lib/python2.4/site-packages/sqlalchemy/orm/strategies.py,
line 204, in lazyload
raise exceptions.InvalidRequestError(Parent instance %s is not
bound to a Session, and no contextual session is established; lazy load
operation of attribute '%s' cannot proceed % (instance.__class__,
self.key))
sqlalchemy.exceptions.InvalidRequestError: Parent instance class
'__main__.A' is not bound to a Session, and no contextual session is
established; lazy load operation of attribute 'b' cannot proceed
Thanks and regards Manlio Perillo
--~--~-~--~~~---~--~~
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 [EMAIL PROTECTED]
For more options, visit this group at
http://groups.google.com/group/sqlalchemy?hl=en
-~--~~~~--~~--~--~---
[sqlalchemy] Additions to model not getting flushed to DB?? (Pylons)
I'm doing a Pylons app and must be doing something stupid because
similar logic seems to work elsewhere, but this just
isn't sticking in the DB. I query to get existing 'contacts',
they're empty so I create a new one, append it, save and flush, but it
doesn't show up in the diagnostic queries I do. Perhaps I'm not
seeing something obvious, any suggestions?
contacts =
self.session.query(model.Contact).select_by(client_id=client.client_id)
print #vendor.upload DBQ contacts=, contacts
contacts = client.contacts
print #vendor.upload DOT contacts=, contacts
contact = model.Contact(client_id=client.client_id,
email=row['contactemail'],
password=row.get('contactpassword'),
firstname=row.get('contactfirstname'),
lastname=row.get('contactlastname'),
)
print #vendor.upload New contact=, contact
print #vendor.upload Query contacts before append=,
self.session.query(model.Contact).select()
contacts.append(contact)
print #vendor.upload Query contacts after append=,
self.session.query(model.Contact).select()
self.session.save(contact)
print #vendor.upload Query contacts aftersave=,
self.session.query(model.Contact).select()
self.session.flush()
print #vendor.upload Query contacts after flush=,
self.session.query(model.Contact).select()
When I run it, you see it creates a new one but then none of the
queries find the contact from a query. An SQL query against the MySQL
DB show's it's not their either.
#vendor.upload DBQ contacts= []
#vendor.upload DOT contacts= []
#vendor.upload New contact= Contact(None,9,[EMAIL PROTECTED],None,None,None
[None, None])
#vendor.upload Query contacts before save= []
#vendor.upload Query contacts after save= []
#vendor.upload Query contacts after flush= []
The client and contact model and relations look like:
client_table = Table('client', metadata,
Column('client_id',Integer,
primary_key=True),
Column('vendor_id',Integer,
ForeignKey('vendor.vendor_id'), nullable=False),
Column('name', String(40), unique=True,
nullable=False),
Column('ts_created', DateTime,
default=func.current_timestamp()),
Column('ts_updated', DateTime,
onupdate=func.current_timestamp()),
)
mapper(Client, client_table,
properties={'contacts': relation(Contact, lazy=False),
'systems': relation(System, lazy=False),
})
contact_table = Table('contact', metadata,
Column('contact_id',Integer,
primary_key=True),
Column('client_id', Integer,
ForeignKey('client.client_id'), nullable=False),
Column('email', String(80),
unique=True, nullable=False),
Column('password', String(40),
nullable=False),
Column('firstname', String(40),
nullable=True),
Column('lastname', String(40),
nullable=True),
Column('ts_created', DateTime,
default=func.current_timestamp()),
Column('ts_updated', DateTime,
onupdate=func.current_timestamp()),
)
Any ideas? 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 [EMAIL PROTECTED]
For more options, visit this group at
http://groups.google.com/group/sqlalchemy?hl=en
-~--~~~~--~~--~--~---
[sqlalchemy] Re: table_foo.c.column_foo.autoincrement returning True always
the flag only applies to primary key integer columns, otherwise is ignored. while we might want to add that logic to the Table class, it would be tricky because whether or not autoincrement is available has some dialect-specific dependencies (such as, Postgres can do BIGSERIAL on a BigInteger but cant do serial on a SmallInteger, both of which are integers). --~--~-~--~~~---~--~~ 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 [EMAIL PROTECTED] For more options, visit this group at http://groups.google.com/group/sqlalchemy?hl=en -~--~~~~--~~--~--~---
[sqlalchemy] Re: Additions to model not getting flushed to DB?? (Pylons)
Doh, I should have turned on the SQL echo. Now that I have, it's even
more puzzling to me. It shows the 'contact' being inserted and
committed -- but it never appears in MySQL!
2006-12-23 17:33:15,140 INFO sqlalchemy.engine.base.Engine.0x..cc BEGIN
2006-12-23 17:33:15,145 INFO sqlalchemy.engine.base.Engine.0x..cc SELECT
current_timestamp
2006-12-23 17:33:15,145 INFO sqlalchemy.engine.base.Engine.0x..cc []
2006-12-23 17:33:15,150 INFO sqlalchemy.engine.base.Engine.0x..cc INSERT INTO
contact (client_id, email, password, firstname, lastname, ts_created,
ts_updated) VALUES (%s, %s, %s, %s, %s, %s, %s)
2006-12-23 17:33:15,150 INFO sqlalchemy.engine.base.Engine.0x..cc [9L, '[EMAIL
PROTECTED]', None, None, None, datetime.datetime(2006, 12, 23, 17, 33, 44),
None]
2006-12-23 17:33:15,158 INFO sqlalchemy.engine.base.Engine.0x..cc COMMIT
#vendor.upload Query contacts after flush=[]
If I switch my Pylons development.ini to use an SQLite DB, the app
works as expected until I get to the same flush():
contact = model.Contact(client_id=client.client_id,
email=row['contactemail'],
password=row.get('contactpassword'),
firstname=row.get('contactfirstname'),
lastname=row.get('contactlastname'))
self.session.save(contact)
self.session.flush()
The Pylons traceback reports:
self.session.flush()
...
sqlalchemy.exceptions.SQLError: (OperationalError) SQL logic error or missing database 'INSERT INTO contact (client_id, email, password, firstname, lastname, ts_created, ts_updated) VALUES (?, ?, ?, ?, ?, ?, ?)' [1, '[EMAIL PROTECTED]', None, None, None, '2006-12-24 00:25:35', None]
The SQL echo looks good, until the ROLLBACK:
2006-12-23 19:25:35,874 INFO sqlalchemy.engine.base.Engine.0x..0c BEGIN
2006-12-23 19:25:35,878 INFO sqlalchemy.engine.base.Engine.0x..0c SELECT
current_timestamp
2006-12-23 19:25:35,878 INFO sqlalchemy.engine.base.Engine.0x..0c []
2006-12-23 19:25:35,879 INFO sqlalchemy.engine.base.Engine.0x..0c INSERT
INTO contact (client_id, email, password, firstname, lastname, ts_created,
ts_updated) VALUES (?, ?, ?, ?, ?, ?, ?)
2006-12-23 19:25:36,031 INFO sqlalchemy.engine.base.Engine.0x..0c [1,
'[EMAIL PROTECTED]', None, None, None, '2006-12-24 00:25:35', None]
2006-12-23 19:25:36,086 INFO sqlalchemy.engine.base.Engine.0x..0c ROLLBACK
That self.session is created with a Pylons __before__ method:
def __before__(self):
self.session = session_context.current
self.q = self.session.query(model.Vendor)
and other methods use this just fine as far as I can tell.
How can I track down whether there's a missing database or what the
SQL logic error might be?
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 [EMAIL PROTECTED]
For more options, visit this group at
http://groups.google.com/group/sqlalchemy?hl=en
-~--~~~~--~~--~--~---
[sqlalchemy] Re: problem with backref
dont access the unloaded b attribute on o unless it is still attached to its session. when you sess.close(), o is no longer attached to a session. also, dont say del o.b, or insure that you reassign a blank list to o.b. before accessing it again. the del operation deletes the list-based attribute entirely and reverts access on it to its class-based lazy load status, which is why the lazy loader is firing off again. --~--~-~--~~~---~--~~ 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 [EMAIL PROTECTED] For more options, visit this group at http://groups.google.com/group/sqlalchemy?hl=en -~--~~~~--~~--~--~---
[sqlalchemy] Re: Additions to model not getting flushed to DB?? (Pylons)
seems like Pylons is not properly maintaining the session within requests. unless there is some way you can reproduce this problem without any dependencies on pylons ? is this using the newest SA/Pylons code that Ben was recently working on ? --~--~-~--~~~---~--~~ 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 [EMAIL PROTECTED] For more options, visit this group at http://groups.google.com/group/sqlalchemy?hl=en -~--~~~~--~~--~--~---
[sqlalchemy] Re: problem with backref
im sorry, i meant a scalar value of None, not a blank list,since you have uselist=False. Assign None to o.b instead of saying del o.b. --~--~-~--~~~---~--~~ 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 [EMAIL PROTECTED] For more options, visit this group at http://groups.google.com/group/sqlalchemy?hl=en -~--~~~~--~~--~--~---
[sqlalchemy] Re: Additions to model not getting flushed to DB?? (Pylons)
Michael Bayer [EMAIL PROTECTED] writes: seems like Pylons is not properly maintaining the session within requests. Yeah, it sure feels like that. :-( unless there is some way you can reproduce this problem without any dependencies on pylons ? is this using the newest SA/Pylons code that Ben was recently working on ? Hrmmm, I'm not sure ... I am not on the bleeding edge with nightly easy_install -U *=dev :-) I'll see if I can reproduce in a small scale in Pylons; if so, I'll try reproducing in standalone SA. Thanks for the pointers. --~--~-~--~~~---~--~~ 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 [EMAIL PROTECTED] For more options, visit this group at http://groups.google.com/group/sqlalchemy?hl=en -~--~~~~--~~--~--~---
[sqlalchemy] Re: Additions to model not getting flushed to DB?? (Pylons)
On Dec 23, 2006, at 8:56 PM, Chris Shenton wrote: Michael Bayer [EMAIL PROTECTED] writes: seems like Pylons is not properly maintaining the session within requests. Yeah, it sure feels like that. :-( unless there is some way you can reproduce this problem without any dependencies on pylons ? is this using the newest SA/Pylons code that Ben was recently working on ? Hrmmm, I'm not sure ... I am not on the bleeding edge with nightly easy_install -U *=dev :-) Pylons trunk (dev) provides a session_context out of the box, but the 0.9.3 release (which it sounds like you're using) does not. Showing how your session_context variable is created might be helpful. I saw a prompt in your last email: self.session.flush() Are you using paster shell, or the interactive debugger? Otherwise nothing looks out of the ordinary minus the fact you're not seeing your data. Obvious question: are you seeing the changes made to the database after the commit? -- Philip Jenvey --~--~-~--~~~---~--~~ 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 [EMAIL PROTECTED] For more options, visit this group at http://groups.google.com/group/sqlalchemy?hl=en -~--~~~~--~~--~--~---
