Kent wrote:
> 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.
as I mentioned earlier, if the value isn't in __dict__ on a persistent
instance, it will be loaded when accessed. Your example doesn't set this
field on the object to be merged - so the merge operation actually expires
the value on the loaded object. At the moment that's because the
attribute missing from __dict__ is assumed to have been expired, so it
expires it on the to-be-merged side as well (otherwise, what would it
merge it to? particularly if the load=False flag is set).
>
> (Also, wondering if some databases allow a primary key to be null...)
I've researched this in the past and they don't. I will look into
re-introducing "allow_null_pks" as a new flag "allow_partial_pks",
defaults to True, will be honored by merge(), you set yours to False.
this is 0.6 only.
>
> 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.
>
>
--
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.
