Great thanks !
There is something I still doesn't understand with automap_base : some
relationships are created only after the first query call (which takes much
more time to terminate).
For instance :
>>> from dbaccess import * # import engine, models, etc.
>>> Dispositif.hardware_collection.attr # doesn't exist yet
Traceback (most recent call last):
File "<stdin>", line 1, in <module>
File
"/usr/local/lib/python2.7/dist-packages/sqlalchemy/ext/associationproxy.py",
line 204, in attr
return (self.local_attr, self.remote_attr)
File
"/usr/local/lib/python2.7/dist-packages/sqlalchemy/ext/associationproxy.py",
line 184, in local_attr
return getattr(self.owning_class, self.target_collection)
AttributeError: type object 'Dispositif' has no attribute
'disp_hdw_collection'
>>> dir(Dispositif) # there is no
'disp_hdw_collection', but 'hardware_collection' exists (seems because I
define it explicitely)
[..., 'classes', 'hardware_collection', 'id', 'metadata', 'prepare']
>>> session = Session() # create the first session
>>> l = session.query(Dispositif).all() # first query call, takes about 2s
>>> Dispositif.hardware_collection.attr # now exists...
(<sqlalchemy.orm.attributes.InstrumentedAttribute object at 0x148edb0>, <
sqlalchemy.orm.attributes.InstrumentedAttribute object at 0x12f2f90>)
>>> dir(Dispositif) # many news relationships and
collections appear here
[..., 'classes', 'disp_hdw_collection', 'hardware_collection', 'id',
'metadata', 'prepare', <some_more_collections...>]
Le lundi 31 août 2015 16:33:52 UTC+3, Michael Bayer a écrit :
>
>
>
> On 8/31/15 8:23 AM, [email protected] <javascript:> wrote:
>
>
> Thank you very much.
>
> Le lundi 31 août 2015 06:17:37 UTC+3, Michael Bayer a écrit :
>
>
>
> On 8/29/15 2:27 PM, [email protected] wrote:
>
> Thanks for the reply.
>
> Le vendredi 28 août 2015 18:52:37 UTC+3, Michael Bayer a écrit :
>
>
>
> On 8/28/15 3:51 AM, [email protected] wrote:
>
> Another question is why sqlalchemy produce two queries to get hardwares
> collections from a dispositif :
>
>
> >>> some_disp.hardwares
> 2015-08-28 10:36:41,722 INFO sqlalchemy.engine.base.Engine SELECT disp_hdw
> .iddispositif AS disp_hdw_iddispositif, disp_hdw.idhardware AS
> disp_hdw_idhardware, disp_hdw.instance AS disp_hdw_instance
> FROM disp_hdw
> WHERE %s = disp_hdw.iddispositif
> 2015-08-28 10:36:41,725 INFO sqlalchemy.engine.base.Engine (2721L,)
> 2015-08-28 10:36:41,768 INFO sqlalchemy.engine.base.Engine SELECT hardware
> .hdw_type AS hardware_hdw_type, hardware.id AS hardware_id, hardware.idbox
> AS hardware_idbox
> FROM hardware
> WHERE hardware.id = %s
> 2015-08-28 10:36:41,772 INFO sqlalchemy.engine.base.Engine (268L,)
>
>
> There's no context given here, but that would indicate that the
> "some_disp" instance has been expired. When you .commit() a session, all
> objects are expired by default. This is so that if, when you next operate
> on the objects, some other transaction has deleted that row, the session
> can let you know that you're working on stale data. See the session docs
> for options on how to control this behavior.
>
>
> Here the context, all in the same session :
>
> >>> from dbaccess import * # import engine, Base and tables objects
> >>> from sqlalchemy.orm.session import sessionmaker
> >>> Session = sessionmaker(bind=engine)
> >>> session = Session()
> >>> l = session.query(Dispositif).all()
> >>> some_disp = l[-2]
> >>> engine.echo = True
> >>> some_disp.hardwares
> 2015-08-29 20:34:30,972 INFO sqlalchemy.engine.base.Engine SELECT disp_hdw
> .iddispositif AS disp_hdw_iddispositif, disp_hdw.idhardware AS
> disp_hdw_idhardware, disp_hdw.instance AS disp_hdw_instance
> FROM disp_hdw
> WHERE %s = disp_hdw.iddispositif
> 2015-08-29 20:34:30,975 INFO sqlalchemy.engine.base.Engine (2721L,)
> 2015-08-29 20:34:31,018 INFO sqlalchemy.engine.base.Engine SELECT hardware
> .hdw_type AS hardware_hdw_type, hardware.id AS hardware_id, hardware.idbox
> AS hardware_idbox
> FROM hardware
> WHERE hardware.id = %s
> 2015-08-29 20:34:31,022 INFO sqlalchemy.engine.base.Engine (268L,)
>
>
> oh. Well this is association proxy, which means it needs to load two
> separate relationships, so that is two SELECT statements with default
> loader strategy.
>
>
> OK.
>
> In fact, *hardwares* can be retrieved in one query.
>
> instead of somewhat like :
>
> SELECT hardware.hdw_type AS hardware_hdw_type, hardware.id AS hardware_id,
> hardware.idbox AS hardware_idbox
> FROM hardware
> JOIN disp_hdw ON hardware.id = disp_hdw.idhardware
> WHERE disp_hdw.iddispositif = %s
>
>
> sure thing, use joined eager loading:
>
> http://docs.sqlalchemy.org/en/rel_1_0/orm/loading_relationships.html
>
>
> If I try :
> >>> x = db.query(Dispositif).options(joinedload(Dispositif.
> hardware_collection)).get(2721)
>
> I got an error : sqlalchemy.exc.ArgumentError: mapper option expects
> string key or list of attributes.
>
>
> if 'hardware_collection' is the association proxy, right now you can't
> pass it directly like that; you can use the helper "attr" with
> joinedload_all:
>
>
> query.options(joinedload_all(*Dispostif.hardware_collection.attr))
>
> or use the actual relationships:
>
>
> quey.options(joinedload(Dispotif.attribute_a).joinedload(DispToHardware.attribute_b))
>
>
>
>
>
>
> But it's still possible to "preload" the association part by using :
> >>> x = db.query(Dispositif).options(joinedload(Dispositif.
> disp_hdw_collection)).get(2721)
>
> So calling x.hardware_collection after that produce only one query.
>
> Is there a better way to do ?
>
> Le vendredi 28 août 2015 10:20:55 UTC+3, [email protected] a écrit :
>
> Hello,
>
> I want to use assocation proxy pattern
> <http://docs.sqlalchemy.org/en/latest/orm/extensions/associationproxy.html>
> with automap. I tried this code :
>
>
> Base = automap_base()
>
> class Dispositif(Base):
> __tablename__ = 'dispositifs'
> hardwares = association_proxy('disp_hardwares', 'hardware')
>
> class
>
> ...
--
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 [email protected].
To post to this group, send email to [email protected].
Visit this group at http://groups.google.com/group/sqlalchemy.
For more options, visit https://groups.google.com/d/optout.
