The benefit of this approach is that you can instantiate the Map class, this is useful if you want to interact with Map objects in any non-trivial way. Here you can specify relationships on the Map class. Prod_id = Column(Integer,ForeignKey( 'products.id')) Id = Column(Integer,Sequence( 'map_seq'),primary_key= True)Ĭat_id = Column(Integer,ForeignKey( 'categories.id')) ![]() Id = Column(Integer,Sequence( 'prod_seq'),primary_key= True) Id = Column(Integer,Sequence( 'cat_seq'),primary_key= True) There are two ways of doing this:įirst, just using models: class Category (Base): We can make use of this like so: oChild = DBSession.query(Child).get( 1)Ī many to many relationship requires an extra table to create mappings between lines. If the string "parent" was used then it would be a normal many to one relationship. Note that the line that configures the relationship has an expression instead of just a string for the backref argument. Id = Column(Integer,Sequence( 'c_seq'),primary_key= True) Id = ColumnColumn(Integer,Sequence( 'p_seq'),primary_key= True)Ĭhild_id = Column(Integer, ForeignKey( 'child.id'))Ĭhild = relationship( "Child", backref=backref( "parent", uselist= False)) # <- class Child (Base): One to one relationships class Parent (Base): OAuthor = DBSession.query(Author).filter_by(name= "Orsan Scott Card") oAuthor.id = thor_id #it works the other way as well OAuthor = thor # oAuthor is now an Author instance. Here are a few ways you can make use of the relationship once it is configured: oBook = DBSession.query(Book).filter_by(name= "Harry Potter and the methods of rationality").first() A book's author is accessable via the author attribute, and an author's books are accessable via the author's books attribute. ![]() Note that the relationship is configured in both directions in one line. Using a string here removes the possibility of certain NameErrors. It doesn't need to be, it can also be a class. The marked line configures the relationship between the models. Id = Column(Integer,Sequence( 'book_seq'),primary_key= True)Īuthor_id = Column(Integer,ForeignKey( 'authors.id'))Īuthor = relationship( "Author",backref= "books") # <- class Author (Base): SQLAlchemy makes leveraging and examining those relationships pretty straight forward.Īssume we are keeping track of the books of various authors. From the example, the books table has a foreign key field pointing to the id field of the authors table. Relationships between SQL tables are described in terms of foreign key relationships. LBooks = DBSession.query(Book).filter(or_(Book.price<20,promote=True)) # returns all books that cost less than 20 OR are being promotedĭBSession.query(Book).order_by(Book.price) #get all books ordered by priceĭBSession.query(Book).order_by(desc(Book.price)) #get all books ordered by price descendingĭBSession.query(Book).count() #returns the number of booksĭBSession.query(Book).offset(5) #offset the result by 5ĭBSession.query(Book).limit(5) # return at most 5 booksĭBSession.query(Book).first() #return the first book only or NoneĭBSession.query(Book).get(8) #return the Book with primary key = 8, or None #logical operations can be used in filters LBooks = DBSession.query(Book).filter_by(author_id=1).filter(Book.price<20) #all books by a specific author, with price<20 LBooks = DBSession.query(Book).filter(Book.price<20) #returns all the books with price <20. LBooks = DBSession.query(Book).filter_by(author_id=1) #returns all the books for a specific author LBooks = DBSession.query(Book) #returns a Query object. Selecting and Filtering #fetch everything Promote = Column(Boolean,default= False) # or as values Queries and Interactions Name = Column(String( 50)) # string column need lengthsĪuthor_id = Column(Integer,ForeignKey( 'authors.id')) # assumes there is a table in the database called 'authors' that has an 'id' columnĭate_added = Column(DateTime, default=) # defaults can be specified as functions Id = Column(Integer,Sequence( 'book_seq'),primary_key= True) # plays nice with all major database engines _tablename_ = "books" #matches the name of the actual database table For example: from import declarative_baseįrom sqlalchemy.orm import scoped_session,sessionmakerįrom zope.sqlalchemy import ZopeTransactionExtensionĭBSession = scoped_session(sessionmaker(extension=ZopeTransactionExtension()))Ĭlass Book (Base): #<. One model is used to describe one database table. That said, if you are familiar with SQL then this cheat sheet should get you well on your way to understanding SQLAlchemy. This cheat sheet sticks to parts of the ORM (Object Relational Mapper) layer,and aims to be a reference not a tutorial. SQLAlchemy is a deep and powerful thing made up of many layers.
0 Comments
Leave a Reply. |