Re: [sqlalchemy] SQLAlchemy transaction ID

['Jonathan Vanasco' via sqlalchemy]

FWIW, within the realm of pyramid_tm, the more common use-cases for 
two-phase transaction support are for sending mail and a dealing with task 
queues - not two separate databases.

On Wednesday, January 27, 2021 at 2:40:21 PM UTC-5 Mike Bayer wrote:

>
>
> On Wed, Jan 27, 2021, at 2:23 PM, Thierry Florac wrote:
>
> Hi,
>
> I'm actually using two databases connections: one is my "main" connection, 
> opened on a ZODB (with RelStorage), and **sometimes** I have to open 
> another connection on another database (and event more sometimes); the two 
> transactions have to be synchronized: if one of them is aborted for any 
> reason, the two transactions have to be aborted.
>
>
>
> OK, then two phase it is
>
> I have always thought that the two-phase transaction was created to handle 
> this kind of use case, but if there is another better solution, I would be 
> very happy to know about it!
>
>
> if you need the ORM to call prepare() then you need the XID and there you 
> are.
>
> This is all stuff that I think outside of the Zope community (but still in 
> Python) you don't really see much of.  If someone's Flask app is writing to 
> Postgresql and MongoDB they're just going to spew data out to mongo and not 
> really worry about it, but that's becasue mongo doesn't have any 2pc 
> support.It's just not that commonly used because we get basically 
> nobody asking about it.
>
>
>
> @jonathan, I made a patch to Pyramid DebugToolbar that I pushed to Github 
> and made a pull request. But I don't know how to provide a test case as a 
> two-phase commit is not supported by SQLite...
> I'll try anyway to provide a description of a "method" I use to reproduce 
> this!
>
>
> So interesting fact, it looks like you are using Oracle for 2pc, that's 
> what that tuple is, and we currently aren't including Oracle 2pc in our 
> test support as cx_Oracle no longer includes the "twophase" flag which I 
> think we needed for some of our more elaborate tests.  At the moment, 
> create_xid() emits a deprecation warning.  I've been in contact with Oracle 
> devs and it looks like we should be supporting 2pc as I can get help from 
> them now for things that aren't working.   I've opened 
> https://github.com/sqlalchemy/sqlalchemy/issues/5884 to look into this.   
> you should have been seeing a deprecation warning in your logs all this 
> time though.
>
>
>
>
>
> Best regards,
> Thierry
> -- 
>   https://www.ulthar.net -- http://pyams.readthedocs.io
>
>
> Le mer. 27 janv. 2021 à 19:19, Mike Bayer  a 
> écrit :
>
>
>
>
> On Wed, Jan 27, 2021, at 8:32 AM, Thierry Florac wrote:
>
>
> Hi,
> I'm actually using SQLAlchemy with Pyramid and zope.sqlalchemy packages.
> My main database connection is a ZODB connection and, when required, I 
> create an SQLAlchemy session which is joined to main transaction using this 
> kind of code:
>
> *  from *sqlalchemy.orm *import *scoped_session, sessionmaker
>
> *  from *zope.sqlalchemy *import *register
> *  from *zope.sqlalchemy.datamanager *import* join_transaction
>
>   _engine = get_engine(*engine*, *use_pool*)
>   if *use_zope_extension*:
>   factory = scoped_session(sessionmaker(*bind*=_engine, *twophase*=
> *True*))
>   else:
>   factory = sessionmaker(*bind*=_engine, *twophase*=*True*)
>   session = factory()
>   if *use_zope_extension*:
>   register(session, *initial_state*=*STATUS_ACTIVE*)
>   if *join*:
>   join_transaction(session, *initial_state*=*STATUS_ACTIVE*)
>
> Everything is working correctly!
>
> So my only question is that I also use Pyramid_debugtoolbar package, which 
> is tracking many SQLAlchemy events, including two-phase commits 
> transactions, and which in this context receives transaction IDs as a three 
> values tuple instead of a simple string (like, for example: (4660, 
> '12345678901234567890123456789012', '0009'), 
> which is raising an exception)!
> Is it normal behaviour, and what does this value mean?
>
>
> I would ask if you really really want to use the "twophase=True" flag, and 
> I would suggest turning it off if you aren't in fact coordinating against 
> multiple RDBMS backends (and even if you are, maybe).   I'm not really sure 
> what that tuple is, I'd have to look but it seems likely to be related to 
> the XID stuff, which is really not something anyone uses these days.
>
>
>
> Best regards,
> Thierry
>
> -- 
>   https://www.ulthar.net -- http://pyams.readthedocs.io
>
>
> -- 
> 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+...@googlegroups.com.
> To view this discussion on the web 

