[sqlalchemy] Polymorphic AbstractConcreteBase , eager load , conditions , etc

[Harshvardhan Gupta]

I tried to use AbstractConcreteBase for polymorphic relationships , but I 
am getting errors. The examples in sqlalchemy cover normal polymorphism 
well, but not those with Abstract Base classes. 


I have already asked a question on stack overflow  

. 

The gist of the question is: 

class OrderItem(Dictifiable, AbstractConcreteBase, db.Model):
pass


class OrderTestItem(OrderItem):
order_id = Column(Integer, ForeignKey("order.id"), primary_key=True)
test_id = Column(Integer, ForeignKey("test.id"), primary_key=True)

test = relationship('Test')
order = relationship('Order')

__mapper_args__ = {
'polymorphic_identity': 'order_test_item',
'concrete': True
}



class Order(Dictifiable, db.Model): # This class has a relation to the 
polymorphic class


id = Column(Integer, Sequence('user_id_seq'), primary_key=True)

user_id = Column(Integer, ForeignKey('user.id'))

user = relationship('User')
items = relationship('OrderItem')


I query like : 

pjoin = polymorphic_union({
'order_test_item': OrderTestItem.__table__,
}, 'type', 'pjoin')

order_items = 
with_polymorphic(OrderItem,[OrderTestItem],selectable=pjoin,flat=True)

And my actual query : 

order = Order.query.join(Order.items.of_type(order_items)).all()


I would like to know what the correct way to query these tables is, how to 
eager load polymorphic tables, and how to filter on the relationships. 

I plan to send a pull request with an example of these test cases after I know 
the answers myself. 


-- 
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] Re: How to maintain a tight transactional scope whilst allowing lazy loading / attribute refreshing?

[KCY]

On Tuesday, 6 March 2018 22:30:46 UTC+1, Jonathan Vanasco wrote:
>
>
>
> On Tuesday, March 6, 2018 at 3:23:42 PM UTC-5, KCY wrote:
>>
>> I recall coming upon a section about this in the SQLAlchemy docs, 
>> although I can't remember where exactly. It's not the problem (if you can 
>> call it that) that I'm describing here. I should double check to make sure 
>> the design doesn't expect to have concurrent edits on the same objects.
>>
>
> I'm not suggesting it is the problem. What I am suggesting is that you're 
> very likely going to have a concurrency/race problem when there is a first 
> Session that lasts the scope of web requests, and a second session which 
> operates on a GUI (you're likely to have this on a web requests only 
> variant too).
>

Ah right, I misunderstood. Thank you for clarifying.

-- 
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] Re: How to maintain a tight transactional scope whilst allowing lazy loading / attribute refreshing?

[Jonathan Vanasco]

On Tuesday, March 6, 2018 at 3:23:42 PM UTC-5, KCY wrote:
>
> I recall coming upon a section about this in the SQLAlchemy docs, although 
> I can't remember where exactly. It's not the problem (if you can call it 
> that) that I'm describing here. I should double check to make sure the 
> design doesn't expect to have concurrent edits on the same objects.
>

I'm not suggesting it is the problem. What I am suggesting is that you're 
very likely going to have a concurrency/race problem when there is a first 
Session that lasts the scope of web requests, and a second session which 
operates on a GUI (you're likely to have this on a web requests only 
variant too).

-- 
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] How to maintain a tight transactional scope whilst allowing lazy loading / attribute refreshing?

[KCY]

On Tuesday, 6 March 2018 21:12:01 UTC+1, Mike Bayer wrote:
>
> On Tue, Mar 6, 2018 at 2:52 PM, KCY  
>> wrote: 
>> > First off thank you for the quick reply. I have seen those resources 
>> you 
>> > linked a few days ago and it guided me partially to my current ideas. 
>> The 
>> > RepositoryContext class is essentially the contextmanager example with 
>> some 
>> > extra helper methods. 
>> > 
>> > I think in trying to keep my example concise I left out too much detail 
>> to 
>> > illustrate the potential problem I'm trying to avoid. To expand upon 
>> it, I 
>> > can imagine a scenario where there are 2 branches, each with their set 
>> of 
>> > leaves. In a scenario with some tabbed UI where both branches are 
>> "open" 
>> > some editing happens on both but that the intention upon saving is to 
>> only 
>> > save the current tab's branch but not commit any of the other changes. 
>>
>> OK so within the scope of "editing" that's where I'd put transactional 
>> scope.E.g. transaction per tab.  But I wouldn't leave these 
>> transactions open for an event-driven GUI waiting for input, only when 
>> it's time to manipulate the state of the database. 
>>
>
That seems like a good idea, I'll have to experiment a bit to see what we 
end up with.

