[sqlalchemy] Re: SQLAlchemy goes back to database when I wouldn't expect it to...

2010-02-10 Thread Kent 
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...

2010-02-10 Thread 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
 
 
 -- 
 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...

2010-02-10 Thread 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
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...

2010-02-10 Thread 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.



 
 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...

2010-02-09 Thread Kent 
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...

2010-02-09 Thread Michael Bayer 
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...

2010-02-09 Thread Kent 
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...

2010-02-09 Thread Kent 
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...

2010-02-09 Thread Michael Bayer 
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...

2010-02-09 Thread 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.



 
 
 
 (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