Re: [sqlalchemy] Re: SQLA pg upsert causes impl rollback, works thru CL query

[Jonathan Vanasco]

I understand your concerns. I dropped pyramid's transaction support a long 
time ago, and prefer to do everything explicitly.

You should be aware that a scoped session and regular session are not 100% 
interchangeable.  There are a few slight differences... though 99.9% of 
users won't be affected by them, and those rare cases can easily work 
around the slight API differences.

-- 
SQLAlchemy - 
The Python SQL Toolkit and Object Relational Mapper

http://www.sqlalchemy.org/

To post example code, please provide an MCVE: Minimal, Complete, and Verifiable 
Example.  See  http://stackoverflow.com/help/mcve for a full description.
--- 
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.

Re: [sqlalchemy] Re: SQLA pg upsert causes impl rollback, works thru CL query

[Richard Rosenberg]

Jonathan:

Yeah. That seems likely.

However, I feel "closer" to my database after removing zope.sqlalchemy from 
the mix. I guess I'm at a point where there is such a thing as too much 
abstraction. . ?

BTW, I did give the nested tx a try, but as we both now know it did not 
work, and probably for the reasons you cited. That extra notification to 
the transaction manager would likely have done the trick. 

For me, right now, it is best to simply remove the zope stuff before the 
app gets too big. While I hate to go backward in time, I also like SQLA so 
much precisely because it gives me so much control.

A fair trade-off to my way of thinking. Persistence matters, and that's why 
some people might read this.

Thanks again. . .

On Wednesday, June 28, 2017 at 3:10:28 PM UTC-6, Jonathan Vanasco wrote:
>
>
> On Wednesday, June 28, 2017 at 4:28:32 PM UTC-4, Mike Bayer wrote:
>>
>> I'm not 100% sure that zope.sqlalchemy unconditionally emits COMMIT 
>> for the session that's associated.  Though overall would need to see 
>> where you're getting request.session from and all that; if it's not 
>> associated with zope.sqlalchemy then you'd need to call 
>> session.commit() explicitly. 
>>
>
> rephrasing Mike's reply slightly... as I (think I) know what's wrong now...
>
> zope.sqlalchemy only knows that you did something with the session if you 
> use the ORM.  you're using sqlalchemy core.
>
> you need to do this:
>
> 
>
> from zope_sqlalchemy import mark_changed
> mark_changed(session)
>
>  
>  
> That will let zop.sqlalchemy know that you did something in the session 
> and it will vote a COMMIT.  otherwise it thinks you did nothing.
>
>

-- 
SQLAlchemy - 
The Python SQL Toolkit and Object Relational Mapper

http://www.sqlalchemy.org/

To post example code, please provide an MCVE: Minimal, Complete, and Verifiable 
Example.  See  http://stackoverflow.com/help/mcve for a full description.
--- 
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.

Re: [sqlalchemy] Re: SQLA pg upsert causes impl rollback, works thru CL query

[Jonathan Vanasco]

On Wednesday, June 28, 2017 at 4:28:32 PM UTC-4, Mike Bayer wrote:
>
> I'm not 100% sure that zope.sqlalchemy unconditionally emits COMMIT 
> for the session that's associated.  Though overall would need to see 
> where you're getting request.session from and all that; if it's not 
> associated with zope.sqlalchemy then you'd need to call 
> session.commit() explicitly. 
>

rephrasing Mike's reply slightly... as I (think I) know what's wrong now...

zope.sqlalchemy only knows that you did something with the session if you 
use the ORM.  you're using sqlalchemy core.

you need to do this:



from zope_sqlalchemy import mark_changed
mark_changed(session)

 
 
That will let zop.sqlalchemy know that you did something in the session and 
it will vote a COMMIT.  otherwise it thinks you did nothing.

-- 
SQLAlchemy - 
The Python SQL Toolkit and Object Relational Mapper

http://www.sqlalchemy.org/

To post example code, please provide an MCVE: Minimal, Complete, and Verifiable 
Example.  See  http://stackoverflow.com/help/mcve for a full description.
--- 
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.

Re: [sqlalchemy] Re: SQLA pg upsert causes impl rollback, works thru CL query

[Richard Rosenberg]

Mike:

Thanks for your reply.

This is a stock pyramid app built with their SQLA "scaffold." As expected, 
any attempt to directly use commit on the session results in:

File 
"/home/richard/vp36/lib/python3.6/site-packages/zope.sqlalchemy-0.7.7-py3.6.egg/zope/sqlalchemy/datamanager.py",
 
line 254, in before_commit
"Transaction must be committed using the transaction manager"
AssertionError: Transaction must be committed using the transaction manager

The SQLA piece seems to be working great, as in the generated SQL is 
exactly what I expect. It's a bit frustrating that SQLA is telling me that 
everything is cool with the generated SQL, but then ROLLBACK sort of 
whimsically appears in the log output.

I'm sure it is not arbitrary, it just looks that way.

I'll give a nested transaction a try, and then see about getting rid of the 
zope stuff, so back to "pure" SQLA if I can manage it. 

The dbsession is created in the following:

in models.__init__

from sqlalchemy import engine_from_config
from sqlalchemy.orm import sessionmaker
from sqlalchemy.orm import configure_mappers
import zope.sqlalchemy

# import or define all models here to ensure they are attached to the
# Base.metadata prior to any initialization routines
from .stemqmodel import (EdgeNode, Tile, TileStat, TileSub, TileUpd)  # 
flake8: noqa

# run configure_mappers after defining all of the models to ensure
# all relationships can be setup
configure_mappers()


