Flask_SQLAlchemy is claiming my value is not boolean?

I ran into the following error:

  File "/home/sandbox/.local/lib/python3.6/site-packages/sqlalchemy/sql/sqltypes.py", line 1973, in _strict_as_bool
    raise TypeError("Not a boolean value: %r" % (value,))
sqlalchemy.exc.StatementError: (builtins.TypeError) Not a boolean value: 'True'
[SQL: INSERT INTO projects (status) VALUES (?)]
[parameters: [{'status': 'True'}]]
127.0.0.1 - - [12/May/2022 21:53:22] "POST / HTTP/1.1" 500 -

I tried as boolean input everything ranging from 0|1, FALSE|TRUE, False|True on my main route. I have also tried to put in the boolean values inbetween quotations. What am I doing wrong?

import os
from flask import Flask
from flask import render_template
from flask import request
from flask import redirect
from flask_sqlalchemy import SQLAlchemy

database_file = "sqlite:///DATA/DATA.db"

app = Flask(__name__)
app.config["SQLALCHEMY_DATABASE_URI"] = database_file
db = SQLAlchemy(app)

class Projects(db.Model):
    __tablename__="projects"
    status = db.Column(db.Boolean, default=False, nullable=False, primary_key=True)

    def __repr__(self):
        return f"projects('{self.status}')"

db.create_all()

@app.route("/", methods=["GET", "POST"])
def home():
    if request.form:
        status = Projects(status=request.form.get("status"))
        db.session.add(status)
        db.session.commit()
    return render_template("home.html")

My base route being as follows

{% extends "layout.html" %}

{% block body %}
<h1> Add new project </h1>
    <form method="POST" action="/">
      <select name="status" placeholder="Project Status">
        <option value=False> Not Active  </option>
        <option value=True> Active </option>
        </select>
      <input type="submit" value="Register Data">
    </form>

{% endblock %}

Answer

The problem you have is that the form submission is returning the selection value as a string – literally "True" or "False" – while the SQL driver expects a boolean type.

There is a Python standard library function distutils.util.strtobool which can safely convert a representation of a true or false value into a boolean type, raising a ValueError if someone puts something naughty into your API (this is much preferred to using eval() which shouldn’t be used on untrusted input).

I would update your route to something like the following:

# At the top
from distutils.util import strtobool

@app.route("/", methods=["GET", "POST"])
def home():
    if request.form:
        try:
            form_status = strtobool(request.form.get("status").lower())
            status = Projects(status=form_status)
            db.session.add(status)
            db.session.commit()
        except ValueError:
            # Handle the error - e.g. flash a message to the user
            flash("Invalid input")
    return render_template("home.html")

One thing to note with strtobool is that distutils is now deprecated as of Python 3.10, and will be removed in 3.12. This answer shows the implementation of it as a function, which is quite trivial, so it’s worth including in your own utility functions for any code expected to last beyond Python 3.12.