Python Data Persistence - ORM - Relationships

Python Data Persistence – ORM – Relationships

In the case of raw SQL, we establish relationships among tables using the FOREIGN KEY constraint. This section describes how relationships are built between tables and mapped classes.

In the previous chapter, our mydb.SQLite database contained an Invoices table that had a relationship with the ‘Products’ and ‘Customers’ table. These relationships were established with foreign keys. We shall now declare Invoice class (that maps Invoices table) and relate it to Product class and Customer class with the help of ForeignKey ( ) function imposed on ProductID and CustID columns in it. This is very similar to the definition of a table in raw SQL.

Example

from sqlalchemy import ForeignKey 
class Invoice(base): 
__tablename__='Invoices' 
‘InvID=Column(Integer, primary_key=True) 
CustID=Column(Integer, ForeignKey(1 Customers. CustID')) 
ProductID=Column(Integer, ForeignKey('Products. ProductID')) 
quantity=Column(Integer)

However, this will not establish a relationship amongst classes. SQLAlchemy’s ORM provides a relationship ( ) function for this purpose. In the Invoice class, ‘prod’ is a relationship property that sets uplink with the Product class, and ‘ c s t ’ attribute is a relationship that establishes a relation between Invoice and Customer class.

prod=relationship("Customer", back_ populates="Invoices") 
cst=relationship("Product", back_ populates="Invoices")

The ‘ back_populates ‘ parameter indicates that ‘prod’ and ‘c.s/’ properties have to be placed on the related mapped classes (Product and Customer respectively) that will handle this relationship in the other direction. The backward relationship is established by the following directives:

Example

Product.Invoices=relationship('Invoice', order_ by=Invoice.InvID, back_populates='cst') 
Customer.Invoices=relationship('Invoice 1, order_ by=Invoice.InvID, back_populates='prod1)

The complete code of the Invoice class is given below. Add it to my classes. py script and recreate the metadata schema by executing create_ all (engine) function.

Example

from sqlalchemy import ForeignKey 
from sqlalchemy.orm import relationship 
class Invoice(base): 
__tablename__='Invoices' 
InvID=Column(Integer, primary_key=True) 
CustID=Column(Integer, ForeignKey('Customers. CustID1)) 
ProductID=Column(Integer, ForeignKey('Products. ProductID')) 
prod=relationship("Customer", back_ populates="Invoices") 
cst=relationship("Product", back_ populates="Invoices") 
quantity=Column(Integer) 
Product.Invoices=relationship('Invoice' , order_ by=Invoice.InvID, back_populates='cst') 
Customer.Invoices=relationship('Invoice' , order_ by=Invoice.InvID, backj?opulates='prod')

The structure of the newly created Invoices table will be echoed on the command terminal:

Example

PRAGMA table_info("Invoices") 
( ) 
CREATE TABLE "Invoices" ( 
"InvID" INTEGER NOT NULL, 
"CustID" INTEGER, 
"ProductID" INTEGER, quantity INTEGER, 
PRIMARY KEY ("InvID"), 
FOREIGN KEY("CustID") REFERENCES "Customers" ("CustID"), 
FOREIGN KEY("ProductID") REFERENCES "Products" ("ProductID") 
)
 ( ) 
COMMIT

Using the Session object, we can now add data in this table as follows:

Example

from sqlalchemy.orm import sessionmaker 
Session = sessionmaker(bind=engine) 
sessionobj = Session() 
il=Invoice(InvID=l, CustID=l, ProductID=l, quantity=2) 
sessionobj.add(il) 
sessionobj.commit()

Likewise, you can add the rest of the records as given in sample data in the previous chapter.

Python Data Persistence – ORM – Relationships

Related