My advice is to simply make a sqlite memory database for testing which is an almost universal pattern for test suites.
You can even make a database per test, that's a common pattern. Otherwise you have to mock everything and yes "double job" it. The ORM doesn't want to "double job" it either, with a sqlite memory database easily available it doesn't have to reinvent what the database does, which would be not only a massively complicated endeavor it also would never behave quite the same way either. On Sat, Sep 29, 2018, 3:47 PM seaders <seader...@gmail.com> wrote: > I've made my system with SQLAlchemy objects pretty pivotal to how it > runs. For testing, I'm not actually testing the dB, I'm testing other > areas of flow of my project, so - as much as possible - I don't necessarily > want to change its setup. > > I would just like to have a kind of "snapshot" of my system, at a point of > time, with everything set up correctly, that I can use for multiple tests. > > In the "normal" scheme of things, the system starts up, connects to the > database, gets everything it needs from there, then does the rest of its > business. I specify a specific competition, it then gets the sport of that > competition, then gets the events of that competition, then a plethora of > dB things that are connected to those events, and things connected to > those, so on and so forth. > > I have my "snapshot" taken and stored as a json file, and what I'm looking > for is a way to put it all back together, as if it'd come from the > database. Currently, in my snapshot, I have only 1 competition, and 1 > sport, so it's easy to say that that competition's "sport" is that 1 > sport. But when I get to all my events, and objects that have events as > parents, and objects that have *those* as parents, for me to pair them all > together, I have to do id matching, and checking and double checking > everything that the ORM would normally do. Basically (unnecessarily) > double-jobbing what SQLAlchemy already does perfectly in a real scenario. > > Again, this is *just* for testing, and testing a side of things that is > 100% not database related, but is dependant on database objects. > > On Saturday, September 29, 2018 at 6:47:53 PM UTC+1, Mike Bayer wrote: > >> >> yes, flush writes to the database. SQLAlchemy is a database access >> tool. If you're trying to build some system of integers and objects >> connected to each other in some way that *resembles* a relational >> database but doesnt actually use one, the ORM is not in that business. >> There's an "index" recipe at >> https://bitbucket.org/zzzeek/sqlalchemy/wiki/UsageRecipes/SessionIndexing >> that allows you to look up objects by different fields in memory, but >> it has very little to do with the ORM, it's just a set of lookups. >> >> There are a lot of techniques that can be used to test ORM models, >> including some that use "mock" queries, check out the example at >> >> https://bitbucket.org/zzzeek/pycon2014_atmcraft/src/master/atmcraft/tests/test_views_mockdb.py. >> >> All the Session.query() you see there is done using mocks. maybe >> that can spark some ideas for how to do what you need even though it >> is quite different (doesn't use a DB though). >> >> >> > >> > On Saturday, September 29, 2018 at 3:18:24 PM UTC+1, Mike Bayer wrote: >> >> >> >> On Sat, Sep 29, 2018 at 7:34 AM seaders <sead...@gmail.com> wrote: >> >> > >> >> > Yep, that helps, nearly doing what I want. >> >> > >> >> > I have a "from_flat_json" function in my base class, so that's a >> perfect place to add the `session.enable_relationship_loading` bit for any >> created objects - that's now working, which is great. >> >> > >> >> > Only thing that's not wanted / right just yet is the ".commit()". >> If possible, I'd even prefer to not have that in the system, but without >> that, a1 isn't available for lookup, i.e. (using your example), >> >> > >> >> > a1 = A(id=1) >> >> > s.add(a1) >> >> > s.enable_relationship_loading(a1) >> >> > >> >> > b1 = B(a_id=1) >> >> > s.add(b1) >> >> > s.enable_relationship_loading(b1) >> >> > assert b1.a is a1 >> >> >> >> So what you have above would literally be SQLAlchemy doing the >> >> querying in memory without even using the database, because at no >> >> point above is anything being sent to the database. You don't need >> >> to "commit" the transaction, for your local work, you only need to >> >> flush() so that the row is present in the DB within the transaction: >> >> >> >> a1 = A(id=1) >> >> s.add(a1) >> >> s.flush() >> >> >> >> b1 = B(a_id=1) >> >> s.add(b1) >> >> s.enable_relationship_loading(b1) >> >> assert b1.a is a1 >> >> >> >> >> >> >> >> >> >> >> >> > >> >> > >> >> > >> >> > On Saturday, September 29, 2018 at 3:33:10 AM UTC+1, Mike Bayer >> wrote: >> >> >> >> >> >> >> >> >> yes that's what those two flags are doing. the way the ORM is >> >> >> designed, it wants you to be setting comp.sport = sport, not >> >> >> comp.sport_id = id, but there's a fair degree of special features >> >> >> these days that allow people to work the other way since it is so >> >> >> commonly requested, which are specifically what those two flags are >> >> >> for. >> >> >> >> >> >> an overview of the situation is at >> >> >> >> https://docs.sqlalchemy.org/en/latest/faq/sessions.html#i-set-the-foo-id-attribute-on-my-instance-to-7-but-the-foo-attribute-is-still-none-shouldn-t-it-have-loaded-foo-with-id-7, >> >> >> >> which is probably the first FAQ question we've ever had. The API >> >> >> features that correspond to load_on_pending and load_pending are >> the >> >> >> load_on_pending relationship flag: >> >> >> >> https://docs.sqlalchemy.org/en/latest/orm/relationship_api.html?highlight=load_on_pending#sqlalchemy.orm.relationship.params.load_on_pending >> >> >> , and enable_relationship_loading at >> >> >> >> https://docs.sqlalchemy.org/en/latest/orm/session_api.html#sqlalchemy.orm.session.Session.enable_relationship_loading, >> >> >> >> which does the same thing with a different scope, that is, for >> >> >> individual objects rather than an entire mapping. >> >> >> >> >> >> both APIs do a similar thing, it's just that load_on_pending is set >> at >> >> >> the level of the class mapping on the relationship, whereas >> >> >> enable_relationship_loading you can just enable for any individual >> >> >> object without any change to the mapping which may be better for >> >> >> running tests. >> >> >> >> >> >> since I never use these I feel I had to test them out to make sure >> I'm >> >> >> not mis-characterizing, so it looks like this. here is >> >> >> load_on_pending: >> >> >> >> >> >> class A(Base): >> >> >> __tablename__ = 'a' >> >> >> >> >> >> id = Column(Integer, primary_key=True) >> >> >> bs = relationship("B", backref=backref("a", >> load_on_pending=True)) >> >> >> >> >> >> >> >> >> class B(Base): >> >> >> __tablename__ = 'b' >> >> >> id = Column(Integer, primary_key=True) >> >> >> a_id = Column(ForeignKey("a.id")) >> >> >> >> >> >> >> >> >> s = Session(e) >> >> >> >> >> >> a1 = A(id=1) >> >> >> s.add(a1) >> >> >> s.commit() >> >> >> >> >> >> b1 = B(a_id=1) >> >> >> s.add(b1) # note you have to add b1 to the session for it to be >> "pending" >> >> >> assert b1.a is a1 >> >> >> >> >> >> Here is enable_relationship_loading: >> >> >> >> >> >> class A(Base): >> >> >> __tablename__ = 'a' >> >> >> >> >> >> id = Column(Integer, primary_key=True) >> >> >> bs = relationship("B", backref="a") >> >> >> >> >> >> >> >> >> class B(Base): >> >> >> __tablename__ = 'b' >> >> >> id = Column(Integer, primary_key=True) >> >> >> a_id = Column(ForeignKey("a.id")) >> >> >> >> >> >> >> >> >> a1 = A(id=1) >> >> >> s.add(a1) >> >> >> s.commit() >> >> >> >> >> >> b1 = B(a_id=1) >> >> >> s.enable_relationship_loading(b1) # note you *don't* have to add >> to >> >> >> the session for this >> >> >> assert b1.a is a1 >> >> >> >> >> >> you can also add b1 to the session at any time above. >> >> >> >> >> >> hope this helps. >> >> >> >> >> >> >> >> >> >> >> >> >> >> >> >> >> >> >> >> >> > >> >> >> > >> >> >> > -- >> >> >> > 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 post to this group, send email to sqlal...@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+...@googlegroups.com. >> >> > To post to this group, send email to sqlal...@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+...@googlegroups.com. >> > To post to this group, send email to sqlal...@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 - 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.