I'll post an update once I have a more fleshed out solution for anyone else 
who is interested.

-- 
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] Re: How to maintain a tight transactional scope whilst allowing lazy loading / attribute refreshing?

[KCY]

I recall coming upon a section about this in the SQLAlchemy docs, although 
I can't remember where exactly. It's not the problem (if you can call it 
that) that I'm describing here. I should double check to make sure the 
design doesn't expect to have concurrent edits on the same objects.

On Tuesday, 6 March 2018 21:10:03 UTC+1, Jonathan Vanasco wrote:
>
> I can't talk about the Session aspect, but IMHO you should track each 
> object with a revision-id or revision-timestamp.  that would allow you to 
> prevent race conditions on edits (you can ensure that an edit applies to 
> the right version), and your GUI can potentially just query the database to 
> detect if the revision changed or not.
>

-- 
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] How to maintain a tight transactional scope whilst allowing lazy loading / attribute refreshing?

[Mike Bayer]

On Tue, Mar 6, 2018 at 2:52 PM, KCY  wrote:
> First off thank you for the quick reply. I have seen those resources you
> linked a few days ago and it guided me partially to my current ideas. The
> RepositoryContext class is essentially the contextmanager example with some
> extra helper methods.
>
> I think in trying to keep my example concise I left out too much detail to
> illustrate the potential problem I'm trying to avoid. To expand upon it, I
> can imagine a scenario where there are 2 branches, each with their set of
> leaves. In a scenario with some tabbed UI where both branches are "open"
> some editing happens on both but that the intention upon saving is to only
> save the current tab's branch but not commit any of the other changes.

OK so within the scope of "editing" that's where I'd put transactional
scope.E.g. transaction per tab.  But I wouldn't leave these
transactions open for an event-driven GUI waiting for input, only when
it's time to manipulate the state of the database.

>
> Based on your reply I do think that perhaps I'm trying to cover all my use
> cases too soon. Given that I do not know all the potential requirements it
> is not feasible to hide away the session completely. To continue on the
> expanded example; this is much easier to solve within a controller /
> presenter type layer that tracks which tab corresponds to which entity and
> then propagating changes to the corresponding entities based on the
> command(s) given.

yeah i was kind of suggesting boring old MVC :)   (like the real, pre-web kind).


>
> For now I think I will take a step back from the persistence layer and flesh
> out the remaining parts of the application. Once I get around to integrating
> it with a proper GUI I will finalize the design.
>
> Thanks again for the quick and detailed reply.
>
>
> On Tuesday, 6 March 2018 19:09:18 UTC+1, Mike Bayer wrote:
>>
>>
>> Some background on this problem is first at:
>>
>> http://docs.sqlalchemy.org/en/latest/orm/session_basics.html#when-do-i-construct-a-session-when-do-i-commit-it-and-when-do-i-close-it
>>  which you might have read already, and also I did a talk that tries
>> to define what perspective the Session is coming from at:
>> http://www.sqlalchemy.org/library.html#thesqlalchemysessionindepth
>>
>> So as far as goals, abstracting away session management is absolutely
>> a great idea and all the things I've written suggest that this is the
>> case.   It doesn't however imply that the entire session is invisible,
>> only that the points at which the session is started and ended are
>> defined in just one place in the application.   The web app case makes
>> this easy since you link the session to the request, but other
>> approaches including having context managers (e.g. with
>> transaction():) or decorators.   You can still have explicit scopes in
>> an application, I just recommend hiding away as much nuts and bolts as
>> is possible.
>>
>> Next part of "goals" here, you refer to an example use case.   I think
>> part of the key is just looking at the terms you used: "save" a leaf,
>> "save" a branch.   We all know the term "save" because that's what we
>> use to refer to document management software, e.g. a word processor,
>> graphical editing tool, or virtually anything else: we "open" our
>> document, we "edit" it, then we "save" it.  The notion that the
>> document is separate from some place that it gets stored is intrinsic.
>>
>> Note that in SQLAlchemy's Session API, the word "save" is not
>> generally used (just for the "save-update" cascade option).   We
>> instead use "add()" and "commit()".   These terms are intentional and
>> they are intended to emphasize that SQLAlchemy's ORM does not view
>> relationally-persisted Python objects with a document-oriented model,
>> because that's not actually how the database sees them.In your
>> example, Tree, Leaf and Branch are highly interrelated - they each
>> have a non-nullable foreign key to their parent table.   It is
>> therefore very awkward to say that we want to "save" one and not the
>> other kind of object; while a "save" of a Tree without the Branch
>> makes sense, it does not make sense to "save" the Branch without the
>> Tree because of the dependencies.
>>
>> If we try to apply the reality of Tree/Leaf/Branch to the document
>> model, it's like saying you're in a word processor, and your users
>> would want to "save" every third paragraph of the document, but not
>> the other two.   This is not feasible or even useful.   In reality,
>> the user works with the "document" and the formatting, paragraphs and
>> text within it are all components of that single unit.
>>
>> In a relational database, the single unit we deal with is the
>> transaction - that's the thing we are "opening" and "saving", if
>> anything, even though this doesn't fit quite so well.  The transaction
>> represents this workspace that we ask our database to create for us,
>> within which we 

