MySQL query with Python/Flask

Question

Just got back to a stalled project. I'm learning Python (with Flask) and playing with a script that uses a bit of MySQL as well.

I've got some Python running on a Raspberry Pi that detects bluetooth to see if people are in or out. This writes to a MySQL table and works fine.

I'm trying to read rows back and currently using this:

conn = MySQLdb.connect(host="localhost", user = "xxxxxxx", passwd = "xxxxxxxx", db = "mydb")
                cursor = conn.cursor()
                cursor.execute("select status from occupants WHERE id = '1'")
                data = cursor.fetchall()
                conn.commit()
                if (data[0] == "In"):
                    result = "In"

In my template file I have this:

{% if result == "In" %}
   Do stuff
{% else %}
   Do other stuff

result is always "None" at the moment... probably due to:

def index(iframe=None, result=None, targettemp=None, status=None, inttemp=None, result1=None, result2=None, result3=None, hiveSessionId=None):

I've done a load of searching but I don't even know if I'm searching for the right thing.

Is this just so horribly wrong that it's not worth saving, or is it a simple mistake?

EDIT: This is the whole route in my Python script:

I've changed one bit which is just to set the results to 1. This just tests that they are being passed through to the template, which works. I've also added an else to result. So now when I look at my output, it displays "Blah" which proves that result != "In" even though I can see that it is in the MySQL table.

@app.route('/')
def index(iframe=None, result=None, targettemp=None, status=None, inttemp=None, result1=None, result2=None, result3=None, hiveSessionId=None):
        import requests
        import bluetooth
        import time
        import datetime
        import MySQLdb
    iframe = "http://xx.xx.xx.xx:xxxx/cam/min.php"
    url = "https://api.prod.bgchprod.info:443/omnia/users"
        if 'hiveSessionId' in session:
                hiveSessionId = session['hiveSessionId']
                headers = {
                    'Content-Type': "application/vnd.alertme.zoo-6.1+json",
                    'Accept': "application/vnd.alertme.zoo-6.1+json",
                    'X-Omnia-Client': "Hive Web Dashboard",
                    'X-Omnia-Access-Token': hiveSessionId,
                    'Cache-Control': "no-cache"
                    }
                response = requests.request("GET", url, headers=headers)
                data=response.json()
                if 'errors' in data:
                        return redirect(url_for('hivelogin'))
                conn = MySQLdb.connect(host="localhost", user = "xxxxx", passwd = "xxxxxx", db = "mydb")
                cursor = conn.cursor()
                cursor.execute("select status from occupants WHERE id = '1'")
                data = cursor.fetchall()
                conn.commit()
                if (data[0] == "In"):
                    result = "In"
                else:
                    result = "Blah"
                result1 = 1
                result2 = 1
                result3 = 1

        url = "https://api-prod.bgchprod.info:443/omnia/nodes/0e5f20fb-ab13-4d43-89ed-ec2481ea9012"
                payload = "{\n    \"nodes\": [{\n        \"attributes\": {\n            \"state\": {\"targetValue\": \"OFF\"}\n        }\n    }]\n}"
                headers = {
                'Content-Type': "application/vnd.alertme.zoo-6.1+json",
                'Accept': "application/vnd.alertme.zoo-6.1+json",
                'X-Omnia-Client': "Hive Web Dashboard",
                'X-Omnia-Access-Token': hiveSessionId,
                'Cache-Control': "no-cache",
        }
                responsetemp = requests.request("PUT", url, data=payload, headers=headers)
                data=responsetemp.json()
                inttemp  = (data['nodes'][0]['attributes']['temperature']['reportedValue'])
                inttemp = round(inttemp,1)
        targettemp  = (data['nodes'][0]['attributes']['targetHeatTemperature']['reportedValue'])
        status  = (data['nodes'][0]['attributes']['stateHeatingRelay']['reportedValue'])
        return render_template('inout.html', iframe=iframe, status=status, targettemp=targettemp, inttemp=inttemp, hiveSessionId=hiveSessionId, result=result, result1=result1, result2=result2, result3=result3)
        return redirect(url_for('hivelogin'))

You're not showing how you actually try to pass the value from a view function to the template, we need to see the actual Flask function and what it returns. Unrelated but `conn.commit()` is not necessary for queries that don't modify values (i.e. SELECT) — roganjosh, Oct 30 '18 at 16:14
Ah! I just changed my code to result = (data[0]) and when I display result it says ('In',). So it's definitely reading the table but I don't know why it doesn't just show In without the brackets and stuff. — AndyJ, Oct 31 '18 at 08:59

score 0 · Answer 1 · answered Dec 12 '18 at 07:50

Tuple never equals to string. It is the reason why your result is Blah. And con.commit() is unnecessary, because in ordinary case query does not need to commit. But if you need to disable query cache. How to disable query cache with mysql.connector

cur.execute("select msg from test limit 2")
data = cur.fetchall()
print(data)  # (('test',), ('test',))
if (data[0] == "test"): #data[0] ('test',)
    result = "In"
else:
    result = "Blah"
print(result) # Blah

con.commit()

MySQL query with Python/Flask

1 Answers1