[sqlalchemy] Re: Testing against DB Connector IntegrityError?
To clarify on my post just now: My problem was that I did with scoped_session() as session: # setup stuff with pytest.raises(IntegrityError): session.add(entity) session.commit() # This should be inside the raises context. So the syntax in the orignal post here is actually correct (I had to manually fix the indent for the code formatting). -- 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] Testing against DB Connector IntegrityError?
Examining the stacktrace more closely revealed that I made the mistake of checking for the error on the add but left the context of that error check before committing. I actually did it the correct way in my all my other tests so I'm not sure why I made the mistake at the very end. My apologies for the false alarm. It works as expected. -- 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] Testing against DB Connector IntegrityError?
In one of my tests I'm checking that a pair of (non primary) fields must be
unique. This definition is done with:
__table_args__ = (UniqueConstraint('entity_id', 'version', name=
'id_version_combined_unique'),)
where `entity_id` is an INT column referring to a foreign id and version is
a plain INT column.
The way I test for it (using pytest):
from sqlalchemy.exc import IntegrityError
def test_entity_version_entity_version_combined_unique(session_with_entities
):
# Ensure created object has duplicate data
with pytest.raises(IntegrityError):
session.add(version_conflict)
session.commit()
But the type of exceptions I get are of type sqlite3.IntegrityError or
pymysql.IntegrityError. Basically the connector's version of the error.
I couldn't find a proper super class (unless you count Exception). I
thought maybe SQLAlchemy's DBAPIError would be able to do it based on the
documentation but it didn't cut it.
Is there a proper way to catch these implementation specific exceptions?
Kind regards,
Kevin CYT
--
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?
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.
Re: [sqlalchemy] How to maintain a tight transactional scope whilst allowing lazy loading / attribute refreshing?
On Tuesday, 6 March 2018 21:12:01 UTC+1, Mike Bayer wrote: > > On Tue, Mar 6, 2018 at 2:52 PM, KCY <kevi...@hotmail.com > >> 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?
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.
[sqlalchemy] Re: How to maintain a tight transactional scope whilst allowing lazy loading / attribute refreshing?
(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?
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?
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] How to maintain a tight transactional scope whilst allowing lazy loading / attribute refreshing?
*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
