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.
