See http://pypi.python.org/pypi/zope.sqlalchemy (quoting):
By default, zope.sqlalchemy puts sessions in an 'active' state when
they are first used. ORM write operations automatically move the
session into a 'changed' state. This avoids unnecessary database
commits. Sometimes it is necessary to interact with the database
directly through SQL. It is not possible to guess whether such an
operation is a read or a write. Therefore we must manually mark the
session as changed when manual SQL statements write to the DB.
>>> session = Session()
>>> conn = session.connection()
>>> users = Base.metadata.tables['test_users']
>>> conn.execute(users.update(users.c.name=='bob'), name='ben')
<sqlalchemy.engine.base.ResultProxy object at ...>
>>> from zope.sqlalchemy import mark_changed
>>> mark_changed(session)
>>> transaction.commit()
>>> session = Session()
>>> session.query(User).all()[0].name
u'ben'
>>> transaction.abort()
If this is a problem you may tell the extension to place the session
in the 'changed' state initially.
>>> Session.configure(extension=ZopeTransactionExtension('changed'))
>>> Session.remove()
>>> session = Session()
>>> conn = session.connection()
>>> conn.execute(users.update(users.c.name=='ben'), name='bob')
<sqlalchemy.engine.base.ResultProxy object at ...>
>>> transaction.commit()
>>> session = Session()
>>> session.query(User).all()[0].name
u'bob'
>>> transaction.abort()
Laurence
On Dec 17, 3:37 pm, Kent <[email protected]> wrote:
> I believe zope.sqlalchemy has no group/mailing list, so this message
> is specifically intended for zope.sqlalchemy developers (whom I'll
> point to this thread):
>
> Please read the above.
>
> Here is a script to demonstrate the behavior zope.sqlalchemy is
> causing. I believe is not an intended behavior (i.e. I think this is a
> bug):
>
> ===================================
> from sqlalchemy import *
> from sqlalchemy.orm import scoped_session, sessionmaker
> from zope.sqlalchemy import ZopeTransactionExtension
> from zope.sqlalchemy import __version__ as zopesqlaversion
> import transaction
>
> print "zope.sqlalchemy version %s" % zopesqlaversion
>
> engine = create_engine('sqlite:///', echo=True)
>
> session_maker = sessionmaker(bind=engine,
> extension=ZopeTransactionExtension())
>
> Session = scoped_session(session_maker)
> Session.execute("create table kb (data varchar)")
> Session.execute("insert into kb values ('data')")
> fetched = Session.execute("select * from kb").fetchall()
> print "fetched: %r" % fetched
> assert fetched == [('data',)]
> # if we use sqla alone, Session.commit() would issue a commit
> # zope.sqlalchemy thinks there is nothing to commit, unless there are
> changes to
> # actual sqla *objects* (then this would issue COMMIT)
> transaction.commit()
>
> Session = scoped_session(session_maker)
> # now we see there was no data committed
> fetched = Session.execute("select * from kb").fetchall()
> print "fetched: %r" % fetched
> assert fetched == [('data',)]
> ===================================
>
> Here is the scripts output:
>
> ===================================
> zope.sqlalchemy version 0.6
> 2010-12-17 10:35:45,684 INFO sqlalchemy.engine.base.Engine.0x...b0d0
> BEGIN
> 2010-12-17 10:35:45,689 INFO sqlalchemy.engine.base.Engine.0x...b0d0
> create table kb (data varchar)
> 2010-12-17 10:35:45,690 INFO sqlalchemy.engine.base.Engine.0x...b0d0
> ()
> 2010-12-17 10:35:45,693 INFO sqlalchemy.engine.base.Engine.0x...b0d0
> insert into kb values ('data')
> 2010-12-17 10:35:45,694 INFO sqlalchemy.engine.base.Engine.0x...b0d0
> ()
> 2010-12-17 10:35:45,695 INFO sqlalchemy.engine.base.Engine.0x...b0d0
> select * from kb
> 2010-12-17 10:35:45,696 INFO sqlalchemy.engine.base.Engine.0x...b0d0
> ()
> fetched: [(u'data',)]
> 2010-12-17 10:35:45,698 INFO sqlalchemy.engine.base.Engine.0x...b0d0
> BEGIN
> 2010-12-17 10:35:45,700 INFO sqlalchemy.engine.base.Engine.0x...b0d0
> select * from kb
> 2010-12-17 10:35:45,701 INFO sqlalchemy.engine.base.Engine.0x...b0d0
> ()
> fetched: []
> Traceback (most recent call last):
> File "zopedemo.py", line 28, in <module>
> assert fetched == [('data',)]
> AssertionError
> ===================================
>
> Thank you for your time/response.
>
> Kent
>
> On Dec 17, 9:40 am, Kent <[email protected]> wrote:
>
>
>
>
>
>
>
> > I had mistaken it for a SQLA flaw, but, from pdb, it appears the
> > behavior I'm observing is being caused by zope.sqlalchemy.
>
> > Thanks for your input.
>
> > On Dec 16, 7:19 pm, Michael Bayer <[email protected]> wrote:
>
> > > That doesn't sound right. Session.commit() issues the commit regardless
> > > of whether or not flush() had any work to do. The usage of
> > > Session.execute() takes place in the scope of the Session's transaction
> > > so work is definitely begun as well.
>
> > > from sqlalchemy import create_engine
> > > from sqlalchemy.orm import Session
>
> > > e = create_engine('sqlite:///test.db', echo=True)
> > > s = Session(e)
>
> > > s.execute("CREATE TABLE foo (data VARCHAR)")
> > > s.execute("INSERT INTO foo (data) VALUES ('data1')")
> > > s.commit()
> > > s.close()
>
> > > e = create_engine('sqlite:///test.db', echo=True)
>
> > > assert e.execute("SELECT * FROM foo").fetchall() == [('data1',)]
>
> > > On Dec 16, 2010, at 2:37 PM, Kent wrote:
>
> > > > From time to time I find I need or prefer "dropping into SQL" for
> > > > certain tasks, in the midst of making the majority of my database
> > > > changes through sqlalchemy's objects; for example, I may use
> > > > session.execute(sql).
>
> > > > I would like these changes to be committed later along with the sqla
> > > > session objects *if and only if* the session issues a commit.
> > > > However, I've observed that if the *only* database changes were made
> > > > via session.execute(), then session.commit() believes there is nothing
> > > > to commit, so the "commit" isn't issued to the database.
>
> > > > Is there a better way to approach what I am trying to accomplish?
> > > > Is there a way to tell a session "by the way, you have changes that
> > > > you don't know about, so when it comes time to commit, please do so"?
>
> > > > Thanks very much, as always,
> > > > Kent
>
> > > > --
> > > > You received this message because you are subscribed to the Google
> > > > Groups "sqlalchemy" group.
> > > > To post to this group, send email to [email protected].
> > > > To unsubscribe from this group, send email to
> > > > [email protected].
> > > > For more options, visit this group
> > > > athttp://groups.google.com/group/sqlalchemy?hl=en.
--
You received this message because you are subscribed to the Google Groups
"sqlalchemy" group.
To post to this group, send email to [email protected].
To unsubscribe from this group, send email to
[email protected].
For more options, visit this group at
http://groups.google.com/group/sqlalchemy?hl=en.
