I need to dynamically pass to the "query" object of SQLAlchemy the parameters of the class next to its attributes

Question

I need to dynamically pass to the "query" object of SQLAlchemy the parameters of the class next to its attributes

Navigation

#1 by (1 votes)

2

I need to dynamically arm the part where the .query() gets the class of the objects and their attributes, that is, that within query dynamically insert the tables and columns received from another application. For example:

session.query(CcSipUser.id).all()

I return the column "id" from the table "CcSipUser", if I want other objects I do the following session.query(CcSipUser.id, CcSipUser.name).all() .

But I need to arm the script depending on the values that happen to me, if with that same table they want "name", "id" or another column of the table.

I am using this form also to transform the values that happen to an object with its attribute.

columnasid = getattr(CcSipUser, "id")
columnasusr = getattr(CcSipUser, "username")
session.query(columnasusr, columnasid).all()

Equivalent to what I wrote earlier, but how could I insert them between the parentheses?

python python-2.7 flask sqlalchemy

asked by Nicol Bolivar 19.09.2016 в 17:17

source

1 answer

Divide an image into 4 equal parts using php Angular js how to call two controllers in the same state?

score 1 · Accepted Answer

If I understand correctly, what you want is to dynamically select the columns when generating the SQL query.

What you can do is to inspect the model (class SQLAlchemy) and obtain a list of the properties (references) that you want to pass as a parameter to the method query of SQLAlchemy.

The function:

def column_selector(model, props):
    columns = []
    for prop in props:
        if hasattr(model, prop):
            columns.append(getattr(model, prop))

    return columns

Then this list, you pass it to the method query() the prefix asterisk (*), for example, session.query(*argument).all() . The asterisk "*" is used in Python to define a variable number of arguments

An example of how to use it:

class Entity(database.Model):

    id = database.Column(database.Integer, primary_key=True)
    name = database.Column(database.String)
    lastname = database.Column(database.String)
    age = database.Column(database.Integer)
    gender = database.Column(database.Integer)


@app.route('/')
def index():

    # creamos a lista de columnas (sus nombres) que queremos de una
    # tabla determinada.
    props = ['name', 'gender']


    # pasamos la lista a la funcion
    columns = column_selector(Entity, props)

    # y pasamos la lista de columnas con el prefijo *
    database.session.query(*columns).all()

    # mas codigo ...

    return ':)'

The line database.session.query(*columns).all() equals to:

database.session.query(Person.name, Person.gender).all()

Notes

I've used Flask-SQLAlchemy for the integration between Flask and SQLAlchemy, but the implementation is the same.
You can see the generated SQL code in the console by setting the property echo to True of the object engine.

directly:

engine.echo = True

If you use Flask-SQLAlchemy:

app.config['SQLALCHEMY_ECHO'] = True