Sqlalchemy many to many

Sqlalchemy many to many DEFAULT

{ Many to Many and Complex Associations. }

Objectives:

By the end of this chapter, you should be able to:

  • Create a many to many relationship using just SQL
  • Setup SqlAlchemy so that it realizes the database has a many to many relationship
  • Define and implement a recursive self join with SQLAlchemy

M - M

Modeling a M:M in SQLAlchemy

The two resources we will be working on are and . We will say that many employees can be part of many different departments.

Let's first imagine what this would look like with SQL. When you want to model a many-to-many relationship between two resources, it turns out that you need THREE tables: one table for each resource, and one to manage the links between the two. This third table is frequently called a join table, or a through table, since you have a many-to-many relationship through this table. Note also that from this perspective, a many-to-many relationship is really just a pair of one-to-many relationships, where each resource is in a one-to-many relationship with the join table.

In the simplest case, our join table will have columns for the foreign keys for both tables, but not much else. In our current example, let's call our join table table and start with the following commands in the terminal:

dropdb many-many-example createdb many-many-example psql many-many-example CREATETABLE employees (id SERIAL PRIMARYKEY, name TEXT, years_at_company INTEGER); CREATETABLE departments (id SERIAL PRIMARYKEY, name TEXT); CREATETABLE employee_departments(id SERIAL PRIMARYKEY, employee_id INTEGERREFERENCES employees (id) ONDELETECASCADE, department_id INTEGERREFERENCES departments (id) ONDELETECASCADE); INSERTINTO employees (name, years_at_company) VALUES ('Elie', 2), ('Michael', 3), ('Angelina', 6), ('Matt', 7), ('Lorien', 2), ('Meg', 4); INSERTINTO departments (name) VALUES ('leadership'), ('education'), ('marketing'), ('evangelism'), ('operations'), ('admissions'); INSERTINTO employee_departments (employee_id, department_id) VALUES (1,1), (1,2), (1,5), (2,1), (2,2), (2,3), (2,4), (3,1), (3,2), (3,3), (3,4), (3,5), (3,6), (4,1), (4,2), (4,3), (4,5), (5,3), (5,4), (6,3), (6,5);

Given this data, let's do some quick SQL review. Verify that the following queries generate tables where each row shows data on:

  1. The name of the employee and their department.

    SELECT e.name, d.name FROM employees e JOIN employee_departments ed ON e.id = ed.employee_id JOIN departments d ON ed.department_id = d.id;
  2. All of the employees' names and the years they've been at the company, provided they are in the department.

    SELECT e.name, e.years_at_company FROM employees e JOIN employee_departments ed ON e.id = employee_id JOIN departments d ON ed.department_id = d.id WHERE d.name = 'leadership';
  3. The name of the employee and number of departments that they are in

    SELECT e.name, COUNT(d.name) FROM employees e JOIN employee_departments ed ON e.id = ed.employee_id JOIN departments d ON d.id = ed.department_id GROUPBY e.name;
  4. The name of the deparment and the number of employees

    SELECT d.name, COUNT(e.name) FROM employees e JOIN employee_departments ed ON e.id = employee_id JOIN departments d ON ed.department_id = d.id GROUPBY d.name;

Associations Using Flask SQLAlchemy

So far we have seen how to implement one-to-many associations with Flask, but as our applications and schemas grow, we need to handle more complex associations. We are going to examine two of them in this section: many to many and self joins. We will also see how to implement them using SQLAlchemy. Let's get started with a new virtual environment!

Our goal will be to move from our understanding of associations using pure SQL to using Flask SQLAlchemy. For that reason, let's drop our database so that we can start fresh.

mkvirtualenv many-many-example workon many-many-example dropdb many-many-example createdb many-many-example pip install flask ipython psycopg2 flask-sqlalchemy flask-migrate flask-wtf flask-modus

As before, let's work with two resources: and . Many employees can be part of many different departments and vice versa. Let's write these models inside of an file. Here's what those models could look like:

fromflaskimportFlaskfromflask_sqlalchemyimportSQLAlchemyfromflask_migrateimportMigrate app = Flask(__name__) app.config['SQLALCHEMY_DATABASE_URI'] = 'postgres://localhost/many-many-example' app.config['SQLALCHEMY_TRACK_MODIFICATIONS'] = False db = SQLAlchemy(app) migrate = Migrate(app, db) EmployeeDepartment = db.Table('employee_departments', db.Column('id', db.Integer, primary_key=True), db.Column('employee_id', db.Integer, db.ForeignKey('employees.id', ondelete="cascade")), db.Column('department_id', db.Integer, db.ForeignKey('departments.id', ondelete="cascade"))) classEmployee(db.Model): __tablename__ = 'employees' id = db.Column(db.Integer, primary_key=True) name = db.Column(db.Text) years_at_company = db.Column(db.Integer) departments = db.relationship("Department", secondary=EmployeeDepartment, backref=db.backref('employees')) def__init__(self, name, years_at_company): self.name = name self.years_at_company = years_at_company classDepartment(db.Model): __tablename__ = 'departments' id = db.Column(db.Integer, primary_key=True) name = db.Column(db.Text) def__init__(self, name): self.name = name

Notice that we don't need a class for our join table: in the event that your join table just stores records of foreign keys, the ORM abstracts away this table from you entirely, and you can work with the many-to-many relationship without needing to directly manipulate the join table. Now let's generate a migration and upgrade!

flask db init flask db migrate -m "creating employees and departments tables" flask db upgrade

Let's see what this looks like by entering an REPL and playing around with this association!

fromappimportdb, Employee, Department e1 = Employee('Elie', 2) e2 = Employee('Michael', 3) e3 = Employee('Angelina', 6) e4 = Employee('Matt', 7) e5 = Employee('Lorien', 2) e6 = Employee('Meg', 4) d1 = Department('leadership') d2 = Department('education') d3 = Department('marketing') d4 = Department('evangelism') d5 = Department('operations') d6 = Department('admissions') d1.employees.extend([e1,e2,e3,e4]) d2.employees.extend([e1,e2,e3,e4]) d3.employees.extend([e2,e3,e4,e5,e6]) e1.departments.extend([d5]) e1.departments """[<Department (transient )>, <Department (transient )>, <Department (transient )>]""" [d.name for d in e2.departments]

Changes With Multiple Blueprints

Now that we have multiple blueprint files, our folder structure will be slightly different than the previous chapter. Below is a sample structure with employees and departments:

. ├── app.py ├── project │   ├── __init__.py │   |── departments │   │   ├── templates │   │   | ├── departments │   │   │   │   ├── index.html │   │   │   │   └── show.html │   │   └── views.py │   ├── employees │   │   ├── templates │   │   | ├── employees │   │   │   | ├── edit.html │   │   │   | ├── index.html │   │   │   | ├── new.html │   │   │   | └── show.html │   │   └── views.py │   ├── models.py │   └── templates │   └── base.html └── requirements.txt

Notice that for now, the file is in the directory. In this structure, models are in 1 file that is shared between all blueprints. Since our models are relatively small, there's not much of a tradeoff between separating our models out versus keeping them in a single file.

Here is the setup code for creating the employees blueprint:

fromflaskimportredirect, render_template, request, url_for, Blueprintfromproject.modelsimportEmployee employees_blueprint = Blueprint( 'employees', __name__, template_folder='templates' )

Adding a Many to Many in a Flask application

Now that you have an understanding of different kinds of associations, let's think about what else needs to change when we work with a many-to-many!

Remember, we do not need to use nested routes since each of these resources are independent, but we do need to be mindful of how our forms will work. Believe it or not, one of the most challenging parts of working with many to many associations is how to build forms that keep all of that data in mind!

Let's take a look at the application here. In this application we simply have a many to many with departments and employees. Our should look almost the same as the above, just without the creation of our app and a different import line:

fromprojectimportdb EmployeeDepartment = db.Table('employee_departments', db.Column('id', db.Integer, primary_key=True), db.Column('employee_id', db.Integer, db.ForeignKey('employees.id', ondelete="cascade")), db.Column('department_id', db.Integer, db.ForeignKey('departments.id', ondelete="cascade"))) classEmployee(db.Model): __tablename__ = 'employees' id = db.Column(db.Integer, primary_key=True) name = db.Column(db.Text) years_at_company = db.Column(db.Integer) departments = db.relationship("Department", secondary=EmployeeDepartment, backref=db.backref('employees')) def__init__(self, name, years_at_company): self.name = name self.years_at_company = years_at_company classDepartment(db.Model): __tablename__ = 'departments' id = db.Column(db.Integer, primary_key=True) name = db.Column(db.Text) def__init__(self, name): self.name = name

