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> 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+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.
--
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.
