Ah, crap, you're totally right. The modified datetime in my application is
only in one of the tables in the joined table object, so my update really
never made any sense.
I think perhaps a serializable transaction is our best bet here. This
should not be a frequent or expensive operation, but it does need to make
certain it's an update from the most recent record.
Just for the record, I did know the SET was the problem in the last
example, it was just an example derived from the 2nd to show the intent. So
I'm only like 90% an idiot.
Thanks a lot as always.
Douglas
On Thursday, 23 June 2016 15:25:19 UTC-4, Mike Bayer wrote:
>
>
>
> On 06/23/2016 02:42 PM, Douglas Russell wrote:
> > Hi,
> >
> > I have a situation where I need to check a condition during an update.
> > In my case it is a timestamp and the function of the condition is to
> > ensure that there was no intermittent update by another between the type
> > the object was edited and when it was saved. In this example though, I
> > just use a string as datetimes are harder to read:
> >
> > Usually I do these updates in the ORM. Here is an example where I update
> > the author name.
> >
> > |
> > import sqlalchemy as sa
> > from sqlalchemy.ext.declarative import declarative_base
> > from sqlalchemy.orm import scoped_session, sessionmaker, relationship
> >
> > engine = sa.create_engine('sqlite:///:memory:', echo=False)
> > session = scoped_session(sessionmaker(bind=engine))
> > Base = declarative_base()
> >
> >
> > class Author(Base):
> > __tablename__ = 'authors'
> > id = sa.Column(sa.Integer, primary_key=True)
> > name = sa.Column(sa.String)
> >
> > def __repr__(self):
> > return '<Author(name={self.name!r})>'.format(self=self)
> >
> > Base.metadata.create_all(engine)
> >
> > author = Author(name='Someone')
> > session.add(author)
> > session.commit()
> > print author
> > # <Author(name=u'Someone')> (Correct)
> >
> > # Update the author via ORM
> > author.name = 'Someone else'
> > session.commit()
> > print author
> > # <Author(name=u'Someone else')> (Correct)
> > |
> >
> > Now lets say that I want to make sure that I only update the author name
> > if the previous value was what I think it was. As far as I can tell, it
> > is not possible to do this with the ORM. So using core:
> >
> > |
> > import sqlalchemy as sa
> > from sqlalchemy.ext.declarative import declarative_base
> > from sqlalchemy.orm import scoped_session, sessionmaker, relationship
> >
> > engine = sa.create_engine('sqlite:///:memory:', echo=False)
> > session = scoped_session(sessionmaker(bind=engine))
> > Base = declarative_base()
> >
> >
> > class Author(Base):
> > __tablename__ = 'authors'
> > id = sa.Column(sa.Integer, primary_key=True)
> > name = sa.Column(sa.String)
> >
> > def __repr__(self):
> > return '<Author(name={self.name!r})>'.format(self=self)
> >
> > Base.metadata.create_all(engine)
> >
> > author = Author(name='Someone')
> > session.add(author)
> > session.commit()
> > print author
> >
> > # author.name = 'Someone else'
> > # session.commit()
> > # print author
> > # <Author(name=u'Someone else')> (Correct)
> >
> > # Update the author via Core, get and display
> > q = author.__table__.update().where(
> > sa.and_(
> > Author.id == author.id,
> > Author.name == 'Someone'
> > )
> > ).values(
> > name='Someone else'
> > )
> > result = session.execute(q)
> > session.commit()
> >
> > print 'Rowcount: ', result.rowcount
> > print author
>
> the ORM does ad-hoc UPDATE statements like this:
>
> s.query(Author).filter(Author.id == author.id).filter(Author.name ==
> 'someone').update({"name": "Someone"})
>
>
> http://docs.sqlalchemy.org/en/latest/orm/query.html?highlight=query.update#sqlalchemy.orm.query.Query.update
>
>
>
>
>
> >
> > So, this works and doesn't look too bad for this very simple example,
> > but this is going to get pretty messy for more complicated ORM objects.
> >
> > |
> > import sqlalchemy as sa
> > from sqlalchemy.ext.declarative import declarative_base
> > from sqlalchemy.orm import scoped_session, sessionmaker, relationship
> >
> > engine = sa.create_engine('sqlite:///:memory:', echo=False)
> > session = scoped_session(sessionmaker(bind=engine))
> > Base = declarative_base()
> >
> > class Author(Base):
> > __tablename__ = 'author'
> >
> > type = sa.Column(sa.String(50))
> >
> > __mapper_args__ = {
> > 'polymorphic_identity': 'author',
> > 'polymorphic_on': type
> > }
> >
> > id = sa.Column(sa.Integer, primary_key=True)
> > name = sa.Column(sa.String)
> >
> > def __repr__(self):
> > return '<Author(name={self.name!r})>'.format(self=self)
> >
> >
> > class AuthorDetail(Author):
> > __tablename__ = 'author_detail'
> >
> > __mapper_args__ = {
> > 'polymorphic_identity': 'author_detail',
> > }
> >
> > id = sa.Column(sa.Integer, sa.ForeignKey('author.id'),
> primary_key=True)
> > age = sa.Column(sa.Integer)
> >
> > def __repr__(self):
> > return '<Author(name={self.name!r},
> > age={self.age})>'.format(self=self)
> >
> > Base.metadata.create_all(engine)
> >
> > author = AuthorDetail(name='Someone', age=20)
> > session.add(author)
> > session.commit()
> > print author
> >
> > q = author.__table__.update().where(
> > sa.and_(
> > Author.id == author.id,
> > Author.name == 'Someone'
> > )
> > ).values(
> > name='Someone else',
> > age=30
> > )
> > result = session.execute(q)
> > session.commit()
> >
> > print 'Rowcount: ', result.rowcount
> > print author
> >
> > |
> >
> > This will fail, because 'name' is not a column in the author_detail
> > table.
>
> that is true, you would need to emit a second UPDATE statement. This is
> how UPDATE works in SQL, unless you are using MySQL's "multi-table
> update" feature (which we support). I see that "name" is part of
> "author" and "age" is part of "author_detail", and you're looking to
> UPDATE them both at the same time. SQL doesn't do that in one statement
> (only mysql's special trick does, and you're illustrating SQLite).
>
>
> At this point, I could write more sophisticated updates to handle
> > this situation, but not having to handle creating those complicated
> > statements (and updating them on any changes) was part of the attraction
> > of using the ORM in the first place.
>
> the ORM's strength is in handling the persistence state of your objects
> as well as applying patterns for loading collections and related objects
> efficiently. It will freely UPDATE objects, including those mapped in
> inheritance hierarchies across multiple tables, on a per-identity basis
> in any way you have instructed; that is, load all the objects you want
> to UPDATE using the filter criteria you describe, change their
> attributes in memory, then flush. I've never seen an ORM that would
> emit multiple UPDATE statements against multiple tables in an
> inheritance hierarchy based on a custom WHERE clause. That would be
> very complicated to implement and just as hard for the end-user to use
> effectively.
>
> However, in this case you are specifically going for a special
> concurrency use case. The fact that UPDATE works on only one table at
> a time is not avoidable; whether or not the ORM can magically emit the
> two UPDATE statements you need, you still aren't updating in isolation
> unless you are using the right transaction isolation in conjunction with
> the right kind of locking for that isolation.
>
> If this is in fact SQLite, you get that for free, since SQLite locks the
> whole database file during a transaction. Otherwise, I'd advise doing a
> SELECT..FOR UPDATE to lock the rows that you need, then emit normal
> UPDATE statements as needed.
>
> The "atomic update" thing depends highly on the kind of backend you're
> using. With SQLite, it's one thing, if you were for example using
> MySQL Galera, then the SELECT..FOR UPDATE wouldn't be available to you
> either.
>
>
>
> >
> > So I guess the question is... Is there a smart way to handle this
> > requirement without having to abandon the ORM? Some kind of ability to
> > modify the update generated by the ORM with the extra where clause (and
> > then determine if the update where matched rows or not as I need to
> > throw an error if the table is not updateable for this reason).
>
> the WHERE clause isn't the problem here, it's the SET clause. It can
> only refer to one table at a time, and your example has two tables
> receiving a SET at once.
>
> >
> > Cheers,
> >
> > Douglas
> >
> > --
> > 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:>
> > <mailto:sqlalchemy+unsubscr...@googlegroups.com <javascript:>>.
> > To post to this group, send email to sqlal...@googlegroups.com
> <javascript:>
> > <mailto:sqlal...@googlegroups.com <javascript:>>.
> > Visit this group at https://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 https://groups.google.com/group/sqlalchemy.
For more options, visit https://groups.google.com/d/optout.
