[sqlalchemy] Re: SQLAlchemy goes back to database when I wouldn't expect it to...
Thanks for the ticket 1681 consideration. Though my understanding of 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 -- 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.
Re: [sqlalchemy] Re: SQLAlchemy goes back to database when I wouldn't expect it to...
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 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.
[sqlalchemy] Re: SQLAlchemy goes back to database when I wouldn't expect it to...
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
Re: [sqlalchemy] Re: SQLAlchemy goes back to database when I wouldn't expect it to...
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. 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
[sqlalchemy] Re: SQLAlchemy goes back to database when I wouldn't expect it to...
I am on version 0.5.8. 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
Re: [sqlalchemy] Re: SQLAlchemy goes back to database when I wouldn't expect it to...
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
[sqlalchemy] Re: SQLAlchemy goes back to database when I wouldn't expect it to...
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
[sqlalchemy] Re: SQLAlchemy goes back to database when I wouldn't expect it to...
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. (Also, wondering if some databases allow a primary key to be null...) 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
Re: [sqlalchemy] Re: SQLAlchemy goes back to database when I wouldn't expect it to...
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
Re: [sqlalchemy] Re: SQLAlchemy goes back to database when I wouldn't expect it to...
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. (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