[sqlalchemy] Re: Saving/merging 'existing' objects
From: sqlalchemy@googlegroups.com [mailto:sqlalch...@googlegroups.com] On Behalf Of Michael Bayer On Jan 4, 2009, at 7:04 PM, Arn Vollebregt wrote: It is my understanding that when one adds an object instance to a session, SQLAlchemy checks for the same object based on it's primary key. I wouldn't say that's accurate - it only looks at the existing identity assigned to the given instance, based on what already- loaded database state is present. If you put two brand new Foo() objects in the session both with id=1, SQLA wouldn't complain at all. It lets the database do its job of detecting that constraint, and the error is an IntegrityError raised by the DBAPI within the flush. But isn't the identity comprised of the object class and the primary key? Would that not make the Foo instances the same in the eyes of SQLAlchemy? Or am I perhaps confusing the id variable with the real primary key which is stored somewhere else? And if so, is there a way to change this behavior, without querying the database? SQLA core never chooses to reinvent functionality that's the database already provides, so you'd have to build something on your end which hashes all instances in a dictionary based on the desired unique attributes.I've hopefully made this easy via the attached example (SQLA 0.5, py2.5). If it works for you we should add it to the wiki's UsageRecipes section. Oeh yes, that works quite good! You whipped that out quite fast :) Clean code as well! Is this declarative approach the only option, or is that just what you happen to prefer? I am trying to keep my database/SQLAlchemy 'layer' separated from my object declarations in my code, hence the question. Right, next stop for me is writing an add_or_merge() function which tries to add an object to the session, and returns an existing object when a UniqueConstraintException is raised. Sounds like a challenge when relations and association_proxy's come into play :) Thx for the help thus far! Regards, Arn Vollebregt --~--~-~--~~~---~--~~ You received this message because you are subscribed to the Google Groups sqlalchemy group. To post to this group, send email to sqlalchemy@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] Saving/merging 'existing' objects
Hi, It is my understanding that when one adds an object instance to a session, SQLAlchemy checks for the same object based on it's primary key. This becomes apparent when one adds the same instance twice, since the second flush() does not execute any SQL. My issue with this occurs when scattered throughout my code two object instances are filled with the same values: from a SQL point of view this would be the same object (using unique constraints that is), but from a Python point of view these are two different objects (instances). In SQL we guard against duplicates through the use of unique constraints (and/or primary keys), but SQLAlchemy only seem to check for the existence of _instance_key using the identity map. Does this mean SQLAlchemy has no knowledge of the unique constraints in the table definition? And if so, is there a way to change this behavior, without querying the database? I was expecting to find something along the line of merge(), or save_or_update(), perhaps in combination with a custom __cmp__ (like) function in the class? Consider the following (over)simplified example: from sqlalchemy import * from sqlalchemy.orm import * engine = create_engine('sqlite:///:memory:', echo=True) metadata = MetaData() Session = sessionmaker(autoflush=False, bind=engine) session = Session() class MyObject(object): def __init__(self, name, value): self.name = name self.value = value def __repr__(self): return MyObject(name=%s, value=%s) % \ (self.name, self.value) myObjectsTable = Table(myobjects, metadata, Column('id', Integer, primary_key=True), Column('name', String(25)), Column('value', String(25)), UniqueConstraint('name', 'value'), ) metadata.create_all(engine) mapper(MyObject, myObjectsTable) myObject1 = MyObject('myObject1', 'value1') myObject2 = MyObject('myObject2', 'value2') session.add(myObject1) session.add(myObject2) session.flush() # writes to DB as expected session.add(myObject1) session.flush() # does nothing, as expected myObject3 = MyObject('myObject1', 'value1') session.add(myObject3) session.flush() # raises an SQL IntegrityError, as expected Of course the last session.flush() will not raise the exception without the UniqueConstraint in the table definition, and the database will essentially contain a duplicate entry, under a different primary key. One could argue that perhaps a composite primary key would be more in place, but a) this is an simplified example b) this would be messy in a relational database and c) I think it would be logical that SQLAlchemy would have knowledge of (and actually mirror) constraints in the database? But perhaps there are valid arguments against this. Regards, Arn Vollebregt --~--~-~--~~~---~--~~ You received this message because you are subscribed to the Google Groups sqlalchemy group. To post to this group, send email to sqlalchemy@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] Decouple association proxy?
Hi, I am wondering if there is a way to completely decouple the association proxy declaration from my Python classes, and contain this to a database class? My goal is not to 'burden' developers looking at the main part of my code with SQLAlchemy when there is no need to directly interact with the database. This in order to heighten the readability/extendability of my code. All the examples I have seen thus far declare the association proxy directly within the class definition, which I would like to avoid. Below I have attached a piece of generalized code to which I would like to apply this principle (let's hope the indentation survives the email...). In reality the class declarations would be located within a different file and imported in the database class to decouple these sections of the code. In case your wondering: the goal of the code is a self-referential n-n relation where the association class has an extra attribute which needs to be accessible from inside the program. Most code has been borrowed from the broker/stocks example[1]. Any ideas? Or is this simply not possible at this time? This is my first encounter with SQLAlchemy, and perhaps I have missed the obvious somehow... Regards, Arn Vollebregt [1] http://www.sqlalchemy.org/docs/05/reference/ext/associationproxy.html#building-complex-views test.py from sqlalchemy import create_engine, MetaData, Table, Column, Integer, String, ForeignKey from sqlalchemy.orm import sessionmaker, mapper, relation from sqlalchemy.ext.associationproxy import association_proxy from sqlalchemy.orm.collections import attribute_mapped_collection engine = create_engine('sqlite:///test.db', echo=True) metadata = MetaData() Session = sessionmaker(bind=engine) session = Session() def createAssociationObject(otherMyObject, myProperty): return AssociationObject(otherMyObject=otherMyObject, myProperty=myProperty) class MyObject(object): myObjects = association_proxy('myPropertyProxy', 'myProperty', creator=createAssociationObject) def __init__(self, name): self.name = name def __str__(self): # potential infinit recursion #return myObject(name=%s, myObjects=%s) % (self.name, self.myObjects) return myObject(name=%s, #myObjects=%i) % (self.name, len (self.myObjects)) def __repr__(self): return self.__str__() class AssociationObject(object): def __init__(self, myObject=None, otherMyObject=None, myProperty=0): self.myObject = myObject self.otherMyObject = otherMyObject self.myProperty = myProperty myObjectsTable = Table(myObjects, metadata, Column('id', Integer, primary_key=True), Column('name', String(25), nullable=False), ) AssociationObjectsTable = Table(associationObjects, metadata, Column('myObjectID', Integer, ForeignKey('myObjects.id'), primary_key=True), Column('otherMyObjectID', Integer, ForeignKey('myObjects.id'), primary_key=True), Column('myProperty', Integer), ) mapper(MyObject, myObjectsTable, properties={ 'myPropertyProxy': relation(AssociationObject, collection_class=attribute_mapped_collection('myProperty'), primaryjoin=AssociationObjectsTable.c.myObjectID==myObjectsTable.c.id,) }) mapper(AssociationObject, AssociationObjectsTable, properties={ 'myObject': relation(MyObject, primaryjoin=AssociationObjectsTable.c.myObjectID==myObjectsTable.c.id,), 'otherMyObject': relation(MyObject, primaryjoin=AssociationObjectsTable.c.otherMyObjectID==myObjectsTable.c.id,) }) metadata.create_all(engine) myObject1 = MyObject('testObject1') myObject2 = MyObject('testObject2') myObject3 = MyObject('testObject3') myObject1.myObjects[myObject2] = 1 myObject2.myObjects[myObject1] = 1 myObject3.myObjects[myObject2] = 1 myObject3.myObjects[myObject1] = 2 session.add(myObject1) session.add(myObject2) session.add(myObject3) session.commit() for myObject in session.query(MyObject).order_by(MyObject.id): print myObject for otherMyObject, myProperty in myObject.myObjects.items(): print \t + str(otherMyObject), str(myProperty) /test.py --~--~-~--~~~---~--~~ You received this message because you are subscribed to the Google Groups sqlalchemy group. To post to this group, send email to sqlalchemy@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: Decouple association proxy?
From: sqlalchemy@googlegroups.com [mailto:sqlalch...@googlegroups.com] On Behalf Of Michael Bayer On Dec 19, 2008, at 2:11 PM, Arn Vollebregt wrote: I am wondering if there is a way to completely decouple the association proxy declaration from my Python classes, and contain this to a database class? easy enough: mapper(MyClass, mytable, properties={...}) MyClass.some_association = assocation_proxy(*args) Hmm, yes, of course :) I'll have to find the fine line between hiding the SQLAlchemy magic, but not hiding too much of the magic, but I can think I can work with this suggestion. Thx! Regards, Arn Vollebregt --~--~-~--~~~---~--~~ You received this message because you are subscribed to the Google Groups sqlalchemy group. To post to this group, send email to sqlalchemy@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 -~--~~~~--~~--~--~---