Updated March 16, 2023
Introduction to SQLAlchemy Many to Many
SQLAlchemy, many to many, are the relationship formed by creating a table that acts as an association table for the two classes of the table and contains two foreign keys that are nothing but the value of the primary keys of each of that table that participates in the relationship.
This article will have a detailed look at the topic of many relationships in SQLAlchemy with the help of subtopics, including overviews SQLAlchemy many to many SQLAlchemy many to many Relationships, create class SQLAlchemy many to many, and a conclusion about the same.
Overview of SQLAlchemy many to many
SQLAlchemy many relationships are when a single record of the table relates to multiple records of another table, and a single record of another table relates to multiple records of the first table. This relationship is implemented in SQLAlchemy by creating an association table consisting of the foreign keys referencing the two primary keys from each table. The scenario most of the time is that the attribute of the classes that are mapped to the two tables in a relationship has the value containing the collection of object values which are of the other tables of association and are used here as the second attribute in the function of relationship().
SQLAlchemy Many to Many Relationship
While defining the many to many relationships between the two entities in SQLAlchemy, we will have to follow certain steps –
- Define the first entity model by defining its class
- Define the second entity model by defining its class
- Defining the association table
There are many real-time scenarios where we will need this relationship to be implemented in SQL Alchemy. All the steps will be defined in detail with the help of an example where we will define the source classes and the association table for the technology sections and the writers.
Create Class SQLAlchemy Many to Many
To understand SQLAlchemy’s many relationships, let us consider one example where we will create a small database of SQLite named educba, which contains two tables, namely technology_sections and writers, where a general assumption is made that a single technology_sections may have multiple writers working on it. However, a single writer can work for multiple technology sections in the organization, which is nothing but refer to as many to many relationships. Here, the tables technology_sections and the writers have many relationships.
The classes of Technology_sections and the Writers have the following definition, which is further mapped to the corresponding tables, namely technology_sections and writers –
from SQLAlchemy import create_engine, ForeignKey, Column, Integer, String
engine = create_engine('sqlite:///educba.db', echo = True)
from SQLAlchemy.ext.declarative import declarative_base
Base = declarative_base()
from SQLAlchemy.orm import relationship
class Technology_sections(Base):
__tablename__ = 'technology_section'
id = Column(Integer, primary_key = True)
name = Column(String)
writers = relationship('Writers', secondary = 'relationship_link')
class Writers(Base):
__tablename__ = 'writer'
id = Column(Integer, primary_key = True)
name = Column(String)
technology_sections = relationship(Technology_sections,secondary='relationship_link')It’s time to write the definition of Relationship_link class which will be Relationship_linked to the table relationship_link containing technology_section_id and writer_id attributes, which refer to technology_section and writer table’s primary keys, respectively.
class Relationship_link (Base):
__tablename__ = 'relationship_link'
technology_section_id = Column (
Integer,
ForeignKey('technology_section.id'),
primary_key = True)
writer_id = Column (
Integer,
ForeignKey('writer.id'),
primary_key = True)The secondary attribute of the relationship function is given to the value of relationship_link. That is class of technology_sections contains the writer’s attribute while the writer’s class contains the technology_sections attribute, which is related to the opposite classes.
We can go for the creation of all the above classes by executing the following statements –
Base.metadata.create_all(engine)
CREATE TABLE technology_section (
id INTEGER NOT NULL,
name VARCHAR,
PRIMARY KEY (id)
)Which gives the following output –
CREATE TABLE writer (
id INTEGER NOT NULL,
name VARCHAR,
PRIMARY KEY (id)
)That results in below output –
CREATE TABLE relationship_link (
technology_section_id INTEGER NOT NULL,
writer_id INTEGER NOT NULL,
PRIMARY KEY (technology_section_id, writer_id),
FOREIGN KEY(technology_section_id) REFERENCES technology_section (id),
FOREIGN KEY(writer_id) REFERENCES writer (id)
)After running this, we get the following output –
The structure of all the created tables is as shown below –
Technology_section table –
Writer table –
Relationship_link table –
When run on python console, the omitting of all the create table queries is done.
We can even give check on the contents by opening them on the SQLiteStudio inside the educba.db. Now, we will go for creating three sample objects of technology_sections and writers class.
ts1 = Technology_sections(name = "Database")
ts2 = Technology_sections(name = "Front End")
ts3 = Technology_sections(name = "Back End")
w1 = Writers(name = "Payal")
w2 = Writers(name = "Aahana")
w3 = Writers(name = "Mayur")We can now write the values of the technology section and writers’ values in each other’s table’s attributes to relate them as shown below, which creates many to many relationships –
w1.technology_sections.append(ts1)
w2.technology_sections.append(ts3)
ts1.writers.append(w3)
ts2.writers.append(w2)
ts3.writers.append(w1)
w3.technology_sections.append(ts2)Now, its time to commit all the changes that we have made in the database tables and classes by simply creating an object of Session that is current_session, as shown here –
from SQLAlchemy.orm import sessionmaker
Session = sessionmaker(bind = engine)
current_session = Session()
current_session.add(w1)
current_session.add(w2)
current_session.add(ts1)
current_session.add(ts2)
current_session.add(ts3)
current_session.add(w3)
current_session.commit()When running on the python console, the omitting of all the below queries is done.
INSERT INTO technology_section (name) VALUES (?) ('Database',)
INSERT INTO technology_section (name) VALUES (?) ('Front End',)
INSERT INTO technology_section (name) VALUES (?) ('Back End',)
INSERT INTO writer (name) VALUES (?) ('Payal',)
INSERT INTO writer (name) VALUES (?) ('Mayur',)
INSERT INTO writer (name) VALUES (?) ('Aahana',)
INSERT INTO relationship_link (technology_section_id, writer_id) VALUES (?, ?) ((1, 2), (3, 1), (2, 3))
INSERT INTO relationship_link (technology_section_id, writer_id) VALUES (?, ?) ((1, 1), (2, 2), (3, 3))To check the effect of the above operations, use SQLiteStudio and view data in the technology_section, writer, and relationship_link tables. We get the following contents of each of the tables –
Technology_section –
Writer table –
Relationship_link table –
We will have to execute the below query to get the resultant displayed –
from SQLAlchemy.orm import sessionmaker
Session = sessionmaker(bind = engine)
current_session = Session()
for sampleValue in current_session.query( Technology_sections, Writers).filter(Relationship_link.technology_section_id == Technology_sections.id,
Relationship_link.writer_id == Writers.id).order_by(Relationship_link.technology_section_id).all():
print ("Technology_sections: {} Name: {}".format(sampleValue.Technology_sections.name, sampleValue.Writers.name))The output of the above code will be shown below, showing the name of the technology and writers linked, demonstrating their relationship.
Conclusion
SQL Alchemy many to many are the relationship between the two entities that are mapped to classes. This relationship can be created by creating an association table containing foreign keys referencing to primary keys of both the related tables.
Recommended Articles
We hope that this EDUCBA information on “SQLAlchemy Many to Many” was beneficial to you. You can view EDUCBA’s recommended articles for more information.
