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