Maybe you're still looking into that, but I still don't understand why
this:
merged.orderdetails[0].saleprice
causes a new issue to the database.
(Also, wondering if some databases allow a primary key to be null...)
Thanks again.
On Feb 9, 2:50 pm, Kent <k...@retailarchitects.com> wrote:
> Ah ha. Thanks for tracking that down, makes sense.
>
> On Feb 9, 2:25 pm, "Michael Bayer" <mike...@zzzcomputing.com> wrote:
>
> > Kent wrote:
> > > I am on version 0.5.8.
>
> > part of your issue is this:
>
> > line1.lineid = '15'
>
> > should be this:
>
> > line1.lineid = 15
>
> > This because the value comes back from the DB as a numeric, not a string,
> > producing the wrong identity key ( (<class '__main__.OrderDetail'>,
> > ('0206001A134', '15')) vs (<class '__main__.OrderDetail'>,
> > (u'0206001A134', 15)) ).
>
> > The merge then issues the eager load for the lead order + 1 detail, and a
> > second select for the additional detail.
>
> > > As far as how upset it is making me: well, I certainly have no right
> > > to demand this very nice, free software be enhanced or changed: I'm
> > > just grateful for it.
>
> > > We will be supporting clients on webservers that are removed by a long
> > > distance from the database server, so I would like to limit the round
> > > trips as much as is feasible...
>
> > > I've taken out most everything and left the logic in a simple case to
> > > create the behavior. Here is the script that will demonstrate:
>
> > > =============================================================================
>
> > > from sqlalchemy import *
> > > from sqlalchemy.orm import *
>
> > > engine = create_engine('postgres://dbuser:dbu...@localhost:5444/
> > > dbuser',echo=True)
> > > metadata = MetaData()
> > > Session = sessionmaker(bind=engine)
> > > session = Session()
>
> > > order_table = Table("orders", metadata,
> > > Column("orderid", Unicode, primary_key=True)
> > > )
>
> > > orderdetail_table = Table("orderdetails",metadata,
> > > Column("orderid", Unicode, ForeignKey('orders.orderid'),
> > > primary_key=True),
> > > Column("lineid", Integer, primary_key=True),
> > > Column("saleprice", Numeric, nullable=False)
> > > )
>
> > > class Order(object):
> > > pass
>
> > > class OrderDetail(object):
> > > pass
>
> > > order_mapper = mapper(Order, order_table,
> > > properties=dict(orderdetails=relation(OrderDetail,
> > > cascade='all,delete-orphan',
> > > single_parent=True,
> > > lazy=False,
> > > backref=backref('parentorder',
> > > cascade='refresh-expire,expunge'))))
>
> > > orderdetail_mapper = mapper(OrderDetail, orderdetail_table)
>
> > > metadata.create_all(engine)
>
> > > o=Order()
> > > o.orderid = '0206001A134' #this order exists in the database - You'll
> > > need to set add it to the DB
>
> > > line1=OrderDetail() #line exists in database - You'll need to
> > > set add it to the DB
> > > line1.orderid = '0206001A134'
> > > line1.lineid = '15'
>
> > > line2=OrderDetail() #new line does not exist in database
> > > line2.orderid = '0206001A134'
>
> > > o.orderdetails = [line1, line2]
>
> > > #
> > > #
> > > # Question a above - the following merge results in 3 SELECT
> > > statements, but the first
> > > # is an eagerly loaded query joined with orderdetails. So, unless the
> > > JOIN returned fewer rows
> > > # (for example, an inner join instead of outer was used), all the
> > > orderdetails should
> > > # already be in existence as persistent objects:
> > > merged=session.merge(o)
>
> > > merged in session.new #this order exists in the database
>
> > > merged.orderdetails[0]
> > > merged.orderdetails[0] in session.new # already in database (in new =
> > > False)
>
> > > merged.orderdetails[1]
> > > merged.orderdetails[1] in session.new # not yet in database (in new =
> > > True)
>
> > > #
> > > # Question b:
> > > # Why does this issue another select? The object should have been
> > > eagerly loaded,
> > > # but even if not that, it was later reSELECTED during the merge()
> > > merged.orderdetails[0].saleprice
>
> > > #
> > > # Question c:
> > > # Are there databases that allow part of a primary key to be undefined
> > > (None)?
> > > # That is a foreign concept to me, so I expected this object would
> > > realize it
> > > # needn't query the database.
> > > merged.orderdetails[1].saleprice
>
> > > =============================================================================
>
> > > Thanks in advance,
>
> > > Kent
>
> > > On Feb 8, 9:57 pm, Michael Bayer <mike...@zzzcomputing.com> wrote:
> > >> On Feb 8, 2010, at 4:12 PM, Kent wrote:
>
> > >> > Ok, here are my questions:
>
> > >> > a) The merge eagerloads the order, along with its lines, but then,
> > >> > directly afterward, issues two additional SELECT statements for the
> > >> > two lines, even though these were already eagerly-loaded. That
> > >> > surprised me. Why is that occurring?
>
> > >> I dont know. I would need more than code fragments to reproduce your
> > >> behavior. (nor do I know what version you're on). It doesn't
> > >> reproduce with a simple test.
>
> > >> > b) When I ask for the property .saleprice on the order line, another
> > >> > SELECT statement is issued. Why does that occur when it was eagerly
> > >> > loaded already?
>
> > >> Same. If the value is in __dict__ it would not issue another load.
>
> > >> > c) In the case of line2, can SQLAlchemy be made to realize that part
> > >> > of the primary key is not set and therefore there is no reason to
> > >> > attempt a fetch from the database? It already detected this was a new
> > >> > record during the merge.
>
> > >> the "fetch for None", meaning issuing a fetch when the primary key was
> > >> completely None, was resolved in a recent 0.5 version, probably 0.5.8.
> > >> However, a partial primary key is considered to be valid. There is a
> > >> flag on the mapper() called allow_null_pks=True which in 0.5 is set to
> > >> False by default - it means that partial primary key is not valid.
> > >> That flag is not in fact checked by merge() in this case, which is
> > >> because the flag was already being removed in 0.6 by the time this fix
> > >> went into place. The flag only led to confusion over and over again
> > >> when users mapped to outerjoins, and didn't receive rows. Whereas
> > >> nobody ever complained about merge issuing a load for None as a key -
> > >> the issue was fixed because I noticed it myself. So you're the first
> > >> person to ever complain about it, which is unfortunate since it may have
> > >> led to a different path for the allow_null_pks flag. I dont like the
> > >> flag very much since its obscure and nobody really ever used it unless I
> > >> told them to directly. The only other way would be for the mapper to
> > >> "guess" if nulls are allowed in the primary key based on the type of
> > >> table/join structure its mapped to. So it depends on how upset this
> > >> behavior is making you if I really need to find some way for it to
> > >> differentiate between None-capable primary keys or not.
>
> > >> > Thanks in advance.
>
> > >> > Kent
>
> > >> > --
> > >> > You received this message because you are subscribed to the Google
> > >> Groups "sqlalchemy" group.
> > >> > To post to this group, send email to sqlalch...@googlegroups.com.
> > >> > To unsubscribe from this group, send email to
> > >> sqlalchemy+unsubscr...@googlegroups.com.
> > >> > For more options, visit this group
> > >> athttp://groups.google.com/group/sqlalchemy?hl=en.
>
> > > --
> > > You received this message because you are subscribed to the Google Groups
> > > "sqlalchemy" group.
> > > To post to this group, send email to sqlalch...@googlegroups.com.
> > > To unsubscribe from this group, send email to
> > > sqlalchemy+unsubscr...@googlegroups.com.
> > > For more options, visit this group at
> > >http://groups.google.com/group/sqlalchemy?hl=en.
>
>
--
You received this message because you are subscribed to the Google Groups
"sqlalchemy" group.
To post to this group, send email to sqlalch...@googlegroups.com.
To unsubscribe from this group, send email to
sqlalchemy+unsubscr...@googlegroups.com.
For more options, visit this group at
http://groups.google.com/group/sqlalchemy?hl=en.
