flask sqlite jinja accessing and updating database help

[pascale]

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

[snippsat]

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 ,at least Python 3.6 or newer 3.7.

[pascale]

Dear snippsat, Thank you for your reply

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.

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

[snippsat]

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.

[pascale]

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

[pascale]

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
Paul
Hooked on Python