We simply have two models along with a join table that we are calling EmployeeDepartment which contains two id's that are foreign keys. Things get a bit more interesting if you start thinking about the forms. For instance, let's say that you want to be able to select the departments to assing to an employee when you create that employee. You might think that you need a form that looks something like this:

fromflask_wtfimportFlaskFormfromwtformsimportTextField, IntegerField, SelectMultipleFieldfromwtforms.validatorsimportDataRequiredfromproject.modelsimportDepartmentclassNewEmployeeForm(FlaskForm): name = TextField('Name', validators=[DataRequired()]) years_at_company = IntegerField('Years At Company', validators=[DataRequired()]) departments = SelectMultipleField('Departments', coerce=int, choices=[(d.id, d.name) for d in Department.query.all()])

Here, we're basically saying that we want a select element on the page with options for each department, where we get the departments from the database in order to create the form.

There are a couple of problems with this approach, however. First, the departments table will only be queried once, when the app starts. This means that if we add some departments and then try to add an employee, we won't be able to tag that employee with the new departments!

Another problem is with the UI - the select element that you get out of the box isn't very nice looking, and while it's possible to select multiple rows, it's not convenient, and the user interface doesn't make it clear that it is even possible.

To fix these problems, we need to do a couple of things:

  1. Create an instance method to get departments on the fly when we're building a form, so that we're guaranteed to have the most up-to-date information,
  2. Overwrite the default HTML that gets generated by so that we can get some checkboxes showing up on the page.

To see how to implement these solutions, you can head over here. Our modified form looks like this:

fromflask_wtfimportFlaskFormfromwtformsimportTextField, IntegerField, SelectMultipleField, widgetsfromwtforms.validatorsimportDataRequiredfromproject.modelsimportDepartmentclassNewEmployeeForm(FlaskForm): name = TextField('Name', validators=[DataRequired()]) years_at_company = IntegerField('Years At Company', validators=[DataRequired()]) departments = SelectMultipleField( 'Departments', coerce=int, widget=widgets.ListWidget(prefix_label=True), option_widget=widgets.CheckboxInput()) defset_choices(self): self.departments.choices = [(d.id, d.name) for d in Department.query.all()]

Don't be too intimidated by this code. First, note that by passing in values for and inside of , we can customize the HTML in our form. In this case, we're telling WTForms that we want to use a for our options, and for each option, we want to use a checkbox to receive user input.

Secondly, we've created an instance method called to grab the department data from the database. If you look at the routing logic for our app, you can see that this function is called on the form before it is passed into any of our templates, so that we can be sure that our forms always have the most up-to-date department information.

To read more about , including why our choices need to be formatted as a list of tuples, check out the WTForms docs.

Additional Types of Joins

Along with the joins we have seen, there are also a few other types of joins which can be used to model special kinds of data. Let's see two examples:

Self Join

What happens if we want to add the idea of a "manager" for our employees? Our manager is going to have the same exact columns as the employee, so what do we do? Create a new table? The answer is a self join, where we join a table on itself! Let's see what that looks like when we add a property called for each employee and a property called employees which refers to each instance that is an employee of another. Here's how we can modify the model:

classEmployee(db.Model): __tablename__ = "employees" id = db.Column(db.Integer, primary_key=True) name = db.Column(db.Text) years_at_company = db.Column(db.Integer) manager_id = db.Column(db.Integer, db.ForeignKey('employees.id')) departments = db.relationship("Department", secondary=EmployeeDepartment, backref=db.backref('employees')) employees = db.relationship("Employee", lazy="joined", backref=db.backref('manager',remote_side=[id])) def__init__(self, first_name, last_name, manager_id=None): self.first_name = first_name self.years_at_company = years_at_company self.manager_id = manager_id

To see what this looks like, run a migration, upgrade, and hop into to verify that the relationship is set up correctly. Try creating a manager, saving that manager, and then creating some more employes who are managed by that manager. You should be able to get the employees via , and from any employee get back to the manager via .

Recursive Self Join

There are times where we also want to have parents of parents. This idea is a recursive self join. We can model that in a very similar way like this:

classNode(db.Model): __tablename__ = 'node' id = db.Column(db.Integer, primary_key=True) parent_id = db.Column(db.Integer, db.ForeignKey('node.id')) data = db.Column(db.String(50)) children = db.relationship("Node", lazy="joined") n= Node(data="first") n2= Node(parent_id=1, data="second") n3= Node(parent_id=2, data="third") n4= Node(parent_id=3, data="fourth") n5= Node(parent_id=4, data="fifth") db.session.add_all([n,n2,n3,n4,n5]) db.session.commit() n.children[0].children[0].children[0].children[0].data

You can read more about self joins here.

When you're ready, move on to Intermediate Flask Exercises

Continue

Sours: https://www.rithmschool.com/courses/intermediate-flask/many-to-many-and-complex-associations

Many to many relationships in SQLAlchemy models (Flask)

Many-to-many relationships are fairly common in many (ahem) applications, and this is to remind myself how to create them - I always seem to forget! These are known as has_many :through associations in the Ruby on Rails world, and are needed whenever any Object A can have many Object B records, and any Object B can also be associated with many Object A records.

One real-world example would be an ecommerce site with database models for and - a User can have many Products through a secondary table (ie ), and a Product can be purchased by many Users through the same Orders table.

In this case, the concept of an "order" which associates users with products is fairly well known - but in situations where this does not apply, it is common to name this secondary table as a combination of both bodels (ie "UserProducts" if the concept of "Orders" is not commonly known). 

Here's how to define it in Flask / SQLAlchemy models.

1. Define your User model

This could look like:

2. Define the Product model

3. And finally, the association table

4. Usage

Once these models have been defined and imported (and presumably your database tables have been created), you can use them easily:


Related Articles

Similar articles you may be interested in.

Sours: https://michaelcho.me/article/many-to-many-relationships-in-sqlalchemy-models-flask
  1. Gmc acadia 2019 price
  2. Outdoor bar cart pottery barn
  3. Still novel discount code

How to build many-to-many relations using SQLAlchemy: a good example

I have read the SQLAlchemy documentation and tutorial about building many-to-many relation but I could not figure out how to do it properly when the association table contains more than the 2 foreign keys.

I have a table of items and every item has many details. Details can be the same on many items, so there is a many-to-many relation between items and details

I have the following:

My association table is (It's defined before the other 2 in the code):

In the documentation, it's said that I need to use the "association object". I could not figure out how to use it properly, since it's mixed declarative with mapper forms and the examples seem not to be complete. I added the line:

as a member of Item class and the line:

as a member of the association table, as described in the documentation.

when I do item = session.query(Item).first(), the item.details is not a list of Detail objects, but a list of ItemDetail objects.

How can I get details properly in Item objects, i.e., item.details should be a list of Detail objects?

asked Apr 22 '11 at

dudukleinduduklein

8, gold badges silver badges bronze badges

Sours: https://stackoverflow.com/questions//how-to-build-many-to-many-relations-using-sqlalchemy-a-good-example
Adding Extra Fields On Many-To-Many Relationships in Django

sqlalchemy many to many relationship

1, Foreword

A many to many relationship is a table that can be associated with multiple tables.  

Now let's design a table structure that can describe the relationship between "book" and "author". The requirements are

  1. A book can be published by several authors
  2. A writer can write several books

2, Table structure and data

The book > M2M > author table is generated by the author table and the book table

3, Examples

# -*- coding: UTF-8 -*-from sqlalchemy import Table, Column, Integer, String, DATE, ForeignKey from sqlalchemy.orm import relationship from sqlalchemy.ext.declarative import declarative_base from sqlalchemy import create_engine # If the inserted data has Chinese, you need to specify charset=utf8 engine = create_engine("mysql+pymysql://[email protected]/study?charset=utf8", encoding='utf-8') Base = declarative_base() # Establish orm Base classBase.metadata.create_all(engine) # After this table is created, it does not need to be maintained book_m2m_author = Table("book_m2m_author", Base.metadata, Column("id", Integer, primary_key=True), Column('books_id', Integer, ForeignKey("books.id")), Column('authors_id', Integer, ForeignKey("authors.id"))) class Book(Base): __tablename__ = "books" id = Column(Integer, primary_key=True) name = Column(String(64)) pub_date = Column(DATE) authors = relationship("Author", secondary=book_m2m_author, backref="books") def__repr__(self): return self.name class Author(Base): __tablename__ = "authors" id = Column(Integer, primary_key=True) name = Column(String(32)) def__repr__(self): return self.name # Create table Base.metadata.create_all(engine)
Create 3 tables
mysql> desc authors; +++++++ | Field | Type | Null | Key | Default | Extra | +++++++ | id | int(11) | NO | PRI | NULL | auto_increment | | name | varchar(32) | YES | | NULL | | +++++++ 2 rows in set ( sec) mysql> desc books; +++++++ | Field | Type | Null | Key | Default | Extra | +++++++ | id | int(11) | NO | PRI | NULL | auto_increment | | name | varchar(64) | YES | | NULL | | | pub_date | date | YES | | NULL | | +++++++ 3 rows in set ( sec) mysql> desc book_m2m_author; +++++++ | Field | Type | Null | Key | Default | Extra | +++++++ | id | int(11) | NO | PRI | NULL | auto_increment | | books_id | int(11) | YES | MUL | NULL | | | authors_id | int(11) | YES | MUL | NULL | | +++++++ 3 rows in set ( sec)
Table structure

4, Insert data

# -*- coding: UTF-8 -*-import m2m_orm from m2m_orm import Author from m2m_orm import Book from sqlalchemy.orm import sessionmaker # Establish session Conversation Session_class = sessionmaker(bind=m2m_orm.engine) # generate session Example session = Session_class() b1 = Book(name="python Study", pub_date="") b2 = Book(name="linux Study", pub_date="") b3 = Book(name="mysql Study", pub_date="") a1 = Author(name="Jack") a2 = Author(name="Jerru") a3 = Author(name="Marry") b1.authors = [a1,a2] b2.authors = [a2,a3] b3.authors = [a1,a2,a3] session.add_all([b1,b2,b3,a1,a2,a3]) session.commit()
insert data
mysql> select * from books; ++++ | id | name | pub_date | ++++ | 1 | python Study | | | 2 | mysql Study | | | 3 | linux Study | | ++++ 3 rows in set ( sec) mysql> select * from authors; +++ | id | name | +++ | 1 | Jack | | 2 | Marry | | 3 | Jerru | +++ 3 rows in set ( sec) mysql> select * from book_m2m_author; ++++ | id | books_id | authors_id | ++++ | 1 | 2 | 1 | | 2 | 2 | 3 | | 3 | 2 | 2 | | 4 | 3 | 3 | | 5 | 3 | 2 | | 6 | 1 | 1 | | 7 | 1 | 3 | ++++ 7 rows in set ( sec)
Data content

5, Query data

# -*- coding: UTF-8 -*-import m2m_orm from m2m_orm import Author from m2m_orm import Book from sqlalchemy.orm import sessionmaker # Establish session Conversation Session_class = sessionmaker(bind=m2m_orm.engine) # generate session Example session = Session_class() print("Check related books through the author table".center(30, '-')) author_obj = session.query(Author).filter(Author.name=='Jack').first() print(author_obj.name, author_obj.books, author_obj.books[0].pub_date) print("Check related authors through book list".center(30, '-')) book_obj = session.query(Book).filter(Book.id==2).first() print(book_obj.name, book_obj.authors) # output Check related books through the author table Jack [python Study, mysql Study] Check related authors through book list mysql Study [Jack, Marry, Jerru]
Query data

6, Delete data

When deleting data, do not worry about boo M2M authors. sqlalchemy will automatically delete the corresponding data for you

delete the author through the book

author_obj = session.query(Author).filter(Author.name=='Jack').first() book_obj = session.query(Book).filter(Book.id==2).first() print(author_obj.name) print(book_obj.authors) book_obj.authors.remove(author_obj) print(book_obj.authors) session.commit() # output Jack [Jack, Marry, Jerru] [Marry, Jerru]

delete the author directly

author_obj = session.query(Author).filter(Author.name=='Jack').first() print(author_obj.name) session.delete(author_obj) session.commit()
mysql> select * from authors; +++ | id | name | +++ | 2 | Marry | | 3 | Jerru | +++ 2 rows in set ( sec) mysql> select * from books; ++++ | id | name | pub_date | ++++ | 1 | python Study | | | 2 | mysql Study | | | 3 | linux Study | | ++++ 3 rows in set ( sec) mysql> select * from book_m2m_author; ++++ | id | books_id | authors_id | ++++ | 2 | 2 | 3 | | 3 | 2 | 2 | | 4 | 3 | 3 | | 5 | 3 | 2 | | 7 | 1 | 3 | ++++ 5 rows in set ( sec) # This is to delete the author directly from the table

Tags: PythonSessionMySQLLinux

Posted on Mon, 04 May by NorthWestSimulations

Sours: https://programmer.help/blogs/sqlalchemy-many-to-many-relationship.html

Many sqlalchemy many to

SuryaSankar/M2M_Association_SQLalchemy.py

importsqlalchemyfromsqlalchemy.ext.declarativeimportdeclarative_basefromsqlalchemyimportColumn, Integer, String, ForeignKeyfromsqlalchemy.ormimportsessionmaker, relationship, backreffromsqlalchemy.ext.associationproxyimportassociation_proxyimportuuidengine=sqlalchemy.create_engine('sqlite:///:memory:')Base=declarative_base()classOrder_Product(Base):__tablename__='order_product'id=Column(String(35), primary_key=True, unique=True)order_id=Column(Integer, ForeignKey('orders.id'), primary_key=True)product_id=Column(Integer, ForeignKey('products.id'), primary_key=True)quantity=Column(Integer)order=relationship("Order", backref=backref("order_products", cascade="all, delete-orphan" ))product=relationship("Product", backref=backref("order_products", cascade="all, delete-orphan" ))def__init__(self, order=None, product=None, quantity=None):self.id=uuid.uuid4().hexself.order=orderself.product=productself.quantity=quantitydef__repr__(self):return'<Order_Product {}>'.format(self.order.name+" "+self.product.name)classProduct(Base):__tablename__='products'id=Column(String(35), primary_key=True, unique=True)name=Column(String(80), nullable=False)orders=relationship("Order", secondary="order_product", viewonly=True)def__init__(self, name):self.id=uuid.uuid4().hexself.name=nameself.orders=[]def__repr__(self):return'<Product {}>'.format(self.name)classOrder(Base):__tablename__='orders'id=Column(String(35), primary_key=True, unique=True)name=Column(String(80), nullable=False)products=relationship("Product", secondary="order_product", viewonly=True)defadd_products(self, items):forproduct, qtyinitems:self.order_products.append(Order_Product(order=self, product=product, quantity=qty))def__init__(self, name):self.id=uuid.uuid4().hexself.name=nameself.products=[]def__repr__(self):return'<Order {}>'.format(self.name)Base.metadata.create_all(engine)Session=sessionmaker(bind=engine)session=Session()prod1=Product(name="Oreo")prod2=Product(name="Hide and Seek")prod3=Product(name="Marie")prod4=Product(name="Good Day")session.add_all([prod1, prod2, prod3, prod4])session.commit()order1=Order( name="First Order")order2=Order( name="Second Order")order1.add_products([ (prod1,4) , (prod2,5) , (prod3,4) ])order2.add_products([ (prod2,6) , (prod1,1) , (prod3,2), (prod4,1) ])session.commit()print"Products array of order1: "printorder1.productsprint"Products array of order2: "printorder2.productsprint"Orders array of prod1: "printprod1.ordersprint"Orders array of prod2: "printprod2.ordersprint"Orders array of prod3: "printprod3.ordersprint"Orders array of prod4: "printprod4.ordersprint"Order_Products Array of order1 : "printorder1.order_productsprint"Order_Products Array of prod1 : "printprod1.order_products
Sours: https://gist.github.com/SuryaSankar/
Creating One-To-Many Relationships in Flask-SQLAlchemy

Declaring Models¶

Generally Flask-SQLAlchemy behaves like a properly configured declarative base from the extension. As such we recommend reading the SQLAlchemy docs for a full reference. However the most common use cases are also documented here.

Things to keep in mind:

  • The baseclass for all your models is called . It’s stored on the SQLAlchemy instance you have to create. See Quickstart for more details.

  • Some parts that are required in SQLAlchemy are optional in Flask-SQLAlchemy. For instance the table name is automatically set for you unless overridden. It’s derived from the class name converted to lowercase and with “CamelCase” converted to “camel_case”. To override the table name, set the class attribute.

Simple Example¶

A very simple example:

classUser(db.Model):id=db.Column(db.Integer,primary_key=True)username=db.Column(db.String(80),unique=True,nullable=False)email=db.Column(db.String(),unique=True,nullable=False)def__repr__(self):return'<User %r>'%self.username

Use to define a column. The name of the column is the name you assign it to. If you want to use a different name in the table you can provide an optional first argument which is a string with the desired column name. Primary keys are marked with . Multiple keys can be marked as primary keys in which case they become a compound primary key.

The types of the column are the first argument to . You can either provide them directly or call them to further specify them (like providing a length). The following types are the most common:

One-to-Many Relationships¶

The most common relationships are one-to-many relationships. Because relationships are declared before they are established you can use strings to refer to classes that are not created yet (for instance if defines a relationship to which is declared later in the file).

Relationships are expressed with the function. However the foreign key has to be separately declared with the class:

classPerson(db.Model):id=db.Column(db.Integer,primary_key=True)name=db.Column(db.String(50),nullable=False)addresses=db.relationship('Address',backref='person',lazy=True)classAddress(db.Model):id=db.Column(db.Integer,primary_key=True)email=db.Column(db.String(),nullable=False)person_id=db.Column(db.Integer,db.ForeignKey('person.id'),nullable=False)

What does do? That function returns a new property that can do multiple things. In this case we told it to point to the class and load multiple of those. How does it know that this will return more than one address? Because SQLAlchemy guesses a useful default from your declaration. If you would want to have a one-to-one relationship you can pass to .

Since a person with no name or an email address with no address associated makes no sense, tells SQLAlchemy to create the column as . This is implied for primary key columns, but it’s a good idea to specify it for all other columns to make it clear to other people working on your code that you did actually want a nullable column and did not just forget to add it.

So what do and mean? is a simple way to also declare a new property on the class. You can then also use to get to the person at that address. defines when SQLAlchemy will load the data from the database:

  • / (which is the default, but explicit is better than implicit) means that SQLAlchemy will load the data as necessary in one go using a standard select statement.

  • / tells SQLAlchemy to load the relationship in the same query as the parent using a statement.

  • works like but instead SQLAlchemy will use a subquery.

  • is special and can be useful if you have many items and always want to apply additional SQL filters to them. Instead of loading the items SQLAlchemy will return another query object which you can further refine before loading the items. Note that this cannot be turned into a different loading strategy when querying so it’s often a good idea to avoid using this in favor of . A query object equivalent to a dynamic relationship can be created using while still being able to use lazy or eager loading on the relationship itself as necessary.

How do you define the lazy status for backrefs? By using the function:

classPerson(db.Model):id=db.Column(db.Integer,primary_key=True)name=db.Column(db.String(50),nullable=False)addresses=db.relationship('Address',lazy='select',backref=db.backref('person',lazy='joined'))

Many-to-Many Relationships¶

If you want to use many-to-many relationships you will need to define a helper table that is used for the relationship. For this helper table it is strongly recommended to not use a model but an actual table:

tags=db.Table('tags',db.Column('tag_id',db.Integer,db.ForeignKey('tag.id'),primary_key=True),db.Column('page_id',db.Integer,db.ForeignKey('page.id'),primary_key=True))classPage(db.Model):id=db.Column(db.Integer,primary_key=True)tags=db.relationship('Tag',secondary=tags,lazy='subquery',backref=db.backref('pages',lazy=True))classTag(db.Model):id=db.Column(db.Integer,primary_key=True)

Here we configured to be loaded immediately after loading a Page, but using a separate query. This always results in two queries when retrieving a Page, but when querying for multiple pages you will not get additional queries.

The list of pages for a tag on the other hand is something that’s rarely needed. For example, you won’t need that list when retrieving the tags for a specific page. Therefore, the backref is set to be lazy-loaded so that accessing it for the first time will trigger a query to get the list of pages for that tag. If you need to apply further query options on that list, you could either switch to the strategy - with the drawbacks mentioned above - or get a query object using and then use it exactly as you would with the query object from a dynamic relationship.

Sours: https://flask-sqlalchemy.palletsprojects.com/en/2.x/models/

You will also like:

Many to Many Relationships



Many to Many relationship between two tables is achieved by adding an association table such that it has two foreign keys - one from each table’s primary key. Moreover, classes mapping to the two tables have an attribute with a collection of objects of other association tables assigned as secondary attribute of relationship() function.

For this purpose, we shall create a SQLite database (mycollege.db) with two tables - department and employee. Here, we assume that an employee is a part of more than one department, and a department has more than one employee. This constitutes many-to-many relationship.

Definition of Employee and Department classes mapped to department and employee table is as follows −

from sqlalchemy import create_engine, ForeignKey, Column, Integer, String engine = create_engine('sqlite:///mycollege.db', echo = True) from sqlalchemy.ext.declarative import declarative_base Base = declarative_base() from sqlalchemy.orm import relationship class Department(Base): __tablename__ = 'department' id = Column(Integer, primary_key = True) name = Column(String) employees = relationship('Employee', secondary = 'link') class Employee(Base): __tablename__ = 'employee' id = Column(Integer, primary_key = True) name = Column(String) departments = relationship(Department,secondary='link')

We now define a Link class. It is linked to link table and contains department_id and employee_id attributes respectively referencing to primary keys of department and employee table.

class Link(Base): __tablename__ = 'link' department_id = Column( Integer, ForeignKey('department.id'), primary_key = True) employee_id = Column( Integer, ForeignKey('employee.id'), primary_key = True)

Here, we have to make a note that Department class has employees attribute related to Employee class. The relationship function’s secondary attribute is assigned a link as its value.

Similarly, Employee class has departments attribute related to Department class. The relationship function’s secondary attribute is assigned a link as its value.

All these three tables are created when the following statement is executed −

Base.metadata.create_all(engine)

The Python console emits following CREATE TABLE queries −

CREATE TABLE department ( id INTEGER NOT NULL, name VARCHAR, PRIMARY KEY (id) ) CREATE TABLE employee ( id INTEGER NOT NULL, name VARCHAR, PRIMARY KEY (id) ) CREATE TABLE link ( department_id INTEGER NOT NULL, employee_id INTEGER NOT NULL, PRIMARY KEY (department_id, employee_id), FOREIGN KEY(department_id) REFERENCES department (id), FOREIGN KEY(employee_id) REFERENCES employee (id) )

We can check this by opening mycollege.db using SQLiteStudio as shown in the screenshots given below −

Department TableEmployee TableLink Table

Next we create three objects of Department class and three objects of Employee class as shown below −

d1 = Department(name = "Accounts") d2 = Department(name = "Sales") d3 = Department(name = "Marketing") e1 = Employee(name = "John") e2 = Employee(name = "Tony") e3 = Employee(name = "Graham")

Each table has a collection attribute having append() method. We can add Employee objects to Employees collection of Department object. Similarly, we can add Department objects to departments collection attribute of Employee objects.

e1.departments.append(d1) e2.departments.append(d3) d1.employees.append(e3) d2.employees.append(e2) d3.employees.append(e1) e3.departments.append(d2)

All we have to do now is to set up a session object, add all objects to it and commit the changes as shown below −

from sqlalchemy.orm import sessionmaker Session = sessionmaker(bind = engine) session = Session() session.add(e1) session.add(e2) session.add(d1) session.add(d2) session.add(d3) session.add(e3) session.commit()

Following SQL statements will be emitted on Python console −

INSERT INTO department (name) VALUES (?) ('Accounts',) INSERT INTO department (name) VALUES (?) ('Sales',) INSERT INTO department (name) VALUES (?) ('Marketing',) INSERT INTO employee (name) VALUES (?) ('John',) INSERT INTO employee (name) VALUES (?) ('Graham',) INSERT INTO employee (name) VALUES (?) ('Tony',) INSERT INTO link (department_id, employee_id) VALUES (?, ?) ((1, 2), (3, 1), (2, 3)) INSERT INTO link (department_id, employee_id) VALUES (?, ?) ((1, 1), (2, 2), (3, 3))

To check the effect of above operations, use SQLiteStudio and view data in department, employee and link tables −

Department Table DataEmployee Table DataLink Table Data

To display the data, run the following query statement −

from sqlalchemy.orm import sessionmaker Session = sessionmaker(bind = engine) session = Session() for x in session.query( Department, Employee).filter(Link.department_id == Department.id, Link.employee_id == Employee.id).order_by(Link.department_id).all(): print ("Department: {} Name: {}".format(x.Department.name, x.Employee.name))

As per the data populated in our example, output will be displayed as below −

Department: Accounts Name: John Department: Accounts Name: Graham Department: Sales Name: Graham Department: Sales Name: Tony Department: Marketing Name: John Department: Marketing Name: Tony
Sours: https://www.tutorialspoint.com/sqlalchemy/sqlalchemy_orm_many_to_many_relationships.htm


4369 4370 4371 4372 4373