Thanks you Michael. Very thorough reply!
1. I will make sure not to use implict execution; rather, stick to the
session object. And you are right, the outcome of the above code is not
pertaining to this implicit execution. (I did not read it from any
tutorial, but typed in the source code and it executes -- one of
interesting aspects of SQLAlchemy, that there are so many variants to do
ostensibly-similar things)
2. To achieve what I need, you present two solutions: either expire the
object to have SQLAlchemy do refresh; or do the update with
synchronize_session=‘evaluate’.
Could you further explain why 2nd choice might not succeed because of
"limited ability"?
Thanks
- Chang
On Thursday, May 1, 2014 7:23:49 AM UTC-7, Michael Bayer wrote:
>
> ok when you run a statement like this:
>
>
> upd = update(X).execute()
>
>
> you’re using something called “implicit execution”. I’d be curious to
> know how you came to be using this style, as the docs have all but hidden
> it away - if some tutorial somewhere is using it, that tutorial is badly
> out of date. It’s a very old style that leads to the kind of confusion
> you’re having.
>
> The ORM Session object in default use establishes a database connection
> when it first is used, and maintains that connection open in a transaction
> as one proceeds. As you use the Session, everything you do should be in
> terms of that connection / transaction. In this case:
>
> upd = update(X).where(…)
> session.execute(upd)
>
> this will allow your update statement to participate in the same
> transaction as that of everything else you’re doing. If your database
> connection is configured for repeatable read isolation, it would otherwise
> not even be able to see the change emitted to this row in a different
> transaction until a new transaction were begun.
>
> But that is probably not what’s happening here. The second part is that
> the ORM does not re-fetch any attributes that are already present. if you
> say:
>
> x = session.query(X).filter_by(id=1).first()
> y = session.query(X).filter_by(id=1).first()
>
> the identity map pattern states that x and y are the same object; but
> also, the ORM will not overwrite the data in the already loaded object.
> This is both for performance as well as so that any pending changes you may
> have made to “x” are not overwritten.
>
> putting both together, we can also expire the object so that it does get
> refreshed:
>
> x = session.query(X).filter_by(id=1).first()
> upd = update(X).where(…)
> session.execute(upd)
> session.expire(x)
>
> # now you’ll have the new state
> x.some_attribute
>
> then you can go a step further. You can use query.update() as well, which
> has limited ability to do this expiration for you:
>
>
> x = session.query(X).filter_by(id=1).first()
> session.query(X).filter_by(id=1).update({ values },
> synchronize_session=‘evaluate’)
>
> # now you’ll have the new state as well, assuming the synchronize
> operation was able to proceed (it raises an error if not)
> x.some_attribute
>
> hope this helps.
>
>
>
>
>
>
>
>
> On May 1, 2014, at 1:40 AM, Chang Kuang <cku...@gmail.com <javascript:>>
> wrote:
>
> I ran into the following situation when using SQLAlchemy, and let me
> explain using an example. Assume there is a table "Department", with three
> columns: ID, name and charter, where ID is auto-generated by DB.
>
> class Department(class_Base):
>> __tablename__ = "department"
>> id = Column(Integer, Sequence('department_id_seq'), primary_key=True)
>> name = Column(String(250), nullable=False, unique=True)
>> charter = Column(String(250), nullable=True)
>> def __repr__(self):
>> return "%s: id: [%d], name: [%s], charter: [%s]" %
>> (self.__class__, self.id, self.name, self.charter)
>
>
>
> Below is the skeleton of code:
>
>> # step 1: grab a Department object using ORM
>>
>> # department name is unique
>> l_department = g_session.query(Department).filter(Department.name ==
>> in_department_name).one()
>>
>> # step 2: modify its charter using SQLExpression
>> l_update_department = update(Department).where(Department.name ==
>> in_department_name).values(charter=bindparam('charter'))
>> l_update_department.execute({'charter': in_department_charter})
>>
>> # step 3: grab it again using ORM
>> l_department = g_session.query(Department).filter(Department.name ==
>> in_department_name).one()
>
>
> What is unexpected is, the field "charter" is the same in step 1 and step
> 3, even though it is modified in step 2. The SQL statements in all three
> steps are good, and the column "charter" indeed got updated per what I
> checked from the database.
>
> Question:
> 1. Is it something I should not do (mixing SQL expression and ORM)?
> However, I did see it works in some cases.
> 2. Is it related to the database transactional capability? I am simply
> testing against SQLite
>
> Thanks!
>
> --
> 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+...@googlegroups.com <javascript:>.
> To post to this group, send email to sqlal...@googlegroups.com<javascript:>
> .
> Visit this group at http://groups.google.com/group/sqlalchemy.
> For more options, visit https://groups.google.com/d/optout.
>
>
>
--
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.
