Re: [sqlalchemy] Re: integrity error in FK relation
Dnia 2009-12-01, Wt o godzinie 21:58 -0800, rajasekhar911 pisze: but this happen only when i use innodb as engine. didnt give an error on myisam on mysql or sqlite. Probably because myisam is not transactional and sqlite usually doesn't care about constraints. Tomasz Jezierski Tefnet www.tefnet.pl On Dec 2, 3:12 am, Michael Bayer mike...@zzzcomputing.com wrote: On Dec 1, 2009, at 2:16 PM, rajasekhar911 wrote: i have following relation b/w 3 tables class A(DeclarativeBase) __tablename__='a' id=Column(Unicode(50))#primarykey name=Column(Unicode(50)) cc=relation(C, \ primaryjoin=id == C.b_id,\ foreign_keys=[C.b_id],\ uselist=False,cascade='all, delete, delete- orphan') class B(DeclarativeBase) __tablename__='b' id=Column(Unicode(50))#primarykey name=Column(Unicode(50)) class C(DeclarativeBase) __tablename__='c' id=Column(Integer)#primarykey name=Column(Unicode(50)) b_id=Column(Unicode(50),ForeignKey('b.id',\ onupdate=CASCADE,ondelete=CASCADE)) bb=B(name='bb') aa=A(name='aa') aa.cc=C(name='ca',b_id=bb.id) you haven't established a relation() between C and B here so SQLA has no awareness that B needs to be inserted before C. Also bb.id is None until the session is flushed. If you use a relation() from C to B, that would solve the issue. Also A.cc is strange here in that you're repurposing the foreign key C.b_id to point to A which is nonsensical to be on A, which has nothing to do with B. DBSession.add(bb) DBSession.add(aa) throws (IntegrityError) (1452, Cannot add or update a child row: a foreign key constraint fails (`test/c`, CONSTRAINT `c_ibfk_1` FOREIGN KEY (`b_id`) REFERENCES `b` (`id`) ON DELETE CASCADE ON UPDATE CASCADE) ) u INSERT INTO c (id, b_id, name) VALUES (%s, %s, %s) if i do a flush in b/w the add it works fine DBSession.add(bb) DBSession.flush() DBSession.add(aa) even bb=B(name='bb') cc=C(name='ca',b_id=bb.id)--removed the relation from B DBSession.add(bb) DBSession.add(cc) throws the same error I am working on turbogears 2.0, SQLAlchemy0.5.6 session is created with autoflush=true and autocommit=true mysql 5.0 with innodb engine shouldn't the flush takes place by default or the transaction should take care of it? -- You received this message because you are subscribed to the Google Groups sqlalchemy group. To post to this group, send email to sqlalch...@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] association_proxy question... I think
Hi all, I've got two tables: Users (id, password) and UserProperties (user_id, name, value). Is there a way to map the properties stored in UserProperties as attributes of User object? I mean, john = User('john', 'password') john.name = John Smith # creates UserProperty('john', 'name', 'John Smith') The set of possible attributes is fixed so I don't mind hard-wiring them to the User object one by one, something like that: class User(...): ... name = some_magic_function(UserProperty, 'name') address = some_magic_function(UserProperty, 'address') I've read on association_proxy but couldn't figure out how to use it in this case. Thanks, -- You received this message because you are subscribed to the Google Groups sqlalchemy group. To post to this group, send email to sqlalch...@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] many to many join condition
Hi devs, I have one problem related to join and adding conditions (sorry for misleading subject, I don't know how to describe it shortly). I have two tables: Product Tag There is many to many relation between tag and products and I need to select product where two tags are set. How to do this using SqlAlchemy? Here are table definitions: ProductTable = sql.Table( Product, meta.metadata, sql.Column(productId , sql.Integer , primary_key=True), ... (not important) ) TagTable = sql.Table( Tag, meta.metadata, sql.Column(tagId , sql.Integer , primary_key=True), ... (not important) ) TagToProductTable = sql.Table( TagToProduct, meta.metadata, sql.Column(tagId , sql.Integer , sql.ForeignKey(Tag.tagId), nullable=False), sql.Column(productId , sql.Integer , sql.ForeignKey(Product.productId), nullable=False) ) My mappers configuration: orm.mapper(Product, Product._table, properties={ tags: orm.relation(Tag, secondary=TagToProductTable), } ) orm.mapper(Tag, Tag._table, properties={} ) Currently I can filter products by ONE tag using join, the query looks like this: Session().query(Product).join(Product.tags, TagTable.c.tagId == tagId).filter(...).order_by(...) If I want to filter by firstTag AND secondTag this of course not works. Is here any easy solution for this problem? Thanks for ideas -- Best regards - Petr Kobalicek http://kobalicek.com -- You received this message because you are subscribed to the Google Groups sqlalchemy group. To post to this group, send email to sqlalch...@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] association_proxy question... I think
See examples in sqlalchemy named dictlike.py und another 2 . here there are for example http://www.google.com/codesearch/p?hl=en#lIxjJFXSjns/trunk/code/prereq/SQLAlchemy-0.4.8/examples/vertical/dictlike.pyq=VerticalPropertyDictMixinsa=Ncd=1ct=rc On Wed, Dec 2, 2009 at 10:30 AM, Sergey V. sergey.volob...@gmail.comwrote: Hi all, I've got two tables: Users (id, password) and UserProperties (user_id, name, value). Is there a way to map the properties stored in UserProperties as attributes of User object? I mean, john = User('john', 'password') john.name = John Smith # creates UserProperty('john', 'name', 'John Smith') The set of possible attributes is fixed so I don't mind hard-wiring them to the User object one by one, something like that: class User(...): ... name = some_magic_function(UserProperty, 'name') address = some_magic_function(UserProperty, 'address') I've read on association_proxy but couldn't figure out how to use it in this case. Thanks, -- You received this message because you are subscribed to the Google Groups sqlalchemy group. To post to this group, send email to sqlalch...@googlegroups.com. To unsubscribe from this group, send email to sqlalchemy+unsubscr...@googlegroups.comsqlalchemy%2bunsubscr...@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 sqlalch...@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: association_proxy question... I think
Try this http://www.google.com/codesearch/p?hl=en#lIxjJFXSjns/trunk/code/prereq/SQLAlchemy-0.4.8/examples/vertical/dictlike.pyq=VerticalPropertyDictMixinsa=Ncd=1ct=rc On Dec 2, 10:30 am, Sergey V. sergey.volob...@gmail.com wrote: Hi all, I've got two tables: Users (id, password) and UserProperties (user_id, name, value). Is there a way to map the properties stored in UserProperties as attributes of User object? I mean, john = User('john', 'password') john.name = John Smith # creates UserProperty('john', 'name', 'John Smith') The set of possible attributes is fixed so I don't mind hard-wiring them to the User object one by one, something like that: class User(...): ... name = some_magic_function(UserProperty, 'name') address = some_magic_function(UserProperty, 'address') I've read on association_proxy but couldn't figure out how to use it in this case. Thanks, -- You received this message because you are subscribed to the Google Groups sqlalchemy group. To post to this group, send email to sqlalch...@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] easy_install SQLAlchemy
Hello, Problem figured out: I opened an issue on Distribute (the setuptools fork): http://bitbucket.org/tarek/distribute/issue/99/easy_install-uses-setupcfg-if-in-working Regards, Alex 2009/12/2 Alexandre Conrad alexandre.con...@gmail.com: Ok, I have my finger on the problem. From a virtualenv at the root of my pylons project (where development.ini lives): -- a...@alex-laptop:~/hg/papyrus/papyrus$ virtualenv --no-site-package foo New python executable in foo/bin/python Installing setuptoolsdone. a...@alex-laptop:~/hg/papyrus/papyrus$ source foo/bin/activate (foo)a...@alex-laptop:~/hg/papyrus/papyrus$ easy_install SQLAlchemy Searching for SQLAlchemy Reading http://www.pylonshq.com/download/ Best match: SQLAlchemy 0.5.2 Downloading http://www.pylonshq.com/download/0.9.7/SQLAlchemy-0.5.2.tar.gz Processing SQLAlchemy-0.5.2.tar.gz Running SQLAlchemy-0.5.2/setup.py -q bdist_egg --dist-dir /tmp/easy_install-IVPutW/SQLAlchemy-0.5.2/egg-dist-tmp-JR8Ktt no previously-included directories found matching 'doc/build/output' zip_safe flag not set; analyzing archive contents... sqlalchemy.databases.mysql: module MAY be using inspect.stack Adding SQLAlchemy 0.5.2 to easy-install.pth file Installed /home/alex/hg/papyrus/papyrus/foo/lib/python2.6/site-packages/SQLAlchemy-0.5.2-py2.6.egg Processing dependencies for SQLAlchemy Finished processing dependencies for SQLAlchemy -- So, the download URL seems to be provided by pylons. Huh? Now, from a virtualenv outside my project: -- a...@alex-laptop:~$ virtualenv --no-site-package foo New python executable in foo/bin/python Installing setuptoolsdone. a...@alex-laptop:~$ source foo/bin/activate (foo)a...@alex-laptop:~$ easy_install SQLAlchemy Searching for SQLAlchemy Reading http://pypi.python.org/simple/SQLAlchemy/ Reading http://www.sqlalchemy.org Best match: SQLAlchemy 0.5.6 Downloading http://pypi.python.org/packages/source/S/SQLAlchemy/SQLAlchemy-0.5.6.tar.gz#md5=65c6d2007969bf5d70307afc65687391 Processing SQLAlchemy-0.5.6.tar.gz Running SQLAlchemy-0.5.6/setup.py -q bdist_egg --dist-dir /tmp/easy_install-nrpteJ/SQLAlchemy-0.5.6/egg-dist-tmp-NZ5I2H no previously-included directories found matching 'doc/build/output' zip_safe flag not set; analyzing archive contents... sqlalchemy.test.orm: module MAY be using inspect.getframeinfo sqlalchemy.test.orm: module MAY be using inspect.stack sqlalchemy.databases.mysql: module MAY be using inspect.stack Adding SQLAlchemy 0.5.6 to easy-install.pth file Installed /home/alex/foo/lib/python2.6/site-packages/SQLAlchemy-0.5.6-py2.6.egg Processing dependencies for SQLAlchemy Finished processing dependencies for SQLAlchemy -- That is correct. Now why is easy_install using pylons as the search path for SQLAlchemy? I guess that's an easy_install related problem. I'll ask elsewhere. Although if someone reading this knows, I'll be happy to know why. Regards, Alex 2009/12/1 Mariano Mara mariano.m...@gmail.com: Excerpts from Tefnet Developers - Tomasz Jezierski's message of Tue Dec 01 19:06:31 -0300 2009: Dnia 2009-12-01, Wt o godzinie 18:45 +0100, Alexandre Conrad pisze: Hey there, just wondering: easy_install SQLAlchemy downloads and installs version 0.5.2. Shouldn't it be 0.5.6? # easy_install SQLAlchemy Searching for SQLAlchemy Reading http://pypi.python.org/simple/SQLAlchemy/ Reading http://www.sqlalchemy.org Best match: SQLAlchemy 0.5.6 Downloading http://pypi.python.org/packages/source/S/SQLAlchemy/SQLAlchemy-0.5.6.tar.gz#md5= 65c6d2007969bf5d70307afc65687391 Processing SQLAlchemy-0.5.6.tar.gz Running SQLAlchemy-0.5.6/setup.py -q bdist_egg --dist-dir /tmp/easy_install-biunCq/SQLAlchemy-0.5.6/egg-dist-tmp-S--F9w no previously-included directories found matching 'doc/build/output' zip_safe flag not set; analyzing archive contents... sqlalchemy.databases.mysql: module MAY be using inspect.stack sqlalchemy.test.orm: module MAY be using inspect.getframeinfo sqlalchemy.test.orm: module MAY be using inspect.stack Adding SQLAlchemy 0.5.6 to easy-install.pth file Installed /usr/lib/python2.5/site-packages/SQLAlchemy-0.5.6-py2.5.egg Processing dependencies for SQLAlchemy Finished processing dependencies for SQLAlchemy It seems it really depends on something related with easy_install (I'm not sure what): I run the process for SqlAlchemy from a virtual environ using python 2.6 and it tried to install 0.5.6 I then deactivate the virtualenv and tried from the easy_install from distribution (Ubuntu 9.04, python2.6) and told me the best match was 0.5.3 BTW, you can always do easy_install SqlAlchemy==0.5.6 Mariano -- You received this message because you are subscribed to the Google Groups sqlalchemy group. To post to this group, send email to
[sqlalchemy] Oracle timestamp and postgres datetime compatibility
Hi all, I have a sa model working with postgres, here is a code fragment: class Test(Base): __tablename__ = 'test' fileld1= Column(Unicode(40), nullable=False, primary_key=True) date = Column(DateTime, nullable=False) in postgres the sqltypes.DateTime is converted in: date timestamp without time zone NOT NULL I tryed to port my model to oracle and I found the same column has been translated in: DATE DATE NOT NULL I think it should be: DATE TIMESTAMP NOT NULL to have the same type between oracle and postgres, any hints? -- You received this message because you are subscribed to the Google Groups sqlalchemy group. To post to this group, send email to sqlalch...@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] Oracle timestamp and postgres datetime compatibility
On Dec 2, 2009, at 8:48 AM, drakkan wrote: Hi all, I have a sa model working with postgres, here is a code fragment: class Test(Base): __tablename__ = 'test' fileld1= Column(Unicode(40), nullable=False, primary_key=True) date = Column(DateTime, nullable=False) in postgres the sqltypes.DateTime is converted in: date timestamp without time zone NOT NULL I tryed to port my model to oracle and I found the same column has been translated in: DATE DATE NOT NULL I think it should be: DATE TIMESTAMP NOT NULL to have the same type between oracle and postgres, Oracle's DATE type stores time information as well: http://it.toolbox.com/blogs/database-solutions/a-comparison-of-oracles-date-and-timestamp-datatypes-6681 DateTime is a generic type that indicates a date that could be historic or in the future, so uses DATE on oracle (PG only has TIMESTAMP available). TIMESTAMP is more like a system time value. To force TIMESTAMP on both platforms, use the non-generic sqlalchemy.TIMESTAMP type. -- You received this message because you are subscribed to the Google Groups sqlalchemy group. To post to this group, send email to sqlalch...@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] extending sqlalchemy for low-level connection auditing
In my SQLAlchemy app, I want to generate a dedicated, low-level log output that will capture sql queries and parameters in a custom- defined format, as well as separate elapsed times for execute and fetch, and number of rows fetched. In addition I'd like identifiers for the session / connection to appear in the log, so it is possible to process the log file and identify all the query activity associated with a given user activity or even physical db session/connection. Can anyone the best starting point to make this kind of extension? I'm fairly new to the library. I'm hoping to find a way that doesn't break encapsulation, or adding custom behavior in a lot of places. Thanks! Jim -- You received this message because you are subscribed to the Google Groups sqlalchemy group. To post to this group, send email to sqlalch...@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] Oracle timestamp and postgres datetime compatibility
On Wed, Dec 2, 2009 at 3:25 PM, Michael Bayer mike...@zzzcomputing.com wrote: from the article: Beware while the TO_CHAR function works with both datatypes, the TRUNC function will not work with a datatype of TIMESTAMP. This is a clear indication that the use of TIMESTAMP datatype should explicitly be used for date and times where a difference in time is of utmost importance, such that Oracle won't even let you compare like values. this suggests to me that DATE is more of a general purpose date/time type whereas TIMESTAMP is specifically when you need granularity to compare the ordering of events down to the millisecond, with some loss in simplicity . This was fixed in version 9.2.0.3.0. Using that release or later, TRUNC works just fine with TIMESTAMP. See the thread at http://forums.oracle.com/forums/thread.jspa?threadID=372457 I should stress that I don't think using DATE is a problem so long as there's a way to get TIMESTAMP instead. Especially if the goal is to support Oracle 8i, where TIMESTAMP doesn't even exist. Ian -- You received this message because you are subscribed to the Google Groups sqlalchemy group. To post to this group, send email to sqlalch...@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] extending sqlalchemy for low-level connection auditing
On Dec 2, 2009, at 5:32 PM, jsa wrote: In my SQLAlchemy app, I want to generate a dedicated, low-level log output that will capture sql queries and parameters in a custom- defined format, as well as separate elapsed times for execute and fetch, and number of rows fetched. In addition I'd like identifiers for the session / connection to appear in the log, so it is possible to process the log file and identify all the query activity associated with a given user activity or even physical db session/connection. Can anyone the best starting point to make this kind of extension? I'm fairly new to the library. I'm hoping to find a way that doesn't break encapsulation, or adding custom behavior in a lot of places. for most of what you want, you want to use ConnectionProxy: http://www.sqlalchemy.org/docs/05/reference/sqlalchemy/interfaces.html#sqlalchemy.interfaces.ConnectionProxy as far as number of rows fetched, that number may or may not be available depending on the DBAPI in use. there may not be any number available until the ResultProxy is fully consumed.if cursor.rowcount doesn't have it (rowcount is normally reserved for rows matched during an UPDATE or DELETE, though some DBAPIs put the number of rows SELECTed in it), you might have to wrap the cursor in some kind of proxy that will count rows as they are fetched, which is a slightly ambitious and performance-hindering project, but you'd also do that within ConnectionProxy. see the example at http://techspot.zzzeek.org/?p=31 too. Thanks! Jim -- You received this message because you are subscribed to the Google Groups sqlalchemy group. To post to this group, send email to sqlalch...@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 sqlalch...@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] Oracle timestamp and postgres datetime compatibility
On Dec 2, 2009, at 5:49 PM, Ian Kelly wrote: On Wed, Dec 2, 2009 at 3:25 PM, Michael Bayer mike...@zzzcomputing.com wrote: from the article: Beware while the TO_CHAR function works with both datatypes, the TRUNC function will not work with a datatype of TIMESTAMP. This is a clear indication that the use of TIMESTAMP datatype should explicitly be used for date and times where a difference in time is of utmost importance, such that Oracle won't even let you compare like values. this suggests to me that DATE is more of a general purpose date/time type whereas TIMESTAMP is specifically when you need granularity to compare the ordering of events down to the millisecond, with some loss in simplicity . This was fixed in version 9.2.0.3.0. Using that release or later, TRUNC works just fine with TIMESTAMP. See the thread at http://forums.oracle.com/forums/thread.jspa?threadID=372457 I should stress that I don't think using DATE is a problem so long as there's a way to get TIMESTAMP instead. Especially if the goal is to support Oracle 8i, where TIMESTAMP doesn't even exist. TIMESTAMP is of course available, though I think in the interests of oracle tradition we should stick to DATE as the default for DateTime.TIMESTAMP suggests a specific use case to me, that of comparing the exact time of event occurence. -- You received this message because you are subscribed to the Google Groups sqlalchemy group. To post to this group, send email to sqlalch...@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] ConcurrentModificationError
I'm getting this: ConcurrentModificationError: updated rowcount 0 does not match number of objects updated 1 when I try to commit a simple deletion. I'm using Sqlite locally but the error also occurs on a Postgres database in a live environment with only ONE user connected. I saw this post from Nov. 11th: http://groups.google.com/group/sqlalchemy/browse_thread/thread/e7175c7351963128but because I'm not doing any copying (just deletion), it didn't seem to provide anything useful. Both session.dirty and session.new are empty. I have a Task class mapped to an association table called TaskTag that has a task_id and tag_id. I'm literally doing a session.delete(task) followed by a session.commit() and session.close(). Here's my mapper: mapper(Task, tasks_table, properties = { 'tags' : relation(Tag, secondary=tasktags_table, lazy = False) }) I suspect this has something to do with the many-to-many relationship, but for the life of me I cannot figure out what's going on. Thanks in advance. -Dave P.S. I use SqlAlchemy so often, I love the framework. Thanks to everyone for your hard work, it's greatly appreciated :-) -- You received this message because you are subscribed to the Google Groups sqlalchemy group. To post to this group, send email to sqlalch...@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] ConcurrentModificationError
On Dec 2, 2009, at 5:46 PM, Dave Paola wrote: I'm getting this: ConcurrentModificationError: updated rowcount 0 does not match number of objects updated 1 when I try to commit a simple deletion. I'm using Sqlite locally but the error also occurs on a Postgres database in a live environment with only ONE user connected. I saw this post from Nov. 11th: http://groups.google.com/group/sqlalchemy/browse_thread/thread/e7175c7351963128 but because I'm not doing any copying (just deletion), it didn't seem to provide anything useful. Both session.dirty and session.new are empty. I have a Task class mapped to an association table called TaskTag that has a task_id and tag_id. I'm literally doing a session.delete(task) followed by a session.commit() and session.close(). Here's my mapper: mapper(Task, tasks_table, properties = { 'tags' : relation(Tag, secondary=tasktags_table, lazy = False) }) I suspect this has something to do with the many-to-many relationship, but for the life of me I cannot figure out what's going on. Thanks in advance. this can happen if you have tasktags_table explicitly mapped elsewhere.the mapper for tasktags_table will issue a DELETE, and then if Task.tags is also modified in some way that affects the same row, the DELETE issued corresponding to the relation() will not find its row.In that case the Concurrent name is referring to two different configurations within a single flush conflicting with each other. If this is your issue, strategies to address include placing viewonly=True on the relation() or using the association proxy pattern (you can even use both if you want to pick and choose how the SQL to load records is emitted). P.S. I use SqlAlchemy so often, I love the framework. Thanks to everyone for your hard work, it's greatly appreciated :-) The compliments are appreciated ! -- You received this message because you are subscribed to the Google Groups sqlalchemy group. To post to this group, send email to sqlalch...@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 sqlalch...@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] ConcurrentModificationError
Indeed, I do have TaskTags mapped to it's own class. However, I never explicitly delete any TaskTag object, only create them. In any case, what would the preferred way to add a new tag to a task (a new entry in the association table)? I was using the ORM to just create a new instance of TaskTag (the mapped class). If having the association table mapped to its own class becomes problematic, what's the convention for accomplishing this? Thanks for your feedback :-) -Dave On Wed, Dec 2, 2009 at 4:58 PM, Michael Bayer mike...@zzzcomputing.comwrote: On Dec 2, 2009, at 5:46 PM, Dave Paola wrote: I'm getting this: ConcurrentModificationError: updated rowcount 0 does not match number of objects updated 1 when I try to commit a simple deletion. I'm using Sqlite locally but the error also occurs on a Postgres database in a live environment with only ONE user connected. I saw this post from Nov. 11th: http://groups.google.com/group/sqlalchemy/browse_thread/thread/e7175c7351963128but because I'm not doing any copying (just deletion), it didn't seem to provide anything useful. Both session.dirty and session.new are empty. I have a Task class mapped to an association table called TaskTag that has a task_id and tag_id. I'm literally doing a session.delete(task) followed by a session.commit() and session.close(). Here's my mapper: mapper(Task, tasks_table, properties = { 'tags' : relation(Tag, secondary=tasktags_table, lazy = False) }) I suspect this has something to do with the many-to-many relationship, but for the life of me I cannot figure out what's going on. Thanks in advance. this can happen if you have tasktags_table explicitly mapped elsewhere. the mapper for tasktags_table will issue a DELETE, and then if Task.tags is also modified in some way that affects the same row, the DELETE issued corresponding to the relation() will not find its row.In that case the Concurrent name is referring to two different configurations within a single flush conflicting with each other. If this is your issue, strategies to address include placing viewonly=True on the relation() or using the association proxy pattern (you can even use both if you want to pick and choose how the SQL to load records is emitted). P.S. I use SqlAlchemy so often, I love the framework. Thanks to everyone for your hard work, it's greatly appreciated :-) The compliments are appreciated ! -- You received this message because you are subscribed to the Google Groups sqlalchemy group. To post to this group, send email to sqlalch...@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 sqlalch...@googlegroups.com. To unsubscribe from this group, send email to sqlalchemy+unsubscr...@googlegroups.comsqlalchemy%2bunsubscr...@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 sqlalch...@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] ConcurrentModificationError
Dave Paola wrote: Indeed, I do have TaskTags mapped to it's own class. However, I never explicitly delete any TaskTag object, only create them. In any case, what would the preferred way to add a new tag to a task (a new entry in the association table)? I was using the ORM to just create a new instance of TaskTag (the mapped class). If having the association table mapped to its own class becomes problematic, what's the convention for accomplishing this? Thanks for your feedback :-) (I'm about 80% sure this paragraph is correct, so take it with a grain of salt). I'm guessing you also have a relation from Task to TaskTags (e.g. Task.task_tags). By default, many-to-many relations will cascade the delete to the the secondary table (so the Task.tags relation cascades DELETEs to tasktags_table), and one-to-many relations will set foreign key columns to NULL on the related table (so the Task.task_tags relation cascades UPDATEs to tasktags_table). Add this all up and SQLAlchemy will try to UPDATE a deleted row or DELETE an updated row, depending on which cascade happens first. A similar situation occurs if the Task.task_tags relation has cascade=delete set (SQLAlchemy would try to DELETE a deleted row). Usually a table like tasktags_table has no columns except for foreign keys to the related tables (e.g. task_id and tag_id columns). If this is the case for your tasktags_table table, you probably don't want to map it at all: just use it as a secondary table in the relation. Otherwise, I would recommend the association_proxy method over the viewonly=True method, because the viewonly=True method leaves a lot of room for things to get out of sync until you commit or expire the session. To use the association_proxy, try this: from sqlalchemy.ext.associationproxy import association_proxy mapper(Task, tasks_table, properties = { # Assumes you have a TaskTag.tag relation. 'tags' : association_proxy( 'task_tags', 'tag', creator=lambda tag: TaskTag(tag=tag)), 'task_tags': relation(TaskTag, lazy=False) }) You only need the creator argument if you want to create TaskTag objects implicitly, e.g. my_task.tags.append(my_tag). I'm also guessing your TaskTag constructor accepts a tag keyword parameter. Hope it helps, -Conor On Wed, Dec 2, 2009 at 4:58 PM, Michael Bayer mike...@zzzcomputing.com mailto:mike...@zzzcomputing.com wrote: On Dec 2, 2009, at 5:46 PM, Dave Paola wrote: I'm getting this: ConcurrentModificationError: updated rowcount 0 does not match number of objects updated 1 when I try to commit a simple deletion. I'm using Sqlite locally but the error also occurs on a Postgres database in a live environment with only ONE user connected. I saw this post from Nov. 11th: http://groups.google.com/group/sqlalchemy/browse_thread/thread/e7175c7351963128 but because I'm not doing any copying (just deletion), it didn't seem to provide anything useful. Both session.dirty and session.new are empty. I have a Task class mapped to an association table called TaskTag that has a task_id and tag_id. I'm literally doing a session.delete(task) followed by a session.commit() and session.close(). Here's my mapper: mapper(Task, tasks_table, properties = { 'tags' : relation(Tag, secondary=tasktags_table, lazy = False) }) I suspect this has something to do with the many-to-many relationship, but for the life of me I cannot figure out what's going on. Thanks in advance. this can happen if you have tasktags_table explicitly mapped elsewhere.the mapper for tasktags_table will issue a DELETE, and then if Task.tags is also modified in some way that affects the same row, the DELETE issued corresponding to the relation() will not find its row.In that case the Concurrent name is referring to two different configurations within a single flush conflicting with each other. If this is your issue, strategies to address include placing viewonly=True on the relation() or using the association proxy pattern (you can even use both if you want to pick and choose how the SQL to load records is emitted). P.S. I use SqlAlchemy so often, I love the framework. Thanks to everyone for your hard work, it's greatly appreciated :-) The compliments are appreciated ! -- You received this message because you are subscribed to the Google Groups sqlalchemy group. To post to this group, send email to sqlalch...@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.