SQLAlchemy goes back to database when I wouldn't expect it to...
Kent
Order's pk is orderid
OrderDetail's composite pk is (orderid,lineid)
A few questions follow output:
>>> o=Order()
>>> o.orderid = '0206001A134' #this order exists in the database
>>>
>>> line1=OrderDetail() #line exists in database
>>> line1.orderid = '0206001A134'
>>> line1.lineid = '15'
>>>
>>> line2=OrderDetail() #line does not exist in database
>>> line2.orderid = '0206001A134'
>>>
>>> o.orderdetails = [line1, line2]
>>> merged=DBSession.merge(o)
20:44:41,547 INFO [sqlalchemy.engine.base.Engine.0x...8ed0] BEGIN
/home/rarch/tg2env/lib/python2.6/site-packages/SQLAlchemy-0.5.8.01-
py2.6.egg/sqlalchemy/engine/default.py:242: SAWarning: Unicode type
received non-unicode bind param value '0206001A134'
param[key.encode(encoding)] = processors[key](compiled_params[key])
20:44:41,556 INFO [sqlalchemy.engine.base.Engine.0x...8ed0] SELECT
orders.orderid AS orders_orderid, orders.type AS orders_type,
orders.orderdate AS orders_orderdate, orders.status AS orders_status,
orders.customerid AS orders_customerid, orders.ordersite AS
orders_ordersite, orders.salesperson1 AS orders_salesperson1,
orders.commisspercent1 AS orders_commisspercent1, orders.deliverytype
AS orders_deliverytype, orders.deliverydate AS orders_deliverydate,
orders.mainorder AS orders_mainorder, orders.sequence AS
orders_sequence, orders.massfinalize AS orders_massfinalize, (SELECT
sum(od__a.qtyordered * od__a.saleprice) AS sum_1
FROM orderdetails AS od__a
WHERE orders.orderid = od__a.orderid) AS totalsale,
products_1.productid AS products_1_productid, products_1.brand AS
products_1_brand, products_1.description AS products_1_description,
products_1.regular AS products_1_regular, products_1.sale AS
products_1_sale, products_1.onhand AS products_1_onhand,
products_1.onorder AS products_1_onorder, products_1.imageurl AS
products_1_imageurl, products_1.special AS products_1_special,
products_1.featured AS products_1_featured, products_1.newproduct AS
products_1_newproduct, orderdetails_1.orderid AS
orderdetails_1_orderid, orderdetails_1.lineid AS
orderdetails_1_lineid, orderdetails_1.productid AS
orderdetails_1_productid, orderdetails_1.qtyordered AS
orderdetails_1_qtyordered, orderdetails_1.saleprice AS
orderdetails_1_saleprice, orderdetails_1.voided AS
orderdetails_1_voided, orderdetails_1.commissiontype AS
orderdetails_1_commissiontype, orderdetails_1.mainorder AS
orderdetails_1_mainorder, orderdetails_1.picked AS
orderdetails_1_picked, customers_1.customerid AS
customers_1_customerid, customers_1.phonenumber AS
customers_1_phonenumber, customers_1.firstname AS
customers_1_firstname, customers_1.lastname AS customers_1_lastname,
customers_1.address1 AS customers_1_address1, customers_1.address2 AS
customers_1_address2, customers_1.city AS customers_1_city,
customers_1.state AS customers_1_state, customers_1.zip AS
customers_1_zip, customers_1.email AS customers_1_email,
customers_1.type AS customers_1_type, customers_1.accountopendate AS
customers_1_accountopendate
FROM orders LEFT OUTER JOIN orderdetails AS orderdetails_1 ON
orders.orderid = orderdetails_1.orderid JOIN products AS products_1 ON
products_1.productid = orderdetails_1.productid LEFT OUTER JOIN
customers AS customers_1 ON customers_1.customerid = orders.customerid
WHERE orders.orderid = %(param_1)s
20:44:41,556 INFO [sqlalchemy.engine.base.Engine.0x...8ed0]
{'param_1': '0206001A134'}
#**************** Question a *****************
20:44:41,570 INFO [sqlalchemy.engine.base.Engine.0x...8ed0] SELECT
orderdetails.orderid AS orderdetails_orderid, orderdetails.lineid AS
orderdetails_lineid, orderdetails.productid AS orderdetails_productid,
orderdetails.qtyordered AS orderdetails_qtyordered,
orderdetails.saleprice AS orderdetails_saleprice, orderdetails.voided
AS orderdetails_voided, orderdetails.commissiontype AS
orderdetails_commissiontype, orderdetails.mainorder AS
orderdetails_mainorder, orderdetails.picked AS orderdetails_picked,
products_1.productid AS products_1_productid, products_1.brand AS
products_1_brand, products_1.description AS products_1_description,
products_1.regular AS products_1_regular, products_1.sale AS
products_1_sale, products_1.onhand AS products_1_onhand,
products_1.onorder AS products_1_onorder, products_1.imageurl AS
products_1_imageurl, products_1.special AS products_1_special,
products_1.featured AS products_1_featured, products_1.newproduct AS
products_1_newproduct
FROM orderdetails JOIN products AS products_1 ON products_1.productid
= orderdetails.productid
WHERE orderdetails.orderid = %(param_1)s AND orderdetails.lineid = %
(param_2)s
20:44:41,570 INFO [sqlalchemy.engine.base.Engine.0x...8ed0]
{'param_1': '0206001A134', 'param_2': '15'}
#**************** Question a *****************
20:44:41,576 INFO [sqlalchemy.engine.base.Engine.0x...8ed0] SELECT
orderdetails.orderid AS orderdetails_orderid, orderdetails.lineid AS
orderdetails_lineid, orderdetails.productid AS orderdetails_productid,
orderdetails.qtyordered AS orderdetails_qtyordered,
orderdetails.saleprice AS orderdetails_saleprice, orderdetails.voided
AS orderdetails_voided, orderdetails.commissiontype AS
orderdetails_commissiontype, orderdetails.mainorder AS
orderdetails_mainorder, orderdetails.picked AS orderdetails_picked,
products_1.productid AS products_1_productid, products_1.brand AS
products_1_brand, products_1.description AS products_1_description,
products_1.regular AS products_1_regular, products_1.sale AS
products_1_sale, products_1.onhand AS products_1_onhand,
products_1.onorder AS products_1_onorder, products_1.imageurl AS
products_1_imageurl, products_1.special AS products_1_special,
products_1.featured AS products_1_featured, products_1.newproduct AS
products_1_newproduct
FROM orderdetails JOIN products AS products_1 ON products_1.productid
= orderdetails.productid
WHERE orderdetails.orderid = %(param_1)s AND orderdetails.lineid = %
(param_2)s
20:44:41,576 INFO [sqlalchemy.engine.base.Engine.0x...8ed0]
{'param_1': '0206001A134', 'param_2': None}
>>>
>>>
>>>
>>> merged in DBSession.new #this order exists in the database
False
>>>
>>>
>>> merged.orderdetails[0] in DBSession.new # already there
False
>>> merged.orderdetails[1] in DBSession.new # not yet in database
True
>>>
>>>
>>> merged.orderdetails[0]
<pylotengine.model.objects.OrderDetail object at 0x7b4df10>
>>> merged.orderdetails[1]
<pylotengine.model.objects.OrderDetail object at 0x7a22050>
>>>
>>>
>>> merged.orderdetails[0].saleprice #************ Question b *********************
20:46:25,083 INFO [sqlalchemy.engine.base.Engine.0x...8ed0] SELECT
orderdetails.productid AS orderdetails_productid,
orderdetails.qtyordered AS orderdetails_qtyordered,
orderdetails.saleprice AS orderdetails_saleprice, orderdetails.voided
AS orderdetails_voided, orderdetails.commissiontype AS
orderdetails_commissiontype, orderdetails.mainorder AS
orderdetails_mainorder, orderdetails.picked AS orderdetails_picked
FROM orderdetails
WHERE orderdetails.orderid = %(param_1)s AND orderdetails.lineid = %
(param_2)s
20:46:25,083 INFO [sqlalchemy.engine.base.Engine.0x...8ed0]
{'param_1': '0206001A134', 'param_2': 15}
Decimal('1999.00')
>>>
>>>
>>>
>>> merged.orderdetails[1].saleprice #*************** Question c ******************
20:46:28,651 INFO [sqlalchemy.engine.base.Engine.0x...8ed0] SELECT
orderdetails.productid AS orderdetails_productid,
orderdetails.qtyordered AS orderdetails_qtyordered,
orderdetails.saleprice AS orderdetails_saleprice, orderdetails.voided
AS orderdetails_voided, orderdetails.commissiontype AS
orderdetails_commissiontype, orderdetails.mainorder AS
orderdetails_mainorder, orderdetails.picked AS orderdetails_picked
FROM orderdetails
WHERE orderdetails.orderid = %(param_1)s AND orderdetails.lineid = %
(param_2)s
20:46:28,651 INFO [sqlalchemy.engine.base.Engine.0x...8ed0]
{'param_1': '0206001A134', 'param_2': None}
>>>
>>>
>>>
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?
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?
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.
Thanks in advance.
Kent
Michael Bayer
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 sqlal...@googlegroups.com.
> To unsubscribe from this group, send email to sqlalchemy+...@googlegroups.com.
> For more options, visit this group at http://groups.google.com/group/sqlalchemy?hl=en.
>
Kent
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:dbuser@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
Michael Bayer
> 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.
Kent
Kent
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.
Michael Bayer
> 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.
Michael Bayer
On Feb 9, 2010, at 7:09 PM, Michael Bayer wrote:
> 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).
if the load flag is set to True though, not sure if this is really ideal behavior and it seems like resetting any pending state on the we-know-to-be-loaded attribute might be the better way to go, so ticket 1681 is a reminder for me to think about this before the 0.6.0 release.
Kent
the software isn't strong enough to recommend (or understand) what you
are suggesting in 1681, I can observe the behavior enough to wonder
"why do we need to go back to the database again?"
>
> > (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 for your consideration, it seems that would be beneficial for
us. As a side note, though, if no databases allow this, why would we
default to True instead of False?
Does allow_partial_pks have additional meaning, such as "complain if
the object only has part of the primary key set?"
You mentioned the main thing was how this affects outer joins. Can
you expand on how this might cause outer joins to return no rows? Is
it because users still expected a row returned from the *other*
tables, even though part of this key is null?
(I don't want to make you go back through the effort of re-adding this
flag if it might cause me unanticipated side-effects that force me to
abandon it, so maybe pointing me to an example of the main complaint
when setting it to False? I'd like attempt to rule out that it might
affect me.)
Thanks
Michael Bayer
On Feb 10, 2010, at 8:36 AM, Kent wrote:
>> 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 for your consideration, it seems that would be beneficial for
> us. As a side note, though, if no databases allow this, why would we
> default to True instead of False?
because people map to outerjoins (often). then you get a partial PK.
>
> Does allow_partial_pks have additional meaning, such as "complain if
> the object only has part of the primary key set?"
not allowing partial pks means dont consider (2, None) to be a primary key - its treated like "None".
>
> You mentioned the main thing was how this affects outer joins. Can
> you expand on how this might cause outer joins to return no rows? Is
> it because users still expected a row returned from the *other*
> tables, even though part of this key is null?
> (I don't want to make you go back through the effort of re-adding this
> flag if it might cause me unanticipated side-effects that force me to
> abandon it, so maybe pointing me to an example of the main complaint
> when setting it to False? I'd like attempt to rule out that it might
> affect me.)
yes, an outerjoin can return a row for table A but NULL for table B.
its totally fine, we have flipped the defaults in 0.6 and we'd just be making the "other" behavior available again.
>
> Thanks
Kent
When I do something "simple" like this script:
o=Order()
o.orderid = 'KBORDE'
ol=OrderDetail()
ol.lineid=1 # exists in database
o.orderdetails=[ol]
mo=DBSession.merge(o)
mo.orderdetails[0] in DBSession.new
mo.orderdetails[0].saleprice = 65
DBSession.flush()
====(output pasted below)=====
I get output that is not what I hoped for in that, because of merge()
not being aware of allow_null_pks with composite keys:
mo.orderdetails[0] in DBSession.new == True.
This is making validation, etc troublesome for me, since I was
inspecting DBSession.new to indicate whether the record exists in the
database. The flush() works it out correctly in the end and sqla does
an update instead of insert, but inspecting DBSession.new is incorrect
semantically.
If you make merge() aware of "allow_partial_pks" in 0.6, will
mo.orderdetails[0] in DBSession.new == False then?
(In a previous post "using merge() with composite key", you mentioned
this:
"""
Your assessment of the issue is correct, in that the reconcilation of
l1/l2 "orderid" does not occur within merge so it remains None. This
behavior is not intentional, except to the degree that merge() was not
intended to run through the dependency rules which occur during a
flush,
instead expecting to receive objects with fully composed primary
keys.
It's not immediately apparent to me what degree of rearchitecture of
the
unit of work would be required for this behavior to be added, or if it
is
even a good idea. I understand the argument in favor. That doesn't
mean
there aren't arguments in opposition, just that they aren't
immediately
obvious.
"""
see http://groups.google.com/group/sqlalchemy/browse_thread/thread/20b199b4f78e7cad)
So I am wondering now if this is the same issue and will it be changed
("fixed") in 0.6?
If so and in the meantime, is there a workaround I could apply to
merge() or is it not very straightforward?
(I can also hack up my userland code to workaround this in the
meantime, but ultimately wanted to know whether this will all be
solved and if there is an easy patch I could apply until then...)
Pasted output to the above script:============================
>>> o=Order()
>>> o.orderid = 'KBORDE'
>>> ol=OrderDetail()
>>> ol.lineid=1
>>> o.orderdetails=[ol]
>>> mo=DBSession.merge(o)
10:09:22,607 INFO [sqlalchemy.engine.base.Engine.0x...2190] BEGIN
/home/rarch/tg2env/lib/python2.6/site-packages/SQLAlchemy-0.5.8.01-
py2.6.egg/sqlalchemy/engine/default.py:242: SAWarning: Unicode type
received non-unicode bind param value 'KBORDE'
param[key.encode(encoding)] = processors[key](compiled_params[key])
10:09:22,617 INFO [sqlalchemy.engine.base.Engine.0x...2190] SELECT
10:09:22,617 INFO [sqlalchemy.engine.base.Engine.0x...2190]
{'param_1': 'KBORDE'}
10:09:22,631 INFO [sqlalchemy.engine.base.Engine.0x...2190] SELECT
orderdetails.orderid AS orderdetails_orderid, orderdetails.lineid AS
orderdetails_lineid, orderdetails.productid AS orderdetails_productid,
orderdetails.qtyordered AS orderdetails_qtyordered,
orderdetails.saleprice AS orderdetails_saleprice, orderdetails.voided
AS orderdetails_voided, orderdetails.commissiontype AS
orderdetails_commissiontype, orderdetails.mainorder AS
orderdetails_mainorder, orderdetails.picked AS orderdetails_picked,
products_1.productid AS products_1_productid, products_1.brand AS
products_1_brand, products_1.description AS products_1_description,
products_1.regular AS products_1_regular, products_1.sale AS
products_1_sale, products_1.onhand AS products_1_onhand,
products_1.onorder AS products_1_onorder, products_1.imageurl AS
products_1_imageurl, products_1.special AS products_1_special,
products_1.featured AS products_1_featured, products_1.newproduct AS
products_1_newproduct
FROM orderdetails JOIN products AS products_1 ON products_1.productid
= orderdetails.productid
WHERE orderdetails.orderid = %(param_1)s AND orderdetails.lineid = %
(param_2)s
10:09:22,631 INFO [sqlalchemy.engine.base.Engine.0x...2190]
{'param_1': None, 'param_2': 1}
>>>
################# this shouldn't be True, should it? ################
>>> mo.orderdetails[0] in DBSession.new ###########################
True
>>>
>>> mo.orderdetails[0].saleprice = 65
>>>
>>> DBSession.flush()
10:09:22,640 INFO [sqlalchemy.engine.base.Engine.0x...2190] UPDATE
orderdetails SET saleprice=%(saleprice)s WHERE orderdetails.orderid = %
(orderdetails_orderid)s AND orderdetails.lineid = %
(orderdetails_lineid)s
10:09:22,640 INFO [sqlalchemy.engine.base.Engine.0x...2190]
{'orderdetails_orderid': 'KBORDE', 'saleprice': 65,
'orderdetails_lineid': 1}
Michael Bayer
On Feb 10, 2010, at 7:10 PM, Kent wrote:
>
> When I do something "simple" like this script:
>
> o=Order()
> o.orderid = 'KBORDE'
> ol=OrderDetail()
> ol.lineid=1 # exists in database
> o.orderdetails=[ol]
> mo=DBSession.merge(o)
>
> mo.orderdetails[0] in DBSession.new
>
> mo.orderdetails[0].saleprice = 65
>
> DBSession.flush()
>
> ====(output pasted below)=====
>
> I get output that is not what I hoped for in that, because of merge()
> not being aware of allow_null_pks with composite keys:
> mo.orderdetails[0] in DBSession.new == True.
>
> This is making validation, etc troublesome for me, since I was
> inspecting DBSession.new to indicate whether the record exists in the
> database. The flush() works it out correctly in the end and sqla does
> an update instead of insert, but inspecting DBSession.new is incorrect
> semantically.
um, if i understand OrderDetail pk is the combination of orderid and lineid, you may have to set "orderid" on your OrderDetail object before merging it. the merge process currently does not populate foreign key columns before testing for the primary key.