Re: [sqlalchemy] FAQ or Feature Ideas for ORM Object and Session

['Jonathan Vanasco' via sqlalchemy]

Ok. I'll generate a docs PR for sqlalchemy and pyramid.   this comes up so 
much.

On Wednesday, January 27, 2021 at 2:25:29 PM UTC-5 Mike Bayer wrote:

>
>
> On Wed, Jan 27, 2021, at 1:12 PM, 'Jonathan Vanasco' via sqlalchemy wrote:
>
> I've been working with a handful of SQLAlchemy and Pyramid based projects 
> recently, and two situations have repeatedly come up:
>
> 1. Given a SQLAlchemy Object, access the SQLAlchemy Session
> 2. Given a SQLAlchemy Object or Session, access the Pyramid Request object
>
> The general solutions I've used to handle this is:
>
> 1. An Object can use the runtime inspection API to grab it's active 
> session:
>
> from sqlalchemy import inspect
>
> @property
> def _active_session(self):
> dbSession = inspect(self).session
> return dbSession
>
>
> There's a much older function sqlalchemy.orm.object_session() that also 
> does this.   I prefer giving people the inspect() interface because I'd 
> rather expose the first class API and not confuse things.   but 
> object_session() isn't going away.
>
>
>
> 2.  Attach the Pyramid request to the session_factory when a session is 
> created:
>
> def get_tm_session(request):
> dbSession = session_factory()
> zope.sqlalchemy.register(dbSession, 
> transaction_manager=transaction_manager, keep_session=True)
> if request is not None:
> def _cleanup(request):
> dbSession.close()
> request.add_finished_callback(_cleanup)
> # cache our request onto the dbsession
> dbSession.pyramid_request = request
> return dbSession
>
> I've needed to implement these patterns in a lot of projects. This makes 
> me wonder if there is/could be a better way.
>
>
> That request would be better placed in session.info which is the official 
> dictionary for third-party things to go.
>
>
>
>
> 1.  Would it be beneficial if ORM objects could surface the current 
> Session, if any, as a documented property ?  I do this in my base classes, 
> but with the overhead of the inspect system, and I repeat this in every 
> project.
>
>
> as a property?  no, we can't do that.we try to add zero "names" to the 
> class of any kind.there's "_sa_instance_state", 
> "_sa_instrumentation_manager" and that's as far as we go; doing absolute 
> zero to the namespace of the mapped class is a fundamental rule of the 
> ORM. 
>
>
>
> 2.  Would it be better for the sessionmaker had any of ?
>
> a. An official namespace were developers could attach information.  
> I'm using `pyramid_request` because I doubt SQLAlchemy will every step on 
> that - but it would be nice if there were a dedicated 
> attribute/object/namespace on the Session
>
>
> session.info:
>
>
> https://docs.sqlalchemy.org/en/13/orm/session_api.html?highlight=session%20info#sqlalchemy.orm.session.Session.info
>
>
> b. `sqlalchemy.orm.session.Session()` could accept a 
> dict/payload/object/whatever on init, which would be attached to a single 
> session in the aforementioned dedicated namespace. 
>
>
> Session.info::)  
>
>
> https://docs.sqlalchemy.org/en/13/orm/session_api.html?highlight=session%20info#sqlalchemy.orm.session.Session.params.info
>
>
>
>
> The usage would be something like:
>
> sess = Session(customized={"request": request})
>
> which might then me accessed as:
>
> sess.customized.request
>
>
> poof! it's done
>
>
>
>
>
>
>
>
>
>
> -- 
> 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+...@googlegroups.com.
> To view this discussion on the web visit 
> https://groups.google.com/d/msgid/sqlalchemy/b5031f10-c2c8-4065-b968-3a55f2bf6daen%40googlegroups.com
>  
> 
> .
>
>
>

-- 
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 view this discussion on the web visit 
https://groups.google.com/d/msgid/sqlalchemy/a36086e9-499f-43af-969f-4f5cf3c0ff96n%40googlegroups.com.

Re: [sqlalchemy] mapper on a temporary class

[Kent Bower]

Excellent.  As always, thanks very much for your time and answers (let
alone awesome software)!


On Wed, Jan 27, 2021 at 1:25 PM Mike Bayer  wrote:

>
>
> On Tue, Jan 26, 2021, at 9:01 PM, Kent Bower wrote:
>
> Thanks a ton for your responses.
>
> Do all the normal columns of an aliased class need to match the ad-hoc
> select to which I map the alias?
>
>
> handwavy handwavy sort of yes, sort of no?there needs to be a 1-1
> correspondence of columns, yes, aliasedclass can't define additional
> attributes that arent there normally.  as to how they are lined up, there's
> an option to do it by name in the aliased() function adapt_on_names.
> otherwise it's expected that your select() is derived from the Table
> objects that are originally mapped.
>
>
>
> > oh if the class doesn't have a mapper, then defintiely, just make
> ad-hoc subclasses of it and map to those.vastly easier that way.
>
> Mapping to a subclass would be different from mapping directly to an
> ad-hoc class?
>
>
> if the class itself is ad-hoc, then you're fine, you can make new classes
> with mappers all you want and they are GCed if you lose them (assuming no
> relationships involving long-lived classes with backrefs), yes.
>
>
>
>
>
>
>  (Mostly I’m concerned whether the very act of adding a mapper to a class
> will keep the reference and prevent its garbage collection or in some other
> way modify the “main” mappers, especially if this is done via a thread.
> Like, would that modify the compiled mappers for the entire process...
> these are the things running through my head.)
>
>
> the configure_mappers() function these days is threadsafe, it uses a
> mutex, so you are "good" there but there's a mutex involved if you are
> looking for high levels of concurrency.   if your ad-hoc classes are not
> subclasses of any long lived classes and if you are careful to limit
> relationships to only point to long lived classes and not have any
> backrefs, it should be OK.  I'd test it though :)set up a weakref.ref()
> to your ad-hoc mapper objects and make sure those ref objects get their
> callback hook invoked.
>
>
>
>
>
>
> On Tue, Jan 26, 2021 at 8:18 PM Mike Bayer 
> wrote:
>
>
>
>
> On Tue, Jan 26, 2021, at 7:31 PM, Kent Bower wrote:
>
> I should have given these details from the get-go:  the use case is a
> specialized select() (dynamically built) which would be extremely
> convenient to map relationships against for convenience in subquery
> loading, etc. So, the class would not already have a mapper.  Can I pass
> non_primary=True anyway, or won’t this work?
>
>
> oh if the class doesn't have a mapper, then defintiely, just make ad-hoc
> subclasses of it and map to those.vastly easier that way.
>
>
>
>
>
>
> On Tue, Jan 26, 2021 at 6:18 PM Mike Bayer 
> wrote:
>
>
>
>
> On Tue, Jan 26, 2021, at 3:16 PM, Kent wrote:
>
> Question: if I add a mapper to a class that is only needed temporarily,
> does using the mapper compile it along side my "normal" mappers such that
> I'll leak memory when I mean for the class to be garbage collected?
>
> Put another way, can I add a mapper to a class that doesn't influence my
> "main mappers" and gets thrown away when the class is thrown away or is
> that not possible?
>
>
> Assuming the class already has a mapper, and this is an additional mapper
> that maps the class in a different way, this feature is called a
> "non-primary mapper" and requires that you pass the non_primary=True flag.
>   If you are using non-primary mappers, then yes they get garbage collected.
>
> This is also a deprecated feature that won't be in SQLAlchemy 2.0.The
> only use case we can identify for "non-primary mappers" is when you want to
> create a relationship() to one, and for that use case we now support the
> "Relationship to AliasedClass" pattern.
>
> For anything else involving retrieving a class from an alternate
> selectable of some kind, you should be able to use AliasedClass for, e.g.
> sqlalchemy.orm.aliased(), so I would recommend using that instead unless
> you are on a very old version of SQLAlchemy.
>
>
>
>
>
>
>
>
> Thanks in advance,
> Kent
>
>
> --
> 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 view this discussion on the web visit
> https://groups.google.com/d/msgid/sqlalchemy/df03ce24-3986-450c-8c3a-a020d5d6adccn%40googlegroups.com
> 
> .
>
>
>
> --
> SQLAlchemy -
> The Python SQL Toolkit and Object Relational Mapper

Re: [sqlalchemy] SQLAlchemy transaction ID

[Mike Bayer]

On Wed, Jan 27, 2021, at 2:23 PM, Thierry Florac wrote:
> Hi,
> I'm actually using two databases connections: one is my "main" connection, 
> opened on a ZODB (with RelStorage), and **sometimes** I have to open another 
> connection on another database (and event more sometimes); the two 
> transactions have to be synchronized: if one of them is aborted for any 
> reason, the two transactions have to be aborted.


OK, then two phase it is

> I have always thought that the two-phase transaction was created to handle 
> this kind of use case, but if there is another better solution, I would be 
> very happy to know about it!

if you need the ORM to call prepare() then you need the XID and there you are.

This is all stuff that I think outside of the Zope community (but still in 
Python) you don't really see much of.  If someone's Flask app is writing to 
Postgresql and MongoDB they're just going to spew data out to mongo and not 
really worry about it, but that's becasue mongo doesn't have any 2pc support.   
 It's just not that commonly used because we get basically nobody asking about 
