Python Forum
flask sqlite jinja accessing and updating database help
Thread Rating:
  • 0 Vote(s) - 0 Average
  • 1
  • 2
  • 3
  • 4
  • 5
flask sqlite jinja accessing and updating database help
#1
Hi,
In my web app I have different devices that are controled by raspi GPIOs, the "name" "pin" and "state" of each is held in a sqlite database. I am following the flaskr blog tutorial as the set up. So I have a app factory and db.py files as per the tutorial, I have used the code sucessfully to be able to add devices but now I want to be able to update the devices status so I can turn on/off the device all the tutorials showing this use a dictionary for the pins (ie "for pin in pins") this works great. Below is my code:
@bp.route('/valves')
def valves(): #finds the device and sends these to the html page
    db = get_db()
    valves = db.execute(
        'SELECT Pin, Name, State FROM valves'
    ).fetchall()
    '''for valve in valves:
      valves[valve]['state'] = GPIO.input(valve)'''
    return render_template('valves/valves.html', valves=valves)

@bp.route('/addValve', methods=('GET', 'POST'))
def addValve():  #adds a new device to the database
    if request.method == 'POST':
        Pin = request.form['Pin']
        Name = request.form['Name']
        upDown = request.form['upDown']
        State = request.form['State']
        error = None

        if not State:
            error = 'set to GPIO.LOW'

        if error is not None:
            flash(error)
        else:
            db = get_db()
            db.execute(
                'INSERT INTO valves (Pin, Name, upDown, State)'
                ' VALUES (?, ?, ?, ?)',
                (Pin, Name, upDown, State)
            )
  db.commit()
            return redirect(url_for('valves.addValve'))

    return render_template('valves/addValve.html')

def get_valve(Pin): # not sure really this is my interpriation of the flaskr tutorial to find the device in db
    valve = get_db().execute(
        'SELECT Pin, State'
        ' FROM valves'
        ' WHERE Pin = ?',
        (Pin,)
    ).fetchone()

    return valve


 @bp.route("/ups/<changeState>/<action>")
def action(changeVanne, action): #this is the part that must change the state of the pin in the database and 
                                   tell pi to activate the GPIO
    valve = get_valve(pin)
    changeState = int(changeState)
    deviceName = valves[changeValve]['Pin']
    if action == "change":
        GPIO.output(changeState, GPIO.HIGH)
        message = "Changed " + deviceName + " state."


    for valve in valves:
        valves[valve]['state'] = GPIO.input(up)


    templateData ={
        'message' : message,
        'valve' : valve
    }

    return render_template('valves/valves.html', **templateData)
this is the error message
Error:
192.168.0.12 - - [09/Feb/2019 17:17:58] "GET /%3Csqlite3.Row%20object%20at%200xb600f3b0%3E/change HTTP/1.1" 404 -
in the first function I have this '''for valve in valves:
valves[valve]['state'] = GPIO.input(valve)''' which I have had to code out as this throughs errors as well. here is the template code;
{% extends 'base.html' %}

{% block header %}
  <h1>{% block title %}Valves{% endblock %}</h1>
    <a class="action" href="{{ url_for('valves.addValve') }}">New Device</a>
{% endblock %}
{% block content %}
  {% for valve in valves %}
   <article class="content">
      <header>
        <div>
          <h1>{{ valve['Name'] }}</h1>
            <div class="about">The state of  {{ valve['Name'] }} is {{ valve['State'] }} change it(<a href="/{{valve}}/change">change</a>)</div>
        </div>
        </div>
       </header>
    </article>
    {% if not loop.last %}
      <hr>
    {% endif %}
  {% endfor %}
   {% if message %}
   <h2>{{ message }}</h2>
   {% endif %}
{% endblock %}
So in brief I need advise on how to put this right!!
Help
Regards
Paul

I have changed some of the code for valves.py
@bp.route("/<int:Pin>/<action>")
def action(changeState, action):
    valve = get_valve(Pin)
    deviceName = valves[valve]['Pin']
    if action == "change":
        GPIO.output(Pin, GPIO.HIGH)
        message = "Changed " + deviceName + " state."

    for valve in valves:
        valves[valve]['state'] = GPIO.input(changed)


    templateData ={
        'message' : message,
        'valve' : valve
    }

    return render_template('valves/valves.html', **templateData)
and valves.html to
{% for valve in valves %}
   <article class="content">
      <header>
        <div>
          <h1>{{ valve['Name'] }}</h1>
            <div class="about">The state of  {{ valve['Name'] }} is {{ valve['State'] }} change it(<a href="/{{ valve['Pin'] }}/change">change</a>)</div>
        </div>
        </div>
       </header>
    </article>
    {% if not loop.last %}
the error is now:
Error:
192.168.0.12 - - [09/Feb/2019 20:35:14] "GET /valves HTTP/1.1" 200 - [2019-02-09 20:35:19,333] ERROR in app: Exception on /24/change [GET] Traceback (most recent call last): File "/usr/local/lib/python2.7/dist-packages/flask/app.py", line 2292, in wsgi_app response = self.full_dispatch_request() File "/usr/local/lib/python2.7/dist-packages/flask/app.py", line 1815, in full_dispatch_request rv = self.handle_user_exception(e) File "/usr/local/lib/python2.7/dist-packages/flask/app.py", line 1718, in handle_user_exception reraise(exc_type, exc_value, tb) File "/usr/local/lib/python2.7/dist-packages/flask/app.py", line 1813, in full_dispatch_request rv = self.dispatch_request() File "/usr/local/lib/python2.7/dist-packages/flask/app.py", line 1799, in dispatch_request return self.view_functions[rule.endpoint](**req.view_args) TypeError: action() got an unexpected keyword argument 'Pin' 192.168.0.12 - - [09/Feb/2019 20:35:19] "GET /24/change HTTP/1.1" 500 -
the url is for the correct pin and change is the action I want when i click "change" all the valves are displayed along with there current state I would add a screen shot but I am to much of a novice!

