How to query a database from my Flask python app?

Question

So far I have successfully connected my code to the MariaDB database I want to query:

from flask import Flask, render_template, request, flash
import mysql.connector
from datetime import date
import mariadb

app = Flask(__name__)

conn = mariadb.connect(host='IP', port= 3306, user='user', password='password', database='myDatabase')

cursor = conn.cursor()

result = cursor.execute('SELECT * FROM myTable LIMIT 10')

@app.route('/')
def index():
    return result
    return render_template('index.html')
    
# run the app.
if __name__ == "__main__":
    # Setting debug to True enables debug output. This line should be
    # removed before deploying a production app.
    app.debug = True
    app.run()

How do I get a query to show up on my HTML page of this web app?

Answer 1

You should get it in any tutorial.

---

You have to send result to render_template as argument

@app.route('/')
def index():
    results = result.fetchall() # get all rows 
    return render_template('index.html', data=results)

and next you can use name data in HTML to display it.

{{ data }}

You may use for-loop in template to format it.

<tabel>
{% for row in data %}
<tr>
    {% for item in row %}
      <td>{{ item }}</td>
    {% endfor %}
</tr>
{% endfor %}
</table>

---

In render_template you can use any name - ie. all_values=data - and use {{ all_values }} in HTML