[sqlalchemy] Problems filtering on primary_join with ORM relationship
Hi, We have a relationship that looks something like this: User 1 - * Posts Posts can have various states, let's say 'DRAFT', 'LIVE', 'DELETED'. Sometimes, we want to get all of the posts in existence for a user by doing: user.all_posts Sometimes, we just want to get posts that are not deleted: user.open_posts We opted to do this by specifying the primary_join on the relationship, thus: open_posts = relationship(Post, = and_(User.id==Post.user_id,ยท or_(Post.status=='DRAFT', Post.status=='LIVE'))) all_posts = relationship(Post) (as suggested here: http://stackoverflow.com/questions/2863786/how-do-i-specify-a-relation-in-sqlalchemy-where-one-condition-requires-a-column ) This worked, but we now have a problem in some of our processing - when we use user.open_posts before changes have been sent to the database, all posts including those deleted are returned. Putting in session.refresh at various points now seems to do the trick, but I wanted to ask: 1. Is our approach generally correct as a way of doing filtered relationships/joins 2. Are session.refresh calls the right way to make it work while changes are still in memory Thanks for any ideas, Ben -- 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] Making an association proxy distinct
Is there an obvious way to set up an association proxy so that it only returns distinct objects? For example, if I have: Post - Tag - Tag_Category and would like to access Post.Tag_Categories so that it contains each category only once. Any tips appreciated, sure I am missing something obvious here. Ben -- 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] Making an association proxy distinct
Thanks, works perfectly. On 1 May 2012 15:16, Michael Bayer mike...@zzzcomputing.com wrote: Since the uniqueness makes this kind of a read only thing, I'd use a plain @property: @property def unique_tags(self): return set(cat.tag for cat in self.tag_categories) On May 1, 2012, at 10:08 AM, Benjamin Sims wrote: Is there an obvious way to set up an association proxy so that it only returns distinct objects? For example, if I have: Post - Tag - Tag_Category and would like to access Post.Tag_Categories so that it contains each category only once. Any tips appreciated, sure I am missing something obvious here. Ben -- 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. -- 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] Debugging 'StatementError: Can't reconnect until invalid transaction is rolled back' error
Thanks for this. As an update, I put in code that notifies me when an error occurs. The real cause of this seems to still be the 'MySQL has gone away' error. This occurs in various places, always as the first DB call in a request (obviously). Is my understanding correct that in order to solve this I have to make sure that all DB calls do a session.close() or session.rollback() when finished? Any thoughts on how to track which call is leaving the connection open? On 31 October 2011 17:16, Michael Bayer mike...@zzzcomputing.com wrote: On Oct 31, 2011, at 12:56 PM, Benjamin Sims wrote: I suspect that my understanding of both threading and Sessions is going to be found pretty wanting here; I've basically just lifted things from Pyramid/SQLAlchemy examples. My understanding is that the framework handles threading, and that based on examples and http://www.sqlalchemy.org/docs/orm/session.html#contextual-thread-local-sessionsthis is the correct way to create a session available throughout the request: DBSession = scoped_session(sessionmaker(extension=ZopeTransactionExtension())) (this is in my models file) I then use this either by doing: DBSession.query() or from models import DBSession session = DBSession() session.query() So, I'm not doing anything explicit in turns of ending the session - I guess I should be, but I had understood that this would happen on issue of next query/completion of request. that's a question for the Pyramid folks. There should also be a single path for handling of exceptions - any unexpected exception would propagate to the same point, there's a Session.rollback() right there, and it gets reported. -- 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.
[sqlalchemy] Debugging 'StatementError: Can't reconnect until invalid transaction is rolled back' error
Hi all, I'm seeing this error intermittently in my application since switching to MySQL - it occurs on several different pages and I can't track down why: StatementError: Can't reconnect until invalid transaction is rolled back (original cause: InvalidRequestError: Can't reconnect until invalid transaction is rolled back) 'SELECT [... the same select every time, which gets a user from the database] I have searched on the error and read the various discussions, for example this one: http://groups.google.com/group/sqlalchemy/browse_thread/thread/a8031eefc4d5d0cd My understanding is that somewhere in my code I need to be ensuring that I do rollback/commit/close when the operation is complete. The problem is... where? Is there a way I can get a log to see the original query/point in my code which triggers the error. If it is relevant, I'm using Pyramid and have recently also suffered from 'MySQL has gone away errors'. Thanks, Ben [also described this problem on the Pyramid list, apologies to those of you that subscribe to both] -- 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] Debugging 'StatementError: Can't reconnect until invalid transaction is rolled back' error
I suspect that my understanding of both threading and Sessions is going to be found pretty wanting here; I've basically just lifted things from Pyramid/SQLAlchemy examples. My understanding is that the framework handles threading, and that based on examples and http://www.sqlalchemy.org/docs/orm/session.html#contextual-thread-local-sessionsthis is the correct way to create a session available throughout the request: DBSession = scoped_session(sessionmaker(extension=ZopeTransactionExtension())) (this is in my models file) I then use this either by doing: DBSession.query() or from models import DBSession session = DBSession() session.query() So, I'm not doing anything explicit in turns of ending the session - I guess I should be, but I had understood that this would happen on issue of next query/completion of request. Thanks, I'll look into it - I was used to Paster, where I could see the logs in real time. I now get errors in the Apache error log... but not whatever is initially causing the one I can see. Ben PS: I do have a separate process that runs in a script via cron and uses the database which would seem like a candidate, but disabling that does not stop the errors On 31 October 2011 14:38, Michael Bayer mike...@zzzcomputing.com wrote: On Oct 31, 2011, at 8:45 AM, Benjamin Sims wrote: Hi all, I'm seeing this error intermittently in my application since switching to MySQL - it occurs on several different pages and I can't track down why: StatementError: Can't reconnect until invalid transaction is rolled back (original cause: InvalidRequestError: Can't reconnect until invalid transaction is rolled back) 'SELECT [... the same select every time, which gets a user from the database] how are you handling threading, as well as Session lifecycle ? this kind of error usually occurs from one of two things: a. sharing Sessions, or objects attached to them, between threads or b. no clear point of Session start/end, incorrect handling of exceptions, etc. I have searched on the error and read the various discussions, for example this one: http://groups.google.com/group/sqlalchemy/browse_thread/thread/a8031eefc4d5d0cd My understanding is that somewhere in my code I need to be ensuring that I do rollback/commit/close when the operation is complete. The problem is... where? Is there a way I can get a log to see the original query/point in my code which triggers the error. sure you should turn on SQLAlchemy logging fully ( http://www.sqlalchemy.org/docs/core/engines.html?highlight=logging#configuring-logging) , and also make sure you aren't squashing exceptions (i.e. except MyException, e: pass type of thing) - also you'd want to add code such as log.error(exception occurred, exc_info=True) to the point at which exceptions are caught so that a full stack trace is written out to the log. -- 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.
[sqlalchemy] UnicodeEncodeError on saving to MySQL
I've recently moved from SQLite to MySQL as the underlying database for my application. The move is causing me various problems, principally around Unicode. I *think* they were not occurring when SQLite was there, so I thought I would ask if anybody on the list has had similar problems. My current difficultly is that when saving content (Chinese, generally) to the database, I get the dreaded: *** UnicodeEncodeError: 'ascii' codec can't encode characters in position 0-4: ordinal not in range(128) error and a rollback. I have checked that: - the MySQL table has a suitable encoding (CHARACTER SET utf8 COLLATE utf8_general_ci;) - the MySQL connection string has the charset on the end (?charset=utf8) - The type for the relevant Column is Unicode (this worked previously with just string, but I have changed it anyway just in case) - The content is unicode at the point it gets sent to the the database (again, this was a plain string previously but seemed to work) However, on save I get the above error from SQLAlchemy. What do I need to be doing to get the content in the right format to send through? Thanks, Ben -- 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] UnicodeEncodeError on saving to MySQL
File scraper.py, line 77, in run session.commit() File /home/benjamin/test/test1/lib/python2.6/site-packages/SQLAlchemy-0.7.2-py2.6-linux-x86_64.egg/sqlalchemy/orm/session.py, line 617, in commit self.transaction.commit() File /home/benjamin/test/test1/lib/python2.6/site-packages/SQLAlchemy-0.7.2-py2.6-linux-x86_64.egg/sqlalchemy/orm/session.py, line 293, in commit self._prepare_impl() File /home/benjamin/test/test1/lib/python2.6/site-packages/SQLAlchemy-0.7.2-py2.6-linux-x86_64.egg/sqlalchemy/orm/session.py, line 277, in _prepare_impl self.session.flush() File /home/benjamin/test/test1/lib/python2.6/site-packages/SQLAlchemy-0.7.2-py2.6-linux-x86_64.egg/sqlalchemy/orm/session.py, line 1493, in flush self._flush(objects) File /home/benjamin/test/test1/lib/python2.6/site-packages/SQLAlchemy-0.7.2-py2.6-linux-x86_64.egg/sqlalchemy/orm/session.py, line 1562, in _flush flush_context.execute() File /home/benjamin/test/test1/lib/python2.6/site-packages/SQLAlchemy-0.7.2-py2.6-linux-x86_64.egg/sqlalchemy/orm/unitofwork.py, line 327, in execute rec.execute(self) File /home/benjamin/test/test1/lib/python2.6/site-packages/SQLAlchemy-0.7.2-py2.6-linux-x86_64.egg/sqlalchemy/orm/unitofwork.py, line 471, in execute uow File /home/benjamin/test/test1/lib/python2.6/site-packages/SQLAlchemy-0.7.2-py2.6-linux-x86_64.egg/sqlalchemy/orm/mapper.py, line 2107, in _save_obj execute(statement, params) File /home/benjamin/test/test1/lib/python2.6/site-packages/SQLAlchemy-0.7.2-py2.6-linux-x86_64.egg/sqlalchemy/engine/base.py, line 1399, in execute params) File /home/benjamin/test/test1/lib/python2.6/site-packages/SQLAlchemy-0.7.2-py2.6-linux-x86_64.egg/sqlalchemy/engine/base.py, line 1532, in _execute_clauseelement compiled_sql, distilled_params File /home/benjamin/test/test1/lib/python2.6/site-packages/SQLAlchemy-0.7.2-py2.6-linux-x86_64.egg/sqlalchemy/engine/base.py, line 1633, in _execute_context context) File /home/benjamin/test/test1/lib/python2.6/site-packages/SQLAlchemy-0.7.2-py2.6-linux-x86_64.egg/sqlalchemy/engine/default.py, line 325, in do_execute cursor.execute(statement, parameters) File /usr/lib64/python2.6/site-packages/MySQLdb/cursors.py, line 158, in execute query = query % db.literal(args) File /usr/lib64/python2.6/site-packages/MySQLdb/connections.py, line 265, in literal return self.escape(o, self.encoders) File /usr/lib64/python2.6/site-packages/MySQLdb/connections.py, line 198, in string_literal return db.string_literal(obj) UnicodeEncodeError: 'ascii' codec can't encode characters in position 0-3: ordinal not in range(128) On 29 September 2011 13:59, Michael Bayer mike...@zzzcomputing.com wrote: Can you attach a traceback for that? Sent from my iPhone On Sep 29, 2011, at 7:45 AM, Benjamin Sims benjamins...@gmail.com wrote: I've recently moved from SQLite to MySQL as the underlying database for my application. The move is causing me various problems, principally around Unicode. I *think* they were not occurring when SQLite was there, so I thought I would ask if anybody on the list has had similar problems. My current difficultly is that when saving content (Chinese, generally) to the database, I get the dreaded: *** UnicodeEncodeError: 'ascii' codec can't encode characters in position 0-4: ordinal not in range(128) error and a rollback. I have checked that: - the MySQL table has a suitable encoding (CHARACTER SET utf8 COLLATE utf8_general_ci;) - the MySQL connection string has the charset on the end (?charset=utf8) - The type for the relevant Column is Unicode (this worked previously with just string, but I have changed it anyway just in case) - The content is unicode at the point it gets sent to the the database (again, this was a plain string previously but seemed to work) However, on save I get the above error from SQLAlchemy. What do I need to be doing to get the content in the right format to send through? Thanks, Ben -- 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. -- 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
Re: [sqlalchemy] Re: UnicodeEncodeError on saving to MySQL
Thanks to you both. Michael, I must apologise - I had overlooked that another field was receiving Unicode characters. Changing this field to the Unicode type seems to have fixed the problem. Victor, you are right of course - I do have an understanding of what the dreaded error actually means, but I had been lazy in prototyping and used Strings. I'm not really sure why SQLite was apparently happy with this, but your mention of it made me think again and understand that I had misdirected myself as to the source of the problem. I will now go back and change all Strings to Unicode to be on the safe side. Ben On 29 September 2011 15:18, Victor Olex victor.o...@vtenterprise.comwrote: I am of the opinion that unless you are absolutely certain that the data will be pure ASCII you should declare string fields in model as Unicode type rather than String. Also have a look at http://farmdev.com/talks/unicode/ because it will help you understand what the dreaded error really means. On Sep 29, 9:06 am, Benjamin Sims benjamins...@gmail.com wrote: File scraper.py, line 77, in run session.commit() File /home/benjamin/test/test1/lib/python2.6/site-packages/SQLAlchemy-0.7.2-py2.6-linux-x86_64.egg/sqlalchemy/orm/session.py, line 617, in commit self.transaction.commit() File /home/benjamin/test/test1/lib/python2.6/site-packages/SQLAlchemy-0.7.2-py2.6-linux-x86_64.egg/sqlalchemy/orm/session.py, line 293, in commit self._prepare_impl() File /home/benjamin/test/test1/lib/python2.6/site-packages/SQLAlchemy-0.7.2-py2.6-linux-x86_64.egg/sqlalchemy/orm/session.py, line 277, in _prepare_impl self.session.flush() File /home/benjamin/test/test1/lib/python2.6/site-packages/SQLAlchemy-0.7.2-py2.6-linux-x86_64.egg/sqlalchemy/orm/session.py, line 1493, in flush self._flush(objects) File /home/benjamin/test/test1/lib/python2.6/site-packages/SQLAlchemy-0.7.2-py2.6-linux-x86_64.egg/sqlalchemy/orm/session.py, line 1562, in _flush flush_context.execute() File /home/benjamin/test/test1/lib/python2.6/site-packages/SQLAlchemy-0.7.2-py2.6-linux-x86_64.egg/sqlalchemy/orm/unitofwork.py, line 327, in execute rec.execute(self) File /home/benjamin/test/test1/lib/python2.6/site-packages/SQLAlchemy-0.7.2-py2.6-linux-x86_64.egg/sqlalchemy/orm/unitofwork.py, line 471, in execute uow File /home/benjamin/test/test1/lib/python2.6/site-packages/SQLAlchemy-0.7.2-py2.6-linux-x86_64.egg/sqlalchemy/orm/mapper.py, line 2107, in _save_obj execute(statement, params) File /home/benjamin/test/test1/lib/python2.6/site-packages/SQLAlchemy-0.7.2-py2.6-linux-x86_64.egg/sqlalchemy/engine/base.py, line 1399, in execute params) File /home/benjamin/test/test1/lib/python2.6/site-packages/SQLAlchemy-0.7.2-py2.6-linux-x86_64.egg/sqlalchemy/engine/base.py, line 1532, in _execute_clauseelement compiled_sql, distilled_params File /home/benjamin/test/test1/lib/python2.6/site-packages/SQLAlchemy-0.7.2-py2.6-linux-x86_64.egg/sqlalchemy/engine/base.py, line 1633, in _execute_context context) File /home/benjamin/test/test1/lib/python2.6/site-packages/SQLAlchemy-0.7.2-py2.6-linux-x86_64.egg/sqlalchemy/engine/default.py, line 325, in do_execute cursor.execute(statement, parameters) File /usr/lib64/python2.6/site-packages/MySQLdb/cursors.py, line 158, in execute query = query % db.literal(args) File /usr/lib64/python2.6/site-packages/MySQLdb/connections.py, line 265, in literal return self.escape(o, self.encoders) File /usr/lib64/python2.6/site-packages/MySQLdb/connections.py, line 198, in string_literal return db.string_literal(obj) UnicodeEncodeError: 'ascii' codec can't encode characters in position 0-3: ordinal not in range(128) On 29 September 2011 13:59, Michael Bayer mike...@zzzcomputing.com wrote: Can you attach a traceback for that? Sent from my iPhone On Sep 29, 2011, at 7:45 AM, Benjamin Sims benjamins...@gmail.com wrote: I've recently moved from SQLite to MySQL as the underlying database for my application. The move is causing me various problems, principally around Unicode. I *think* they were not occurring when SQLite was there, so I thought I would ask if anybody on the list has had similar problems. My current difficultly is that when saving content (Chinese, generally) to the database, I get the dreaded: *** UnicodeEncodeError: 'ascii' codec can't encode characters in position 0-4: ordinal not in range(128) error and a rollback. I have checked that: - the MySQL table has a suitable encoding (CHARACTER SET utf8 COLLATE utf8_general_ci;) - the MySQL connection string has the charset on the end (?charset=utf8) - The type for the relevant Column is Unicode (this worked previously with just string, but I have changed it anyway just in case) - The content
Re: [sqlalchemy] Bidirectional relationships with association proxies
Thanks so much - I realise now that the documentation I should have been
reading was:
http://www.sqlalchemy.org/docs/orm/relationships.html#backref-arguments
Ben
On 31 August 2011 22:50, Michael Bayer mike...@zzzcomputing.com wrote:
dependency rule tried to blank out primary key means:
1. A references B, B has a foreign key to A.
2. A is deleted.
3. each B associated with A must therefore have the foreign key of A set to
NULL (this is the default behavior if 'delete' cascade isn't configured).
4. the foreign key on B is also part of the primary key. SQLA detects this
and raises an error.
The solution, B must also be deleted when A is deleted.
In your example, you're probably trying to do this as I see cascade=all,
delete-orphan. Except this cascade must go on the side that points A to B,
in this case ObjectA.associations and ObjectB.associations (put it another
way - the side that does *not* have the foreign key on it).Since
associations is on the backref side of the declaration here, use the
backref() function and put cascade='all, delete-orphan' on that side.
On Aug 31, 2011, at 7:04 AM, Benjamin Sims wrote:
Hi,
I'm still having troubles correctly figuring my various many-to-many
relations. Originally, this was a 'standard' many-to-many; that is, a
secondary table was specified.
However, I now need to add further information to the relation and am
converting into an AssociationObject. My problem is in ensuring that the
original calls still work. So, currently I have:
class Association(Base):
__tablename__ = 'objecta_objectb'
objecta_id = Column(Integer, ForeignKey('objecta.id'),
primary_key=True)
objectb_id = Column(Integer, ForeignKey('objectb.id'),
primary_key=True)
objecta = relationship(ObjectA, backref = associations, cascade =
'all, delete-orphan')
objectb = relationship(ObjectB, backref = associations, cascade =
'all, delete-orphan')
def __init__(self, objecta = None, objectb = None):
self.objecta = objecta
self.objectb = objectb
pass
class ObjectA(Base):
objectbs = association_proxy('associations', 'objectb', creator=lambda
b:
Association(objectb=b))
class ObjectB(Base):
objectas = association_proxy('associations', 'objecta', creator=lambda
a:
Association(objecta=a))
The point of all this is so that I can create the associations by doing:
objecta_instance.objectbs.append(objectb_instance)
or
association_instance = Association(objectb = objectb_instance)
objecta_instance.associations.append(association_instance)
Sorry if the above is rather convoluted; it is based on
http://www.preetk.com/node/sqlalchemy-part-2-declarative-bi-directional-association-classes/,
which is somewhat clearer.
Anyway, using the above I get:
AssertionError: Dependency rule tried to blank-out primary key column
'objecta_objectb.objectb_id' on instance 'Association at 0x109277b90'
What am I doing wrong? I understand that it is somehow related to how the
cascade is set up, but I'm not sure how to move forward.
Thanks,
Ben
--
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.
--
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] Bidirectional relationships with association proxies
Hi,
I'm still having troubles correctly figuring my various many-to-many
relations. Originally, this was a 'standard' many-to-many; that is, a
secondary table was specified.
However, I now need to add further information to the relation and am
converting into an AssociationObject. My problem is in ensuring that the
original calls still work. So, currently I have:
class Association(Base):
__tablename__ = 'objecta_objectb'
objecta_id = Column(Integer, ForeignKey('objecta.id'), primary_key=True)
objectb_id = Column(Integer, ForeignKey('objectb.id'), primary_key=True)
objecta = relationship(ObjectA, backref = associations, cascade =
'all, delete-orphan')
objectb = relationship(ObjectB, backref = associations, cascade =
'all, delete-orphan')
def __init__(self, objecta = None, objectb = None):
self.objecta = objecta
self.objectb = objectb
pass
class ObjectA(Base):
objectbs = association_proxy('associations', 'objectb', creator=lambda
b:
Association(objectb=b))
class ObjectB(Base):
objectas = association_proxy('associations', 'objecta', creator=lambda
a:
Association(objecta=a))
The point of all this is so that I can create the associations by doing:
objecta_instance.objectbs.append(objectb_instance)
or
association_instance = Association(objectb = objectb_instance)
objecta_instance.associations.append(association_instance)
Sorry if the above is rather convoluted; it is based on
http://www.preetk.com/node/sqlalchemy-part-2-declarative-bi-directional-association-classes/,
which is somewhat clearer.
Anyway, using the above I get:
AssertionError: Dependency rule tried to blank-out primary key column
'objecta_objectb.objectb_id' on instance 'Association at 0x109277b90'
What am I doing wrong? I understand that it is somehow related to how the
cascade is set up, but I'm not sure how to move forward.
Thanks,
Ben
--
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] Cascade in many-to-many relationships: when orphans are not orphans
Thanks for the help with my query the other day - as ever response was swift and bang on. I'm now trying to set up another m:n relationship in ORM correctly. Pseudocode: parentA.children.append(child1) parentA.children.append(child2) parentB.children.append(child2) session.delete(parentA) At this stage, I would like child1 to be deleted and child2 to survive. However, if I use (cascade = all), then both children will be deleted when ParentA is. I hoped that delete-orphan would be applicable in this situation, but that requires that single-parent be True, which I understand it cannot for a true many-to-many. So I guess what I am asking is - is it possible for child objects which have still have remaining parents to survive, while deleting those with no parents left? Thanks, Ben -- 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] Correct way to delete Association Objects
I have a many-to-many relationship with attributes: Parent - AssocObject - Child I'm trying to set it up so that when an AssocObject is removed from parent, that instance of AssocObject and the Child are also deleted. So in my view I do: del parent.associatedobjects I have changed the model so that the definition of Parent includes associatedobjects = relationship(AssocObject, cascade = delete, delete-orphan) This seems to work, however when I then try to add a new AssocObject: parent.associatedobjects[0] = AssocObject() I get a warning: Object of type AssocObject not in session, add operation along 'Parent.associatedobjects' will not proceed What am I doing wrong? Thanks, Ben -- 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] Integrity error when using association_proxy - one of the foreign keys is missing
Thanks so much for the help. For the record, setting an __init__ argument on
the association argument did the trick.
Ben
On 2 Aug 2011, at 01:47, Michael Bayer mike...@zzzcomputing.com wrote:
On Aug 1, 2011, at 8:34 PM, somewhatofftheway wrote:
Hi,
I'm trying to convert a 'simple' many-to-many relationship to an
association object in order to allow the relationship to have
attributes. I've followed the code in examples/association/
proxied_association.py fairly closely (or so I thought) but it isn't
working for me.
As an example, let's say I am trying to create a relationship between
an RSS feed and the pages from the feed but I want a feed_date on the
relationship. Currently, my code look something like this:
class Page (Base):
__tablename__ = 'pages'
id = Column(Integer, primary_key=True)
posts = relationship(Post, cascade=all, delete-orphan,
backref='pages')
feeds = association_proxy(feeds, feed)
class Post(Base)
__tablename__ = 'pages_feeds'
page_id = Column(Integer, ForeignKey('pages.id'),
primary_key=True)
feed_id = Column(Integer, ForeignKey('feeds.id'),
primary_key=True)
feed = relationship(Feed, lazy='joined')
I haven't changed the Feed class at all.
When I do something along the lines of:
page = Page()
feed = Feed()
page.feeds.append(feed)
the query that is issued is as follows:
IntegrityError: (IntegrityError) pages_feeds.feed_id may not be NULL
u'INSERT INTO pages_feeds (page_id) VALUES (?)' (1,)
So, clearly I have missed out the part of the config that explains
that adds in the second foreign key. Could anybody point me to where
please?
you need a constructor and/or creator that generates Post() with the feed
attached to it, like def __init__(self, feed): self.feed = feed, then the
assoc proxy with creator=Post.
--
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.
