Re: [sqlalchemy] Automatically set primary key to None when deleted?
On Wed, Jul 2, 2014 at 9:22 PM, Mike Bayer mike...@zzzcomputing.com wrote: On 7/2/14, 10:05 PM, Paul Molodowitch wrote: Suppose I have a super simple table like this: class Dinosaur(Base): __tablename__ = 'dinosaurs' id = Column(Integer, primary_key=True) name = Column(String(255)) We assume that the id is set up in such a way that by default it always gets a unique value - ie, it uses autoincrement in MySQL, or a sequence in postgres, etc. Now, suppose I get an instance of this class, and then delete it: steggy = session.query(Dinosaur).filter_by(name='Steggy').one() print steggy.id session.delete(steggy) session.commit() print steggy.id What I'd ideally like to see is that it first print the id of the row that it pulled from the database, and then print 'None': 30 None Is there any way that I can configure the id column / property so that it is automatically cleared on delete like this? the steggy object is a proxy for a database row. when you delete that row, then commit the transaction, the object is detached from the session, and everything on it is expired. there is no row. check inspect(steggy).deleted, it will say True - that means in your system, the object is meaningless. ideally no part of your program would be looking at that proxy any more, you should throw it away. it means nothing. That makes sense... but if if it really means nothing, and we shouldn't be looking at it, then why keep it's attributes around at all? Particularly since sqlalchemy has already established that it's willing to expire dict members when they may not be valid anymore - ie, what it does to clear any cached values from a row proxy after the session is committed. Of course, you could make the case that other pieces of the program may want to inspect the data that was on there, after the fact... maybe you're going to print out something that says, RIP Steggy, or something - but in that case, the one field that really DOESN'T make any sense in this case (and it seems like it would be a common pattern!) is the one that exists solely as a means to look it up in the database, it's auto-incremented id column. Which is what prompted this question... If not, as a consolation prize, I'd also be interested in the easiest way to query if a given instance exists in the database - ie, I could do something like: session.exists(steggy) OR steggy.exists() from sqlalchemy import inspect def exists(session, obj): state = inspect(obj) return session.query(state.mapper).get(state.identity) is None print exists(sess, a1) Hmm... very interesting. I'll have to read up what what exactly this is doing (ie, what is state.identity?)... It's possibly that inspect(steggy).deleted may just give me what I need though. Thanks for both those tips! (In case you couldn't tell, I'm still new to / exploring sqlalchemy...) ...which, in this case, would simply run a query to see if any dinosaurs exist with the name Steggy. that's totally different. That's a WHERE criterion on the name field, which is not the primary key. that's something specific to your class there. True. There's really no way for a generic exists function to know what conditions you want to query a generic class on to determine existence. Which is why I was suggesting the uniqueness constraint... Needing to set up some extra parameters to make this possible - such as adding a unique constraint on the name column - OK, so you want a function that a. receives an object b. looks for UNIQUE constraints on it c. queries by those unique constraints (I guess you want the first one present? not clear. a table can have a lot of unique constraints on it) that would be: Sort of - the thinking here was that you could just ask, If I tried to insert this object into the table, would it violate any uniqueness constraints?, and get back a boolean result... and you could use that as a reasonable heuristic for determining existence, in a fairly generic way. from sqlalchemy import inspect, UniqueConstraint def exists(session, obj): state = inspect(obj) table = state.mapper.local_table for const in table.constraints: if isinstance(const, UniqueConstraint): crit = and_(*[col == getattr(obj, col.key) for col in const]) return session.query(state.mapper).filter(crit).count() 0 else: return False Yep, it looks like that's doing basically what I was thinking of. Thanks! the unique constraints are a set though. not necessarily deterministic which one it would locate first. I'd use more of some kind of declared system on the class: Not clear on why this matters - if we're iterating through all the constraints, and returning True if any of them is matched, what difference does it make which one is evaluated first? Except potentially from a performance standpoint, I suppose... class X(Base):
Re: [sqlalchemy] Automatically set primary key to None when deleted?
On 7/3/14, 1:01 PM, Paul Molodowitch wrote: That makes sense... but if if it really means nothing, and we shouldn't be looking at it, then why keep it's attributes around at all? because it is an additional step to actually erase the attributes and just hadn't been considered. Particularly since sqlalchemy has already established that it's willing to expire dict members when they may not be valid anymore - ie, what it does to clear any cached values from a row proxy after the session is committed. well it doesn't expire the deleted object right now because it's been evicted from the Session by the time the commit goes to expire things. Changing that behavior now would definitely bite a lot of people who depend on how it is right now (other people who are also looking at their deleted objects against my recommendations... :) ) Of course, you could make the case that other pieces of the program may want to inspect the data that was on there, after the fact... maybe you're going to print out something that says, RIP Steggy, or something - but in that case, the one field that really DOESN'T make any sense in this case (and it seems like it would be a common pattern!) is the one that exists solely as a means to look it up in the database, it's auto-incremented id column. Which is what prompted this question... well all the cols don't exist anymore, not just the primary key. the inspect(obj).deleted call does allow this information (that the object was deleted) to be known, though not very transparently. from sqlalchemy import inspect def exists(session, obj): state = inspect(obj) return session.query(state.mapper).get(state.identity) is None print exists(sess, a1) Hmm... very interesting. I'll have to read up what what exactly this is doing (ie, what is state.identity?)... it's documented here: http://docs.sqlalchemy.org/en/rel_0_9/orm/internals.html?highlight=instancestate#sqlalchemy.orm.state.InstanceState.identity from sqlalchemy import inspect, UniqueConstraint def exists(session, obj): state = inspect(obj) table = state.mapper.local_table for const in table.constraints: if isinstance(const, UniqueConstraint): crit = and_(*[col == getattr(obj, col.key) for col in const]) return session.query(state.mapper).filter(crit).count() 0 else: return False Yep, it looks like that's doing basically what I was thinking of. Thanks! the unique constraints are a set though. not necessarily deterministic which one it would locate first. I'd use more of some kind of declared system on the class: Not clear on why this matters - if we're iterating through all the constraints, and returning True if any of them is matched, what difference does it make which one is evaluated first? Except potentially from a performance standpoint, I suppose... what if there are two constraints, and only one is satisfied for a given object's values (e.g. the constraint is now satisfied by some other row), the other one is not present. Is the answer True or False? -- You received this message because you are subscribed to the Google Groups sqlalchemy group. To unsubscribe from this group and stop receiving emails from it, send an email to sqlalchemy+unsubscr...@googlegroups.com. To post to this group, send email to sqlalchemy@googlegroups.com. Visit this group at http://groups.google.com/group/sqlalchemy. For more options, visit https://groups.google.com/d/optout.
Re: [sqlalchemy] Automatically set primary key to None when deleted?
Particularly since sqlalchemy has already established that it's willing to expire dict members when they may not be valid anymore - ie, what it does to clear any cached values from a row proxy after the session is committed. well it doesn't expire the deleted object right now because it's been evicted from the Session by the time the commit goes to expire things. Changing that behavior now would definitely bite a lot of people who depend on how it is right now (other people who are also looking at their deleted objects against my recommendations... :) ) Makes sense - maybe it could be a configurable option? Dunno how many people (besides me!) would be interested in such behavior, though... =P from sqlalchemy import inspect def exists(session, obj): state = inspect(obj) return session.query(state.mapper).get(state.identity) is None print exists(sess, a1) Hmm... very interesting. I'll have to read up what what exactly this is doing (ie, what is state.identity?)... it's documented here: http://docs.sqlalchemy.org/en/rel_0_9/orm/internals.html?highlight=instancestate#sqlalchemy.orm.state.InstanceState.identity Thanks for the link! the unique constraints are a set though. not necessarily deterministic which one it would locate first. I'd use more of some kind of declared system on the class: Not clear on why this matters - if we're iterating through all the constraints, and returning True if any of them is matched, what difference does it make which one is evaluated first? Except potentially from a performance standpoint, I suppose... what if there are two constraints, and only one is satisfied for a given object's values (e.g. the constraint is now satisfied by some other row), the other one is not present. Is the answer True or False? In the scenario I was envisioning, True (ie, it exists). Basically, Would it violate ANY unique constraints if I tried to insert it? Yes. Of course, I see your point: that in some situations, this might not fit conceptually with the answer to the question, Does THIS object exist in the database? But I guess that's likely your point... that there isn't really a good universal way to answer that question. -- You received this message because you are subscribed to the Google Groups sqlalchemy group. To unsubscribe from this group and stop receiving emails from it, send an email to sqlalchemy+unsubscr...@googlegroups.com. To post to this group, send email to sqlalchemy@googlegroups.com. Visit this group at http://groups.google.com/group/sqlalchemy. For more options, visit https://groups.google.com/d/optout.
[sqlalchemy] Automatically set primary key to None when deleted?
Suppose I have a super simple table like this: class Dinosaur(Base): __tablename__ = 'dinosaurs' id = Column(Integer, primary_key=True) name = Column(String(255)) We assume that the id is set up in such a way that by default it always gets a unique value - ie, it uses autoincrement in MySQL, or a sequence in postgres, etc. Now, suppose I get an instance of this class, and then delete it: steggy = session.query(Dinosaur).filter_by(name='Steggy').one() print steggy.id session.delete(steggy) session.commit() print steggy.id What I'd ideally like to see is that it first print the id of the row that it pulled from the database, and then print 'None': 30 None Is there any way that I can configure the id column / property so that it is automatically cleared on delete like this? If not, as a consolation prize, I'd also be interested in the easiest way to query if a given instance exists in the database - ie, I could do something like: session.exists(steggy) OR steggy.exists() ...which, in this case, would simply run a query to see if any dinosaurs exist with the name Steggy. Needing to set up some extra parameters to make this possible - such as adding a unique constraint on the name column - would be potentially possible. And yes, I know I can always fall back on just manually constructing a query against the name field myself... -- You received this message because you are subscribed to the Google Groups sqlalchemy group. To unsubscribe from this group and stop receiving emails from it, send an email to sqlalchemy+unsubscr...@googlegroups.com. To post to this group, send email to sqlalchemy@googlegroups.com. Visit this group at http://groups.google.com/group/sqlalchemy. For more options, visit https://groups.google.com/d/optout.
Re: [sqlalchemy] Automatically set primary key to None when deleted?
On 7/2/14, 10:05 PM, Paul Molodowitch wrote:
Suppose I have a super simple table like this:
class Dinosaur(Base):
__tablename__ = 'dinosaurs'
id = Column(Integer, primary_key=True)
name = Column(String(255))
We assume that the id is set up in such a way that by default it
always gets a unique value - ie, it uses autoincrement in MySQL, or a
sequence in postgres, etc.
Now, suppose I get an instance of this class, and then delete it:
steggy = session.query(Dinosaur).filter_by(name='Steggy').one()
print steggy.id
session.delete(steggy)
session.commit()
print steggy.id
What I'd ideally like to see is that it first print the id of the row
that it pulled from the database, and then print 'None':
30
None
Is there any way that I can configure the id column / property so that
it is automatically cleared on delete like this?
the steggy object is a proxy for a database row. when you delete that
row, then commit the transaction, the object is detached from the
session, and everything on it is expired. there is no row. check
inspect(steggy).deleted, it will say True - that means in your system,
the object is meaningless. ideally no part of your program would be
looking at that proxy any more, you should throw it away. it means nothing.
as far as setting everything to None, you could try a handler like this:
@event.listens_for(Session, 'after_flush')
def after_flush(sess, ctx):
for obj in sess.deleted:
mapper = inspect(obj)
for key in mapper.attrs.keys():
obj.__dict__[key] = None
If not, as a consolation prize, I'd also be interested in the easiest
way to query if a given instance exists in the database - ie, I could do
something like:
session.exists(steggy)
OR
steggy.exists()
from sqlalchemy import inspect
def exists(session, obj):
state = inspect(obj)
return session.query(state.mapper).get(state.identity) is None
print exists(sess, a1)
...which, in this case, would simply run a query to see if any
dinosaurs exist with the name Steggy.
that's totally different. That's a WHERE criterion on the name field,
which is not the primary key. that's something specific to your class
there.
Needing to set up some extra parameters to make this possible - such
as adding a unique constraint on the name column -
OK, so you want a function that a. receives an object b. looks for
UNIQUE constraints on it c. queries by those unique constraints (I guess
you want the first one present? not clear. a table can have a lot of
unique constraints on it) that would be:
from sqlalchemy import inspect, UniqueConstraint
def exists(session, obj):
state = inspect(obj)
table = state.mapper.local_table
for const in table.constraints:
if isinstance(const, UniqueConstraint):
crit = and_(*[col == getattr(obj, col.key) for col in const])
return session.query(state.mapper).filter(crit).count() 0
else:
return False
the unique constraints are a set though. not necessarily deterministic
which one it would locate first. I'd use more of some kind of declared
system on the class:
class X(Base):
lookup_class_via = ('name',)
id = Column(Integer, primary_key=True)
name = Column(String)
from sqlalchemy import inspect, UniqueConstraint
def exists(session, obj):
crit = and_(*[col == getattr(obj, col.key) for col in
obj.__class__.lookup_class_via])
return session.query(state.mapper).filter(crit).count() 0
--
You received this message because you are subscribed to the Google Groups
sqlalchemy group.
To unsubscribe from this group and stop receiving emails from it, send an email
to sqlalchemy+unsubscr...@googlegroups.com.
To post to this group, send email to sqlalchemy@googlegroups.com.
Visit this group at http://groups.google.com/group/sqlalchemy.
For more options, visit https://groups.google.com/d/optout.
