[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
-~--~~~~--~~--~--~---
