Python - Template 4Geeks Academy - Quick Start

many_to_many.md

Many to many relationships

Example 1, using Table

from flask_sqlalchemy import SQLAlchemy


db = SQLAlchemy()


user_fav_planet = db.Table('user_fav_planet',
                           db.Column('user_id', db.Integer, db.ForeignKey('user.id')),
                           db.Column('planet_id', db.Integer, db.ForeignKey('planet.id')))


class User(db.Model):
    id = db.Column(db.Integer, primary_key=True)
    email = db.Column(db.String(120), unique=True, nullable=False)
    password = db.Column(db.String(80), unique=False, nullable=False)
    is_active = db.Column(db.Boolean(), unique=False, nullable=False)
    user_favorite_planet = db.relationship('Planet', secondary=user_fav_planet)  # Favoritos

    def __repr__(self):
        return '<User %r>' % self.id

    def serialize(self):
        return {"id": self.id,
                "email": self.email,
                "is_active": self.is_active}


class Planet(db.Model):
    id = db.Column(db.Integer, primary_key=True)
    name = db.Column(db.String(250), nullable=False)
    rotation_period = db.Column(db.String(20))
    orbital_period = db.Column(db.String(20))
    diameter = db.Column(db.String(20))
    climate = db.Column(db.String(100))
    gravity = db.Column(db.String(100))
    terrain = db.Column(db.String(100))
    surface_water = db.Column(db.String(20))
    population = db.Column(db.String(20))

    def __repr__(self):
        return '<Planet %r>' % self.id

    def serialize(self):
        return {"id": self.id,
                "name": self.name,
                "rotation_period": self.rotation_period,
                "orbital_period": self.orbital_period,
                "diameter": self.diameter,
                "climate": self.climate,
                "gravity": self.gravity,
                "terrain": self.terrain,
                "surface_water": self.surface_water,
                "population": self.population}

Example 2, using Model

from sqlalchemy import Column, ForeignKey, Integer, String, Float, Boolean
from sqlalchemy.ext.declarative import declarative_base
from sqlalchemy.orm import relationship
from sqlalchemy import create_engine
from eralchemy2 import render_er


Base = declarative_base()


class User(Base):
    __tablename__ = 'user'
    id = Column(Integer, primary_key=True)
    username = Column(String(50), nullable=False, unique=True)
    email = Column(String(100), nullable=False) 
    password = Column(String(15), nullable=False)
    # Relationship
    # planets_favourites = relationship('Favourites_planets')
    # characters_favourites = relationship('Favourites_characters')


class Character(Base):
    __tablename__ = 'character'
    id = Column(Integer, primary_key=True)
    name = Column(String(50), nullable=False)
    height = Column(Integer)
    gender = Column(Boolean(), nullable=False)
    eyes_color = Column(String(15), nullable=False)
    hair_color = Column(String(15), nullable=False)
    skin_color = Column(String(15))
    birth_year = Column(String(10))
    # Relationship
    # characters_favourites = relationship('Favourites_characters')


class Planet(Base):
    __tablename__ = 'planet'
    id = Column(Integer, primary_key=True)
    name = Column(String(50), nullable=False)
    mass = Column(Float)
    climate = Column(String(250))
    terrain = Column(String(250))
    gravity = Column(String(250))
    diameter = Column(Integer)
    rotation = Column(Integer)
    population = Column(Integer)
    translation = Column(Integer)
    # Relationship
    # planets_favourites = relationship('Favourites_planets')


class CharacterFavorites(Base):
    __tablename__ = 'characterfavorites'
    id = Column(Integer, primary_key=True)
    # FK and Relationship User
    user_id = Column(Integer, ForeignKey('user.id'))
    user = relationship(User)
    # FK and Relationship Character
    character_id = Column(Integer, ForeignKey('character.id'))
    character = relationship(Character)


class PlanetFavorites(Base):
    __tablename__ = 'planetfavorites'
    id = Column(Integer, primary_key=True)
    # FK and Relationship User
    user_id = Column(Integer, ForeignKey('user.id'))
    user = relationship(User)
    # FK and Relationship Planet
    planet_id = Column(Integer, ForeignKey('planet.id'))
    planet = relationship(Planet)