Hi Michael,
thanks for the reply.
I thought I had tested the code, but I did not do so properly.
The essence of the logic I posted works. Here are the few corrections:
You are right that I should not use strings for partition_id when it is
defined as integer. This is a piece of test code I cooked up. In the actual
production code, partition_id comes from another API and it is already
integer.
The other bug I had in my code was that:
in the lines:
for db_part in db_parts:
dev_part = dev_parts.get(db_part.partition_id, None)
if dev_part is not None:
# update db partition with that from device
db_part.partition_id = dev_part.partition_id
db_part.partition_name = dev_part.partition_name
# remove the device partition since we have updated the db
del dev_part[db_part.partition_id]
The last line should have read: del dev_parts[db_part.partition_id]
You also had mentioned that "the code doesn’t seem to show anything that would
result in any DELETEs emitted".
I am using a list comprehension to update the list of test_partitions
associated with the test_device database entry.
This list comprehension updates the test_partition list ('partitions'
attribute) for the device by keeping only those partitions
that are in the dict data structure (simulated to get data from a data
collector on the device).
The lines of code that updates the 'partitions' attribute is:
# First delete partitions in database that are not in device
db_parts[:] = [db_part for db_part in db_parts
if dev_parts.get(db_part.partition_id, None) is not None]
db_parts was set in an earlier line of code as follows:
ndev =
sess.query(TestDevice).filter(TestDevice.dns_name=='testdev3').first()
print 'ndev:', ndev
if ndev is not None:
db_parts = ndev.partitions # <--- partitions attribute set through
backref on TestPartition object.
After I made the changes, the code works as expected:
Before update, the test_partition table:
basicinv=> select * from test_partition ;
id | partition_name | partition_id | device_id
----+----------------+--------------+-----------
42 | test456 | 456 | 41
43 | test345 | 345 | 41
44 | test123 | 123 | 41
(3 rows)
After running the code:
basicinv=> select * from test_partition ;
id | partition_name | partition_id | device_id
----+----------------+--------------+-----------
42 | test456-2 | 456 | 41
43 | test345-1 | 345 | 41
45 | test678 | 678 | 41
46 | test567 | 567 | 41
(4 rows)
basicinv=>
As I expected, partitions with primary keys 42 and 43 got updated (ie. not
deleted and inserted with new primary key rows).
Thanks for your feedback.
On Monday, July 21, 2014 7:13:46 AM UTC-7, Michael Bayer wrote:
>
> not to mention you’re using strings to set integer values, again a bad
> idea, can only confuse your database:
>
> part.partition_id = ‘345’ # <— this is a string
>
>
> partition_id = sa.Column(sa.Integer, nullable=False) # <— should be integer
>
>
> with this program you need to create a short test case that actually runs
> and step through it with pdb as well as echo=True to analyze more
> accurately what’s happening and when.
>
>
>
> On Jul 21, 2014, at 10:10 AM, Michael Bayer <mik...@zzzcomputing.com
> <javascript:>> wrote:
>
> the code there doesn’t seem to show anything that would result in any
> DELETEs emitted. a DELETE here would only occur if you deassociated a
> TestDevice and a TestPartition by removing from the TestDevice.partitions
> collection or setting a TestPartition.device to None, and I don’t see that.
> All of the manipulations you’re doing with part.partition_id have nothing
> to do with any of that, SQLAlchemy’s relationships have no idea what you’re
> doing with those, and overall it's a bad idea to mix the usage of
> “obj.foreign_key = <id>” along with direct manipulation of the relationship
> (where you say dev_part.device = ndev) together. SQLAlchemy’s
> relationship management code knows nothing about any of those foreign key
> sets. See
> http://docs.sqlalchemy.org/en/rel_0_9/faq.html#i-set-the-foo-id-attribute-on-my-instance-to-7-but-the-foo-attribute-is-still-none-shouldn-t-it-have-loaded-foo-with-id-7
> .
>
>
>
>
>
>
>
> On Jul 21, 2014, at 2:55 AM, Bala Ramakrishnan <bal...@gmail.com
> <javascript:>> wrote:
>
> I had asked this question on Stack Overflow. The details are at this link:
>
>
> http://stackoverflow.com/questions/24836816/updating-a-few-children-in-one-to-many-relationship-deletes-all-rows-and-adds-ne
>
> The summary is I have a parent class A and a bidirectional one-to-many
> relationship with class B. When I update the class B list for an instance
> of A, the update may involve deleting some class B instances, updating some
> of them, and adding new ones. However, I find that SqlAlchemy deletes all
> ROWS of classB, and the inserts the necessary new rows. I would have
> expected SqlAchemy to insert only new rows, not delete and add those rows
> that are being updated. Do you know what is wrong with my code?
>
> Thanks.
>
>
> --
> You received this message because you are subscribed to the Google Groups
> "sqlalchemy" group.
> To unsubscribe from this group and stop receiving emails from it, send an
> email to sqlalchemy+...@googlegroups.com <javascript:>.
> To post to this group, send email to sqlal...@googlegroups.com
> <javascript:>.
> Visit this group at http://groups.google.com/group/sqlalchemy.
> For more options, visit https://groups.google.com/d/optout.
>
>
>
> --
> You received this message because you are subscribed to the Google Groups
> "sqlalchemy" group.
> To unsubscribe from this group and stop receiving emails from it, send an
> email to sqlalchemy+...@googlegroups.com <javascript:>.
> To post to this group, send email to sqlal...@googlegroups.com
> <javascript:>.
> Visit this group at http://groups.google.com/group/sqlalchemy.
> For more options, visit https://groups.google.com/d/optout.
>
>
>
--
You received this message because you are subscribed to the Google Groups
"sqlalchemy" group.
To unsubscribe from this group and stop receiving emails from it, send an email
to sqlalchemy+unsubscr...@googlegroups.com.
To post to this group, send email to sqlalchemy@googlegroups.com.
Visit this group at http://groups.google.com/group/sqlalchemy.
For more options, visit https://groups.google.com/d/optout.
