Consider a simple Order to OrderDetail relationship.
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
--
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.
