0

I'm trying to add a new user using marshmallow_sqlalchemy and sqlalchemy to my database. This user has a many to many relationship with my profile table but I dont know how to do it for many to many relationship. I would like to know if this is a bad approach and if you guys have any tutorial about it.

Thanks in advance

def post_user(user):

    login = usuario.get('login')
    query = User.query.filter(User.login == login).one_or_none()

    if query is None:
        schema = UserSchema()
        new_user = schema.load(user, session=session).data
        session.add(new_user)
        session.commit(new_user)

        return schema.dump(new_user).data, 201

    else:
        abort(409, 'Usuario já existe')

this is the data I'm trying to post

{
  "email": "diogo.silva@enforcegroup.com.br",
  "id": 1,
  "id_pessoa": "husdg25-14sde5s4",
  "login": "diogo.silva",
  "perfil": {
     "area": {
     "id": 2,
     "nome": "NPL"
   },
   "id": 2,
    "nome": "Junior",
    "sistema": {
    "id": 2,
    "nome": "xgracco"
    }
  },
  "primeiro_nome": "Diogo",
  "ramal": "398",
  "responsavel_id": 2,
  "ultimo_nome": "Silva"
}

My models

perfil_sistema = Table('perfil_sistema', Base.metadata,
    Column('perfil_id', Integer, ForeignKey('tb_perfil.id')),
    Column('sistema_id', Integer, ForeignKey('tb_sistema.id'))
)

perfil_area = Table('perfil_area', Base.metadata,
    Column('perfil_id', Integer, ForeignKey('tb_perfil.id')),
    Column('area_id', Integer, ForeignKey('tb_area.id'))
)

class Perfil(Base):
    __tablename__ = 'tb_perfil'

    id = Column(Integer, primary_key=True)
    nome = Column(String(100), nullable=True)
    created_at = Column(TIMESTAMP, default=datetime.now)
    updated_at = Column(TIMESTAMP, default=datetime.now, onupdate=datetime.now)
    sistema = relationship('Sistema', secondary=perfil_sistema, backref="sistemas")
    area = relationship('Area', secondary=perfil_area, backref="areas")


    def __init__(self, nome, sistema, area):
        self.nome = nome
        self.sistema = sistema
        self.area = area

    def update_to_db(self):
        db_session.merge(self)
        db_session.commit()

    def save_to_db(self):
        db_session.add(self)
        db_session.commit()

    def delete_from_db(self):
        db_session.delete(self)
        db_session.commit()


class PerfilSchema(ModelSchema):

    sistema = fields.Nested(SistemaSchema,  many=True)
    area =  fields.Nested(AreaSchema,  many=True)

    class Meta:
        model = Perfil
        sqla_session = db_session


class Sistema(Base):

    __tablename__ = 'tb_sistema'

    id = Column(Integer, primary_key=True)
    nome = Column(String(100), nullable=False)
    created_at = Column(TIMESTAMP, default=datetime.now)
    updated_at = Column(TIMESTAMP, default=datetime.now, onupdate=datetime.now)

    def update_to_db(self):
        db_session.merge(self)
        db_session.commit()

    def save_to_db(self):
        db_session.add(self)
        db_session.commit()

    def delete_from_db(self):
        db_session.delete(self)
        db_session.commit()

 class SistemaSchema(ModelSchema):
    class Meta:
        model = Sistema
        sqla_session = db_session


class Area(Base):
    __tablename__ = 'tb_area'

    id = Column(Integer, primary_key=True)
    nome = Column(String(100), nullable=True)
    ad = Column(String(100), nullable=True)
    created_at = Column(TIMESTAMP, default=datetime.now)
    updated_at = Column(TIMESTAMP, default=datetime.now, onupdate=datetime.now)

   def __init__(self, nome):
        self.nome = nome

   def update_to_db(self):
        db_session.merge(self)
        db_session.commit()

   def save_to_db(self):
        db_session.add(self)
        db_session.commit()

   def delete_from_db(self):
        db_session.delete(self)
        db_session.commit()


class AreaSchema(ModelSchema):
    class Meta:
        model = Area
        sqla_session = db_session
Diogo Silva
  • 195
  • 3
  • 17
  • What is exactly your User model? Do you want the many2many relationship be filled automatically and generically with your schema load, or are you fine with adding two custom lines (one to request the profile, and one to add the profile to your user). Useful in the second case: https://docs.sqlalchemy.org/en/13/orm/basic_relationships.html – Rémi Bonnet May 03 '19 at 19:51
  • sorry I added the models, thanks for your help I will read the docs – Diogo Silva May 03 '19 at 20:31

0 Answers0