[ Python 文章收集 ] SQLAlchemy ORM - Building Relationship

Source From Here 
Preface 
This session describes creation of another table which is related to already existing one in our database. The customers table contains master data of customers. We now need to create invoices table which may have any number of invoices belonging to a customer. This is a case of one to many relationships. 

Building Relationship 
Using declarative, we define this table along with its mapped class, Invoices as given below: 
- build_rel_ex1.py 

view plaincopy to clipboardprint?

1. from sqlalchemy import Column, Integer, String, ForeignKey  
2. from sqlalchemy import create_engine  
3. from sqlalchemy.ext.declarative import declarative_base  
4. from sqlalchemy.orm.session import sessionmaker  
5. from sqlalchemy.orm import relationship  
6.   
7.   
8. db_string = "postgresql://postgres:[password]@localhost/testdb"  
9. engine = create_engine(db_string, echo=True)  
10. Base = declarative_base()  
11.   
12. class Customers(Base):  
13.     __tablename__ = 'customers'  
14.     id = Column(Integer, primary_key=True)  
15.     name = Column(String)  
16.     address = Column(String)  
17.     email = Column(String)  
18.   
19. class Invoice(Base):  
20.    __tablename__ = 'invoices'  
21.   
22.    id = Column(Integer, primary_key = True)  
23.    custid = Column(Integer, ForeignKey('customers.id'))  
24.    invno = Column(Integer)  
25.    amount = Column(Integer)  
26.    customer = relationship("Customer", back_populates = "invoices")  
27.   
28. Customers.invoices = relationship("Invoice", order_by = Invoice.id, back_populates = "customer")  
29. Base.metadata.create_all(engine)  
30.   
31. Session = sessionmaker(bind = engine)  
32. session = Session()  

This will send a CREATE TABLE query to PostgreSQL engine as below: 

view plaincopy to clipboardprint?

1. CREATE TABLE invoices (  
2.    id INTEGER NOT NULL,  
3.    custid INTEGER,  
4.    invno INTEGER,  
5.    amount INTEGER,  
6.    PRIMARY KEY (id),  
7.    FOREIGN KEY(custid) REFERENCES customers (id)  
8. )  

We can check that new table is created: 

testdb=# \d+ invoices

view plaincopy to clipboardprint?

1.                                                 Table "public.invoices"  
2. Column |  Type   | Collation | Nullable |               Default                | Storage | Stats target | Description  
3. --------+---------+-----------+----------+--------------------------------------+---------+--------------+-------------  
4. id     | integer |           | not null | nextval('invoices_id_seq'::regclass) | plain   |              |  
5. custid | integer |           |          |                                      | plain   |              |  
6. invno  | integer |           |          |                                      | plain   |              |  
7. amount | integer |           |          |                                      | plain   |              |  
8. Indexes:  
9.     "invoices_pkey" PRIMARY KEY, btree (id)  
10. Foreign-key constraints:  
11.     "invoices_custid_fkey" FOREIGN KEY (custid) REFERENCES customers(id)  

Invoice class applies ForeignKey construct on custid attribute. This directive indicates that values in this column should be constrained to be values present in id column in customers table. This is a core feature of relational databases, and is the “glue” that transforms unconnected collection of tables to have rich overlapping relationships. 

A second directive, known as relationship(), tells the ORM that the Invoice class should be linked to the Customer class using the attribute Invoice.customer. The relationship() uses the foreign key relationships between the two tables to determine the nature of this linkage, determining that it is many to one. 

An additional relationship() directive is placed on the Customer mapped class under the attribute Customer.invoices. The parameter relationship.back_populates is assigned to refer to the complementary attribute names, so that each relationship() can make intelligent decision about the same relationship as expressed in reverse. On one side, Customers.invoices refers to Invoices instance, and on the other side, Invoice.customer refers to a list of Customers instances. 

The relationship function is a part of Relationship API of SQLAlchemy ORM package. It provides a relationship between two mapped classes. This corresponds to a parent-child or associative table relationship. Following are the Basic Relationship Patterns found: 

One To Many 
A One to Many relationship refers to parent with the help of a foreign key on the child table. relationship() is then specified on the parent, as referencing a collection of items represented by the child. The relationship.back_populates parameter is used to establish a bidirectional relationship in one-to-many, where the “reverse” side is a many to one. 

Many To One 
On the other hand, Many to One relationship places a foreign key in the parent table to refer to the child. relationship() is declared on the parent, where a new scalar-holding attribute will be created. Here again the relationship.back_populates parameter is used for Bidirectionalbehaviour. 

One To One 
One To One relationship is essentially a bidirectional relationship in nature. The uselist flag indicates the placement of a scalar attribute instead of a collection on the “many” side of the relationship. To convert one-to-many into one-to-one type of relation, set uselist parameter to false. 

Many To Many 
Many to Many relationship is established by adding an association table related to two classes by defining attributes with their foreign keys. It is indicated by the secondary argument to relationship(). Usually, the Table uses the MetaData object associated with the declarative base class, so that the ForeignKey directives can locate the remote tables with which to link. The relationship.back_populates parameter for each relationship() establishes a bidirectional relationship. Both sides of the relationship contain a collection. 

Supplement 
* Next - Working with Related Objects 
* SQLAlchemy Doc - Basic Relationship Patterns 
* SQLAlchemy Doc - Relationships API 
* SQLAlchemy Doc - Defining Constraints and Indexes