admin管理员组文章数量:1026989
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.
Share Improve this question asked Jun 11, 2015 at 16:06 jclark754jclark754 1,0142 gold badges14 silver badges32 bronze badges 01 Answer
Reset to default 5Rather 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.
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.
Share Improve this question asked Jun 11, 2015 at 16:06 jclark754jclark754 1,0142 gold badges14 silver badges32 bronze badges 01 Answer
Reset to default 5Rather 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.
本文标签： javascriptRendering SQL data on HTMLJS with FlaskStack Overflow
版权声明：本文标题：javascript - Rendering SQL data on HTMLJS with Flask - Stack Overflow 内容由热心网友自发贡献，该文观点仅代表作者本人， 转载请联系作者并注明出处：http://it.en369.cn/questions/1745656594a2161631.html， 本站仅提供信息存储空间服务，不拥有所有权，不承担相关法律责任。如发现本站有涉嫌抄袭侵权/违法违规的内容，一经查实，本站将立刻删除。
发表评论