[sqlalchemy] Re: preview an update?
On Thu, Jun 18, 2009 at 4:57 PM, Michael Bayermike...@zzzcomputing.com wrote:
you can get most of this stuff from the session without any flush
occurring. at the object level are the new, dirty, and deleted
collections on Session. At the attribute level the
attributes.get_history() method will illustrate the full changes made
since the last flush on an individual object attribute. get_history()
should be in the API docs.
Thank you! get_history is just what I needed. However, I spent a
long time being confused by something that I think is a very obscure
bug.
If you make an update to a table that has a relation to a view, then
get_history on an attribute in the view, then the history is
forgotten. I'm attaching a file that duplicates it completely, but
here's the gist of it (after setting up as described in the docs' ORM
tutorial):
ed = session.query(User).first()
print sqlalchemy.orm.attributes.get_history(ed, 'fullname')
((), [u'Ed Jones'], ())
ed.fullname = 'Eduardo Jones'
print sqlalchemy.orm.attributes.get_history(ed, 'fullname')
(['Eduardo Jones'], (), [u'Ed Jones'])
# so far so good
print sqlalchemy.orm.attributes.get_history(ed.userview, 'name')
((), [u'ed'], ())
print sqlalchemy.orm.attributes.get_history(ed, 'fullname')
((), ['Eduardo Jones'], ())
... now get_history thinks fullname has always been Eduardo Jones.
Is this a bug I should file, or something I should have expected?
Thanks as always!
--
- Catherine
http://catherinedevlin.blogspot.com/
*** PyOhio * July 25-26, 2009 * pyohio.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
-~--~~~~--~~--~--~---
from sqlalchemy import create_engine
engine = create_engine('sqlite:///:memory:', echo=False)
from sqlalchemy import Table, Column, Integer, String, MetaData, ForeignKey
metadata = MetaData()
users_table = Table('users', metadata,
Column('id', Integer, primary_key=True),
Column('name', String(12)),
Column('fullname', String(40)),
Column('password', String(40))
)
metadata.create_all(engine)
class User(object):
def __init__(self, name, fullname, password):
self.name = name
self.fullname = fullname
self.password = password
def __repr__(self):
return User('%s','%s', '%s') % (self.name, self.fullname, self.password)
from sqlalchemy.orm import mapper, relation, backref
mapper(User, users_table)
ed_user = User('ed', 'Ed Jones', 'edspassword')
from sqlalchemy.orm import sessionmaker
Session = sessionmaker(bind=engine)
session = Session()
session.add(ed_user)
session.commit()
#
# everything up to here comes from the ORM tutorial at http://www.sqlalchemy.org/docs/05/ormtutorial.html
engine.connect().execute('CREATE VIEW userview AS SELECT id, name FROM users')
class UserView(object):
pass
userview = Table('userview', metadata,
Column('id', ForeignKey('users.id'), primary_key=True),
autoload=True, autoload_with=engine)
mapper(UserView, userview,
properties={'user': relation(User, uselist=False, lazy=True,
backref=backref('userview', uselist=False))})
import sqlalchemy.orm.attributes
ed = session.query(User).first()
print sqlalchemy.orm.attributes.get_history(ed, 'fullname')
ed.fullname = 'Eduardo Jones'
print sqlalchemy.orm.attributes.get_history(ed, 'fullname')
print sqlalchemy.orm.attributes.get_history(ed.userview, 'name')
print sqlalchemy.orm.attributes.get_history(ed, 'fullname')
[sqlalchemy] Re: preview an update?
Catherine Devlin wrote: (['Eduardo Jones'], (), [u'Ed Jones']) # so far so good print sqlalchemy.orm.attributes.get_history(ed.userview, 'name') ((), [u'ed'], ()) print sqlalchemy.orm.attributes.get_history(ed, 'fullname') ((), ['Eduardo Jones'], ()) ... now get_history thinks fullname has always been Eduardo Jones. Is this a bug I should file, or something I should have expected? I'm going to say that ed.userview did a lazy load and triggered an autoflush. the autoflush will reset the attributes back to unchanged so that is expected. --~--~-~--~~~---~--~~ 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: preview an update?
On Jun 18, 2009, at 4:27 PM, Catherine Devlin wrote: I'm building a complex ETL tool with SQLAlchemy that will sometimes need to let the user preview a changed record - not actually carry out the update, just find out which fields would be changed, and to what values. I'm having trouble figuring out a good way to do it. Is there a way to get a sort of preview of an update - get information about what updates are pending for the next flush, or get a flush to report back its changes without actually performing it? you can get most of this stuff from the session without any flush occurring. at the object level are the new, dirty, and deleted collections on Session. At the attribute level the attributes.get_history() method will illustrate the full changes made since the last flush on an individual object attribute. get_history() should be in the API docs. what you can't get without flushing are newly generated primary key identifiers, server side defaults that havent fired off, and the results of cascades like delete or on update cascades. however if this does what you need this is definitely the best way to go as it doesnt require issuing a flush() which then has to be rolled back internally. One approach would be to set echo to True, let the user see the echo, then roll back the transaction, but there are a couple reasons I don't think that will work for me. I don't want to make the user mentally parse SQL statements; I'd like to get a dict of the pending changes and write my own code to display them attractively, instead. Anyway, I'm having trouble getting echo to work on this app. (The whole thing is part of a TurboGears project, and my echoes aren't showing up, probably because of something I don't understand about the SQLAlchemy logging configuration.) If you went down this road, use a ConnectionProxy to capture all the SQL expressions as they are emitted.this is also in the API docs.The unit tests use a custom proxy to capturing SQL and expressions various operations, using a decorator to enable/disable the capturing. you can capture the SQL expression constructs directly where you'll have access to all the Table objects and bind params and you can display it in any way suitable. Depending on your needs I'd use one of the above approaches and I would not recommend instrumenting any of the ORM internals. --~--~-~--~~~---~--~~ 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 -~--~~~~--~~--~--~---
