python - Handle column names in form for update query with Flask-SQLAlchemy

Question

I'm using Flask and SQLAlchemy and I would like to write an HTML form to perform a database update. However, I would like the user to build the query for convenience:

from a select the user selects the column to be modified, in an input he inserts the new value, from a second select he selects the column for the where clause and in the last input text he inserts the value of the condition.

From a logical point of view I thought of something like this:

exhb = Exhibition.query.filter_by (request.form.get ('wh') = request.form ['cond']). first ()
exhb.request.form.get ('columns') = Request.Form ['val']
db.session.commit ()

But it returns:

SyntaxError: keyword can not be an expression

I tried to use two variables to assign wh and columns but, although the value are read correctly, it returns me:

Entity '<class' connect.Exhibition '>' has no property 'wh'

How can I then simply get the name of the two columns to be used in the query? Are there any built-in methods to do this?

Answer 1

I would suggest you query your database first and pass the row id as the value for your select in html

In your flask app

@app.route('/exhibition', methods=['GET', 'POST'])
def exhibition():
    options = session.query(Exhibition) #however you query your db
    return render_template('exhibition.html', options = options)

in your html (I am guessing you are using jinja for your templating)

<select>
{%for option in options%}
  <option value={{option.id}}>{{option.text}}</option>
{endfor}
</select>

Then you can fetch the row to be modified by the row id which is what you get when the form is posted.

Then when you are updating the row, ensure you specify the specific column to be updated eg

row_to_edit = session.query(Exhibition).filter_by(id = request.form ['input_field']))
#specify what you are updating
row_to_edit.column_to_update = request.form ['input_field']
# Then you can comit column_to_edit

To make sure it is easy for the person editing to identify the column name, and to reduce the burden of having to validate a free text entry matching a column name, i suggest you add a second select field that displays the column names so that free text input is just for the column value The view above becomes

@app.route('/exhibition', methods=['GET', 'POST'])
    def exhibition():
        options = session.query(Exhibition) #however you query your db
        cols = Exhibition.__table__.columns
        return render_template('exhibition.html', options = options, cols = cols)

the second select

<select>
{%for col in cols%}
  <option value={{col.key}}>{{col.key}}</option>
{endfor}
</select>

You can make the option text more readable by iterating through the cols and coming up with a list with a value and readable text for the option

specify the column you are updating like this

row_to_edit.getattr(row_to_edit, request.form ['select_field_for_column'] = request.form ['input_field_column_value']

I know the first part is confusing. ordinarily you would do it row_to_edit.column_to_update, but since in our case the column to update is a variable we use getattr() to get the col name equal to the variable

If you are familiar with wtforms, or you are a fast learner, they would give you a more elegant way of handing this

Answer 2

The request.form.get() call in your filter call forms an expression (see docs), which is not permitted. As a result, filter_by() and filter() expect keyword arguments for the left-hand side of the equality operator.

That is, if you place a function call there (request.form.get() in your case), Python will raise the keyword can not be an expression error. In other words, you cannot use the results of your form requests directly.

Rather, you will need to explicitly pass keyword arguments in your code. Given that you've presented a preset list of column options in your form(s), you can handle this set of options explicitly in your view function with something like:

def query_construction_view():
    ....
    wh = request.form.get('wh')
    cond = request.form['cond']
    if wh == 'column_option1':
        exhb = Exhibition.query.filter_by(column_option1=cond)
        ....
    elif wh == 'column_option2':
        exhb = Exhibition.query.filter_by(column_option2=cond)
        ....
    # etc.

Note how the left-hand side of the equals-sign in each filter_by() call is a keyword, rather than a function call or a string. Also observe that you can use variables and expressions for the right-hand side, i.e. your cond arguments taken from the form input.

This is not the most scalable solution as the crucial bits are hard-coded. I don't know what your use case is, but you may be better served by using a pre-packaged database front-end like Flask-Admin to handle database interactions.

See also SQLAlchemy Query API for more on filter() and filter_by().