SQLAlchemy Core
- Using Set Operations
- Using Functions
- Using Conjunctions
- Using Joins
- Multiple Table Deletes
- Parameter-Ordered Updates
- Using Multiple Table Updates
- Using Multiple Tables
- Using DELETE Expression
- Using UPDATE Expression
- Using Aliases
- Using Textual SQL
- Selecting Rows
- Executing Expression
- SQL Expressions
- Creating Table
- Connecting to Database
- Expression Language
SQLAlchemy ORM
- Dialects
- Many to Many Relationships
- Deleting Related Objects
- Eager Loading
- Common Relationship Operators
- Working with Joins
- Working with Related Objects
- Building Relationship
- Textual SQL
- Returning List and Scalars
- Filter Operators
- Applying Filter
- Updating Objects
- Using Query
- Adding Objects
- Creating Session
- Declaring Mapping
SQLAlchemy Useful Resources
Selected Reading
- Who is Who
- Computer Glossary
- HR Interview Questions
- Effective Resume Writing
- Questions and Answers
- UPSC IAS Exams Notes
SQLAlchemy ORM - Building Relationship
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.
Using declarative, we define this table along with its mapped class, Invoices as given below −
from sqlalchemy import create_engine, ForeignKey, Column, Integer, String engine = create_engine( sqpte:///sales.db , echo = True) from sqlalchemy.ext.declarative import declarative_base Base = declarative_base() from sqlalchemy.orm import relationship class Customer(Base): __tablename__ = customers id = Column(Integer, primary_key = True) name = Column(String) address = Column(String) email = Column(String) class Invoice(Base): __tablename__ = invoices id = Column(Integer, primary_key = True) custid = Column(Integer, ForeignKey( customers.id )) invno = Column(Integer) amount = Column(Integer) customer = relationship("Customer", back_populates = "invoices") Customer.invoices = relationship("Invoice", order_by = Invoice.id, back_populates = "customer") Base.metadata.create_all(engine)
This will send a CREATE TABLE query to SQLite engine as below −
CREATE TABLE invoices ( id INTEGER NOT NULL, custid INTEGER, invno INTEGER, amount INTEGER, PRIMARY KEY (id), FOREIGN KEY(custid) REFERENCES customers (id) )
We can check that new table is created in sales.db with the help of SQLiteStudio tool.
Invoices class apppes 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 pnked 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 pnkage, 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 intelpgent decision about the same relationship as expressed in reverse. On one side, Invoices.customer refers to Invoices instance, and on the other side, Customer.invoices refers to a pst 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 estabpsh 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 usepst 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 usepst parameter to false.
Many To Many
Many to Many relationship is estabpshed 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 pnk. The relationship.back_populates parameter for each relationship() estabpshes a bidirectional relationship. Both sides of the relationship contain a collection.
Advertisements