def get_engine(settings, prefix='sqlalchemy.'):
return engine_from_config(settings, prefix)


def get_session_factory(engine):
factory = sessionmaker()
factory.configure(bind=engine)
return factory


def get_tm_session(session_factory, transaction_manager):
"""
Get a ``sqlalchemy.orm.Session`` instance backed by a transaction.

This function will hook the session to the transaction manager which
will take care of committing any changes.

- When using pyramid_tm it will automatically be committed or aborted
  depending on whether an exception is raised.

- When using scripts you should wrap the session in a manager yourself.
  For example::

  import transaction

  engine = get_engine(settings)
  session_factory = get_session_factory(engine)
  with transaction.manager:
  dbsession = get_tm_session(session_factory, 
transaction.manager)

"""
dbsession = session_factory()
zope.sqlalchemy.register(
dbsession, transaction_manager=transaction_manager)
return dbsession


def includeme(config):
"""
Initialize the model for a Pyramid app.

Activate this setup using ``config.include('testsqla.models')``.

"""
settings = config.get_settings()

# use pyramid_tm to hook the transaction lifecycle to the request
config.include('pyramid_tm')

session_factory = get_session_factory(get_engine(settings))
config.registry['dbsession_factory'] = session_factory

# make request.dbsession available for use in Pyramid
config.add_request_method(
# r.tm is the transaction manager used by pyramid_tm
lambda r: get_tm_session(session_factory, r.tm),
'dbsession',
reify=True
)

And in the pyramid app __init__ like so:

from pyramid.config import Configurator
from sqlalchemy import engine_from_config
import logging
import time
import socket
#
log = logging.getLogger(__name__)


def main(global_config, **settings):
""" This function returns a Pyramid WSGI application.
"""
### This hack must come before the call to Configurator

config = Configurator(settings=settings)
config.include('pyramid_mako')
config.include('.models')
config.add_static_view('static', 'static', cache_max_age=3600)
config.add_route('home', '/')
config.add_route('register', '/register')
config.add_route('loader', '/loader')
config.add_route('register_edgenode_view', '/registeredgeview')
config.add_route('register_edgenode_action', '/registeredge')
config.add_route('view_edgenode_status', '/viewedgestatus')
config.add_route('test_lineup', '/testlineup')
config.scan()
config.registry['last_request'] = time.time()
return config.make_wsgi_app()

Mostly just generated code, via cookiecutter. The relevant line is:
config.include(.models)

At this point, I miss the old "stock" SQLA integration that made me manage 
my own transactions. A. . .The good old days.

On Wednesday, June 28, 2017 at 2:28:32 PM UTC-6, Mike Bayer wrote:
>
> I'm not 100% sure that zope.sqlalchemy unconditionally emits COMMIT 
> for the session that's associated.  Though overall would need to see 
> where you're getting request.session from and all that; if it's not 
> associated with zope.sqlalchemy then you'd need to call 
> session.commit() explicitly. 
>
> On Wed, Jun 28, 2017 at 3:43 PM, Jonathan Vanasco  > wrote: 
> > 
> >> 
> >> On Wednesday, June 28, 2017 at 3:16:52 PM UTC-4, 

Re: [sqlalchemy] Re: SQLA pg upsert causes impl rollback, works thru CL query

[Mike Bayer]

I'm not 100% sure that zope.sqlalchemy unconditionally emits COMMIT
for the session that's associated.  Though overall would need to see
where you're getting request.session from and all that; if it's not
associated with zope.sqlalchemy then you'd need to call
session.commit() explicitly.

On Wed, Jun 28, 2017 at 3:43 PM, Jonathan Vanasco  wrote:
>
>>
>> On Wednesday, June 28, 2017 at 3:16:52 PM UTC-4, Richard Rosenberg wrote:
>
>
> On Wednesday, June 28, 2017 at 3:16:52 PM UTC-4, Richard Rosenberg wrote:
>>
>>
>> I am absolutely puzzled, but it seems likely that pyramid_tm is in the way
>> somehow. It always wants to do its own thing, and calling commit explicitly
>> is something it seems to abhor. My next step is to wrap this in:
>>
>> with transaction.manager as tx:
>>
>> But this is really not what I want. I'm tempted to rip out all of the
>> zopish stuff and go with SQLA's session, but before I try that, I thought it
>> might be worthwhile to get some further information.
>>
>
> For a quick-fix, i'd try to run this bit within a nested
> transaction/savepoint:
>
> http://docs.sqlalchemy.org/en/latest/orm/session_transaction.html
>
>
> I doubt the problem is in `pyramid_tm`.  that package just wraps the request
> in some logic to hook into the transaction package; all the real work is
> done by `zope.sqlalchemy`.  The issue is possibly linked to your version of
> `zope.sqlalchemy` or `pyscopg2` (or other driver).  I'd try to update
> those... but from what I see here, you could probably just dump this into a
> nested transaction, which will limit the scope of the rollback.
>
>
> --
> SQLAlchemy -
> The Python SQL Toolkit and Object Relational Mapper
>
> http://www.sqlalchemy.org/
>
> To post example code, please provide an MCVE: Minimal, Complete, and
> Verifiable Example. See http://stackoverflow.com/help/mcve for a full
> description.
> ---
> 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.

-- 
SQLAlchemy - 
The Python SQL Toolkit and Object Relational Mapper

http://www.sqlalchemy.org/

To post example code, please provide an MCVE: Minimal, Complete, and Verifiable 
Example.  See  http://stackoverflow.com/help/mcve for a full description.
--- 
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.