Issues querying form data using SQLAlchemy

Question

I'm trying to do something real simple (so I thought), but having some problems a couple hours of research has failed to resolve.

Simply put I'm trying to take 2 form data entries(using Flask-WTForms) and query the values entered against a table. A basic search function.

I've managed to get to the point that I can see SQLAlchemy running A query, just not having any luck with it and triggering the rollback().

2016-01-27 03:27:39,127 INFO sqlalchemy.engine.base.Engine BEGIN (implicit)
2016-01-27 03:27:39,128 INFO sqlalchemy.engine.base.Engine SELECT "Motor"."Asset Tag" AS "Motor_Asset             Tag", "Motor"."Horsepower" AS "Motor_Horsepower" 
FROM "Motor" 
WHERE "Motor"."Asset Tag" AND "Motor"."Horsepower"
2016-01-27 03:27:39,128 INFO sqlalchemy.engine.base.Engine {}
2016-01-27 03:27:39,129 INFO sqlalchemy.engine.base.Engine ROLLBACK

Here are some code snippets.

View:

@app.route('/searchassets', methods=['GET', 'POST'])
def searchassets():
    form = SearchAssetsForm()
    results = None
    if request.method == "POST":
        try:
            asset_tag = form.asset_search.data 
            horsepower = form.horsepower_search.data
            results = Motor.query.filter(Motor.asset_tag, Motor.horsepower).all()
        except:
            db.session.rollback()
            flash("Error.")
        return render_template('searchassets.html', form=form, results=results)
return render_template('searchassets.html', form=form, results=results)

Form Model:

class SearchAssetsForm(Form):
asset_search = StringField('asset_search')
horsepower_search = IntegerField('horsepower_search')

Template:

{% extends "basehead.html" %}
{% block content %}
<center>
<br>
<div class="container">
  <h1>Search Asset Attributes Below</h1>
  <br>
  <form action="" method="post" name="submit">
  {{ form.hidden_tag() }}
  <p>
    {{ form.asset_search(placeholder=" Enter Asset Tag") }}
 </p>
 <p>
     {{ form.horsepower_search(placeholder=" Enter Horsepower") }}
 </p>
    <input class="btn btn-default" type="submit" value="Submit">
  </form>
<br>
<table>
<th> </th>
<th>Asset Tag</th>
<th>Horsepower</th>
<tr>
    <td>{% print(results.asset_tag) %}</td>
    <td>{% print(results.horsepower) %}</td>
</tr>
</table>
</center>

{% endblock %}

score 1 · Accepted Answer · answered Jan 27 '16 at 03:47

1

Well, you have a bare except clause that is making the debugging a guessing game. At least, temporarily remove it and see what the actual error is. Or, log the exception and the traceback.

I think you are not using the checks in the filter() correctly, should not it be:

Motor.query.filter(Motor.asset_tag == asset_tag, 
                   Motor.horsepower == horsepower).all()

Or, in case you want filter_by():

Motor.query.filter(asset_tag=asset_tag, 
                   horsepower=horsepower).all()

answered Jan 27 '16 at 03:47

alecxe

462,703
120
1,088
1,195

You were right, it was incorrect syntax on my end. I thought because I specified the Motor model at the beginning of the query it would associate the argument objects on its own. But now I see it had no way of knowing why it should associate the `asset_tag` object with it's `asset_tag` column. A lesson in poor variable choices! I thank you very much. One other quick question; When I change my template formatting to `{% print(results) %}` It successfully prints the data objects just not the column values. I'm guessing my `print(results.asset_tag)` is looking in the wrong place too? – xGlorify Jan 27 '16 at 04:32
@xGlorify glad to help. You would need to iterate over the results in the template and then using a dot notation to access each object in results. Please see http://stackoverflow.com/help/someone-answers. Thanks. – alecxe Jan 27 '16 at 04:41
Don't call `print` in your templates. Use `{{ results }}` instead. The reason `results.asset_tag` doesn't work is that `results` is a list. You need to iterate over it. `{% for result in results %}{{ result.asset_tag }}{% endfor %}`. – dirn Jan 27 '16 at 04:46
Thanks for the input - I actually had it iterating over before I ran into the query syntax problems and I took it out. Popped it back in and everything is working great(ish). One question though; is there a reason `{{ text }}` is considered better practice than `{% print(object.data) %}`? Just curious. – xGlorify Jan 27 '16 at 06:23
@xGlorify from what I recall, there is no print function inside the templates. http://stackoverflow.com/questions/12848286/print-the-value-of-a-variable-in-python-django. Thanks. – alecxe Jan 27 '16 at 10:32

Issues querying form data using SQLAlchemy

1 Answers1