it.


> 
> @jonathan, I made a patch to Pyramid DebugToolbar that I pushed to Github and 
> made a pull request. But I don't know how to provide a test case as a 
> two-phase commit is not supported by SQLite...
> I'll try anyway to provide a description of a "method" I use to reproduce 
> this!

So interesting fact, it looks like you are using Oracle for 2pc, that's what 
that tuple is, and we currently aren't including Oracle 2pc in our test support 
as cx_Oracle no longer includes the "twophase" flag which I think we needed for 
some of our more elaborate tests.  At the moment, create_xid() emits a 
deprecation warning.  I've been in contact with Oracle devs and it looks like 
we should be supporting 2pc as I can get help from them now for things that 
aren't working.   I've opened 
https://github.com/sqlalchemy/sqlalchemy/issues/5884 to look into this.   you 
should have been seeing a deprecation warning in your logs all this time though.




> 
> Best regards,
> Thierry
> -- 
>   https://www.ulthar.net -- http://pyams.readthedocs.io
> 
> 
> Le mer. 27 janv. 2021 à 19:19, Mike Bayer  a écrit :
>> __
>> 
>> 
>> On Wed, Jan 27, 2021, at 8:32 AM, Thierry Florac wrote:
>>> 
>>> Hi,
>>> I'm actually using SQLAlchemy with Pyramid and zope.sqlalchemy packages.
>>> My main database connection is a ZODB connection and, when required, I 
>>> create an SQLAlchemy session which is joined to main transaction using this 
>>> kind of code:
>>> 
>>> *  from *sqlalchemy.orm *import *scoped_session, sessionmaker
>>> 
>>> *  from *zope.sqlalchemy *import *register
>>> *  from *zope.sqlalchemy.datamanager *import* join_transaction
>>> 
>>>   _engine = get_engine(*engine*, *use_pool*)
>>>   if *use_zope_extension*:
>>>   factory = scoped_session(sessionmaker(*bind*=_engine, 
>>> *twophase*=*True*))
>>>   else:
>>>   factory = sessionmaker(*bind*=_engine, *twophase*=*True*)
>>>   session = factory()
>>>   if *use_zope_extension*:
>>>   register(session, *initial_state*=*STATUS_ACTIVE*)
>>>   if *join*:
>>>   join_transaction(session, *initial_state*=*STATUS_ACTIVE*)
>>> 
>>> Everything is working correctly!
>>> 
>>> So my only question is that I also use Pyramid_debugtoolbar package, which 
>>> is tracking many SQLAlchemy events, including two-phase commits 
>>> transactions, and which in this context receives transaction IDs as a three 
>>> values tuple instead of a simple string (like, for example: (4660, 
>>> '12345678901234567890123456789012', '0009'), 
>>> which is raising an exception)!
>>> Is it normal behaviour, and what does this value mean?
>> 
>> I would ask if you really really want to use the "twophase=True" flag, and I 
>> would suggest turning it off if you aren't in fact coordinating against 
>> multiple RDBMS backends (and even if you are, maybe).   I'm not really sure 
>> what that tuple is, I'd have to look but it seems likely to be related to 
>> the XID stuff, which is really not something anyone uses these days.
>> 
>> 
>>> 
>>> Best regards,
>>> Thierry
>>> 
>>> -- 
>>>   https://www.ulthar.net -- http://pyams.readthedocs.io
>>> 

>>> -- 
>>> 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 view this discussion on the web visit 
>>> https://groups.google.com/d/msgid/sqlalchemy/CAPX_VWCwQmHEsynbsSa54%2BvkWH5xkZC_-hh359iM%3D%2BYh1f%3DE6A%40mail.gmail.com
>>>  
>>> 

Re: [sqlalchemy] FAQ or Feature Ideas for ORM Object and Session

[Mike Bayer]

On Wed, Jan 27, 2021, at 1:12 PM, 'Jonathan Vanasco' via sqlalchemy wrote:
> I've been working with a handful of SQLAlchemy and Pyramid based projects 
> recently, and two situations have repeatedly come up:
> 
> 1. Given a SQLAlchemy Object, access the SQLAlchemy Session
> 2. Given a SQLAlchemy Object or Session, access the Pyramid Request object
> 
> The general solutions I've used to handle this is:
> 
> 1. An Object can use the runtime inspection API to grab it's active session:
> 
> from sqlalchemy import inspect
> 
> @property
> def _active_session(self):
> dbSession = inspect(self).session
> return dbSession

