using merge() with composite key
Kent
of "see if this entity already exists in the database; if not -> add
it; if so -> update it"
From what I can tell, session.merge() was invented for this very
purpose?
merge() seems inconsistent in a way to me; I'll paste a simplified
model here:
At this point nothing exists in the database.
=============================
from sqlalchemy import *
from sqlalchemy.orm import *
engine = create_engine('oracle://inv:inv@localhost:1521/xe',
echo=True)
metadata = MetaData()
Session = sessionmaker(bind=engine)
session = Session()
order_table = Table("orders", metadata,
Column("id", Unicode(255), primary_key=True),
Column("customer", Unicode(255))
)
order_detail_table = Table("orderdetails",metadata,
Column("orderid", Unicode(255), ForeignKey('orders.id'),
primary_key=True),
Column("line", Integer, primary_key=True),
Column("product", Unicode(255)),
Column("qtyordered",Numeric)
)
class Order(object):
pass
class OrderDetail(object):
pass
order_mapper = mapper(Order, order_table)
orderdetail_mapper = mapper(OrderDetail, order_detail_table,
properties=dict(parentorder=relation(Order,
cascade='merge',
backref='orderdetails')))
metadata.create_all(engine)
ord = Order()
ord.id = '55'
ord.customer = 'Kent'
ln1 = OrderDetail()
ln1.line = 1
ln1.product = 'ARMLESSCHAIR'
ln2 = OrderDetail()
ln2.line = 2
ln2.product = 'WHITESECTIONAL'
ord.orderdetails = [ln1,ln2]
session.add(ord)
session.flush()
session.commit()
==============================================
This works as I expect:
2010-01-14 00:50:14,441 INFO sqlalchemy.engine.base.Engine.0x...2e50
BEGIN
/home/rarch/tg2env/lib/python2.6/site-packages/SQLAlchemy-0.5.2-
py2.6.egg/sqlalchemy/engine/default.py:241: SAWarning: Unicode type
received non-unicode bind param value 'Kent'
param[key.encode(encoding)] = processors[key](compiled_params[key])
/home/rarch/tg2env/lib/python2.6/site-packages/SQLAlchemy-0.5.2-
py2.6.egg/sqlalchemy/engine/default.py:241: SAWarning: Unicode type
received non-unicode bind param value '55'
param[key.encode(encoding)] = processors[key](compiled_params[key])
2010-01-14 00:50:14,442 INFO sqlalchemy.engine.base.Engine.0x...2e50
INSERT INTO orders (id, customer) VALUES (:id, :customer)
2010-01-14 00:50:14,442 INFO sqlalchemy.engine.base.Engine.0x...2e50
{'customer': 'Kent', 'id': '55'}
/home/rarch/tg2env/lib/python2.6/site-packages/SQLAlchemy-0.5.2-
py2.6.egg/sqlalchemy/engine/default.py:241: SAWarning: Unicode type
received non-unicode bind param value 'ARMLESSCHAIR'
param[key.encode(encoding)] = processors[key](compiled_params[key])
2010-01-14 00:50:14,450 INFO sqlalchemy.engine.base.Engine.0x...2e50
INSERT INTO orderdetails (orderid, line, product, qtyordered) VALUES
(:orderid, :line, :product, :qtyordered)
2010-01-14 00:50:14,450 INFO sqlalchemy.engine.base.Engine.0x...2e50
{'orderid': '55', 'line': 1, 'product': 'ARMLESSCHAIR', 'qtyordered':
None}
/home/rarch/tg2env/lib/python2.6/site-packages/SQLAlchemy-0.5.2-
py2.6.egg/sqlalchemy/engine/default.py:241: SAWarning: Unicode type
received non-unicode bind param value 'WHITESECTIONAL'
param[key.encode(encoding)] = processors[key](compiled_params[key])
2010-01-14 00:50:14,455 INFO sqlalchemy.engine.base.Engine.0x...2e50
INSERT INTO orderdetails (orderid, line, product, qtyordered) VALUES
(:orderid, :line, :product, :qtyordered)
2010-01-14 00:50:14,455 INFO sqlalchemy.engine.base.Engine.0x...2e50
{'orderid': '55', 'line': 2, 'product': 'WHITESECTIONAL',
'qtyordered': None}
2010-01-14 00:50:15,367 INFO sqlalchemy.engine.base.Engine.0x...2e50
COMMIT
What I want to draw attention to is that the 2 order detail INSERTs
understand via the ORM that the primary key is {'orderid': '55',
'line': 1} and {'orderid': '55', 'line': 2}, even though I never told
either line its orderid was '55'. I only told the parent record
(Order) that its id was '55'.
Now, to the merge() question...
Say I run the *exact* same beginning code, except I substitute a
session.merge() statement instead of the session.add() statement:
==============================
ord = Order()
ord.id = '55'
ord.customer = 'Kent'
ln1 = OrderDetail()
ln1.line = 1
ln1.product = 'ARMLESSCHAIR'
ln2 = OrderDetail()
ln2.line = 2
ln2.product = 'WHITESECTIONAL'
ord.orderdetails = [ln1,ln2]
dbord = session.merge(ord)
==============================
What I expected is for merge() to figure out and reconcile any
differences in the memory Object with what it finds in the database.
I was surprised to see this error in the output:
sqlalchemy.exc.IntegrityError: (IntegrityError) ORA-01400: cannot
insert NULL into ("INV"."ORDERDETAILS"."ORDERID")
'INSERT INTO orderdetails (orderid, line, product, qtyordered) VALUES
(:orderid, :line, :product, :qtyordered)' {'orderid': None, 'line': 1,
'product': 'ARMLESSCHAIR', 'qtyordered': None}
(the full output pasted below)
Apparently, since I did not tell the objects ln1 and ln2 that they had
orderid of '55', merge() had this problem. This seems inconsistent
with add(), who was able to figure out that the primary key had
orderid of '55' via the ORM.
In other words, shouldn't the assigment:
ord.orderdetails = [ln1,ln2]
in conjunction with the relation given to the ORM be enough so that
merge() understands the orderid is '55' instead of None?
What this intentionally designed this way to avoid ambiguities (was
this record intended to be added to this order, etc.)? Or should merge
() philosophically work this out like add() does?
Thanks in advance,
Kent
full output pasted here=========
2010-01-14 01:08:37,887 INFO sqlalchemy.engine.base.Engine.0x...2e50
BEGIN
2010-01-14 01:08:37,889 INFO sqlalchemy.engine.base.Engine.0x...2e50
SELECT orders.id AS orders_id, orders.customer AS orders_customer
FROM orders
WHERE orders.id = :param_1
2010-01-14 01:08:37,889 INFO sqlalchemy.engine.base.Engine.0x...2e50
{'param_1': '55'}
2010-01-14 01:08:37,910 INFO sqlalchemy.engine.base.Engine.0x...2e50
SELECT orderdetails.orderid AS orderdetails_orderid, orderdetails.line
AS orderdetails_line, orderdetails.product AS orderdetails_product,
orderdetails.qtyordered AS orderdetails_qtyordered
FROM orderdetails
WHERE orderdetails.orderid = :param_1 AND orderdetails.line = :param_2
2010-01-14 01:08:37,910 INFO sqlalchemy.engine.base.Engine.0x...2e50
{'param_1': None, 'param_2': 1}
2010-01-14 01:08:37,923 INFO sqlalchemy.engine.base.Engine.0x...2e50
SELECT orderdetails.orderid AS orderdetails_orderid, orderdetails.line
AS orderdetails_line, orderdetails.product AS orderdetails_product,
orderdetails.qtyordered AS orderdetails_qtyordered
FROM orderdetails
WHERE orderdetails.orderid = :param_1 AND orderdetails.line = :param_2
2010-01-14 01:08:37,923 INFO sqlalchemy.engine.base.Engine.0x...2e50
{'param_1': None, 'param_2': 2}
2010-01-14 01:08:37,926 INFO sqlalchemy.engine.base.Engine.0x...2e50
SELECT orderdetails.qtyordered AS orderdetails_qtyordered
FROM orderdetails
WHERE orderdetails.orderid = :param_1 AND orderdetails.line = :param_2
2010-01-14 01:08:37,927 INFO sqlalchemy.engine.base.Engine.0x...2e50
{'param_1': None, 'param_2': 1}
2010-01-14 01:08:37,932 INFO sqlalchemy.engine.base.Engine.0x...2e50
SELECT orderdetails.qtyordered AS orderdetails_qtyordered
FROM orderdetails
WHERE orderdetails.orderid = :param_1 AND orderdetails.line = :param_2
2010-01-14 01:08:37,932 INFO sqlalchemy.engine.base.Engine.0x...2e50
{'param_1': None, 'param_2': 2}
2010-01-14 01:08:37,933 INFO sqlalchemy.engine.base.Engine.0x...2e50
INSERT INTO orderdetails (orderid, line, product, qtyordered) VALUES
(:orderid, :line, :product, :qtyordered)
2010-01-14 01:08:37,934 INFO sqlalchemy.engine.base.Engine.0x...2e50
{'orderid': None, 'line': 1, 'product': 'ARMLESSCHAIR', 'qtyordered':
None}
2010-01-14 01:08:37,936 INFO sqlalchemy.engine.base.Engine.0x...2e50
ROLLBACK
Traceback (most recent call last):
File "<stdin>", line 1, in <module>
File "/home/rarch/tg2env/lib/python2.6/site-packages/
SQLAlchemy-0.5.2-py2.6.egg/sqlalchemy/orm/session.py", line 1211, in
merge
prop.merge(self, instance, merged, dont_load, _recursive)
File "/home/rarch/tg2env/lib/python2.6/site-packages/
SQLAlchemy-0.5.2-py2.6.egg/sqlalchemy/orm/properties.py", line 661, in
merge
getattr(dest.__class__, self.key).impl._set_iterable(dest_state,
dest_list)
File "/home/rarch/tg2env/lib/python2.6/site-packages/
SQLAlchemy-0.5.2-py2.6.egg/sqlalchemy/orm/attributes.py", line 729, in
_set_iterable
old = self.get(state)
File "/home/rarch/tg2env/lib/python2.6/site-packages/
SQLAlchemy-0.5.2-py2.6.egg/sqlalchemy/orm/attributes.py", line 375, in
get
value = callable_()
File "/home/rarch/tg2env/lib/python2.6/site-packages/
SQLAlchemy-0.5.2-py2.6.egg/sqlalchemy/orm/strategies.py", line 589, in
__call__
result = q.all()
File "/home/rarch/tg2env/lib/python2.6/site-packages/
SQLAlchemy-0.5.2-py2.6.egg/sqlalchemy/orm/query.py", line 1186, in all
return list(self)
File "/home/rarch/tg2env/lib/python2.6/site-packages/
SQLAlchemy-0.5.2-py2.6.egg/sqlalchemy/orm/query.py", line 1279, in
__iter__
self.session._autoflush()
File "/home/rarch/tg2env/lib/python2.6/site-packages/
SQLAlchemy-0.5.2-py2.6.egg/sqlalchemy/orm/session.py", line 902, in
_autoflush
self.flush()
File "/home/rarch/tg2env/lib/python2.6/site-packages/
SQLAlchemy-0.5.2-py2.6.egg/sqlalchemy/orm/session.py", line 1347, in
flush
self._flush(objects)
File "/home/rarch/tg2env/lib/python2.6/site-packages/
SQLAlchemy-0.5.2-py2.6.egg/sqlalchemy/orm/session.py", line 1417, in
_flush
flush_context.execute()
File "/home/rarch/tg2env/lib/python2.6/site-packages/
SQLAlchemy-0.5.2-py2.6.egg/sqlalchemy/orm/unitofwork.py", line 244, in
execute
UOWExecutor().execute(self, tasks)
File "/home/rarch/tg2env/lib/python2.6/site-packages/
SQLAlchemy-0.5.2-py2.6.egg/sqlalchemy/orm/unitofwork.py", line 707, in
execute
self.execute_save_steps(trans, task)
File "/home/rarch/tg2env/lib/python2.6/site-packages/
SQLAlchemy-0.5.2-py2.6.egg/sqlalchemy/orm/unitofwork.py", line 722, in
execute_save_steps
self.save_objects(trans, task)
File "/home/rarch/tg2env/lib/python2.6/site-packages/
SQLAlchemy-0.5.2-py2.6.egg/sqlalchemy/orm/unitofwork.py", line 713, in
save_objects
task.mapper._save_obj(task.polymorphic_tosave_objects, trans)
File "/home/rarch/tg2env/lib/python2.6/site-packages/
SQLAlchemy-0.5.2-py2.6.egg/sqlalchemy/orm/mapper.py", line 1352, in
_save_obj
c = connection.execute(statement.values(value_params), params)
File "/home/rarch/tg2env/lib/python2.6/site-packages/
SQLAlchemy-0.5.2-py2.6.egg/sqlalchemy/engine/base.py", line 824, in
execute
return Connection.executors[c](self, object, multiparams, params)
File "/home/rarch/tg2env/lib/python2.6/site-packages/
SQLAlchemy-0.5.2-py2.6.egg/sqlalchemy/engine/base.py", line 874, in
_execute_clauseelement
return self.__execute_context(context)
File "/home/rarch/tg2env/lib/python2.6/site-packages/
SQLAlchemy-0.5.2-py2.6.egg/sqlalchemy/engine/base.py", line 896, in
__execute_context
self._cursor_execute(context.cursor, context.statement,
context.parameters[0], context=context)
File "/home/rarch/tg2env/lib/python2.6/site-packages/
SQLAlchemy-0.5.2-py2.6.egg/sqlalchemy/engine/base.py", line 950, in
_cursor_execute
self._handle_dbapi_exception(e, statement, parameters, cursor,
context)
File "/home/rarch/tg2env/lib/python2.6/site-packages/
SQLAlchemy-0.5.2-py2.6.egg/sqlalchemy/engine/base.py", line 931, in
_handle_dbapi_exception
raise exc.DBAPIError.instance(statement, parameters, e,
connection_invalidated=is_disconnect)
sqlalchemy.exc.IntegrityError: (IntegrityError) ORA-01400: cannot
insert NULL into ("INV"."ORDERDETAILS"."ORDERID")
'INSERT INTO orderdetails (orderid, line, product, qtyordered) VALUES
(:orderid, :line, :product, :qtyordered)' {'orderid': None, 'line': 1,
'product': 'ARMLESSCHAIR', 'qtyordered': None}
Michael Bayer
>
> ord = Order()
> ord.id = '55'
> ord.customer = 'Kent'
>
> ln1 = OrderDetail()
> ln1.line = 1
> ln1.product = 'ARMLESSCHAIR'
> ln2 = OrderDetail()
> ln2.line = 2
> ln2.product = 'WHITESECTIONAL'
>
> ord.orderdetails = [ln1,ln2]
>
> dbord = session.merge(ord)
>
> ==============================
>
>
> What I expected is for merge() to figure out and reconcile any
> differences in the memory Object with what it finds in the database.
> orderid of '55', merge() had this problem. This seems inconsistent
> with add(), who was able to figure out that the primary key had
> orderid of '55' via the ORM.
>
> In other words, shouldn't the assigment:
> ord.orderdetails = [ln1,ln2]
> in conjunction with the relation given to the ORM be enough so that
> merge() understands the orderid is '55' instead of None?
>
> What this intentionally designed this way to avoid ambiguities (was
> this record intended to be added to this order, etc.)? Or should merge
> () philosophically work this out like add() does?
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.
Kent
Assuming I fully compose the primary keys, I am still struggling with
merge():
The model is exactly as I've pasted in my original post, except that
I've changed the mapper to cascade delete and delete-orphan, since I
read it would solve my issue...
So the new mapper is:
=======
orderdetail_mapper = mapper(OrderDetail, order_detail_table,
properties=dict(parentorder=relation(Order,
cascade='all,delete-orphan',single_parent=True,
backref='orderdetails')))
=======
Start over with a fresh database. Now, I add two "line details" like
my original post.
================
ord = Order()
ord.id = '55'
ord.customer = 'Kent'
ln1 = OrderDetail()
ln1.line = 1
ln1.orderid = '55'
ln1.product = 'DESK'
ln2 = OrderDetail()
ln2.line = 2
ln2.orderid = '55'
ln2.product = 'WHITESECTIONAL'
ord.orderdetails = [ln1,ln2]
session.add(ord)
session.flush()
session.commit()
==================
This behaves as I expected.
Now, onto merge. Say that ln1 has been deleted and ln3 added:
====
ord = Order()
ord.id = '55'
ord.customer = 'Kent'
ln2 = OrderDetail()
ln2.line = 2
ln2.orderid = '55'
ln2.product = 'WHITESECTIONAL'
ln3 = OrderDetail() # new line
ln3.line = 3
ln3.orderid = '55'
ln3.product = 'CHAIR'
ord.orderdetails = [ln2,ln3]
dbord = session.merge(ord)
session.commit()
======
I expected merge to INSERT ln3 (which it attempts to do) and DELETE
ln1, which it doesn't do, but fails with this exception:
AssertionError: Dependency rule tried to blank-out primary key column
'orderdetails.orderid' on instance '<OrderDetail at 0x2cc5850>'
Is my use-case for merge() still not really what it was designed for?
Can you explain what is going on or what I am doing wrong?
Third question: I extrapolate that merge() expects *all* fields are
populated? In other words, if merge() encounters a field whose value
is None, will it update that field to null in the database (as opposed
to leaving the value untouched in the database)? Does None mean
"leave it alone" or "update to null"?
full output pasted here:
===========
2010-01-14 02:45:16,296 INFO sqlalchemy.engine.base.Engine.0x...4e50
BEGIN
2010-01-14 02:45:16,296 INFO sqlalchemy.engine.base.Engine.0x...4e50
SELECT orders.id AS orders_id, orders.customer AS orders_customer
FROM orders
WHERE orders.id = :param_1
2010-01-14 02:45:16,296 INFO sqlalchemy.engine.base.Engine.0x...4e50
{'param_1': '55'}
2010-01-14 02:45:16,298 INFO sqlalchemy.engine.base.Engine.0x...4e50
SELECT orderdetails.orderid AS orderdetails_orderid, orderdetails.line
AS orderdetails_line, orderdetails.product AS orderdetails_product,
orderdetails.qtyordered AS orderdetails_qtyordered
FROM orderdetails
WHERE orderdetails.orderid = :param_1 AND orderdetails.line = :param_2
2010-01-14 02:45:16,298 INFO sqlalchemy.engine.base.Engine.0x...4e50
{'param_1': '55', 'param_2': 2}
2010-01-14 02:45:16,302 INFO sqlalchemy.engine.base.Engine.0x...4e50
SELECT orderdetails.orderid AS orderdetails_orderid, orderdetails.line
AS orderdetails_line, orderdetails.product AS orderdetails_product,
orderdetails.qtyordered AS orderdetails_qtyordered
FROM orderdetails
WHERE orderdetails.orderid = :param_1 AND orderdetails.line = :param_2
2010-01-14 02:45:16,302 INFO sqlalchemy.engine.base.Engine.0x...4e50
{'param_1': '55', 'param_2': 3}
2010-01-14 02:45:16,305 INFO sqlalchemy.engine.base.Engine.0x...4e50
SELECT orderdetails.qtyordered AS orderdetails_qtyordered
FROM orderdetails
WHERE orderdetails.orderid = :param_1 AND orderdetails.line = :param_2
2010-01-14 02:45:16,305 INFO sqlalchemy.engine.base.Engine.0x...4e50
{'param_1': '55', 'param_2': 3}
/home/rarch/tg2env/lib/python2.6/site-packages/SQLAlchemy-0.5.2-
py2.6.egg/sqlalchemy/engine/default.py:241: SAWarning: Unicode type
received non-unicode bind param value 'CHAIR'
param[key.encode(encoding)] = processors[key](compiled_params[key])
2010-01-14 02:45:16,306 INFO sqlalchemy.engine.base.Engine.0x...4e50
INSERT INTO orderdetails (orderid, line, product, qtyordered) VALUES
(:orderid, :line, :product, :qtyordered)
2010-01-14 02:45:16,307 INFO sqlalchemy.engine.base.Engine.0x...4e50
{'orderid': '55', 'line': 3, 'product': 'CHAIR', 'qtyordered': None}
2010-01-14 02:45:16,308 INFO sqlalchemy.engine.base.Engine.0x...4e50
SELECT orderdetails.orderid AS orderdetails_orderid, orderdetails.line
AS orderdetails_line, orderdetails.product AS orderdetails_product,
orderdetails.qtyordered AS orderdetails_qtyordered
FROM orderdetails
WHERE :param_1 = orderdetails.orderid
2010-01-14 02:45:16,308 INFO sqlalchemy.engine.base.Engine.0x...4e50
{'param_1': '55'}
>>>
>>>
>>>
>>> session.commit()
2010-01-14 02:45:30,143 INFO sqlalchemy.engine.base.Engine.0x...4e50
ROLLBACK
Traceback (most recent call last):
File "<stdin>", line 1, in <module>
File "/home/rarch/tg2env/lib/python2.6/site-packages/
SQLAlchemy-0.5.2-py2.6.egg/sqlalchemy/orm/session.py", line 673, in
commit
self.transaction.commit()
File "/home/rarch/tg2env/lib/python2.6/site-packages/
SQLAlchemy-0.5.2-py2.6.egg/sqlalchemy/orm/session.py", line 378, in
commit
self._prepare_impl()
File "/home/rarch/tg2env/lib/python2.6/site-packages/
SQLAlchemy-0.5.2-py2.6.egg/sqlalchemy/orm/session.py", line 362, in
_prepare_impl
self.session.flush()
File "/home/rarch/tg2env/lib/python2.6/site-packages/
SQLAlchemy-0.5.2-py2.6.egg/sqlalchemy/orm/session.py", line 1347, in
flush
self._flush(objects)
File "/home/rarch/tg2env/lib/python2.6/site-packages/
SQLAlchemy-0.5.2-py2.6.egg/sqlalchemy/orm/session.py", line 1417, in
_flush
flush_context.execute()
File "/home/rarch/tg2env/lib/python2.6/site-packages/
SQLAlchemy-0.5.2-py2.6.egg/sqlalchemy/orm/unitofwork.py", line 244, in
execute
UOWExecutor().execute(self, tasks)
File "/home/rarch/tg2env/lib/python2.6/site-packages/
SQLAlchemy-0.5.2-py2.6.egg/sqlalchemy/orm/unitofwork.py", line 707, in
execute
self.execute_save_steps(trans, task)
File "/home/rarch/tg2env/lib/python2.6/site-packages/
SQLAlchemy-0.5.2-py2.6.egg/sqlalchemy/orm/unitofwork.py", line 724, in
execute_save_steps
self.execute_dependencies(trans, task)
File "/home/rarch/tg2env/lib/python2.6/site-packages/
SQLAlchemy-0.5.2-py2.6.egg/sqlalchemy/orm/unitofwork.py", line 733, in
execute_dependencies
self.execute_dependency(trans, dep, False)
File "/home/rarch/tg2env/lib/python2.6/site-packages/
SQLAlchemy-0.5.2-py2.6.egg/sqlalchemy/orm/unitofwork.py", line 719, in
execute_dependency
dep.execute(trans, isdelete)
File "/home/rarch/tg2env/lib/python2.6/site-packages/
SQLAlchemy-0.5.2-py2.6.egg/sqlalchemy/orm/unitofwork.py", line 686, in
execute
delete=delete)
File "/home/rarch/tg2env/lib/python2.6/site-packages/
SQLAlchemy-0.5.2-py2.6.egg/sqlalchemy/orm/dependency.py", line 353, in
process_dependencies
self._synchronize(state, child, None, False, uowcommit)
File "/home/rarch/tg2env/lib/python2.6/site-packages/
SQLAlchemy-0.5.2-py2.6.egg/sqlalchemy/orm/dependency.py", line 395, in
_synchronize
sync.clear(state, self.parent, self.prop.synchronize_pairs)
File "/home/rarch/tg2env/lib/python2.6/site-packages/
SQLAlchemy-0.5.2-py2.6.egg/sqlalchemy/orm/sync.py", line 28, in clear
raise AssertionError("Dependency rule tried to blank-out primary
key column '%s' on instance '%s'" % (r, mapperutil.state_str(dest)))
AssertionError: Dependency rule tried to blank-out primary key column
'orderdetails.orderid' on instance '<OrderDetail at 0x2cc5850>'
Michael Bayer
looking through CHANGES I can see mentions of several issues regarding
flushing, merging, and composite primary keys, though none seem quite to
match this particular symptom. in any case 0.5.2 is an old release so I
would ask that you confirm this behavior on 0.5.7.
> --
> You received this message because you are subscribed to the Google Groups
> "sqlalchemy" group.
> To post to this group, send email to sqlal...@googlegroups.com.
> To unsubscribe from this group, send email to
> sqlalchemy+...@googlegroups.com.
> For more options, visit this group at
> http://groups.google.com/group/sqlalchemy?hl=en.
>
>
>
Kent
> looking through CHANGES I can see mentions of several issues regarding
> flushing, merging, and composite primary keys, though none seem quite to
> match this particular symptom. in any case 0.5.2 is an old release so I
> would ask that you confirm this behavior on 0.5.7.
>
>
> > populated? In other words, if merge() encounters a field whose value
> > is None, will it update that field to null in the database (as opposed
> > to leaving the value untouched in the database)? Does None mean
> > "leave it alone" or "update to null"?
>
Yes, fair enough. In the meantime, do you know the answer to my
"Third question"?
Michael Bayer
> Thanks for your prompt response.
>
> Assuming I fully compose the primary keys, I am still struggling with
> merge():
>
> The model is exactly as I've pasted in my original post, except that
> I've changed the mapper to cascade delete and delete-orphan, since I
> read it would solve my issue...
>
> So the new mapper is:
>
> =======
> orderdetail_mapper = mapper(OrderDetail, order_detail_table,
> properties=dict(parentorder=relation(Order,
> cascade='all,delete-orphan',single_parent=True,
> backref='orderdetails')))
oh, sorry, you have "cascade" on the wrong side. the parent->child for
"orphan" is Order->OrderDetail, so the cascade needs to be on
"orderdetails" for the orphan to take place.
Also I wonder how single_parent=True came to be there ? Oh i guess
because of the erroneous "delete-orphan"...you can take that out too.
Michael Bayer
None is going to "update to null". But this is not the same as "empty",
i.e. the key isn't present in obj.__dict__ - that will amount to a skip.
When you first create the object, the __dict__ is blank as far as values -
The "None" only appears there when you first access the scalar attribute.
If I were writing SQLA today, perhaps I'd have been a little more
draconian-yet-consistent and made non-present attributes raise
AttributeError, but this is what we have.
Kent
> None is going to "update to null". But this is not the same as "empty",
> i.e. the key isn't present in obj.__dict__ - that will amount to a skip.
> When you first create the object, the __dict__ is blank as far as values -
> The "None" only appears there when you first access the scalar attribute.
>
> If I were writing SQLA today, perhaps I'd have been a little more
> draconian-yet-consistent and made non-present attributes raise
> AttributeError, but this is what we have.
>
Well, as it is, that *does* allow for more flexibility, I suppose.
As far as 0.5.7, I confirmed the same issue:
"AssertionError: Dependency rule tried to blank-out primary key column
'orderdetails.orderid' on instance '<OrderDetail at 0x1a757a90>'"
>>> dbord = session.merge(ord)
2010-01-14 03:31:01,807 INFO sqlalchemy.engine.base.Engine.0x...00d0
BEGIN
2010-01-14 03:31:01,808 INFO sqlalchemy.engine.base.Engine.0x...00d0
SELECT orders.id AS orders_id, orders.customer AS orders_customer
FROM orders
WHERE orders.id = :param_1
2010-01-14 03:31:01,808 INFO sqlalchemy.engine.base.Engine.0x...00d0
{'param_1': '55'}
2010-01-14 03:31:01,810 INFO sqlalchemy.engine.base.Engine.0x...00d0
SELECT orderdetails.orderid AS orderdetails_orderid, orderdetails.line
AS orderdetails_line, orderdetails.product AS orderdetails_product,
orderdetails.qtyordered AS orderdetails_qtyordered
FROM orderdetails
WHERE orderdetails.orderid = :param_1 AND orderdetails.line = :param_2
2010-01-14 03:31:01,811 INFO sqlalchemy.engine.base.Engine.0x...00d0
{'param_1': '55', 'param_2': 2}
2010-01-14 03:31:01,883 INFO sqlalchemy.engine.base.Engine.0x...00d0
SELECT orderdetails.orderid AS orderdetails_orderid, orderdetails.line
AS orderdetails_line, orderdetails.product AS orderdetails_product,
orderdetails.qtyordered AS orderdetails_qtyordered
FROM orderdetails
WHERE orderdetails.orderid = :param_1 AND orderdetails.line = :param_2
2010-01-14 03:31:01,884 INFO sqlalchemy.engine.base.Engine.0x...00d0
{'param_1': '55', 'param_2': 3}
2010-01-14 03:31:01,885 INFO sqlalchemy.engine.base.Engine.0x...00d0
SELECT orderdetails.orderid AS orderdetails_orderid, orderdetails.line
AS orderdetails_line, orderdetails.product AS orderdetails_product,
orderdetails.qtyordered AS orderdetails_qtyordered
FROM orderdetails
WHERE :param_1 = orderdetails.orderid
2010-01-14 03:31:01,886 INFO sqlalchemy.engine.base.Engine.0x...00d0
{'param_1': '55'}
>>>
>>>
>>> session.commit()
2010-01-14 03:31:13,245 INFO sqlalchemy.engine.base.Engine.0x...00d0
ROLLBACK
Traceback (most recent call last):
File "<stdin>", line 1, in <module>
File "/home/rarch/trunk/sa057/lib/python2.6/site-packages/
SQLAlchemy-0.5.7-py2.6.egg/sqlalchemy/orm/session.py", line 671, in
commit
self.transaction.commit()
File "/home/rarch/trunk/sa057/lib/python2.6/site-packages/
SQLAlchemy-0.5.7-py2.6.egg/sqlalchemy/orm/session.py", line 378, in
commit
self._prepare_impl()
File "/home/rarch/trunk/sa057/lib/python2.6/site-packages/
SQLAlchemy-0.5.7-py2.6.egg/sqlalchemy/orm/session.py", line 362, in
_prepare_impl
self.session.flush()
File "/home/rarch/trunk/sa057/lib/python2.6/site-packages/
SQLAlchemy-0.5.7-py2.6.egg/sqlalchemy/orm/session.py", line 1354, in
flush
self._flush(objects)
File "/home/rarch/trunk/sa057/lib/python2.6/site-packages/
SQLAlchemy-0.5.7-py2.6.egg/sqlalchemy/orm/session.py", line 1432, in
_flush
flush_context.execute()
File "/home/rarch/trunk/sa057/lib/python2.6/site-packages/
SQLAlchemy-0.5.7-py2.6.egg/sqlalchemy/orm/unitofwork.py", line 261, in
execute
UOWExecutor().execute(self, tasks)
File "/home/rarch/trunk/sa057/lib/python2.6/site-packages/
SQLAlchemy-0.5.7-py2.6.egg/sqlalchemy/orm/unitofwork.py", line 753, in
execute
self.execute_save_steps(trans, task)
File "/home/rarch/trunk/sa057/lib/python2.6/site-packages/
SQLAlchemy-0.5.7-py2.6.egg/sqlalchemy/orm/unitofwork.py", line 774, in
execute_save_steps
self.execute_dependencies(trans, task)
File "/home/rarch/trunk/sa057/lib/python2.6/site-packages/
SQLAlchemy-0.5.7-py2.6.egg/sqlalchemy/orm/unitofwork.py", line 783, in
execute_dependencies
self.execute_dependency(trans, dep, False)
File "/home/rarch/trunk/sa057/lib/python2.6/site-packages/
SQLAlchemy-0.5.7-py2.6.egg/sqlalchemy/orm/unitofwork.py", line 765, in
execute_dependency
dep.execute(trans, isdelete)
File "/home/rarch/trunk/sa057/lib/python2.6/site-packages/
SQLAlchemy-0.5.7-py2.6.egg/sqlalchemy/orm/unitofwork.py", line 732, in
execute
delete=delete)
File "/home/rarch/trunk/sa057/lib/python2.6/site-packages/
SQLAlchemy-0.5.7-py2.6.egg/sqlalchemy/orm/dependency.py", line 369, in
process_dependencies
self._synchronize(state, child, None, False, uowcommit)
File "/home/rarch/trunk/sa057/lib/python2.6/site-packages/
SQLAlchemy-0.5.7-py2.6.egg/sqlalchemy/orm/dependency.py", line 411, in
_synchronize
sync.clear(state, self.parent, self.prop.synchronize_pairs)
File "/home/rarch/trunk/sa057/lib/python2.6/site-packages/
SQLAlchemy-0.5.7-py2.6.egg/sqlalchemy/orm/sync.py", line 28, in clear
raise AssertionError("Dependency rule tried to blank-out primary
key column '%s' on instance '%s'" % (r, mapperutil.state_str(dest)))
AssertionError: Dependency rule tried to blank-out primary key column
'orderdetails.orderid' on instance '<OrderDetail at 0x1a757a90>'
Michael Bayer
>
>> None is going to "update to null". � �But this is not the same as
>> "empty",
>> i.e. the key isn't present in obj.__dict__ - that will amount to a skip.
>> When you first create the object, the __dict__ is blank as far as values
>> -
>> The "None" only appears there when you first access the scalar
>> attribute.
>>
>> If I were writing SQLA today, perhaps I'd have been a little more
>> draconian-yet-consistent and made non-present attributes raise
>> AttributeError, but this is what we have.
>>
>
> Well, as it is, that *does* allow for more flexibility, I suppose.
>
> As far as 0.5.7, I confirmed the same issue:
you need to flip around where you put the "delete-orphan" rule - see my
previous email on the subject.
Kent
> you need to flip around where you put the "delete-orphan" rule - see my
> previous email on the subject.
>
In other words:
order_mapper = mapper(Order, order_table, properties=dict
(orderdetails=relation(OrderDetail,
cascade='all,delete-orphan', single_parent=True,
backref='parentorder')))
orderdetail_mapper = mapper(OrderDetail, order_detail_table)
=====
Thank you very much.
That also solved the earlier problem with merge. I don't need to
supply both portions of the composite key... it works that out.