I have updated my code again as the last error related to the function get_value(Pin)
def get_valve(id):
    valve = get_db().execute(
        'SELECT Pin, State'
        ' FROM valves'
        ' WHERE Pin = ?',
        (id,)
    ).fetchone()

    return valve

@bp.route("/<changeState>/<change>")
def action(changeState, change):
    valve = get_valve(id)
    changeState = int(changeState)
    deviceName = valves[valve][changeState]
    if action == "change":
        GPIO.output(changeState, GPIO.HIGH)
        message = "Changed " + deviceName + " state."

    for valve in valves:
        valves[valve]['state'] = GPIO.input(valve)
What is missing now is the code to update the database from the last function, any way the error now is;
Error:
File "/home/pi/heating/homeHeating/valves.py", line 60, in action valve = get_valve(id) File "/home/pi/heating/homeHeating/valves.py", line 52, in get_valve (id,) InterfaceError: Error binding parameter 0 - probably unsupported type. 192.168.0.12 - - [09/Feb/2019 21:44:55] "GET /24/change HTTP/1.1" 500 -
If there is anybody out there who can give me a guiding hand, this is an important part for me to understand as i intend to create several webapps where the data base will need to be updated.
regards
paul
Reply
#2
(Feb-09-2019, 05:52 PM)pascale Wrote: If there is anybody out there who can give me a guiding hand, this is an important part for me to understand as i intend to create several webapps where the data base will need to be updated.
It's a little hard to follow as it's not code i can run,so i have not take a deeper look.
A couple of advice use Flask-SQLAlchemy for all DB stuff in Flask,it's very well made easier to use and better error feedback.
Just stop using Python 2.7 today Dodgy,at least Python 3.6 or newer 3.7.
Reply
#3
Dear snippsat, Thank you for your reply

Quote:A couple of advice use Flask-SQLAlchemy for all DB stuff in Flask,it's very well made easier to use and better error feedback.
I have wanted to use flask-SQLAlchemy on this project after following Miguel grinbergs tutorial but this project is in /var/www/html/ of pi and I couldnt install it so abandoned.
Quote:Just stop using Python 2.7 today Dodgy,at least Python 3.6 or newer 3.7.
Is my code 2.7? on my computer I have 3.5 I will upgrade to 3.7
Many thanks for taking a look at my code, I am completely lost and dont know what to do I will have to start again with this part
Warm regards
Paul
Reply
#4
(Feb-10-2019, 05:06 PM)pascale Wrote: Is my code 2.7? on my computer
Your error message dos reveal that you use Python 2.7.
Has to use python3 to execute code,python will call Python 2.7.
Error:
File "/usr/local/lib/python2.7/dist-packages/flask/app.py", line 2292, in wsgi_app
I use pyenv always for my setup on Linux,can look at setup here.
A older tutorial with Python 3.5 on Mint 18.
Raspbian OS Python 3.
Reply
#5
Thanks again snippsat.
I have now installed 3.7 and I will follow your setup for pyenv and use that. I understand what you mean, as I have not been using an environement for this project, I have set up an environement in the new project directory but the python directory is 3.5. I will delete it and follow your link and start again.
Warm regards
Paul
Reply
#6
Hi snippsat I have followed your advice and installed pyenv, It works a treat.
You can ignore this thread as I am now going to start all over again.
What I was hoping to achieve is a web page with a button that when pressed activated a GPIO for 3 seconds and then the GPIO returned to its origional setting and the web page reset so i could press the button again if needed. The pin information will be storeed in a database along with its name etc. and can then be accessed in other parts of the project, The tutorial I was following stored the pin info in nested dictionarys, and I could not understand how to use a database instead.
Warm regards Wink
Paul
Hooked on Python
Reply


Possibly Related Threads…
Thread Author Replies Views Last Post
  Webscrape using RPi and SQlite database, always write the last value in database Armond 0 493 Jul-19-2023, 09:11 PM
Last Post: Armond
  Flask: sqlalchemy.exc.OperationalError: (sqlite3.OperationalError) database is locked pythonpaul32 1 2,028 Apr-04-2023, 07:44 AM
Last Post: Larz60+
  Flask and SQLAlchemy question: Database is being created but tables aren't adding pythonpaul32 3 4,411 Feb-07-2023, 10:48 AM
Last Post: pythonpaul32
  Flask/non-flask database sharing MorganSamage 2 1,136 Feb-03-2023, 12:05 PM
Last Post: MorganSamage
  [Solved] Browser won't parse text from yaml loaded into Jinja SpongeB0B 1 952 Jul-07-2022, 09:37 PM
Last Post: SpongeB0B
  Save JSON data to sqlite database on Django Quin 0 2,804 Mar-26-2022, 06:22 PM
Last Post: Quin
Question Accessing a value of a dictionary with jinja.... SpongeB0B 2 10,326 Aug-06-2021, 09:05 AM
Last Post: ndc85430
  Error updating one to many relationship in Flask/ SQLAlchemy atindra 0 3,300 Apr-15-2021, 10:29 PM
Last Post: atindra
  Jinja sort values from request.form SpongeB0B 2 2,185 Jul-26-2020, 07:41 AM
Last Post: SpongeB0B
  Flask export/upload database table in cvs/xlsx format steve87bg 4 6,758 Jun-19-2020, 01:46 PM
Last Post: steve87bg

Forum Jump:

User Panel Messages

Announcements
Announcement #1 8/1/2020
Announcement #2 8/2/2020
Announcement #3 8/6/2020