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 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 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} On Feb 10, 9:59 am, Michael Bayer <mike...@zzzcomputing.com> wrote: > 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 > > > -- > > 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.
