Re: [sqlalchemy] duplicate column name error when subquery() or join() a subclass
Hi Michael,
I modified your code and got the error with subquery(). I remember I used
to have the same error with the join() method but couldn't reproduce it
with this sample. The SQLAlchemy version is still 0.6.8.
I really appreciate your reply and help.
--
from sqlalchemy import *
from sqlalchemy.orm import *
from sqlalchemy.ext.declarative import declarative_base
Base= declarative_base()
class BaseClass(object):
id = Column(id, Integer, primary_key=True, key=id)
class Resource(Base, BaseClass):
__tablename__ = resources
class BasePrAc(Resource):
__tablename__ = base_products_accessories
_polymorphicIdentity = Column(polymorphic_identity, String(20),
key=polymorphicIdentity)
__mapper_args__ = {
'polymorphic_on': _polymorphicIdentity,
'polymorphic_identity': None
}
id = Column(id, Integer, ForeignKey(resources.id), primary_key=True)
class Product(BasePrAc):
__tablename__ = products
__mapper_args__ = {
'polymorphic_identity': 'Product'
}
id = Column(id, Integer, ForeignKey(base_products_accessories.id),
primary_key=True)
class Accessory(BasePrAc):
__tablename__ = accessories
__mapper_args__ = {
'polymorphic_identity': 'Accessory'
}
id = Column(id, Integer, ForeignKey(base_products_accessories.id),
primary_key=True)
class Post(Base, BaseClass):
__tablename__ = posts
basePrAcId = Column(Integer, ForeignKey('base_products_accessories.id'))
basePrAc = relationship(BasePrAc, uselist=False,
backref=backref(_posts, collection_class=set))
e = create_engine(mysql://root@localhost/sample?charset=utf8, echo=True)
Base.metadata.create_all(e)
s = Session(e)
s.add_all([
Post(
basePrAc=Product()
),
Post(
basePrAc=Accessory()
),
Post(
basePrAc=Accessory()
)
])
sq = s.query(BasePrAc).subquery()
q = s.query(Post).join((sq, sq.c.id == Post.basePrAcId)).all()
print q
2012-05-03 18:42:03,539 INFO sqlalchemy.engine.base.Engine.0x...ae90
CREATE TABLE resources (
id INTEGER NOT NULL AUTO_INCREMENT,
PRIMARY KEY (id)
)
2012-05-03 18:42:03,539 INFO sqlalchemy.engine.base.Engine.0x...ae90 ()
2012-05-03 18:42:03,657 INFO sqlalchemy.engine.base.Engine.0x...ae90 COMMIT
2012-05-03 18:42:03,658 INFO sqlalchemy.engine.base.Engine.0x...ae90
CREATE TABLE base_products_accessories (
polymorphic_identity VARCHAR(20),
id INTEGER NOT NULL,
PRIMARY KEY (id),
FOREIGN KEY(id) REFERENCES resources (id)
)
2012-05-03 18:42:03,658 INFO sqlalchemy.engine.base.Engine.0x...ae90 ()
2012-05-03 18:42:03,769 INFO sqlalchemy.engine.base.Engine.0x...ae90 COMMIT
2012-05-03 18:42:03,770 INFO sqlalchemy.engine.base.Engine.0x...ae90
CREATE TABLE accessories (
id INTEGER NOT NULL,
PRIMARY KEY (id),
FOREIGN KEY(id) REFERENCES base_products_accessories (id)
)
2012-05-03 18:42:03,770 INFO sqlalchemy.engine.base.Engine.0x...ae90 ()
2012-05-03 18:42:03,893 INFO sqlalchemy.engine.base.Engine.0x...ae90 COMMIT
2012-05-03 18:42:03,893 INFO sqlalchemy.engine.base.Engine.0x...ae90
CREATE TABLE posts (
id INTEGER NOT NULL AUTO_INCREMENT,
`basePrAcId` INTEGER,
PRIMARY KEY (id),
FOREIGN KEY(`basePrAcId`) REFERENCES base_products_accessories (id)
)
2012-05-03 18:42:03,893 INFO sqlalchemy.engine.base.Engine.0x...ae90 ()
2012-05-03 18:42:04,016 INFO sqlalchemy.engine.base.Engine.0x...ae90 COMMIT
2012-05-03 18:42:04,017 INFO sqlalchemy.engine.base.Engine.0x...ae90
CREATE TABLE products (
id INTEGER NOT NULL,
PRIMARY KEY (id),
FOREIGN KEY(id) REFERENCES base_products_accessories (id)
)
2012-05-03 18:42:04,017 INFO sqlalchemy.engine.base.Engine.0x...ae90 ()
2012-05-03 18:42:04,140 INFO sqlalchemy.engine.base.Engine.0x...ae90 COMMIT
/usr/local/lib/python2.7/dist-packages/SQLAlchemy-0.6.8-py2.7.egg/sqlalchemy/sql/expression.py:2104:
SAWarning: Column 'id' on table sqlalchemy.sql.expression.Select at
0x1bb1390; Select object being replaced by another column with the same
key. Consider use_labels for select() statements.
self[column.key] = column
2012-05-03 18:42:04,145 INFO sqlalchemy.engine.base.Engine.0x...ae90 BEGIN
(implicit)
2012-05-03 18:42:04,146 INFO sqlalchemy.engine.base.Engine.0x...ae90 INSERT
INTO resources () VALUES ()
2012-05-03 18:42:04,146 INFO sqlalchemy.engine.base.Engine.0x...ae90 ()
2012-05-03 18:42:04,147 INFO sqlalchemy.engine.base.Engine.0x...ae90 INSERT
INTO resources () VALUES ()
2012-05-03 18:42:04,147 INFO sqlalchemy.engine.base.Engine.0x...ae90 ()
2012-05-03 18:42:04,147 INFO sqlalchemy.engine.base.Engine.0x...ae90 INSERT
INTO resources () VALUES ()
2012-05-03 18:42:04,147 INFO sqlalchemy.engine.base.Engine.0x...ae90 ()
2012-05-03 18:42:04,148 INFO sqlalchemy.engine.base.Engine.0x...ae90 INSERT
INTO base_products_accessories (polymorphic_identity, id) VALUES (%s, %s)
2012-05-03
Re: [sqlalchemy] cascade delete in relationship and session.execute(table.update())
I really appreciate your help! It works great without any problem.
I tried session.refresh(storeobject) and I was wondering why it didn't
work, now I learned the difference from your method.
Again, thank you very much, and have a great weekend.
On Tue, Jan 3, 2012 at 10:56 AM, Michael Bayer mike...@zzzcomputing.comwrote:
On Jan 2, 2012, at 4:06 PM, Wubin wrote:
class Product(PolymorphicClass): #there are different types of the
product
__tablename__ = products
id = Column(id, Integer, primary_key=True, key=id)
name = Column(name, String(50), unique=True, nullable=False)
storeId = Column(store_id, Integer, ForeignKey(store.id),
key=storeId)
store = relationship(Store,
uselist=False,
backref=backref(_products, collection_class=set,
cascade=all,
delete))
class Store(object):
__tablename__ = stores
id = Column(id, Integer, primary_key=True, key=id)
name = Column(name, String(50), unique=True, nullable=False)
I tried to use query object to update the storeId column in the
Product class, like:
session.query(Product).filter(Product.storeId==oldStoreId).update({Product.storeId:
newStoreId})
but the sqlalchemy rejected this with the Only update via a single
table query is currently supported message.
This would indicate that PolymorphicClass is mapped to a table as well.
A DELETE or UPDATE statement, in standard SQL, doesn't support more than
one table being affected at the same time (only MySQL has an extended
syntax that supports this but it's not supported by the ORM). There's
also a syntax that supports only one table being updated, but multiple
tables in the FROM clause which on Postgresql is UPDATE..FROM, and
SQLAlchemy now supports that too, but again the ORM doesn't yet have
support for that to be integrated.
So then I decided to use
session.execute(Product.__table__.values().where()) to update the
table and it works fine.
OK
But in the final step deleting old store, I
tried to delete the store object(now the store has no product after
the update), and the store object is deleted...but with the products
that previously belong to this store.
I guess the cascade delete in the relationship does not notice if I
use session.execute() to update the table. So my question is...(1) Is
there anyway to tell the relationship hey now those products no
longer belong to you, and you shouldn't delete them when you are to
deleted?
yeah just expire the collection:
session.expire(storeobject, ['name_of_products_collection'])
(2) Is there any trick, even the polymorphic class can use
the query object to update table, without getting Only update via a
single table query error? I still prefer to use session.query()
instead of session.execute()...
Right now you can only pass in the base class, I took a look since we do
support UPDATE..FROM for supporting DBs, the controversial part here is
that an UPDATE against the child table which then refers to the base table
would need WHERE criterion to join the two together, which introduces
tricky decisionmaking. But one possibility is to just leave that up to
the user in this case.
I've added http://www.sqlalchemy.org/trac/ticket/2365 to look at this
possibility.
--
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.
--
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.
Re: [sqlalchemy] Column parameters: default=callable is working, but onupdate=callable is not.
Hi Michael,
I really appreciate your quick reply, and that you made me clear for this
issue.
Actually my goal is to log when an object was last modified, and who did
it. So I guess I need to use MapperExtension to implement this.
Thank you very much, and have a nice day.
On Thu, Dec 1, 2011 at 2:32 PM, Michael Bayer mike...@zzzcomputing.comwrote:
On Dec 1, 2011, at 1:32 AM, Wubin wrote:
Hi,
I created two classes Resource and BaseMedia, and BaseMedia is a
subclass of Resource. The table mapping is implemented as below:
class Resource(Database.Base):
__tablename__ = resources
createUserId = Column(create_user_id, Integer,
ForeignKey(users.id), nullable=True, key=createUserId,
default=currentUserId)
modifyUserId = Column(modify_user_id, Integer,
ForeignKey(users.id), nullable=True, key=modifyUserId,
default=currentUserId, onupdate=currentUserId)
class BaseMedia(Resource.Resource):
__tablename__ = base_media
id = Column(id, Integer, ForeignKey(resources.id),
primary_key=True)
__mapper_args__ = { 'extension':
BaseMediaMapperExtension.BaseMediaMapperExtension() }
name = Column(name, Unicode(50))
type = Column(type, String(50))
size = Column(size, Integer)
and then, when I try to use session.add() to insert a new BaseMedia
object, the parameter default=currentUserId in both createUserId
and modifyUserId columns is working properly. However, if I use
session.merge() to update the name column in an existing BaseMedia
object, the name field is updated correctly in the database, but the
name is on the base_media table, not resources, so no onupdate
proceeds when only columns against base_media are modified.
onupdate is mostly used for timestamp columns to log when a row was last
modified. Using it to set integer foreign key values seems pretty
questionable.
--
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.
--
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.