There's a much older function sqlalchemy.orm.object_session() that also does 
this.   I prefer giving people the inspect() interface because I'd rather 
expose the first class API and not confuse things.   but object_session() isn't 
going away.


> 
> 2.  Attach the Pyramid request to the session_factory when a session is 
> created:
> 
> def get_tm_session(request):
> dbSession = session_factory()
> zope.sqlalchemy.register(dbSession, transaction_manager=transaction_manager, 
> keep_session=True)
> if request is not None:
> def _cleanup(request):
> dbSession.close()
> request.add_finished_callback(_cleanup)
> # cache our request onto the dbsession
> dbSession.pyramid_request = request
> return dbSession
> 
> I've needed to implement these patterns in a lot of projects. This makes me 
> wonder if there is/could be a better way.

That request would be better placed in session.info which is the official 
dictionary for third-party things to go.


> 
> 
> 1.  Would it be beneficial if ORM objects could surface the current Session, 
> if any, as a documented property ?  I do this in my base classes, but with 
> the overhead of the inspect system, and I repeat this in every project.

as a property?  no, we can't do that.we try to add zero "names" to the 
class of any kind.there's "_sa_instance_state", 
"_sa_instrumentation_manager" and that's as far as we go; doing absolute zero 
to the namespace of the mapped class is a fundamental rule of the ORM. 


> 
> 2.  Would it be better for the sessionmaker had any of ?
> 
> a. An official namespace were developers could attach information.  I'm 
> using `pyramid_request` because I doubt SQLAlchemy will every step on that - 
> but it would be nice if there were a dedicated attribute/object/namespace on 
> the Session

session.info:

https://docs.sqlalchemy.org/en/13/orm/session_api.html?highlight=session%20info#sqlalchemy.orm.session.Session.info


> b. `sqlalchemy.orm.session.Session()` could accept a 
> dict/payload/object/whatever on init, which would be attached to a single 
> session in the aforementioned dedicated namespace. 

Session.info::)  

https://docs.sqlalchemy.org/en/13/orm/session_api.html?highlight=session%20info#sqlalchemy.orm.session.Session.params.info



> 
> The usage would be something like:
> 
> sess = Session(customized={"request": request})
> 
> which might then me accessed as:
> 
> sess.customized.request

poof! it's done




> 
> 
> 
> 
> 
> 

> -- 
> 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 view this discussion on the web visit 
> https://groups.google.com/d/msgid/sqlalchemy/b5031f10-c2c8-4065-b968-3a55f2bf6daen%40googlegroups.com
>  
> .

-- 
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 view this discussion on the web visit 
https://groups.google.com/d/msgid/sqlalchemy/34cedbfb-ddc8-4c3d-a9b1-94e4e4a92b3a%40www.fastmail.com.

Re: [sqlalchemy] SQLAlchemy transaction ID

[Thierry Florac]

Hi,
I'm actually using two databases connections: one is my "main" connection,
opened on a ZODB (with RelStorage), and **sometimes** I have to open
another connection on another database (and event more sometimes); the two
transactions have to be synchronized: if one of them is aborted for any
reason, the two transactions have to be aborted.
I have always thought that the two-phase transaction was created to handle
this kind of use case, but if there is another better solution, I would be
very happy to know about it!

@jonathan, I made a patch to Pyramid DebugToolbar that I pushed to Github
and made a pull request. But I don't know how to provide a test case as a
two-phase commit is not supported by SQLite...
I'll try anyway to provide a description of a "method" I use to reproduce
this!

Best regards,
Thierry
-- 
  https://www.ulthar.net -- http://pyams.readthedocs.io


Le mer. 27 janv. 2021 à 19:19, Mike Bayer  a
écrit :

