I am getting going on an sqlalchemy project. Ideally, I'd like a sort
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:i...@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}
--
You received this message because you are subscribed to the Google Groups
"sqlalchemy" group.
To post to this group, send email to [email protected].
To unsubscribe from this group, send email to
[email protected].
For more options, visit this group at
http://groups.google.com/group/sqlalchemy?hl=en.
