Join in sqlalchemy

Question

Join in sqlalchemy

Navigation

#1 by (2 votes)

1

I am trying to pass a query that I have made - and I already tried it in SQL - to SQLAlchemy in python using Flask.

This is the query in SQL:

select Reclamo.IdReclamo from
Reclamo,ServicioAsignacion,ReclamoServicioAsignacionAfectado where
ServicioAsignacion.IdServicioAsignacion=ReclamoServicioAsignacionAfectado.idServicioAsignacion 
and Reclamo.IdReclamo=ReclamoServicioAsignacionAfectado.idReclamo 
and Reclamo.masivo=1 and Reclamo.estado<>'Solucionado' 
and ServicioAsignacion.IdServicioAsignacion=232750

These are my tables in the model.py :

class reclamo(db.Model):
    __tablename__ = 'Reclamo'
    idreclamo = db.Column('IdReclamo', db.Integer, primary_key=True)
    estado = db.Column('estado', db.String(255))
    idcliente = db.Column('idCliente', db.Integer,
                          db.ForeignKey('Cliente.IdCliente'))
    cliente = db.relationship('cliente', backref='reclamo')
    idservicioasignacion = db.Column('idServicioAsignacion', db.Integer) 
    reclamoservicioasignacionafectado = db.relationship('reclamoservicioasignacionafectado', backref ='reclamo', lazy='dynamic')
    masivo = db.Column('masivo', db.Integer)

class servicioasignacion(db.Model):
    __tablename__ = 'ServicioAsignacion'
    idservicioasignacion = db.Column('IdServicioAsignacion', db.Integer, primary_key = True)
    idcliente = db.Column('idCliente', db.Integer, db.ForeignKey('Cliente.IdCliente'))
    cliente = db.relationship('cliente', backref = 'servicioasignacion')

class reclamoservicioasignacionafectado(db.Model):
    __tablename__ = 'ReclamoServicioAsignacionAfectado'
    idreclamoservicioasignacionafectado = db.Column('idReclamoServicioAsignacionAfectado', db.Integer, primary_key=True)

    idreclamo = db.Column('idReclamo', db.Integer, db.ForeignKey('Reclamo.IdReclamo'))
    idservicioasignacion = db.Column('idServicioAsignacion', db.Integer, db.ForeignKey('ServicioAsignacion.IdServicioAsignacion'))
    servicioasignacion = db.relationship('servicioasignacion', backref= 'reclamoservicioasignacionafectado')
    idcliente = db.Column('idCliente', db.Integer,db.ForeignKey('Cliente.IdCliente'))
    cliente  = db.relationship('cliente', backref = 'reclamoservicioasignacionafectado')

And this is the query I'm trying, but it throws me an error ( TypeError: 'BaseQuery' object is not callable ):

@app.route('/verificarincidencia/<idservice>', methods=['GET'])
@requires_auth
def verificarincidencia_get(idservice):
    if request.method == 'GET':
        checkincidencia = reclamo.query(reclamo,servicioasignacion,reclamoservicioasignacionafectado).join(servicioasignacion).join(reclamoservicioasignacionafectado).filter(servicioasignacion.idservicioasignacion==reclamoservicioasignacionafectado.idservicioasignacion).filter(reclamo.idreclamo==reclamoservicioasignacionafectado.idreclamo).filter(reclamo.masivo==1).filter(reclamo.estado!='Solucionado').filter(servicioasignacion.idservicioasignacion==idservice).all()
        return jsonify( {'reclamo': [elemento.as_dict() for elemento in checkincidencia] })

Since thank you very much (if I get to have the solution I publish it). I'm sure I'm not really understanding the issue of putting together a join in SQLAlchemy but maybe someone can help me.

sql python join flask sqlalchemy

asked by Francisco Puebla 23.09.2016 в 17:00

source

1 answer

Error viewing a report crystal report vs2010 in version vs2012 [closed] Spring STS 3.8.1 runs very slow in Ubuntu 16.04

score 2 · Accepted Answer

I will give an example using a very simple structure. Then it will be your task to apply the same concepts to your needs.

Having some entities with the following relational structure.

How to define them in SQLAlchemy using the Declarative System but with the Added Flask-SQLAlchemy would be like this:

class Cliente(db.Model):

    id = db.Column(db.Integer, primary_key=True)
    nombre = db.Column(db.String(200))  # nombre del cliente

    def __repr__(self):
        return "<Cliente nombre='%s'>" % (self.nombre)


class Servicio(db.Model):

    id = db.Column(db.Integer, primary_key=True)
    nombre = db.Column(db.String(200))  # nombre del servicio

    def __repr__(self):
        return "<Servicio nombre='%s'>" % (self.nombre)


class Estado(db.Model):
    id = db.Column(db.Integer, primary_key=True)
    nombre = db.Column(db.String(200))  # nombre del servicio

    def __repr__(self):
        return "<Estado nombre='%s'>" % (self.nombre)

To save space, I did not define the __tablename__ property in any class. Flask-SQLAlchemy automatically sets it with the name of the class in lower case. Thus, Client will become __tablename__ = 'cliente' .

Finally the class with which the other entities are related.

class Reclamo(db.Model):

    id = db.Column(db.Integer, primary_key=True)
    cliente_id = db.Column(db.Integer, db.ForeignKey('cliente.id'))
    servicio_id = db.Column(db.Integer, db.ForeignKey('servicio.id'))
    estado_id = db.Column(db.Integer, db.ForeignKey('estado.id'))

    # Aqui pasamos el nombre de las clases y no el nombre de la tabla
    servicio = db.relationship('Servicio')
    cliente = db.relationship('Cliente')
    estado = db.relationship('Estado')

    def __repr__(self):
        return "<Reclamo id='%s'>" % (self.id)

Queries

To send a join that includes all the tables and return all the records of the entity Reclamo , you would do it like this:

Notice that we are using the query method of the session and not the Claim table. We pass all the tables that we want to be part of the SELECT and then in the method join the claims on which we want to make the JOIN .

db.session.query(Reclamo, Servicio, Cliente, Estado).\
    join(Reclamo.servicio, Reclamo.cliente, Reclamo.estado).\
    all()

If we want to apply a clause WHERE we add a filter using filter :

db.session.query(Reclamo, Servicio, Cliente, Estado).\
    join(Reclamo.servicio, Reclamo.cliente, Reclamo.estado).\
    filter(Estado.id == 1).\
    all()

The above will return only the claims that have id equal to 1. We can apply as many filters as we want. Which is equivalent to adding the operator AND after WHERE .

Result returned

In all cases, a list of tuples is returned, and these tuples contain the instances of the classes (tables) passed to the query method. Assuming that we already have data in the database, if we print in the console it should appear:

[(<Reclamo id='1'>, <Servicio nombre='Internet'>, <Estado nombre='abierto'>, <Cliente nombre='Maria'>),
 (<Reclamo id='2'>, <Servicio nombre='Internet'>, <Estado nombre='abierto'>, <Cliente nombre='Mirta'>)]

Display precompiled SQL code in console:

We have to enable the echo mode of the engine used in the conccion:

# de forma directa
db.engine.echo = True

# agregando la configuración en Flask
app.config['SQLALCHEMY_ECHO'] = True

Note

Adding all the possibilities offered by SQLAlchemy when making inquiries would greatly lengthen this response. Above I just put the basic ways to send requests using JOIN to the database.