>
>
> On Wed, Jan 27, 2021, at 8:32 AM, Thierry Florac wrote:
>
>
> Hi,
> I'm actually using SQLAlchemy with Pyramid and zope.sqlalchemy packages.
> My main database connection is a ZODB connection and, when required, I
> create an SQLAlchemy session which is joined to main transaction using this
> kind of code:
>
> *  from *sqlalchemy.orm *import *scoped_session, sessionmaker
>
> *  from *zope.sqlalchemy *import *register
> *  from *zope.sqlalchemy.datamanager *import* join_transaction
>
>   _engine = get_engine(*engine*, *use_pool*)
>   if *use_zope_extension*:
>   factory = scoped_session(sessionmaker(*bind*=_engine, *twophase*=
> *True*))
>   else:
>   factory = sessionmaker(*bind*=_engine, *twophase*=*True*)
>   session = factory()
>   if *use_zope_extension*:
>   register(session, *initial_state*=*STATUS_ACTIVE*)
>   if *join*:
>   join_transaction(session, *initial_state*=*STATUS_ACTIVE*)
>
> Everything is working correctly!
>
> So my only question is that I also use Pyramid_debugtoolbar package, which
> is tracking many SQLAlchemy events, including two-phase commits
> transactions, and which in this context receives transaction IDs as a three
> values tuple instead of a simple string (like, for example: (4660,
> '12345678901234567890123456789012', '0009'),
> which is raising an exception)!
> Is it normal behaviour, and what does this value mean?
>
>
> I would ask if you really really want to use the "twophase=True" flag, and
> I would suggest turning it off if you aren't in fact coordinating against
> multiple RDBMS backends (and even if you are, maybe).   I'm not really sure
> what that tuple is, I'd have to look but it seems likely to be related to
> the XID stuff, which is really not something anyone uses these days.
>
>
>
> Best regards,
> Thierry
>
> --
>   https://www.ulthar.net -- http://pyams.readthedocs.io
>
>
> --
> 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 view this discussion on the web visit
> https://groups.google.com/d/msgid/sqlalchemy/CAPX_VWCwQmHEsynbsSa54%2BvkWH5xkZC_-hh359iM%3D%2BYh1f%3DE6A%40mail.gmail.com
> 
> .
>
>
> --
> 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 view this discussion on the web visit
> https://groups.google.com/d/msgid/sqlalchemy/e5458f0a-4cf1-4328-93e5-4c827641c590%40www.fastmail.com
> 
> .
>

-- 
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 view this discussion on the web visit 

Re: [sqlalchemy] mapper on a temporary class

[Mike Bayer]

On Tue, Jan 26, 2021, at 9:01 PM, Kent Bower wrote:
> Thanks a ton for your responses. 
> 
> Do all the normal columns of an aliased class need to match the ad-hoc select 
> to which I map the alias?

handwavy handwavy sort of yes, sort of no?there needs to be a 1-1 
correspondence of columns, yes, aliasedclass can't define additional attributes 
that arent there normally.  as to how they are lined up, there's an option to 
do it by name in the aliased() function adapt_on_names.  otherwise it's 
expected that your select() is derived from the Table objects that are 
originally mapped.


> 
> > oh if the class doesn't have a mapper, then defintiely, just make ad-hoc 
> > subclasses of it and map to those.vastly easier that way.
> 
> Mapping to a subclass would be different from mapping directly to an ad-hoc 
> class? 

if the class itself is ad-hoc, then you're fine, you can make new classes with 
mappers all you want and they are GCed if you lose them (assuming no 
relationships involving long-lived classes with backrefs), yes.




> 
> 
>  (Mostly I’m concerned whether the very act of adding a mapper to a class 
> will keep the reference and prevent its garbage collection or in some other 
> way modify the “main” mappers, especially if this is done via a thread. Like, 
> would that modify the compiled mappers for the entire process... these are 
> the things running through my head.)

the configure_mappers() function these days is threadsafe, it uses a mutex, so 
you are "good" there but there's a mutex involved if you are looking for high 
levels of concurrency.   if your ad-hoc classes are not subclasses of any long 
lived classes and if you are careful to limit relationships to only point to 
long lived classes and not have any backrefs, it should be OK.  I'd test it 
though :)set up a weakref.ref() to your ad-hoc mapper objects and make sure 
those ref objects get their callback hook invoked.




> 
> 
> On Tue, Jan 26, 2021 at 8:18 PM Mike Bayer  wrote:
>> __
>> 
>> 
>> On Tue, Jan 26, 2021, at 7:31 PM, Kent Bower wrote:
>>> I should have given these details from the get-go:  the use case is a 
>>> specialized select() (dynamically built) which would be extremely 
>>> convenient to map relationships against for convenience in subquery 
>>> loading, etc. So, the class would not already have a mapper.  Can I pass 
>>> non_primary=True anyway, or won’t this work?
>> 
>> oh if the class doesn't have a mapper, then defintiely, just make ad-hoc 
>> subclasses of it and map to those.vastly easier that way.
>> 
>> 
>> 
>>> 
>>> 
>>> 
>>> On Tue, Jan 26, 2021 at 6:18 PM Mike Bayer  wrote:
 __
 
 
 On Tue, Jan 26, 2021, at 3:16 PM, Kent wrote:
> Question: if I add a mapper to a class that is only needed temporarily, 
> does using the mapper compile it along side my "normal" mappers such that 
> I'll leak memory when I mean for the class to be garbage collected?
> 
> Put another way, can I add a mapper to a class that doesn't influence my 
> "main mappers" and gets thrown away when the class is thrown away or is 
> that not possible?
 
 Assuming the class already has a mapper, and this is an additional mapper 
 that maps the class in a different way, this feature is called a 
 "non-primary mapper" and requires that you pass the non_primary=True flag. 
