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.
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.