Raspberry Pi Forums

Posted: **Thu Jan 17, 2019 1:25 am**

Hi there

I've followed multiple tutorials to write from sensors to a database and then query this within a webpage.

What I'd like to do is add more queries (so as well as showing temp and humidity, show, say high/low for last 24 hours).

I can do the queries fine (one at a time) - but how do I structure the webpage for more than one query at a time? It just dumps results in rows atm from a single query.

Code: Select all


from flask import Flask, render_template, request
import mysql.connector as mariadb

app = Flask(__name__)

@app.route('/')

def list():
    conn = mariadb.connect(user='user',password='pw',database='weather')
    cur = conn.cursor()
    cur.execute('SELECT TempSensor1, HumidSensor1, ts FROM readings ORDER BY readingID LIMIT 1;')
    rows = cur.fetchall()
    return render_template("temphumid.html", rows = rows)

if __name__ == '__main__':
    app.run(debug = True, host = '0.0.0.0')

and

Code: Select all

<html>
<body>
<h1>Temperature and Humidity<h1>
<table border = 0>
{% for row in rows%}
<tr><td>Current Temperature:<td>{{ row[0]}}<td>degC
<tr><td>Current Humidity:<td>{{ row[1]}}<td>Rel%
<tr><td>Last Reading:<td>{{ row[2]}}
{% endfor %}
</table>
</body>
</html>

So I want to add a new query, say

SELECT TempSensor1 FROM readings ORDER BY TempSensor1 DESC LIMIT 1;

How do I query this in the app and call it in the template?!

Many thanks anyone!

Will

Posted: **Thu Jan 17, 2019 9:54 am**

Do you want the two queries to appear on the same page but a different table or do you want a different page for the results of this new query?

Do you understand what the current code is doing?

Posted: **Thu Jan 17, 2019 4:01 pm**

I want to combine more than one query on the current page.

At the moment it outputs the query as row[0], row [1] etc.

I want to define variables eg {{ temp }}, {{ humid }}, {{ maxtemp }}, {{ maxhumid }} and pass these through.

I don't know how to alter the code so instead of

rows = cur.fetchall()

I can have

{{ temp }} = query 1

{{ maxtemp }} = query 2

Does this make sense?!

Thanks so much

Posted: **Thu Jan 17, 2019 4:11 pm**

rows = cur.fetchall() - I'm pretty sure this is what needs to change (to cur.fetchone??) - something like -

TempSensor1 = cur.fetchone()
HumidSensor1 = cur.fetchone()

*Then new Query for max Temp*

MaxTemp = cur.fetchone()

*Then new Query for max Humid*

MaxHumid = cur.fetchone()

Then pass {{ TempSensor1 }} {{ HumidSensor1 }} {{ MaxTemp }} {{MaxHumid }} to template

Posted: **Thu Jan 17, 2019 4:49 pm**

Think I got there in the end! Prob more elegant way, but thanks. Now to figure out why variable is appearing in html as

(Decimal('22.2999992371'),)

And not just the number - looks like a tuple?

Code: Select all

from flask import Flask, render_template, request
import mysql.connector as mariadb

app = Flask(__name__)

@app.route('/')

def list():
    conn = mariadb.connect(user='usr',password='pw',database='weather')
    cur = conn.cursor()
    cur.execute('SELECT TempSensor1 FROM readings ORDER BY readingID LIMIT 1;')
    CurrentTemp = cur.fetchone()

    cur.execute('SELECT HumidSensor1 FROM readings ORDER BY readingID LIMIT 1;')
    CurrentHumid = cur.fetchone()

    cur.execute('SELECT TS FROM readings ORDER BY readingID LIMIT 1;')
    TS = cur.fetchone()
    return render_template("temphumid.html", CurrentTemp = CurrentTemp, CurrentHumid = CurrentHumid, TS = TS)

Posted: **Thu Jan 17, 2019 5:06 pm**

OK calling TempSensor1[0] removes the other elements

Probably talking to myself at this point

Would still like to combine the three queries into one though... Probably call the one query Current = cur.fetchall() and call {{ Current[0] }}, {{ Current[1] }} etc...

Posted: **Thu Jan 17, 2019 8:43 pm**

Hello,
I assume that your table 'readings' has more than one column. Your query sample point to this direction.
SQL is very flexible. You can write

cur.execute('SELECT TempSensor1,HumidSensor1,TS FROM readings ORDER BY readingID LIMIT 1;')
data = cur.fetchone()
print( "data", data)
print("Temp", data[0])
print("Humi", data[1] )

and read out multiple fields in one run. Be sure to have an index on readingID. And doublecheck your names and replace those I have used.

Raspberry Pi Forums

Using multiple mariaDB queries in Flask webpage

Using multiple mariaDB queries in Flask webpage

Re: Using multiple mariaDB queries in Flask webpage

Re: Using multiple mariaDB queries in Flask webpage

Re: Using multiple mariaDB queries in Flask webpage

Re: Using multiple mariaDB queries in Flask webpage

Re: Using multiple mariaDB queries in Flask webpage

Re: Using multiple mariaDB queries in Flask webpage