If you are using non-primary mappers, then yes they get garbage 
 collected.
 
 This is also a deprecated feature that won't be in SQLAlchemy 2.0.The 
 only use case we can identify for "non-primary mappers" is when you want 
 to create a relationship() to one, and for that use case we now support 
 the "Relationship to AliasedClass" pattern.
 
 For anything else involving retrieving a class from an alternate 
 selectable of some kind, you should be able to use AliasedClass for, e.g. 
 sqlalchemy.orm.aliased(), so I would recommend using that instead unless 
 you are on a very old version of SQLAlchemy.
 
 
 
 
 
 
 
 
> Thanks in advance,
> Kent
> 

> -- 
> 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 view this discussion on the web visit 
> https://groups.google.com/d/msgid/sqlalchemy/df03ce24-3986-450c-8c3a-a020d5d6adccn%40googlegroups.com
>  
> 

Re: [sqlalchemy] SQLAlchemy transaction ID

[Mike Bayer]

On Wed, Jan 27, 2021, at 8:32 AM, Thierry Florac wrote:
> 
> Hi,
> I'm actually using SQLAlchemy with Pyramid and zope.sqlalchemy packages.
> My main database connection is a ZODB connection and, when required, I create 
> an SQLAlchemy session which is joined to main transaction using this kind of 
> code:
> 
> *  from *sqlalchemy.orm *import *scoped_session, sessionmaker
> 
> *  from *zope.sqlalchemy *import *register
> *  from *zope.sqlalchemy.datamanager *import* join_transaction
> 
>   _engine = get_engine(*engine*, *use_pool*)
>   if *use_zope_extension*:
>   factory = scoped_session(sessionmaker(*bind*=_engine, 
> *twophase*=*True*))
>   else:
>   factory = sessionmaker(*bind*=_engine, *twophase*=*True*)
>   session = factory()
>   if *use_zope_extension*:
>   register(session, *initial_state*=*STATUS_ACTIVE*)
>   if *join*:
>   join_transaction(session, *initial_state*=*STATUS_ACTIVE*)
> 
> Everything is working correctly!
> 
> So my only question is that I also use Pyramid_debugtoolbar package, which is 
> tracking many SQLAlchemy events, including two-phase commits transactions, 
> and which in this context receives transaction IDs as a three values tuple 
> instead of a simple string (like, for example: (4660, 
> '12345678901234567890123456789012', '0009'), 
> which is raising an exception)!
> Is it normal behaviour, and what does this value mean?

I would ask if you really really want to use the "twophase=True" flag, and I 
would suggest turning it off if you aren't in fact coordinating against 
multiple RDBMS backends (and even if you are, maybe).   I'm not really sure 
what that tuple is, I'd have to look but it seems likely to be related to the 
XID stuff, which is really not something anyone uses these days.


> 
> Best regards,
> Thierry
> 
> -- 
>   https://www.ulthar.net -- http://pyams.readthedocs.io
> 

> -- 
> 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 view this discussion on the web visit 
> https://groups.google.com/d/msgid/sqlalchemy/CAPX_VWCwQmHEsynbsSa54%2BvkWH5xkZC_-hh359iM%3D%2BYh1f%3DE6A%40mail.gmail.com
>  
> .

-- 
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 view this discussion on the web visit 
https://groups.google.com/d/msgid/sqlalchemy/e5458f0a-4cf1-4328-93e5-4c827641c590%40www.fastmail.com.

[sqlalchemy] FAQ or Feature Ideas for ORM Object and Session

['Jonathan Vanasco' via sqlalchemy]

I've been working with a handful of SQLAlchemy and Pyramid based projects 
recently, and two situations have repeatedly come up:

1. Given a SQLAlchemy Object, access the SQLAlchemy Session
2. Given a SQLAlchemy Object or Session, access the Pyramid Request object

The general solutions I've used to handle this is:

1. An Object can use the runtime inspection API to grab it's active session:

from sqlalchemy import inspect

@property
def _active_session(self):
dbSession = inspect(self).session
return dbSession

2.  Attach the Pyramid request to the session_factory when a session is 
created:

def get_tm_session(request):
dbSession = session_factory()
zope.sqlalchemy.register(dbSession, 
transaction_manager=transaction_manager, keep_session=True)
if request is not None:
def _cleanup(request):
dbSession.close()
request.add_finished_callback(_cleanup)
# cache our request onto the dbsession
dbSession.pyramid_request = request
return dbSession

I've needed to implement these patterns in a lot of projects. This makes me 
wonder if there is/could be a better way.


