[sqlalchemy] insertion of an object with a user defined field value
Hi, I have the following 2 declarative objects to represent a book and its pages: class Book(Base): __tablename__ = 'books' id = Column(Integer, primary_key=True) title = Column(String(32)) class Page(Base): __tablename__ = 'pages' id = Column(Integer, primary_key=True) book_id = Column(Integer, ForeignKey('books.id'), index=True) content = Column(String(1000)) book = relation(Book, backref='pages', order_by=id) def __init__(self, page_no, content, book_id=None): self.id = page_no self.content = content if book_id is not None: self.book_id = book_id I would like to read and insert info for books in a library (i.e. by calling session.add(Book(My Book)) a vast number of times), keep their database assigned ids in a dictionary and then insert info for pages of these books (i.e. by calling session.add(Page(1, Hello my book!, 1)) without requiring to fetch book object from database and adding the page to it. However, the book_id field in pages table is inserted as NULL instead of the value assigned in the constructor. Is there a way to achieve this behavior? The aim of such kind of approach is to avoid keeping book objects in the memory or retrieving those from database during in the initial creation of the database. --~--~-~--~~~---~--~~ 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] Re: insertion of an object with a user defined field value
Thanks for the quick response. However, the problem I face is not being able to access the id assigned by database but not being able to modify the corresponding field in Page instance. To be more clear: bk_ids = {} for title in ('Tom Sawyer', 'Huck Finn'): book = Book(title=title) session.add(book) session.flush() bk_ids[title] = book.id session.commit() for i, content in enumerate(('Once upon a time there was a little fellow called Tom.', 'His surname was Sawyer.')): page = Page(i, content, bk_ids['Tom Sawyer']) session.add(page) session.commit() When I check the database, book_id field in pages table is not modified as it supposed (or at least I suppose) to be. On Nov 9, 3:12 pm, Mike Conley mconl...@gmail.com wrote: The id is generate by the database engine, not SQLAlchemy, so session.add() does nothing to push your object to the database and generate the id. You need to execute session.flush() after session.add() to write the book to the database and generate the id. After the flush() operation, the book id is available to save in your dictionary. something like this: bk_ids = {} for title in ('Tom Sawyer', 'Huck Finn'): book = Book(title=title) session.add(book) session.flush() bk_ids[title] = book.id session.commit() Without the flush(), the id will be NULL. --~--~-~--~~~---~--~~ 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] Re: insertion of an object with a user defined field value
It does indeed! It is my bad, it works as expected, the problem was due to not flushing properly before storing the values in the dictionary. Sorry for the inconvenience and thank you for the support. On Nov 9, 4:46 pm, Mike Conley mconl...@gmail.com wrote: Using your class definitions, it seems to work. What is different? Base.metadata.bind=create_engine('sqlite:///') Base.metadata.create_all() session=sessionmaker()() bk_ids = {} for title in ('Tom Sawyer', 'Huck Finn'): book = Book(title=title) session.add(book) session.flush() bk_ids[title] = book.id session.commit() print bk_ids for i, content in enumerate(( 'Once upon a time there was a little fellow called Tom.', 'His surname was Sawyer.')): page = Page(i, content, bk_ids['Tom Sawyer']) session.add(page) session.commit() for page in session.query(Page): print 'page:',page.id,' book:',page.book_id --~--~-~--~~~---~--~~ 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 -~--~~~~--~~--~--~---