Re: [sqlalchemy] Re: not announcing minor releases on the mailing list or twitter for now

[Mike Bayer]

On Tue, Mar 6, 2018 at 2:47 PM, Jonathan Vanasco  wrote:
> I can possibly help with the Twitter/blog stuff, I know that sort of stuff
> well.
>
> I'll absolutely try to help, I'm just not sure I can do what you exactly
> envision in a convenient timeframe.

that's the thing, I'm mostly done for now :)   need to get back to the
dayjob for a few weeks

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

[sqlalchemy] Re: How to maintain a tight transactional scope whilst allowing lazy loading / attribute refreshing?

[KCY]

(Third time's the charm, I messed up my original reply)

First off thank you for the quick reply. I have seen those resources you 
linked a few days ago and it guided me partially to my current ideas. The 
RepositoryContext class is essentially the contextmanager example with some 
extra helper methods.

I think in trying to keep my example concise I left out too much detail to 
illustrate the potential problem I'm trying to avoid. To expand upon it, I 
can imagine a scenario where there are 2 branches, each with their set of 
leaves. In a scenario with some tabbed UI where both branches are "open" 
some editing happens on both but that the intention upon saving is to only 
save the current tab's branch but not commit any of the other changes.

Based on your reply I do think that perhaps I'm trying to cover all my use 
cases too soon. Given that I do not know all the potential requirements it 
is not feasible to hide away the session completely. To continue on the 
expanded example; this is much easier to solve within a controller / 
presenter type layer that tracks which tab corresponds to which entity and 
then propagating changes to the corresponding entities based on the 
command(s) given.

For now I think I will take a step back from the persistence layer and 
flesh out the remaining parts of the application. Once I get around to 
integrating it with a proper GUI I will finalize the design.

Thanks again for the quick and detailed reply.

-- 
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] Re: How to maintain a tight transactional scope whilst allowing lazy loading / attribute refreshing?

[KCY]

I didn't mean to include that whole message history and I can't seem to 
edit/delete it. My apologies.

-- 
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] How to maintain a tight transactional scope whilst allowing lazy loading / attribute refreshing?

[KCY]

First off thank you for the quick reply. I have seen those resources you 
linked a few days ago and it guided me partially to my current ideas. The 
RepositoryContext class is essentially the contextmanager example with some 
extra helper methods.

I think in trying to keep my example concise I left out too much detail to 
illustrate the potential problem I'm trying to avoid. To expand upon it, I 
can imagine a scenario where there are 2 branches, each with their set of 
leaves. In a scenario with some tabbed UI where both branches are "open" 
some editing happens on both but that the intention upon saving is to only 
save the current tab's branch but not commit any of the other changes.