1.  Would it be beneficial if ORM objects could surface the current 
Session, if any, as a documented property ?  I do this in my base classes, 
but with the overhead of the inspect system, and I repeat this in every 
project.

2.  Would it be better for the sessionmaker had any of ?

a. An official namespace were developers could attach information.  I'm 
using `pyramid_request` because I doubt SQLAlchemy will every step on that 
- but it would be nice if there were a dedicated attribute/object/namespace 
on the Session
b. `sqlalchemy.orm.session.Session()` could accept a 
dict/payload/object/whatever on init, which would be attached to a single 
session in the aforementioned dedicated namespace. 

The usage would be something like:

sess = Session(customized={"request": request})

which might then me accessed as:

sess.customized.request






-- 
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 view this discussion on the web visit 
https://groups.google.com/d/msgid/sqlalchemy/b5031f10-c2c8-4065-b968-3a55f2bf6daen%40googlegroups.com.

[sqlalchemy] Re: SQLAlchemy transaction ID

['Jonathan Vanasco' via sqlalchemy]

Thierry,

Would you mind putting together a test-case on this?  I haven't experienced 
that before, and I authored that feature in the debugtoolbar.  If I can 
recreate it, I'll put together a fix and work with the pyramid team to get 
a new release out asap.

On Wednesday, January 27, 2021 at 8:32:34 AM UTC-5 tfl...@gmail.com wrote:

> Hi,
> I'm actually using SQLAlchemy with Pyramid and zope.sqlalchemy packages.
> My main database connection is a ZODB connection and, when required, I 
> create an SQLAlchemy session which is joined to main transaction using this 
> kind of code:
>
>   from sqlalchemy.orm import scoped_session, sessionmaker
>
>   from zope.sqlalchemy import register
>   from zope.sqlalchemy.datamanager import join_transaction
>
>   _engine = get_engine(engine, use_pool)
>   if use_zope_extension:
>   factory = scoped_session(sessionmaker(bind=_engine, twophase=True))
>   else:
>   factory = sessionmaker(bind=_engine, twophase=True)
>   session = factory()
>   if use_zope_extension:
>   register(session, initial_state=STATUS_ACTIVE)
>   if join:
>   join_transaction(session, initial_state=STATUS_ACTIVE)
>
> Everything is working correctly!
>
> So my only question is that I also use Pyramid_debugtoolbar package, which 
> is tracking many SQLAlchemy events, including two-phase commits 
> transactions, and which in this context receives transaction IDs as a three 
> values tuple instead of a simple string (like, for example: (4660, 
> '12345678901234567890123456789012', '0009'), 
> which is raising an exception)!
> Is it normal behaviour, and what does this value mean?
>
> Best regards,
> Thierry
>
> -- 
>   https://www.ulthar.net -- http://pyams.readthedocs.io
>

-- 
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 view this discussion on the web visit 
https://groups.google.com/d/msgid/sqlalchemy/8728dadd-102f-4751-a798-d1a5794145den%40googlegroups.com.

[sqlalchemy] SQLAlchemy transaction ID

[Thierry Florac]

Hi,
I'm actually using SQLAlchemy with Pyramid and zope.sqlalchemy packages.
My main database connection is a ZODB connection and, when required, I
create an SQLAlchemy session which is joined to main transaction using this
kind of code:

  from sqlalchemy.orm import scoped_session, sessionmaker

  from zope.sqlalchemy import register
  from zope.sqlalchemy.datamanager import join_transaction

  _engine = get_engine(engine, use_pool)
  if use_zope_extension:
  factory = scoped_session(sessionmaker(bind=_engine, twophase=True))
  else:
  factory = sessionmaker(bind=_engine, twophase=True)
  session = factory()
  if use_zope_extension:
  register(session, initial_state=STATUS_ACTIVE)
  if join:
  join_transaction(session, initial_state=STATUS_ACTIVE)

Everything is working correctly!

So my only question is that I also use Pyramid_debugtoolbar package, which
is tracking many SQLAlchemy events, including two-phase commits
transactions, and which in this context receives transaction IDs as a three
values tuple instead of a simple string (like, for example: (4660,
'12345678901234567890123456789012', '0009'),
which is raising an exception)!
Is it normal behaviour, and what does this value mean?

Best regards,
Thierry

-- 
  https://www.ulthar.net -- http://pyams.readthedocs.io

-- 
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 view this discussion on the web visit 
https://groups.google.com/d/msgid/sqlalchemy/CAPX_VWCwQmHEsynbsSa54%2BvkWH5xkZC_-hh359iM%3D%2BYh1f%3DE6A%40mail.gmail.com.