[sqlalchemy] Re: New instance ExtraStat with identity key (...) conflicts with persistent instance ExtraStat
Michael,
Thanks for the pointer, that makes great sense, and once again points
how my generally small database design skills. I'll update my code to
try this out.
Again,
Thanks!
Doug
From: sqlalchemy@googlegroups.com [mailto:[EMAIL PROTECTED]
On Behalf Of Michael Bayer
Sent: Saturday, November 29, 2008 9:28 PM
To: sqlalchemy@googlegroups.com
Subject: [sqlalchemy] Re: New instance ExtraStat with identity key (...)
conflicts with persistent instance ExtraStat
Simon's suggestion about the duplicate name still holds. Your
relation from Stat-ExtraStat currently needs to be one-to-one since you
cannot have more than one ExtraStat referencing a single Stat, due to
the PK constraint on ExtraStat.name. The error is raising at the point
of query() since autoflush is kicking in - use session.flush() to
isolate the error.
On Nov 29, 2008, at 12:18 PM, Doug Farrell wrote:
Hi all,
I'm having a problem with a new instance of a relation conflicting with
an existing instance. I'm using SA 0.5rc with Sqlite3. Here are my
simplified classes:
class Stat(sqladb.Base):
__tablename__ = stats
name = Column(String(32), primary_key=True)
total= Column(Integer)
created = Column(DateTime, default=datetime.datetime.now())
updated = Column(DateTime)
states = Column(PickleType, default={})
extraStats = relation(ExtraStat, backref=stat)
class ExtraStat(sqladb.Base):
__tablename__ = extrastats
name = Column(String(32), ForeignKey(stats.name),
primary_key=True)
total= Column(Integer)
created = Column(DateTime, default=datetime.datetime.now())
updated = Column(DateTime)
states = Column(PickleType, default={})
The above Stat class has a one-to-many relationship with the ExtraStat
class (which I think I've implemented correctly). Later in the
program I create an in memory data model that has as part of it's
components two
dictionaries that contain Stat instances. Those Stat instances have
relationships to ExtraStat instances. My problem comes in the
following when I'm trying to update the data in those instances/tables.
Here is a section of code that throws the exception:
pressName = press%s % pressNum
# add new ExtraStat instances as relations
self._addProductStatsPress(productType, pressName)
self._addPressStatsProduct(pressName, productType)
try:
extraStat = session.query(Stat). \
filter(Stat.name==productType). \
join(extraStats). \
filter(ExtraStat.name==pressName).one()
except:
extraStat = ExtraStat(pressName, ExtraStat.PRESS_TYPE)
self.productStats[productType].extraStats.append(extraStat)
extraStat.states.setdefault(sstate, 0)
extraStat.states[sstate] += 1
extraStat.updated = now
extraStat = session.merge(extraStat)
try:
extraStat = session.query(Stat). \
filter(Stat.name==pressName). \
join(extraStats). \
filter(ExtraStat.name==productType).one() throws
exception right here
except:
extraStat = ExtraStat(productType, ExtraStat.PRODUCT_TYPE)
self.pressStats[pressName].extraStats.append(extraStat)
extraStat.states.setdefault(sstate, 0)
extraStat.states[sstate] += 1
extraStat.updated = now
The marked area is wear it throws the exception. I'm not sure what to
do here to get past this, any help or ideas would be greatly
appreciated.
The exact exception is as follows:
Sqlalchemy.orm.exc.FlushError: New instance [EMAIL PROTECTED] With identity
key (class '__main__.ExtraStat',(u'C',)) conflicts with persistent
instance [EMAIL PROTECTED]
Thanks!
Doug
--~--~-~--~~~---~--~~
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: New instance ExtraStat with identity key (...) conflicts with persistent instance ExtraStat
I keep thinking one of my relatives has emailed me when you post here
and I get all excited. Any chance you can change your name ? :-)
2008/12/2 Doug Farrell [EMAIL PROTECTED]:
Michael,
Thanks for the pointer, that makes great sense, and once again points how my
generally small database design skills. I'll update my code to try this out.
Again,
Thanks!
Doug
From: sqlalchemy@googlegroups.com [mailto:[EMAIL PROTECTED] On
Behalf Of Michael Bayer
Sent: Saturday, November 29, 2008 9:28 PM
To: sqlalchemy@googlegroups.com
Subject: [sqlalchemy] Re: New instance ExtraStat with identity key (...)
conflicts with persistent instance ExtraStat
Simon's suggestion about the duplicate name still holds. Your relation
from Stat-ExtraStat currently needs to be one-to-one since you cannot have
more than one ExtraStat referencing a single Stat, due to the PK constraint
on ExtraStat.name. The error is raising at the point of query() since
autoflush is kicking in - use session.flush() to isolate the error.
On Nov 29, 2008, at 12:18 PM, Doug Farrell wrote:
Hi all,
I'm having a problem with a new instance of a relation conflicting with
an existing instance. I'm using SA 0.5rc with Sqlite3. Here are my
simplified classes:
class Stat(sqladb.Base):
__tablename__ = stats
name = Column(String(32), primary_key=True)
total= Column(Integer)
created = Column(DateTime, default=datetime.datetime.now())
updated = Column(DateTime)
states = Column(PickleType, default={})
extraStats = relation(ExtraStat, backref=stat)
class ExtraStat(sqladb.Base):
__tablename__ = extrastats
name = Column(String(32), ForeignKey(stats.name),
primary_key=True)
total= Column(Integer)
created = Column(DateTime, default=datetime.datetime.now())
updated = Column(DateTime)
states = Column(PickleType, default={})
The above Stat class has a one-to-many relationship with the ExtraStat
class (which I think I've implemented correctly). Later in the
program I create an in memory data model that has as part of it's
components two
dictionaries that contain Stat instances. Those Stat instances have
relationships to ExtraStat instances. My problem comes in the
following when I'm trying to update the data in those instances/tables.
Here is a section of code that throws the exception:
pressName = press%s % pressNum
# add new ExtraStat instances as relations
self._addProductStatsPress(productType, pressName)
self._addPressStatsProduct(pressName, productType)
try:
extraStat = session.query(Stat). \
filter(Stat.name==productType). \
join(extraStats). \
filter(ExtraStat.name==pressName).one()
except:
extraStat = ExtraStat(pressName, ExtraStat.PRESS_TYPE)
self.productStats[productType].extraStats.append(extraStat)
extraStat.states.setdefault(sstate, 0)
extraStat.states[sstate] += 1
extraStat.updated = now
extraStat = session.merge(extraStat)
try:
extraStat = session.query(Stat). \
filter(Stat.name==pressName). \
join(extraStats). \
filter(ExtraStat.name==productType).one() throws
exception right here
except:
extraStat = ExtraStat(productType, ExtraStat.PRODUCT_TYPE)
self.pressStats[pressName].extraStats.append(extraStat)
extraStat.states.setdefault(sstate, 0)
extraStat.states[sstate] += 1
extraStat.updated = now
The marked area is wear it throws the exception. I'm not sure what to
do here to get past this, any help or ideas would be greatly
appreciated.
The exact exception is as follows:
Sqlalchemy.orm.exc.FlushError: New instance [EMAIL PROTECTED] With identity
key (class '__main__.ExtraStat',(u'C',)) conflicts with persistent
instance [EMAIL PROTECTED]
Thanks!
Doug
--~--~-~--~~~---~--~~
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] inferring object class/table directly
Hi,
If I have an ORM object, it is sometimes convenient to be able to infer
the class directly. Eg. consider this function.
def add_patient_obj(session, patient_obj):
Check if object primary key exists in db. If so,exit, else
add.
pid = patient_obj.id
#print session.query(Patient).filter_by(id=pid).count()
if session.query(Patient).filter_by(id=pid).count() 0:
print Patient object with id %s is already in db.%pid
exit
else:
session.save(patient_obj)
session.commit()
But I want a generic version. Since patient_obj knows what class is
belongs to, it should be possible not to have to state the class directly,
which here is Patient.
I have done the following, which works, but is hideous, horrible, ugly,
fragile hack. Can anyone suggest a better way of doing this?
Please CC me on any reply. Thanks in advance.
Regards, Faheem.
def add_obj(session, obj):
Check if object primary key exists in db. If so,exit, else
add.
c = str(type(obj)).split(')[1].split(.)[1]
s = q = session.query(+ c +)
exec(s)
pid = obj.id
if q.filter_by(id=pid).count() 0:
print Patient object with id %s is already in db.%pid
exit
else:
session.save(obj)
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] a-directional i.e. bi-directional m:m relations
I could use some insightful suggestions here: What do you think of this use case? Let's say I have a class Foo mapped to a table 'foos', and a many:many relation between these objects, stored in the table 'foo_relations'. columns on foos: id (Integer) data (Text) columns on foo_relations: this_foo_id (Integer) that_foo_id (Integer) But this implicitly creates a direction to the m:m relation, something that I don't want. I want this m:m relation to be bi- directional (or you could call it adirectional). The setup above makes one foo into this and the other into that. It's not, of course, because of what I labeled them; it's because the columns have to be labeled at all. But I don't want a specific directionality here. Now when I want to find out whether a Foo has a relation to another Foo, I have to check whether there exists any row in foo_relations that has the given Foo as either as this OR that. Also, what if I need backrefs on the foo_relations mapper? The backref from 'this' and the backref from 'that' would both point to something called a foo, but they would have to be given separate labels in order ot not be conflicting property names -- when really, I would not want to know if a foo was the 'that' or the 'this' of some foo relation. So ideally in a case like this, I could set an option that says the m:m relation is bidirectional, and that the backrefs for both foreign keys in the m:m table should really point to the same place (or at least be unioned together). I have a feeling that would violate some part of the RDBMS standards, and I'm perfectly willing to go without or work around. This is more of a philosophical point for learning's sake -- what do other people do in such cases? Thanks, Eric --~--~-~--~~~---~--~~ 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: inferring object class/table directly
def add_obj(session, obj):
Check if object primary key exists in db. If so,exit, else
add.
pid = obj.id
if session.query(obj.__class__).filter_by(id=pid).count():
print Patient object with id %s is already in db.%pid
exit
else:
session.save(obj)
session.commit()
Not too difficult. You can also use type(obj) instead of
obj.__class__.
Furthermore, if you really need to determine the object's class's
mapped table,
obj_table = obj.__class__._sa_class_manager.mapper.mapped_table
Of course, being an underscored thing, _sa_class_manager is not
something you should count on from version to version of sqlalchemy,
so keep that in consideration and don't use it anywhere you don't plan
to maintain.
Eric
On Dec 2, 2:24 pm, Faheem Mitha [EMAIL PROTECTED] wrote:
Hi,
If I have an ORM object, it is sometimes convenient to be able to infer
the class directly. Eg. consider this function.
def add_patient_obj(session, patient_obj):
Check if object primary key exists in db. If so,exit, else
add.
pid = patient_obj.id
#print session.query(Patient).filter_by(id=pid).count()
if session.query(Patient).filter_by(id=pid).count() 0:
print Patient object with id %s is already in db.%pid
exit
else:
session.save(patient_obj)
session.commit()
But I want a generic version. Since patient_obj knows what class is
belongs to, it should be possible not to have to state the class directly,
which here is Patient.
I have done the following, which works, but is hideous, horrible, ugly,
fragile hack. Can anyone suggest a better way of doing this?
Please CC me on any reply. Thanks in advance.
Regards, Faheem.
def add_obj(session, obj):
Check if object primary key exists in db. If so,exit, else
add.
c = str(type(obj)).split(')[1].split(.)[1]
s = q = session.query(+ c +)
exec(s)
pid = obj.id
if q.filter_by(id=pid).count() 0:
print Patient object with id %s is already in db.%pid
exit
else:
session.save(obj)
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: inferring object class/table directly
On Dec 2, 2008, at 6:04 PM, Eric Ongerth wrote: def add_obj(session, obj): Check if object primary key exists in db. If so,exit, else add. pid = obj.id if session.query(obj.__class__).filter_by(id=pid).count(): print Patient object with id %s is already in db.%pid exit else: session.save(obj) session.commit() Not too difficult. You can also use type(obj) instead of obj.__class__. Furthermore, if you really need to determine the object's class's mapped table, obj_table = obj.__class__._sa_class_manager.mapper.mapped_table here's the API way: object_mapper(obj).mapped_table --~--~-~--~~~---~--~~ 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: a-directional i.e. bi-directional m:m relations
On Dec 2, 2008, at 5:54 PM, Eric Ongerth wrote: Now when I want to find out whether a Foo has a relation to another Foo, I have to check whether there exists any row in foo_relations that has the given Foo as either as this OR that. Also, what if I need backrefs on the foo_relations mapper? The backref from 'this' and the backref from 'that' would both point to something called a foo, but they would have to be given separate labels in order ot not be conflicting property names -- when really, I would not want to know if a foo was the 'that' or the 'this' of some foo relation. So ideally in a case like this, I could set an option that says the m:m relation is bidirectional, and that the backrefs for both foreign keys in the m:m table should really point to the same place (or at least be unioned together). I have a feeling that would violate some part of the RDBMS standards, and I'm perfectly willing to go without or work around. This is more of a philosophical point for learning's sake -- what do other people do in such cases? you can store two rows in the association table, one for each direction. or provide an accessor which just unions together the forwards and backwards references between Foo objects. or make a readonly relation() that does the appropriate OR logic. I might even try combining both of those techniques somehow. --~--~-~--~~~---~--~~ 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: inferring object class/table directly
[This message has also been posted.] Hi Eric, Thanks very much for the improvement. On Tue, 2 Dec 2008 15:04:34 -0800 (PST), Eric Ongerth [EMAIL PROTECTED] wrote: def add_obj(session, obj): Check if object primary key exists in db. If so,exit, else add. pid = obj.id if session.query(obj.__class__).filter_by(id=pid).count(): print Patient object with id %s is already in db.%pid exit else: session.save(obj) session.commit() Yes, I was looking for this, and printed out obj.__dict__ but didn't see it there. A dictionary of attributes is very useful in theory, but doesn't always seem to have all attributes. Is this documented anywhere? Not too difficult. You can also use type(obj) instead of obj.__class__. I thought of trying this, but didn't. It didn't seem likely to work, anyway. Is either of these preferred over the other in terms of API stability, and if so, why? Furthermore, if you really need to determine the object's class's mapped table, obj_table = obj.__class__._sa_class_manager.mapper.mapped_table Of course, being an underscored thing, _sa_class_manager is not something you should count on from version to version of sqlalchemy, so keep that in consideration and don't use it anywhere you don't plan to maintain. Not sure what the object class's mapped table is, but will look it up. Regards, Faheem. --~--~-~--~~~---~--~~ 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: inferring object class/table directly
[This message has also been posted.] On Tue, 2 Dec 2008 18:25:19 -0500, Michael Bayer [EMAIL PROTECTED] wrote: On Dec 2, 2008, at 6:04 PM, Eric Ongerth wrote: [snip] Furthermore, if you really need to determine the object's class's mapped table, obj_table = obj.__class__._sa_class_manager.mapper.mapped_table here's the API way: object_mapper(obj).mapped_table Hi Michael, Thanks for the clarification. Regards, Faheem. --~--~-~--~~~---~--~~ 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 -~--~~~~--~~--~--~---