Based on your reply I do think that perhaps I'm trying to cover all my use 
cases too soon. Given that I do not know all the potential requirements it 
is not feasible to hide away the session completely. To continue on the 
expanded example; this is much easier to solve within a controller / 
presenter type layer that tracks which tab corresponds to which entity and 
then propagating changes to the corresponding entities based on the 
command(s) given.

For now I think I will take a step back from the persistence layer and 
flesh out the remaining parts of the application. Once I get around to 
integrating it with a proper GUI I will finalize the design.

Thanks again for the quick and detailed reply.

On Tuesday, 6 March 2018 19:09:18 UTC+1, Mike Bayer wrote:

>
> Some background on this problem is first at: 
>
> http://docs.sqlalchemy.org/en/latest/orm/session_basics.html#when-do-i-construct-a-session-when-do-i-commit-it-and-when-do-i-close-it
>  
>  which you might have read already, and also I did a talk that tries 
> to define what perspective the Session is coming from at: 
> http://www.sqlalchemy.org/library.html#thesqlalchemysessionindepth 
>
> So as far as goals, abstracting away session management is absolutely 
> a great idea and all the things I've written suggest that this is the 
> case.   It doesn't however imply that the entire session is invisible, 
> only that the points at which the session is started and ended are 
> defined in just one place in the application.   The web app case makes 
> this easy since you link the session to the request, but other 
> approaches including having context managers (e.g. with 
> transaction():) or decorators.   You can still have explicit scopes in 
> an application, I just recommend hiding away as much nuts and bolts as 
> is possible. 
>
> Next part of "goals" here, you refer to an example use case.   I think 
> part of the key is just looking at the terms you used: "save" a leaf, 
> "save" a branch.   We all know the term "save" because that's what we 
> use to refer to document management software, e.g. a word processor, 
> graphical editing tool, or virtually anything else: we "open" our 
> document, we "edit" it, then we "save" it.  The notion that the 
> document is separate from some place that it gets stored is intrinsic. 
>
> Note that in SQLAlchemy's Session API, the word "save" is not 
> generally used (just for the "save-update" cascade option).   We 
> instead use "add()" and "commit()".   These terms are intentional and 
> they are intended to emphasize that SQLAlchemy's ORM does not view 
> relationally-persisted Python objects with a document-oriented model, 
> because that's not actually how the database sees them.In your 
> example, Tree, Leaf and Branch are highly interrelated - they each 
> have a non-nullable foreign key to their parent table.   It is 
> therefore very awkward to say that we want to "save" one and not the 
> other kind of object; while a "save" of a Tree without the Branch 
> makes sense, it does not make sense to "save" the Branch without the 
> Tree because of the dependencies. 
>
> If we try to apply the reality of Tree/Leaf/Branch to the document 
> model, it's like saying you're in a word processor, and your users 
> would want to "save" every third paragraph of the document, but not 
> the other two.   This is not feasible or even useful.   In reality, 
> the user works with the "document" and the formatting, paragraphs and 
> text within it are all components of that single unit. 
>
> In a relational database, the single unit we deal with is the 
> transaction - that's the thing we are "opening" and "saving", if 
> anything, even though this doesn't fit quite so well.  The transaction 
> represents this workspace that we ask our database to create for us, 
> within which we manipulate as much data as we'd like, then we persist 
> it back. I wouldn't build an application that tries to address the 
> case of the user that wants to "save" a branch but not a leaf, I would 
> address the use case of an application where the user wants to open up 
> a session that works with a series of interlinked objects and persists 
> it.  That is, while I don't think you should have them setting up 
> their own sessionmaker() 

[sqlalchemy] Re: not announcing minor releases on the mailing list or twitter for now

[Jonathan Vanasco]

I can possibly help with the Twitter/blog stuff, I know that sort of stuff 
well.

I'll absolutely try to help, I'm just not sure I can do what you exactly 
envision in a convenient timeframe.

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

not announcing minor releases on the mailing list or twitter for now

[Mike Bayer]

Hi all -

Some of you may have noticed that several SQLAlchemy releases have
gone out in the past few weeks without my customary email on these
lists, nor without a tweet from the SQLAlchemy twitter account.

