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):
> 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
>
>
Yup... this could work... though, I'm thinking the best way to get what I
would want would be to combine this class-attribute approach with the
after_flush
callback you suggested earlier, and do something like:
class X(Base):
flush_attrs = ('id',)
id = Column(Integer, primary_key=True)
name = Column(string)
@event.listens_for(Session, 'after_flush')
def after_flush(sess, ctx):
for obj in sess.deleted:
for key in getattr(obj, 'flush_attrs', []):
obj.__dict__[key] = None
Or, I could try and get even fancier, and automatically set any
auto-incremented Integer primary-keys to None, if the class inherits from a
certain base class...
Anyway, you've given me a lot of food for thought, thank you for your
suggestions!
--
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.
