[sqlalchemy] Re: Is there a way to replace object in DB?
On Jan 15, 2008 6:54 PM, Michael Bayer [EMAIL PROTECTED] wrote: The last commit fails with: sqlalchemy.exceptions.IntegrityError: (IntegrityError) Referers.objectId may not be NULL u'UPDATE Referers SET objectId=? WHERE Referers.id = ?' [None, 1] right thats because the instance doesnt exist yet. its better for you to just use the straight ahead query.get(), if None then save() approach. Do you mean obj2 (id of which should go to objectId column in this UPDATE)? If so, why it doesn't exist? It's saved (updated in fact) just several lines above, so it must be both in DB and in session. The last lines of the original test case for convenience: [...] obj2 = replace(session, ModelObject(1, u'title2')) session.commit() ref2 = ModelReferer(1, obj2) replace(session, ref2) session.commit() --~--~-~--~~~---~--~~ 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 [EMAIL PROTECTED] For more options, visit this group at http://groups.google.com/group/sqlalchemy?hl=en -~--~~~~--~~--~--~---
[sqlalchemy] Re: Is there a way to replace object in DB?
On Jan 16, 2008, at 4:43 AM, Denis S. Otkidach wrote: On Jan 15, 2008 6:54 PM, Michael Bayer [EMAIL PROTECTED] wrote: The last commit fails with: sqlalchemy.exceptions.IntegrityError: (IntegrityError) Referers.objectId may not be NULL u'UPDATE Referers SET objectId=? WHERE Referers.id = ?' [None, 1] right thats because the instance doesnt exist yet. its better for you to just use the straight ahead query.get(), if None then save() approach. Do you mean obj2 (id of which should go to objectId column in this UPDATE)? If so, why it doesn't exist? It's saved (updated in fact) just several lines above, so it must be both in DB and in session. The last lines of the original test case for convenience: [...] obj2 = replace(session, ModelObject(1, u'title2')) session.commit() ref2 = ModelReferer(1, obj2) replace(session, ref2) session.commit() I cant tell what your issue is there without the backing data showing the full picture. We dont support manipulating _instance_key manually as a supported use case, so issues are not surprising. heres an example using public APIs: def replace(session, cls, id, **kwargs): obj = session.query(cls).get(id) if obj is None: obj = cls(id=id, **kwargs) session.save(obj) else: for key in kwargs: setattr(obj, key, kwargs[key]) return obj obj2 = replace(session, ModelObject, 1, title=u'title2') session.commit() ref2 = replace(session, ModelReferer, 1, object=obj2) session.commit() the above will also use less SQL than how you were doing it. if that function is producing the same issue, provide a test case that includes the supporting data and runs fully since from your example I dont know if ModelReferer(1) is supposed to be present or not. --~--~-~--~~~---~--~~ 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 [EMAIL PROTECTED] For more options, visit this group at http://groups.google.com/group/sqlalchemy?hl=en -~--~~~~--~~--~--~---
[sqlalchemy] Re: Is there a way to replace object in DB?
On Jan 11, 2008 8:41 PM, Michael Bayer [EMAIL PROTECTED] wrote:
what that looks like to me is that you're attempting to query the
database for object ID #1 using merge().
when you merge(), its going to treat the object similarly to how it
does using session.save_or_update(). that is, it looks for an
_instance_key attribute to determine if the object represents a
transient or persisted instance.
So you could hack the way youre doing it like:
obj2 = ModelObject(1, u'title2')
obj2._instance_key = session.identity_key(instance=obj2)
session.merge(obj2)
session.commit()
we have yet to define a completely public API for the above operation,
i.e. treat this object as though its persistent. im not sure yet
how we could define one that has a straightforward use case which
wouldn't add confusion.
Sometimes this doesn't work:
---8---
import sqlalchemy as sa, logging
from sqlalchemy.orm import mapper, sessionmaker, relation
logging.getLogger('sqlalchemy.engine').setLevel(logging.INFO)
logging.basicConfig()
class ModelObject(object):
def __init__(self, id, title):
self.id = id
self.title = title
class ModelReferer(object):
def __init__(self, id, object):
self.id = id
self.object = object
metadata = sa.MetaData()
objectsTable = sa.Table(
'Objects', metadata,
sa.Column('id', sa.Integer, primary_key=True),
sa.Column('title', sa.String(255), nullable=False),
)
referersTable = sa.Table(
'Referers', metadata,
sa.Column('id', sa.Integer, primary_key=True),
sa.Column('objectId', sa.Integer, sa.ForeignKey('Objects.id'),
nullable=False),
)
objectsMapper = mapper(ModelObject, objectsTable)
referersMapper = mapper(ModelReferer, referersTable,
properties={'object': relation(ModelObject)})
engine = sa.create_engine('sqlite://')
metadata.create_all(engine, checkfirst=True)
session = sessionmaker(bind=engine)()
def replace(session, obj):
identityKey = session.identity_key(instance=obj)
oldObj = session.get(*identityKey[:2])
if oldObj is None:
session.save(obj)
return obj
else:
obj._instance_key = identityKey
return session.merge(obj)
obj1 = ModelObject(1, u'title1')
replace(session, obj1)
ref1 = ModelReferer(1, obj1)
replace(session, ref1)
session.commit()
session.clear()
# Another program. We have to insure that object with id=1 exists in DB and has
# certain properties.
obj2 = replace(session, ModelObject(1, u'title2'))
session.commit()
ref2 = ModelReferer(1, obj2)
replace(session, ref2)
session.commit()
---8---
The last commit fails with:
sqlalchemy.exceptions.IntegrityError: (IntegrityError)
Referers.objectId may not be NULL u'UPDATE Referers SET objectId=?
WHERE Referers.id = ?' [None, 1]
--~--~-~--~~~---~--~~
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 [EMAIL PROTECTED]
For more options, visit this group at
http://groups.google.com/group/sqlalchemy?hl=en
-~--~~~~--~~--~--~---
[sqlalchemy] Re: Is there a way to replace object in DB?
On Jan 15, 2008, at 8:20 AM, Denis S. Otkidach wrote: On Jan 11, 2008 8:41 PM, Michael Bayer [EMAIL PROTECTED] wrote: what that looks like to me is that you're attempting to query the database for object ID #1 using merge(). when you merge(), its going to treat the object similarly to how it does using session.save_or_update(). that is, it looks for an _instance_key attribute to determine if the object represents a transient or persisted instance. So you could hack the way youre doing it like: obj2 = ModelObject(1, u'title2') obj2._instance_key = session.identity_key(instance=obj2) session.merge(obj2) session.commit() we have yet to define a completely public API for the above operation, i.e. treat this object as though its persistent. im not sure yet how we could define one that has a straightforward use case which wouldn't add confusion. Sometimes this doesn't work: The last commit fails with: sqlalchemy.exceptions.IntegrityError: (IntegrityError) Referers.objectId may not be NULL u'UPDATE Referers SET objectId=? WHERE Referers.id = ?' [None, 1] right thats because the instance doesnt exist yet. its better for you to just use the straight ahead query.get(), if None then save() approach. --~--~-~--~~~---~--~~ 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 [EMAIL PROTECTED] For more options, visit this group at http://groups.google.com/group/sqlalchemy?hl=en -~--~~~~--~~--~--~---
[sqlalchemy] Re: Is there a way to replace object in DB?
On Jan 11, 2008, at 12:30 PM, Denis S. Otkidach wrote: # Another program. We have to insure that object with id=1 exists in DB and has # certain properties. obj2 = ModelObject(1, u'title2') session.merge(obj2) session.commit() what that looks like to me is that you're attempting to query the database for object ID #1 using merge(). when you merge(), its going to treat the object similarly to how it does using session.save_or_update(). that is, it looks for an _instance_key attribute to determine if the object represents a transient or persisted instance. So you could hack the way youre doing it like: obj2 = ModelObject(1, u'title2') obj2._instance_key = session.identity_key(instance=obj2) session.merge(obj2) session.commit() we have yet to define a completely public API for the above operation, i.e. treat this object as though its persistent. im not sure yet how we could define one that has a straightforward use case which wouldn't add confusion. Anyway, the legit way to go is this (and this is what the above merge() is doing anyway): obj2 = session.query(ModelObject).get(1) if not obj2: obj2 = ModelObject(1, u'title2') session.save(obj2) else: obj2.title= u'title2' session.commit() --~--~-~--~~~---~--~~ 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 [EMAIL PROTECTED] For more options, visit this group at http://groups.google.com/group/sqlalchemy?hl=en -~--~~~~--~~--~--~---
[sqlalchemy] Re: Is there a way to replace object in DB?
On Dec 28, 2007 6:25 PM, Michael Bayer [EMAIL PROTECTED] wrote:
On Dec 28, 2007, at 5:50 AM, Denis S. Otkidach wrote:
Sure, I can get an object from DB and copy data from new one. But
there is a lot of object types, so have to invent yet another meta
description for it (while it already exists in sqlalchemy). And
requirements changes often, so I have to change scheme in 2 places.
This is not good and error prone. Why I have to invent new description
when there is already one from sqlalchemy mapping? Can't I use it for
my purpose? Something like merge(objFromDB, newObj) will solve the
problem.
session.merge() does copy the attributes of one object into another.
theres some bugs with dont_load that have been fixed in trunk so try
out the trunk if you have problems.
This doesn't work: I have the same IntegrityError or FlushError
depending on whether original object exists in the session (line
session.clear() is commented in the code below). What I do wrong?
---8---
import sqlalchemy as sa, logging
from sqlalchemy.orm import mapper, sessionmaker
logging.getLogger('sqlalchemy.engine').setLevel(logging.INFO)
logging.basicConfig()
class ModelObject(object):
def __init__(self, id, title):
self.id = id
self.title = title
metadata = sa.MetaData()
objectTable = sa.Table(
'Objects', metadata,
sa.Column('id', sa.Integer, primary_key=True),
sa.Column('title', sa.String(255), nullable=False),
)
objectsMapper = mapper(ModelObject, objectTable)
engine = sa.create_engine('sqlite://')
metadata.create_all(engine, checkfirst=True)
session = sessionmaker(bind=engine)()
obj1 = ModelObject(1, u'title1')
session.save(obj1)
session.commit()
session.clear()
# Another program. We have to insure that object with id=1 exists in DB and has
# certain properties.
obj2 = ModelObject(1, u'title2')
session.merge(obj2)
session.commit()
---8---
--~--~-~--~~~---~--~~
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 [EMAIL PROTECTED]
For more options, visit this group at
http://groups.google.com/group/sqlalchemy?hl=en
-~--~~~~--~~--~--~---
[sqlalchemy] Re: Is there a way to replace object in DB?
On Dec 28, 2007, at 5:50 AM, Denis S. Otkidach wrote: Sure, I can get an object from DB and copy data from new one. But there is a lot of object types, so have to invent yet another meta description for it (while it already exists in sqlalchemy). And requirements changes often, so I have to change scheme in 2 places. This is not good and error prone. Why I have to invent new description when there is already one from sqlalchemy mapping? Can't I use it for my purpose? Something like merge(objFromDB, newObj) will solve the problem. session.merge() does copy the attributes of one object into another. theres some bugs with dont_load that have been fixed in trunk so try out the trunk if you have problems. --~--~-~--~~~---~--~~ 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 [EMAIL PROTECTED] For more options, visit this group at http://groups.google.com/group/sqlalchemy?hl=en -~--~~~~--~~--~--~---
[sqlalchemy] Re: Is there a way to replace object in DB?
On Dec 28, 2007 1:00 AM, Rick Morrison [EMAIL PROTECTED] wrote: Here's the idiom that should work: def ensure_object(sess, id): o = sess.Query(ModelObject).get(id)# if found, o is now loaded into session if not o: o = ModelObject(1, u'title') sess.save(o) sess.flush() return o This is not what I need: in your example object is not updated with new data. Let me describe the problem. There is a data, that must exist in DB. I have a setup script that gets such data from other source as model objects and pushes it to DB. No problems to run this script ones. But a life changes and the new must-have data appear. No problems to run it several times if pushed data can't be changed. Unfortunately they can be changed, so I have to replace it. I can't delete them before inserting since there are foreign key references. Sure, I can get an object from DB and copy data from new one. But there is a lot of object types, so have to invent yet another meta description for it (while it already exists in sqlalchemy). And requirements changes often, so I have to change scheme in 2 places. This is not good and error prone. Why I have to invent new description when there is already one from sqlalchemy mapping? Can't I use it for my purpose? Something like merge(objFromDB, newObj) will solve the problem. --~--~-~--~~~---~--~~ 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 [EMAIL PROTECTED] For more options, visit this group at http://groups.google.com/group/sqlalchemy?hl=en -~--~~~~--~~--~--~---
[sqlalchemy] Re: Is there a way to replace object in DB?
I think you're thinking of .load() .get() does not throw on not found. On 12/27/07, braydon fuller [EMAIL PROTECTED] wrote: you can also use 'try' to avoid error messages: def ensure_object(db, id): try: o = db.Query(ModelObject).get(id) except: o = ModelObject(1, u'title') db.save(o) db.commit() return o -Braydon Rick Morrison wrote: ...if you're just checking to see if something exists in the database, why not just try to .load() it, and then construct it afresh if you don't find it? This kind of operation is sometimes called an upsert ...some database engines support it, some don't. Most don't. But what all database engines DO support is a query, followed by either an insert, or an update as appropriate. Here's the idiom that should work: def ensure_object(sess, id): o = sess.Query(ModelObject).get(id)# if found, o is now loaded into session if not o: o = ModelObject(1, u'title' sess.save(o) sess.flush() return o --~--~-~--~~~---~--~~ 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 [EMAIL PROTECTED] For more options, visit this group at http://groups.google.com/group/sqlalchemy?hl=en -~--~~~~--~~--~--~---
[sqlalchemy] Re: Is there a way to replace object in DB?
On Dec 26, 2007 10:38 PM, Michael Bayer [EMAIL PROTECTED] wrote:
if you have an instance which you are unsure if it already exists, you
can add it to a session using session.save_or_update(instance). The
decision between INSERT and UPDATE is ultimately decided by the
presence of an attribute on the instance called _instance_key.
I'd like mapper to use UPDATE for newly constructed object (i.e.
object without _instance_key attribute) when a record with the same
primary key already exists in DB.
In most cases, this attribute is not something you need to worry about;
if an instance has been flushed or loaded from a session, it will have
the attribute, or if you've just constructed it and not yet persisted
it, the attribute will not be there. If you think you need to
manually manipulate this attribute, perhaps you can describe your
specific use case so that we can recommend the best way to accomplish
it.
OK, below is a use/test case:
---8---
import sqlalchemy as sa, logging
from sqlalchemy.orm import mapper, sessionmaker
logging.getLogger('sqlalchemy.engine').setLevel(logging.INFO)
logging.basicConfig()
class ModelObject(object):
def __init__(self, id, title):
self.id = id
self.title = title
metadata = sa.MetaData()
objectTable = sa.Table(
'Objects', metadata,
sa.Column('id', sa.Integer, primary_key=True),
sa.Column('title', sa.String(255), nullable=False),
)
objectsMapper = mapper(ModelObject, objectTable)
engine = sa.create_engine('sqlite://')
metadata.create_all(engine, checkfirst=True)
session = sessionmaker(bind=engine)()
obj = ModelObject(1, u'title')
session.save(obj)
session.commit()
session.clear()
# Another program. We have to insure that object with id=1 exists in DB and has
# certain properties.
obj = ModelObject(1, u'title')
session.save_or_update(obj)
session.commit()
---8---
--~--~-~--~~~---~--~~
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 [EMAIL PROTECTED]
For more options, visit this group at
http://groups.google.com/group/sqlalchemy?hl=en
-~--~~~~--~~--~--~---
[sqlalchemy] Re: Is there a way to replace object in DB?
...if you're just checking to see if something exists in the database, why
not just try to .load() it, and then construct it afresh if you don't find
it?
This kind of operation is sometimes called an upsert ...some database
engines support it, some don't. Most don't. But what all database engines DO
support is a query, followed by either an insert, or an update as
appropriate.
Here's the idiom that should work:
def ensure_object(sess, id):
o = sess.Query(ModelObject).get(id)# if found, o is now loaded into
session
if not o:
o = ModelObject(1, u'title')
sess.save(o)
sess.flush()
return o
On 12/27/07, Denis S. Otkidach [EMAIL PROTECTED] wrote:
On Dec 26, 2007 10:38 PM, Michael Bayer [EMAIL PROTECTED] wrote:
if you have an instance which you are unsure if it already exists, you
can add it to a session using session.save_or_update(instance). The
decision between INSERT and UPDATE is ultimately decided by the
presence of an attribute on the instance called _instance_key.
I'd like mapper to use UPDATE for newly constructed object (i.e.
object without _instance_key attribute) when a record with the same
primary key already exists in DB.
In most cases, this attribute is not something you need to worry about;
if an instance has been flushed or loaded from a session, it will have
the attribute, or if you've just constructed it and not yet persisted
it, the attribute will not be there. If you think you need to
manually manipulate this attribute, perhaps you can describe your
specific use case so that we can recommend the best way to accomplish
it.
OK, below is a use/test case:
---8---
import sqlalchemy as sa, logging
from sqlalchemy.orm import mapper, sessionmaker
logging.getLogger('sqlalchemy.engine').setLevel(logging.INFO)
logging.basicConfig()
class ModelObject(object):
def __init__(self, id, title):
self.id = id
self.title = title
metadata = sa.MetaData()
objectTable = sa.Table(
'Objects', metadata,
sa.Column('id', sa.Integer, primary_key=True),
sa.Column('title', sa.String(255), nullable=False),
)
objectsMapper = mapper(ModelObject, objectTable)
engine = sa.create_engine('sqlite://')
metadata.create_all(engine, checkfirst=True)
session = sessionmaker(bind=engine)()
obj = ModelObject(1, u'title')
session.save(obj)
session.commit()
session.clear()
# Another program. We have to insure that object with id=1 exists in DB
and has
# certain properties.
obj = ModelObject(1, u'title')
session.save_or_update(obj)
session.commit()
---8---
--~--~-~--~~~---~--~~
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 [EMAIL PROTECTED]
For more options, visit this group at
http://groups.google.com/group/sqlalchemy?hl=en
-~--~~~~--~~--~--~---
[sqlalchemy] Re: Is there a way to replace object in DB?
you can also use 'try' to avoid error messages: def ensure_object(db, id): try: o = db.Query(ModelObject).get(id) except: o = ModelObject(1, u'title') db.save(o) db.commit() return o -Braydon Rick Morrison wrote: ...if you're just checking to see if something exists in the database, why not just try to .load() it, and then construct it afresh if you don't find it? This kind of operation is sometimes called an upsert ...some database engines support it, some don't. Most don't. But what all database engines DO support is a query, followed by either an insert, or an update as appropriate. Here's the idiom that should work: def ensure_object(sess, id): o = sess.Query(ModelObject).get(id)# if found, o is now loaded into session if not o: o = ModelObject(1, u'title' sess.save(o) sess.flush() return o --~--~-~--~~~---~--~~ 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 [EMAIL PROTECTED] For more options, visit this group at http://groups.google.com/group/sqlalchemy?hl=en -~--~~~~--~~--~--~---
