I have created a simple web app that intends to pull data from a sql database (specifically, forest fire occurrences and relevant metadata in the U.S.). I am able to query the database in Python using flask/sqlite3 and print the resulting list of tuples that match the query. However, I am unfamiliar with how I could take the resulting list of results and render it on a google map dynamically. So for every record returned there would be a marker with lat/long and then a dialog box displaying metadata.

Relevant snippet:

@app.route('/results', methods=['POST'])
def results():
    form_input = {
        'start_date': request.form['yearfrom'] + '-' + request.form['monthfrom'] + '-' + request.form['dayfrom']
                      + 'T00:00:00',
        'end_date': request.form['yearto'] + '-' + request.form['monthto'] + '-' + request.form['dayto']
                      + 'T00:00:00',
        'state': request.form['state'],
        'fire_size': request.form['size']
    }
    # Stores only query terms without blank values
    query_terms = {k: v for k, v in form_input.items() if v != 'blank' and k != 'start_date' and k != 'end_date'}

    query_string = 'SELECT latitude,longitude, fire_name, discovery_date, stat_cause_descr, fire_size, state FROM fires' \
                   ' WHERE ' + " ".join(["%s='%s' and" % (key, value) for (key, value)
                    in query_terms.items()]) + ' discovery_date BETWEEN ' + "'%s'" % form_input['start_date'] \
                   + ' and ' + "'%s'" % form_input['end_date']

    query = query_db(query_string, one=False)
    pp.pprint(query)
    return render_template('results.html', fire_name=query[0]['fire_name'])

Printing "query" returns:

[   {   'discovery_date': '2013-01-01T00:00:00',
        'fire_name': 'VOYAGER',
        'fire_size': 0.1,
        'latitude': 34.56083333,
        'longitude': -118.67833333,
        'stat_cause_descr': 'Equipment Use',
        'state': 'CA'},
    {   'discovery_date': '2013-01-01T00:00:00',
        'fire_name': 'MIDDLE',
        'fire_size': 1.0,
        'latitude': 33.077,
        'longitude': -111.75919,
        'stat_cause_descr': 'Arson',
        'state': 'AZ'},
    {   'discovery_date': '2013-01-01T00:00:00',
        'fire_name': 'GLACIER 13',
        'fire_size': 0.5,
        'latitude': 47.624939,
        'longitude': -103.415214,
        'stat_cause_descr': 'Miscellaneous',
        'state': 'ND'},

So retrieving is working as intended. I know in the return statement I would need to specify what terms to render on html...I am just unsure how to iterate through all returned records to display one marker for each. I would greatly appreciate some direction.

I have created a simple web app that intends to pull data from a sql database (specifically, forest fire occurrences and relevant metadata in the U.S.). I am able to query the database in Python using flask/sqlite3 and print the resulting list of tuples that match the query. However, I am unfamiliar with how I could take the resulting list of results and render it on a google map dynamically. So for every record returned there would be a marker with lat/long and then a dialog box displaying metadata.

Relevant snippet:

@app.route('/results', methods=['POST'])
def results():
    form_input = {
        'start_date': request.form['yearfrom'] + '-' + request.form['monthfrom'] + '-' + request.form['dayfrom']
                      + 'T00:00:00',
        'end_date': request.form['yearto'] + '-' + request.form['monthto'] + '-' + request.form['dayto']
                      + 'T00:00:00',
        'state': request.form['state'],
        'fire_size': request.form['size']
    }
    # Stores only query terms without blank values
    query_terms = {k: v for k, v in form_input.items() if v != 'blank' and k != 'start_date' and k != 'end_date'}

    query_string = 'SELECT latitude,longitude, fire_name, discovery_date, stat_cause_descr, fire_size, state FROM fires' \
                   ' WHERE ' + " ".join(["%s='%s' and" % (key, value) for (key, value)
                    in query_terms.items()]) + ' discovery_date BETWEEN ' + "'%s'" % form_input['start_date'] \
                   + ' and ' + "'%s'" % form_input['end_date']

    query = query_db(query_string, one=False)
    pp.pprint(query)
    return render_template('results.html', fire_name=query[0]['fire_name'])

Printing "query" returns:

[   {   'discovery_date': '2013-01-01T00:00:00',
        'fire_name': 'VOYAGER',
        'fire_size': 0.1,
        'latitude': 34.56083333,
        'longitude': -118.67833333,
        'stat_cause_descr': 'Equipment Use',
        'state': 'CA'},
    {   'discovery_date': '2013-01-01T00:00:00',
        'fire_name': 'MIDDLE',
        'fire_size': 1.0,
        'latitude': 33.077,
        'longitude': -111.75919,
        'stat_cause_descr': 'Arson',
        'state': 'AZ'},
    {   'discovery_date': '2013-01-01T00:00:00',
        'fire_name': 'GLACIER 13',
        'fire_size': 0.5,
        'latitude': 47.624939,
        'longitude': -103.415214,
        'stat_cause_descr': 'Miscellaneous',
        'state': 'ND'},

So retrieving is working as intended. I know in the return statement I would need to specify what terms to render on html...I am just unsure how to iterate through all returned records to display one marker for each. I would greatly appreciate some direction.

• javascript
• python
• html
• google-maps
• flask

1 Answer 1

Rather than passing one result to the template, pass the entire collection. Then use Jinja blocks to loop over the results.

return render_template(template, fires=query)

A basic example that just prints each fire name:

{% for fire in fires %}
    {{ fire['fire_name'] }}
{% endfor %}

If you already have a JSON patible collection and need to pass the JSON to JavaScript code, you can use some built in Jinja filters.

var fires = {{ fires|tojson|safe }};

Unrelated, your query is open to injection attacks right now. Rather than using string formatting to insert user input, you should use parameterized queries.

query = 'select * from user where username = ?'
result = cursor.execute(query, ('davidism',))

A much more powerful solution is to use Flask-SQLAlchemy or another ORM instead to map tables to classes and build queries using functions rather than strings.

Unrelated, you'll have a much easier time working with form data by using a form library such as Flask-WTF, which will render forms, and validate and convert input for you.