[sqlalchemy] How to config Many-to-many with condition, in Sqlalchemy
I'm use sqlalchemy 0.6.4. I have 2 classes: Question and Tag, they are many-to-many. {{{ class Question(Base): __tablename__ = questions id = Column(Integer, primary_key=True) deleted = Column(Boolean) ... tags = relationship('Tag', secondary=r_questions_tags) class Tag(Base): __tablename__ = tags id = Column(BigInteger, primary_key=True) questions = relationship('Question', secondary=r_questions_tags) }}} So, tag.questions will get all the questions belong to a tag. But now, since the Question has a deleted column, I hope to do like this: {{{ class Tag(Base): ... # get non-deleted questions questions = relationship('Question', secondary=r_questions_tags, condition='Question.deleted==False') # get deleted questions deleted_questions = relationship('Question', secondary=r_questions_tags, condition='Question.deleted==True') }}} But unfortunately, there is no such condition parameter. What can I do now? -- 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] double data insert in many-to-many relations
Hi. I'm building a db with many-to-many relations, ex. * Table Users * Table Messages * Table Messages_Users when the users sends a message to other users i'm inserting that message to the db like so, *Messages - insert 1 row with the message, values ( title, text , date, etc... ) *Messages_Users - insert 2 rows ( one with connetion to the sending user and message,second with connection to the recieving user and message), values ( user_id, message_id, id_from, id_to, etc...) at the process of inserting the second row to the Messages_Users table i'm getting an error, ConcurrentModificationError: Updated rowcount 0 does not match number of objects updated 1 Mabey there's a better way to do this? Anyone have a clue? -- 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] How to select questions which have no answers
I've two classes: Question and Answer. A question may have 0 or many answers. class Question(Base): __tablename__ = questions answers = relationship('Answer', backref='question', primaryjoin=Question.id==Answer.question_id) class Answer(Base): __tablename__ = answers Now I want to find all the questions have no answers, how to do it? I tried: Session.query(Question).filter('count(Question.answers)==0').all() It is incorrect. What is the right one? -- 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] double data insert in many-to-many relations
On Wed, Sep 1, 2010 at 5:14 AM, Dobrysmak lukasz.szyman...@gmail.comwrote: [...] ConcurrentModificationError: Updated rowcount 0 does not match number of objects updated 1 Mabey there's a better way to do this? Anyone have a clue? Hi :) you should have to many-to-many relationships and two association tables: messages_users_receiving and messages_users_sending. So you configure two many to many relationships with different secondary tables. Cheers, Francisco Souza Software developer at Giran and also full time Open source evangelist at full time English: http://www.franciscosouza.net Portuguese: http://www.franciscosouza.com.br Twitter: @franciscosouza +55 27 3026 0264 On Wed, Sep 1, 2010 at 5:14 AM, Dobrysmak lukasz.szyman...@gmail.comwrote: Hi. I'm building a db with many-to-many relations, ex. * Table Users * Table Messages * Table Messages_Users when the users sends a message to other users i'm inserting that message to the db like so, *Messages - insert 1 row with the message, values ( title, text , date, etc... ) *Messages_Users - insert 2 rows ( one with connetion to the sending user and message,second with connection to the recieving user and message), values ( user_id, message_id, id_from, id_to, etc...) at the process of inserting the second row to the Messages_Users table i'm getting an error, ConcurrentModificationError: Updated rowcount 0 does not match number of objects updated 1 Mabey there's a better way to do this? Anyone have a clue? -- 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: double data insert in many-to-many relations
Hi. Okay, thanks ;-) This solution seems pretty good, but can i associate two records from MESSAGES_USERS_RECIEVING and MESSAGES_USERS_SENDING to one (the same) message record in messages table? If yes, then how? On 1 Wrz, 13:04, Francisco Souza franci...@franciscosouza.net wrote: On Wed, Sep 1, 2010 at 5:14 AM, Dobrysmak lukasz.szyman...@gmail.comwrote: [...] ConcurrentModificationError: Updated rowcount 0 does not match number of objects updated 1 Mabey there's a better way to do this? Anyone have a clue? Hi :) you should have to many-to-many relationships and two association tables: messages_users_receiving and messages_users_sending. So you configure two many to many relationships with different secondary tables. Cheers, Francisco Souza Software developer at Giran and also full time Open source evangelist at full time English:http://www.franciscosouza.net Portuguese:http://www.franciscosouza.com.br Twitter: @franciscosouza +55 27 3026 0264 On Wed, Sep 1, 2010 at 5:14 AM, Dobrysmak lukasz.szyman...@gmail.comwrote: Hi. I'm building a db with many-to-many relations, ex. * Table Users * Table Messages * Table Messages_Users when the users sends a message to other users i'm inserting that message to the db like so, *Messages - insert 1 row with the message, values ( title, text , date, etc... ) *Messages_Users - insert 2 rows ( one with connetion to the sending user and message,second with connection to the recieving user and message), values ( user_id, message_id, id_from, id_to, etc...) at the process of inserting the second row to the Messages_Users table i'm getting an error, ConcurrentModificationError: Updated rowcount 0 does not match number of objects updated 1 Mabey there's a better way to do this? Anyone have a clue? -- 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...@googlegrou ps.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] Loading attributes for Transient objects
I'm looking for a way to load mapped attributes for a *Transient* object. (I've actually needed exactly this several times... it is a recurring need.) Say I have an object and have no intention at this point of adding it to the database (so it is not Persistent or even Pending), but the local side of the foreign key attributes are populated for a specific attribute or collection. I would like to get the same SQL or Query that sqla would render if this were a Persistent object and a lazy loaded attribute. For example, say I had an Order object which has a lazily loaded Customer relationship. for a persistent ord object, I say: ord.customer and sqlalchemy issues SELECT customers.customerid ... FROM customers WHERE customers.customerid = :param_1 {'param_1': '7'} If ord were Transient, but ord.customerid were set to 7, then I would like to get the Query object that returns the above SQL, despite this being a Transient object, so I can manually populate this attribute. Thanks very much in advance. Kent -- 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] How to convert the column names to lower case.....
Hi , In sqlalchemy i am facing issue in getting the column names of the tbl in lower case when i use auto_load = true.To get the column names in the lower case, we made a temporary fix shown below. class LogRepo(Repository): def get_schema(self, table_name): tbl = Table(table_name, metadata, autoload=True, autoload_with=self.session.bind) class LogTable(Entity): pass mapper(LogTable, tbl) columns = {} for column in LogTable.__dict__: if not column.startswith('_'): columns[column.lower()] = LogTable.__dict__[column] for col in columns: setattr(LogTable, col, columns[col]) return LogTable Is there any other short way to fix the issue? Please guide me on this. -- 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] Formula for determining Transient, Pending, Persistent, Detached
Please correct me if I'm mistaken and let me know if there is a better way: if attributes.instance_state(instance).has_identity: instance is Persistent or Detached if attributes.instance_state(instance).session_id: instance is Pending or Persistent Thanks, Kent -- 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] Re: How to add a record to one-to-many relation using a single query?
You can call query.subquery().as_scalar() for now.My problem with as_scalar() is that I hate the name, but scalar() is already taken. On Sep 1, 2010, at 1:32 AM, Russell Warren wrote: Another question on this... Is there any particular reason that orm.query objects don't have as_scalar methods? While trying to hack around and figure out how to make this work, one thing I definitely tried was substituting one-item-result queries in as scalar replacement values in a similar fashion to the select you showed. It did not work because Query objects are not accepted as binding parameters even if they only have one column. If there were an equivalent as_scalar call on a query as what exists for the Select object, it seems like this type of scalar variable substitution could be cleanly done and remain in the ORM frame of mind. To demonstrate the obvious equivalence, below is a small comparison... s = select([User.id]).where(User.name == 'jack') q = sess.query(User.id).filter(User.name == jack) print s SELECT users.id FROM users WHERE users.name = :name_1 print q SELECT users.id AS users_id FROM users WHERE users.name = :name_1 s.as_scalar() sqlalchemy.sql.expression._ScalarSelect at 0x1a40a90; Select object q.as_scalar() Traceback (most recent call last): File string, line 1, in fragment AttributeError: 'Query' object has no attribute 'as_scalar' If queries had as_scalar(), the equivalent way to add an address with one query would just be... uid = sess.query(User.id).filter(User.name == jack).as_scalar() sess.add(Address(user_id = uid, email_address = 'blah')) which is 100% readable and logical to me, at least from a usage perspective. You would just need to know about the as_scalar method. Having said that, the more I look at the combo ORM+select way, that is also quite logical and readable. I just need to burn into my mind that the base sql toolkit and orm work really nicely together and that I don't necessarily need to stick to one side of the fence. -- 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] How to config Many-to-many with condition, in Sqlalchemy
On Sep 1, 2010, at 3:44 AM, Freewind wrote: I'm use sqlalchemy 0.6.4. I have 2 classes: Question and Tag, they are many-to-many. {{{ class Question(Base): __tablename__ = questions id = Column(Integer, primary_key=True) deleted = Column(Boolean) ... tags = relationship('Tag', secondary=r_questions_tags) class Tag(Base): __tablename__ = tags id = Column(BigInteger, primary_key=True) questions = relationship('Question', secondary=r_questions_tags) }}} So, tag.questions will get all the questions belong to a tag. But now, since the Question has a deleted column, I hope to do like this: {{{ class Tag(Base): ... # get non-deleted questions questions = relationship('Question', secondary=r_questions_tags, condition='Question.deleted==False') # get deleted questions deleted_questions = relationship('Question', secondary=r_questions_tags, condition='Question.deleted==True') }}} But unfortunately, there is no such condition parameter. What can I do now? you would like to use primaryjoin and secondaryjoin here. -- 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.
[sqlalchemy] Re: Formula for determining Transient, Pending, Persistent, Detached
I suppose attributes.instance_state(instance).session_id is not None is more correct than attributes.instance_state(instance).session_id On Sep 1, 11:30 am, Kent k...@retailarchitects.com wrote: Please correct me if I'm mistaken and let me know if there is a better way: if attributes.instance_state(instance).has_identity: instance is Persistent or Detached if attributes.instance_state(instance).session_id: instance is Pending or Persistent Thanks, Kent -- 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] How to select questions which have no answers
On Sep 1, 2010, at 5:15 AM, Freewind wrote: I've two classes: Question and Answer. A question may have 0 or many answers. class Question(Base): __tablename__ = questions answers = relationship('Answer', backref='question', primaryjoin=Question.id==Answer.question_id) class Answer(Base): __tablename__ = answers Now I want to find all the questions have no answers, how to do it? I tried: Session.query(Question).filter('count(Question.answers)==0').all() It is incorrect. What is the right one? In SQL, finding all the X with no related Y is always accomplished using a NOT EXISTS predicate. SQLAlchemy provides an exists() construct in the general case, and with a relationship() you can also use the any() operator which is a more concise way to express it. See the tutorial at http://www.sqlalchemy.org/docs/ormtutorial.html#using-exists (in this case you'd want to say ~Question.answers.any()). -- 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] Loading attributes for Transient objects
On Sep 1, 2010, at 10:50 AM, Kent wrote: I'm looking for a way to load mapped attributes for a *Transient* object. (I've actually needed exactly this several times... it is a recurring need.) Say I have an object and have no intention at this point of adding it to the database (so it is not Persistent or even Pending), but the local side of the foreign key attributes are populated for a specific attribute or collection. I would like to get the same SQL or Query that sqla would render if this were a Persistent object and a lazy loaded attribute. For example, say I had an Order object which has a lazily loaded Customer relationship. for a persistent ord object, I say: ord.customer and sqlalchemy issues SELECT customers.customerid ... FROM customers WHERE customers.customerid = :param_1 {'param_1': '7'} If ord were Transient, but ord.customerid were set to 7, then I would like to get the Query object that returns the above SQL, despite this being a Transient object, so I can manually populate this attribute. Thanks very much in advance. Here's the problem with that.You have some object with no association to any session, and therefore no transaction. Yet you want x.y to emit a query to the database. What context should this query be executed under ?If you're looking for an ad-hoc connection from the connection pool, you can certainly do that but SQLA could never make that decision for you (well if you used SQLA 0.2 it would, but that was a different time). So here perhaps there is actually a Session at play. Why don't you want to add this object to the Session so that its pending and can do what it needs ? A common reason is because emiting a Query means autoflush is going to kick in and try to INSERT the object before its ready. For that we suggest either getting a hold of the related things ahead of time, or disabling autoflush. This is easy to do and we have recipes to plug it into with here: http://www.sqlalchemy.org/trac/wiki/UsageRecipes/DisableAutoflush But going back to your initial request, if you have customerid 7, just session.query(Customer).get(7). That's the simplest way here. When you're transient, you're looking to populate ord.customer with something, not as much have it magically figure itself out with some pre-flush kind of behavior. -- 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] How to convert the column names to lower case.....
On Sep 1, 2010, at 9:06 AM, girish wrote: Hi , In sqlalchemy i am facing issue in getting the column names of the tbl in lower case when i use auto_load = true.To get the column names in the lower case, we made a temporary fix shown below. class LogRepo(Repository): def get_schema(self, table_name): tbl = Table(table_name, metadata, autoload=True, autoload_with=self.session.bind) class LogTable(Entity): pass mapper(LogTable, tbl) columns = {} for column in LogTable.__dict__: if not column.startswith('_'): columns[column.lower()] = LogTable.__dict__[column] for col in columns: setattr(LogTable, col, columns[col]) return LogTable Is there any other short way to fix the issue? Please guide me on this. That's amazing that the above approach would work. There's plenty of places in the ORM where it assumes the name of a mapped attribute matches the attribute name on the column. Here you'd want to wrap mapper() inside a function that does what you see here: http://www.sqlalchemy.org/docs/mappers.html#attribute-names-for-mapped-columns that is, def mapper(cls, table, **kw): properties = kw.setdefault('properties', {}) properties.update( [(col.name.lower(), col) for col in table.c ) return orm.mapper(cls, table, **kw) -- 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] Formula for determining Transient, Pending, Persistent, Detached
On Sep 1, 2010, at 11:30 AM, Kent wrote: Please correct me if I'm mistaken and let me know if there is a better way: if attributes.instance_state(instance).has_identity: instance is Persistent or Detached if attributes.instance_state(instance).session_id: instance is Pending or Persistent from sqlalchemy.orm import object_session from sqlalchemy.orm.util import has_identity transient: object_session(obj) is None and not has_identity(obj) detached: object_session(obj) is None and has_identity(obj) pending, persistent etc. You've basically figured out the less public ways of doing the same thing. -- 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] Re: double data insert in many-to-many relations
On Wed, Sep 1, 2010 at 8:59 AM, Dobrysmak lukasz.szyman...@gmail.comwrote: Hi. Okay, thanks ;-) This solution seems pretty good, but can i associate two records from MESSAGES_USERS_RECIEVING and MESSAGES_USERS_SENDING to one (the same) message record in messages table? If yes, then how? Yes, you can! ;) Looking on design, you can have the message Hello sent from John (a user) to Mary (other user), then you will have the following rows on database: *Table messages:* ID TEXT 1 Hello *Table users:* ID NAME 1 John 2 Mary *Table messages_users_receiving:* ID MESSAGE_IDUSER_ID 1 1 2 (Mary) *Table messages_users_sending *ID MESSAGE_IDUSER_ID 1 1 (the same) 1 (John) But here you can check your model: a message can be sent to one or more users, but can a message be sent from more than one user? If a message is sent only by a user, you should have a many to one relationship between message and user (the sender), and a many to many relationshop between the same tables (the receivers, that can be one or more users). Best regards, Francisco Souza Software developer at Giran and also full time Open source evangelist at full time English: http://www.franciscosouza.net Portuguese: http://www.franciscosouza.com.br Twitter: @franciscosouza +55 27 3026 0264 On Wed, Sep 1, 2010 at 8:59 AM, Dobrysmak lukasz.szyman...@gmail.comwrote: Hi. Okay, thanks ;-) This solution seems pretty good, but can i associate two records from MESSAGES_USERS_RECIEVING and MESSAGES_USERS_SENDING to one (the same) message record in messages table? If yes, then how? On 1 Wrz, 13:04, Francisco Souza franci...@franciscosouza.net wrote: On Wed, Sep 1, 2010 at 5:14 AM, Dobrysmak lukasz.szyman...@gmail.com wrote: [...] ConcurrentModificationError: Updated rowcount 0 does not match number of objects updated 1 Mabey there's a better way to do this? Anyone have a clue? Hi :) you should have to many-to-many relationships and two association tables: messages_users_receiving and messages_users_sending. So you configure two many to many relationships with different secondary tables. Cheers, Francisco Souza Software developer at Giran and also full time Open source evangelist at full time English:http://www.franciscosouza.net Portuguese:http://www.franciscosouza.com.br Twitter: @franciscosouza +55 27 3026 0264 On Wed, Sep 1, 2010 at 5:14 AM, Dobrysmak lukasz.szyman...@gmail.com wrote: Hi. I'm building a db with many-to-many relations, ex. * Table Users * Table Messages * Table Messages_Users when the users sends a message to other users i'm inserting that message to the db like so, *Messages - insert 1 row with the message, values ( title, text , date, etc... ) *Messages_Users - insert 2 rows ( one with connetion to the sending user and message,second with connection to the recieving user and message), values ( user_id, message_id, id_from, id_to, etc...) at the process of inserting the second row to the Messages_Users table i'm getting an error, ConcurrentModificationError: Updated rowcount 0 does not match number of objects updated 1 Mabey there's a better way to do this? Anyone have a clue? -- 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 sqlalchemy%2bunsubscr...@googlegrou ps.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.