The reason for this is that I have created scripts that
comprehensively run through the many, many steps used to produce a
SQLAlchemy release, including building the documentation, changing
dates, writing the CHANGES file, applying all the tags and such, with
the goal being that for me to personally release SQLAlchemy is no
longer a life-changing event;   I can instead release as needed, e.g.
every few weeks if needed or if urgent changes have been committed
even every few days, without it becoming a major time sink and more
importantly without my needing to be "on" mentally; I can do it
automatically and not worry too much about things going wrong if I'm
tired or not able to concentrate deeply.

Previously, the burden of releasing would cause me to wait longer, and
more critically the release would continually get pushed back as more
little bug reports and pull requests kept coming in and I would want
them all "on board" so that I wouldn't have to worry about releasing
again.  This was a dysfunctional way of operating, and while we can
probably thank "slight dysfunction" as one of the reasons SQLAlchemy
even exists, making releases much easier is now allowing me to just
put out a release without waiting to be alert and while knowing I can
get to the next round of pending issues and release them just as
easily.

I am still adding a brief blog post to the site per release, and that
part is yet to be "automated", although I would like to make this part
of the automated process as well.

To that end, the production of the blog post would also perform a copy
operation out to the google groups mailing list, and also to the
SQLAlchemy twitter account.  Automating these tasks is not something I
have the time to do right now, as just getting the bulk of the release
process was enough. All of SQLAlchemy's infrastructure has been
fully scripted at this point, including all web servers and CI servers
are built and maintained using Ansible scripts, and the release
scripts use bash scripting.  Contributors who have an interest in
being part of SQLAlchemy's "infrastructure" project and who may wish
to help with automating mailing list and twitter posts can be added to
the group that has access to the source code for these tools.

thanks all for listening!

- mike


So for now, because I need to be able to push out a release quickly and without

-- 
You received this message because you are subscribed to the Google Groups 
"sqlalchemy-alembic" group.
To unsubscribe from this group and stop receiving emails from it, send an email 
to sqlalchemy-alembic+unsubscr...@googlegroups.com.
For more options, visit https://groups.google.com/d/optout.

[sqlalchemy] not announcing minor releases on the mailing list or twitter for now

[Mike Bayer]

Hi all -

Some of you may have noticed that several SQLAlchemy releases have
gone out in the past few weeks without my customary email on these
lists, nor without a tweet from the SQLAlchemy twitter account.

The reason for this is that I have created scripts that
comprehensively run through the many, many steps used to produce a
SQLAlchemy release, including building the documentation, changing
dates, writing the CHANGES file, applying all the tags and such, with
the goal being that for me to personally release SQLAlchemy is no
longer a life-changing event;   I can instead release as needed, e.g.
every few weeks if needed or if urgent changes have been committed
even every few days, without it becoming a major time sink and more
importantly without my needing to be "on" mentally; I can do it
automatically and not worry too much about things going wrong if I'm
tired or not able to concentrate deeply.

Previously, the burden of releasing would cause me to wait longer, and
more critically the release would continually get pushed back as more
little bug reports and pull requests kept coming in and I would want
them all "on board" so that I wouldn't have to worry about releasing
again.  This was a dysfunctional way of operating, and while we can
probably thank "slight dysfunction" as one of the reasons SQLAlchemy
even exists, making releases much easier is now allowing me to just
put out a release without waiting to be alert and while knowing I can
get to the next round of pending issues and release them just as
easily.

I am still adding a brief blog post to the site per release, and that
part is yet to be "automated", although I would like to make this part
of the automated process as well.

To that end, the production of the blog post would also perform a copy
operation out to the google groups mailing list, and also to the
SQLAlchemy twitter account.  Automating these tasks is not something I
have the time to do right now, as just getting the bulk of the release
process was enough. All of SQLAlchemy's infrastructure has been
fully scripted at this point, including all web servers and CI servers
are built and maintained using Ansible scripts, and the release
scripts use bash scripting.  Contributors who have an interest in
being part of SQLAlchemy's "infrastructure" project and who may wish
to help with automating mailing list and twitter posts can be added to
the group that has access to the source code for these tools.

thanks all for listening!

- mike


So for now, because I need to be able to push out a release quickly and without

-- 
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] How to maintain a tight transactional scope whilst allowing lazy loading / attribute refreshing?

[Mike Bayer]

