I'm writting an employee calender website as a practice project but i can't get my SQL conditions to work

I have the following relevent code:

from flask import Flask, render_template, request, redirect, g
from werkzeug.security import check_password_hash, generate_password_hash
from datetime import datetime
import sqlite3

app = Flask(__name__)

DATABASE = 'hoved.db'
#alt database udfyrering

#Henter db resultater
def get_db():
    db = getattr(g,'_database', None)
    if db is None:
        db = g._database = sqlite3.connect(DATABASE)
        db.row_factory = sqlite3.Row
    return db

#kan bruges til at lave en query på data i databasen
def query_db(query, args=(), one=False):
    cur = get_db().execute(query, args)
    all_data = cur.fetchall()
    cur.close()
    return (all_data[0] if all_data else None) if one else all_data

#frigiver resourcer når man er færdig med databasen   
@app.teardown_appcontext
def close_connection(exception):
    db = getattr(g, '_database', None)
    if db is not None:
        db.close() 


@app.route("/", methods=["GET", "POST"])
def index():

    #
    medarbejderenavne = []
    vagthvemhvornår = []

    for user in query_db('select empid, empname from emp'):   
        medarbejderenavne.append(user['empname'])

        vagter = (query_db(
            'SELECT shifts.empid, shifts.empid, weekday, day FROM shifts '
            'JOIN emp ON emp.empid = shifts.empid '
            'WHERE shifts.empid = ?', [user['empid']]))
            
        vagthvemhvornår.append(vagter)
    for shifts in vagthvemhvornår:
        for shift in shifts:
            print(dict(shift))


    if request.method == "GET":
        return render_template("index.html", name=medarbejderenavne, vagter=vagthvemhvornår)
    
    if request.method == "POST":

        return render_template("test.html")

Where in index am i supposed to get a list of dictionarys that i can use in my jinja template

{% for nam in name %}
        <div class="row g-2 ">
          <div class="col-md-1">
            <div class="dates responsivetext">{{nam}}</div>
          </div>
          {%if vagter[loop.index0]['weekday'] == 0 %}
          <div class="col-md-1">
            <div class="cliente responsivetext">vagt</div>
          </div>
          {% endif %}

but i just can't figure out how to run the if statement. I know that weekday in my first dictionary should be 0 according to my database

I'm writting an employee calender website as a practice project but i can't get my SQL conditions to work

I have the following relevent code:

from flask import Flask, render_template, request, redirect, g
from werkzeug.security import check_password_hash, generate_password_hash
from datetime import datetime
import sqlite3

app = Flask(__name__)

DATABASE = 'hoved.db'
#alt database udfyrering

#Henter db resultater
def get_db():
    db = getattr(g,'_database', None)
    if db is None:
        db = g._database = sqlite3.connect(DATABASE)
        db.row_factory = sqlite3.Row
    return db

#kan bruges til at lave en query på data i databasen
def query_db(query, args=(), one=False):
    cur = get_db().execute(query, args)
    all_data = cur.fetchall()
    cur.close()
    return (all_data[0] if all_data else None) if one else all_data

#frigiver resourcer når man er færdig med databasen   
@app.teardown_appcontext
def close_connection(exception):
    db = getattr(g, '_database', None)
    if db is not None:
        db.close() 


@app.route("/", methods=["GET", "POST"])
def index():

    #
    medarbejderenavne = []
    vagthvemhvornår = []

    for user in query_db('select empid, empname from emp'):   
        medarbejderenavne.append(user['empname'])

        vagter = (query_db(
            'SELECT shifts.empid, shifts.empid, weekday, day FROM shifts '
            'JOIN emp ON emp.empid = shifts.empid '
            'WHERE shifts.empid = ?', [user['empid']]))
            
        vagthvemhvornår.append(vagter)
    for shifts in vagthvemhvornår:
        for shift in shifts:
            print(dict(shift))


    if request.method == "GET":
        return render_template("index.html", name=medarbejderenavne, vagter=vagthvemhvornår)
    
    if request.method == "POST":

        return render_template("test.html")

Where in index am i supposed to get a list of dictionarys that i can use in my jinja template

{% for nam in name %}
        <div class="row g-2 ">
          <div class="col-md-1">
            <div class="dates responsivetext">{{nam}}</div>
          </div>
          {%if vagter[loop.index0]['weekday'] == 0 %}
          <div class="col-md-1">
            <div class="cliente responsivetext">vagt</div>
          </div>
          {% endif %}

but i just can't figure out how to run the if statement. I know that weekday in my first dictionary should be 0 according to my database

• python
• html
• sqlite
• flask
• jinja2

1 Answer 1

Pay attention to the structure of your data. Your Jinja code fails because you're querying the "weekday" column from a list of rows, not from a single row. Therefore, the comparison with 0 fails.

Based on the information provided, it's difficult to understand exactly what your goal is. I think you want to display a weekly calendar showing which days each employee has to work.

My example would be as follows, where all shifts are requested in one query and then displayed in a table.

The database schema is this.

CREATE TABLE IF NOT EXISTS emp (
    id  INTEGER PRIMARY KEY AUTOINCREMENT,
    name TEXT NOT NULL
);

CREATE TABLE IF NOT EXISTS shifts (
    id INTEGER PRIMARY KEY AUTOINCREMENT,
    weekday INTEGER NOT NULL, 
    empid INTEGER NOT NULL,
    FOREIGN KEY (empid) REFERENCES emp (empid), 
    UNIQUE (empid, weekday)
);

INSERT INTO emp (name) VALUES ("User 1");
INSERT INTO emp (name) VALUES ("User 2");

INSERT INTO shifts (empid, weekday) VALUES (1, 0);
INSERT INTO shifts (empid, weekday) VALUES (1, 6);
INSERT INTO shifts (empid, weekday) VALUES (2, 4);

The endpoint looks like this.

@app.route('/')
def index():
    week = (
        (1, 'Monday'), 
        (2, 'Tuesday'), 
        (3, 'Wednesday'), 
        (4, 'Thursday'), 
        (5, 'Friday'), 
        (6, 'Saturday'), 
        (0, 'Sunday'),
    )
    data = query_db('''
        SELECT 
            emp.id AS empid, 
            emp.name AS empname, 
            shifts.id AS shiftid, 
            shifts.weekday AS weekday
        FROM emp JOIN shifts ON emp.id = shifts.empid 
        ORDER BY emp.id
    ''')
    return render_template('index.html', **locals()) 

In the corresponding template, this section would be.

<table width="100%" border="1">
  <thead>
    <tr>
      <th>Name</th>
      {% for _, name in week -%}
        <th>{{ name }}</th>
      {% endfor -%}        
    </tr>
  </thead>
  <tbody>
    {% for _, shifts in data|groupby("empid") -%}
    <tr>
      <th>{{ (shifts|first).empname }}</th>
      {% for day, _ in week -%}
        {% set dayshifts = shifts|selectattr("weekday", "equalto", day) -%}
        {% if dayshifts|list -%}
          <td>watch</td>
        {% else -%}
          <td></td>
        {% endif -%}
      {% endfor -%}
    </tr>
    {% endfor -%}
  </tbody>
</table>