On Tue, Mar 6, 2018 at 5:14 AM, KCY  wrote:
> Context
>
> I'm currently designing the business and persistence layer that is going to
> be used in various frontend applications (Web and standalone). To that end
> I've been trying to reconcile ORM entities with a tight session scope but
> I'm constantly running into the same issues. For web I haven't had a big
> problem as sessions scoped to a single request work just fine, but for
> standalone applications I've been having architectural problems. To
> illustrate this, I'm using a simplified example.
>
> Setup - Model and database definition
>
> # MODELS
>
> class _Base(object):
> @declared_attr
> def __tablename__(cls):# This just generates table name from the
> class name.
> name = cls.__name__
> table_name = name[0]
> for c in name[1:]:
> if c.isupper():
> table_name += '_'
> table_name += c
> return table_name.lower()
>
> id = Column(Integer, primary_key=True)
>
>
> Base = declarative_base(cls=_Base)
>
> class Tree(Base):
> type = Column(String(200))
>
> branches = relationship("Branch", back_populates="tree")
>
> def __repr__(self):
> return "".format(self.id,
> self.type, self.branches)
>
>
> class Branch(Base):
> name = Column(String(200))
>
> tree_id = Column(Integer, ForeignKey('tree.id'), nullable=False)
> tree = relationship("Tree", back_populates='branches')
>
>
> leaves = relationship("Leaf", back_populates='branch')
>
> def __repr__(self):
> return "".format(self.id,
> self.name, self.leaves)
>
>
> class Leaf(Base):
> size = Column(Integer)
>
> branch_id = Column(Integer, ForeignKey('branch.id'), nullable=False)
> branch = relationship("Branch", back_populates='leaves')
>
> def __repr__(self):
> return "".format(self.id, self.size)
>
>
> # Database setup
>
> db_conn_string = "sqlite://"
> self.engine = create_engine(db_conn_string)
> Base.metadata.bind = self.engine
> self.DBSession = sessionmaker(bind=self.engine)
>
>
> Goals
>
> Since this layer will be interacted with by many other developers in the
> future I wanted to abstract away session management. This in turn means I
> want to prevent potential side effects from occurring.
>
> As an example: In a scenario where someone modifies a Leaf object (but
> doesn't want to save it yet) and then proceeds to modify a Branch object
> elsewhere and saves it. The expected behaviour here is that the Branch
> modification is persisted, but not the Leaf changes. So clearly having an
> application wide session is not a good idea, but how do I properly separate
> this out?

Some background on this problem is first at:
http://docs.sqlalchemy.org/en/latest/orm/session_basics.html#when-do-i-construct-a-session-when-do-i-commit-it-and-when-do-i-close-it
 which you might have read already, and also I did a talk that tries
to define what perspective the Session is coming from at:
http://www.sqlalchemy.org/library.html#thesqlalchemysessionindepth

So as far as goals, abstracting away session management is absolutely
a great idea and all the things I've written suggest that this is the
case.   It doesn't however imply that the entire session is invisible,
only that the points at which the session is started and ended are
defined in just one place in the application.   The web app case makes
this easy since you link the session to the request, but other
approaches including having context managers (e.g. with
transaction():) or decorators.   You can still have explicit scopes in
an application, I just recommend hiding away as much nuts and bolts as
is possible.

Next part of "goals" here, you refer to an example use case.   I think
part of the key is just looking at the terms you used: "save" a leaf,
"save" a branch.   We all know the term "save" because that's what we
use to refer to document management software, e.g. a word processor,
graphical editing tool, or virtually anything else: we "open" our
document, we "edit" it, then we "save" it.  The notion that the
document is separate from some place that it gets stored is intrinsic.

Note that in SQLAlchemy's Session API, the word "save" is not
generally used (just for the "save-update" cascade option).   We
instead use "add()" and "commit()".   These terms are intentional and
they are intended to emphasize that SQLAlchemy's ORM does not view
relationally-persisted Python objects with a document-oriented model,
because that's not actually how the database sees them.In your
example, Tree, Leaf and Branch are highly interrelated - they each
have a non-nullable foreign key to their parent table.   It is
therefore very awkward to say that we want to "save" one and not the
other kind of object; while a "save" of a Tree without the Branch
makes sense, it does not make sense to 

[sqlalchemy] How to maintain a tight transactional scope whilst allowing lazy loading / attribute refreshing?

[KCY]

*Context*

I'm currently designing the business and persistence layer that is going to 
be used in various frontend applications (Web and standalone). To that end 
I've been trying to reconcile ORM entities with a tight session scope but 
I'm constantly running into the same issues. For web I haven't had a big 
problem as sessions scoped to a single request work just fine, but for 
standalone applications I've been having architectural problems. To 
illustrate this, I'm using a simplified example.

*Setup - Model and database definition*

*# MODELS*

class _Base(object):
@declared_attr
def __tablename__(cls):# This just generates table name from the class 
name.
name = cls.__name__
table_name = name[0]
for c in name[1:]:
if c.isupper():
table_name += '_'
table_name += c
return table_name.lower()

id = Column(Integer, primary_key=True)


Base = declarative_base(cls=_Base)

class Tree(Base):
type = Column(String(200))

branches = relationship("Branch", back_populates="tree")

def __repr__(self):
return "".format(self.id, 
self.type, self.branches)


class Branch(Base):
name = Column(String(200))

tree_id = Column(Integer, ForeignKey('tree.id'), nullable=False)
tree = relationship("Tree", back_populates='branches')


leaves = relationship("Leaf", back_populates='branch')

def __repr__(self):
return "".format(self.id, self.name, 
self.leaves)


class Leaf(Base):
size = Column(Integer)

branch_id = Column(Integer, ForeignKey('branch.id'), nullable=False)
branch = relationship("Branch", back_populates='leaves')

def __repr__(self):
return "".format(self.id, self.size)


# Database setup

db_conn_string = "sqlite://"
self.engine = create_engine(db_conn_string)
Base.metadata.bind = self.engine
self.DBSession = sessionmaker(bind=self.engine)


*Goals*

Since this layer will be interacted with by many other developers in the 
future I wanted to abstract away session management. This in turn means I 
want to prevent potential side effects from occurring.

As an example: In a scenario where someone modifies a Leaf object (but 
doesn't want to save it yet) and then proceeds to modify a Branch object 
elsewhere and saves it. The expected behaviour here is that the Branch 
modification is persisted, but not the Leaf changes. So clearly having an 
application wide session is not a good idea, but how do I properly separate 
this out?

*Attempted solutions*

*1. Detached entities with eager loading.*

Initially I simply closed each session once a context was closed and tried 
to use the detached objects.

class RepositoryContext(object):
def __enter__(self):
self.session = get_session()
return CrudRepository(self.session)# Provides simple crud methods 
like add(entity), retrieve_all(entity_class), etc...

def __exit__(self, exc_type, exc_val, exc_tb):
try:
self.session.commit()
except Exception:
self.session.rollback()
raise
finally:
self.session.close()


I mark all relationships I want as eager loaded relations using 
`lazy='subquery'` and remove relationship definitions where that is not the 
case. My new model looks like this:

class Tree(Base):
type = Column(String(200))

branches = relationship("Branch", lazy="subquery")

def __repr__(self):
return "".format(self.id, 
self.type, self.branches)


class Branch(Base):
name = Column(String(200))

tree_id = Column(Integer, ForeignKey('tree.id'), nullable=False)


def __repr__(self):
return "".format(self.id, self.name)


class Leaf(Base):
size = Column(Integer)

branch_id = Column(Integer, ForeignKey('branch.id'), nullable=False)

def __repr__(self):
return "".format(self.id, self.size)


So if I want to get a relationship that was previously lazy loaded I'd have 
to load it within a RepositoryContext, which I could live with.

The problem happens when I start updating entries. Because of the detached 
nature I'm forced to manually refresh entities each time they are updated. 
This means instead of a simple update statement I now have to perform this 
*merge-commit-add-refresh* cycle for every entity. It technically works but 
it's performing a lot more database requests than it should and I fear this 
will not scale properly.

*2. Separate commit session*

Another solution I've tried is to have two sessions, one that is 
application wide and another that is newly created within a new context. 
The idea is to have a "link_session" to which entities keep attached to so 
they can load/refresh attributes and have a "merge_session" which perform 
insert/updates/removals. Whilst seeming like a good idea at first I seem to 
be having trouble