This is my first big Flask project, I am building a CRUD app to help manage cases for a relocation company (personal project). I want to have a db table for Companies (ie, our clients), Packages (the packages we offer), and one for the price we charge every company for every package, as this varies.

Unfortunately, I didn’t get very far before encountering a big issue. I am currently working on an “Add Company” page, where the user can add a new customer (company), providing the company name, contact addresses, as well as the prices we will charge that company for each package we offer. The package name and ID form fields are correctly populating the form based on information in the db, leaving the user to add the correct price. However, once I hit submit, it the package name and ID values are “lost”. I have tried so many print statement debugs, and the issue seems to be that the info I send to the form disappears after hitting submit (for some reason the price, provided by the user, persists). If you could take the time to review and assist me in finding a solution, I would be extremely grateful! I have attached the relevant snippets below, full project can be found on github.

Here is the error I get when I hit submit (function cannot access package_id):

sqlalchemy.exc.IntegrityError: (sqlite3.IntegrityError) NOT NULL constraint failed: company_package.package_id
[SQL: INSERT INTO company_package (company_id, package_id, price) VALUES (?, ?, ?) RETURNING id]
[parameters: (14, None, 1.0)]

Python view function:

@app.route("/companies/add_company", methods=["GET", "POST"])
@login_required
def add_company():
    form = NewCompanyForm()

    #fetch all packages and send them to company form package field
    if request.method == "GET":
        # Fetch all packages from db
        packages = Package.query.all()
        for package in packages:
            package_form = PackagePriceForm()
            package_form.package_id.data = package.id
            package_form.package_name.data = package.name
            package_form.price = None
            form.packages.append_entry(package_form)

    #submit the company section of the form
    if form.validate_on_submit() and request.method == "POST":

        company = Company(
            name = form.name.data,
            contact = form.contact.data,
            notes = form.notes.data
        )

        db.session.add(company)
        db.session.flush() # Flush to get the company ID for the relationships

        #pull the price for each package and send to db

        for package_form in form.packages:

            company_package = CompanyPackage(
                company_id = company.id,
                package_id = package_form.package_id.data,
                price = package_form.price.data               
            )
            db.session.add(company_package)


        db.session.commit()
        flash("Company and package prices successfully added")
        return redirect(url_for("companies"))
    
    else:
        print(form.errors)
    
    return render_template("add_company.html", title="Add New Company", form=form)

HTML

{% extends "base.html" %}

{% block content %}
<h1>Add New Company</h1>
<form action="", method="POST">
    {{ form.hidden_tag() }}
    <p>
        {{form.name.label }}
        {{ form.name(size=32) }}
        {% for error in form.name.errors %}
        <span style="color:red">[{{ error }}] </span>
        {% endfor %}
    </p>
    <p>
        {{form.contact.label }}
        {{ form.contact(size=32) }}
        {% for error in form.contact.errors %}
        <span style="color:red">[{{ error }}] </span>
        {% endfor %}
    </p>
    <p>
        {{form.notes.label }}
        {{ form.notes(size=32) }}
        {% for error in form.notes.errors %}
        <span style="color:red">[{{ error }}] </span>
        {% endfor %}
    </p>
    <h2>Package Prices</h2>
    <table>
        <tr>
            <th>Package</th>
            <th>Price</th>
            
        </tr>
        {% for package_form in form.packages %}
        <tr>
            <td>{{ package_form.package_name.data }}</td>
            <td>{{ package_form.price() }} </td>
            <td>{{ package_form.package_id.data }} </td>  
        </tr>
        {% endfor %}
    </table>
    <p>{{ form.submit() }} </p>
</form>
{% endblock %}

Forms

class NewCompanyForm(FlaskForm):
    name = StringField("Company Name", validators=[DataRequired()])
    contact = StringField("Contact email(s)", validators=[DataRequired()])
    notes = TextAreaField("Additional Notes")
    packages = FieldList(FormField(PackagePriceForm))
    submit = SubmitField("Submit")



class PackagePriceForm(FlaskForm):
    package_id = HiddenField("Package ID", render_kw={"readonly":True})  # Hidden field for package ID
    package_name = StringField("Package Name", render_kw={"readonly":True})  # Read-only field to display the package name
    price = FloatField('Price', validators=[NumberRange(min=0)], default="")

    class Meta:
        csrf = False

db Models

class Package(db.Model):
    id: so.Mapped[int] = so.mapped_column(primary_key=True)
    name: so.Mapped[str] = so.mapped_column(sa.String(100), index=True, unique=True)
    description: so.Mapped[Optional[str]] = so.mapped_column(sa.String(500))

    # Relationships
    company_packages: so.Mapped[List["CompanyPackage"]] = so.relationship(back_populates="package")
    assignees: so.Mapped[List["Assignee"]] = so.relationship(
        secondary="assignee_package",
        back_populates="packages"
    )


class Company(db.Model):
    id: so.Mapped[int] = so.mapped_column(primary_key=True)
    name: so.Mapped[str] = so.mapped_column(sa.String(100), index=True, unique=True)
    contact: so.Mapped[str] = so.mapped_column(sa.String(256))
    notes: so.Mapped[Optional[str]] = so.mapped_column(sa.String(1064))

    # Relationships
    assignees: so.Mapped[List["Assignee"]] = so.relationship(back_populates="company")
    company_packages: so.Mapped[List["CompanyPackage"]] = so.relationship(back_populates="company")


class CompanyPackage(db.Model):
    id: so.Mapped[int] = so.mapped_column(primary_key=True)
    company_id: so.Mapped[int] = so.mapped_column(sa.ForeignKey("company.id"))
    package_id: so.Mapped[int] = so.mapped_column(sa.ForeignKey("package.id"))
    price: so.Mapped[float] = so.mapped_column(sa.Float)

    # Relationships
    company: so.Mapped["Company"] = so.relationship(back_populates="company_packages")
    package: so.Mapped["Package"] = so.relationship(back_populates="company_packages")

When the form is submitted, it should create a new entry in the Company table. It should also create new entries in the CompanyPackage table, one entry for every package, where the company_id is that of the newly created Company, and the package_id is the id for the package in question. My code is almost working, but it is failing to pull the package_id and as a result, my SQL query is failing.

A print statement before validation (ie, at the GET request stage, where FieldList(FormField(PackagePriceForm)))is populated shows that the correct package names and ids are being pulled from the DB. The package name is printed correctly in that table, and although a hidden field, the package_id is visible through browser developer tools / inspect element (Ctrl + Shift + I).

Executing the same print statement after submit (form.validate_on_submit), the package_name and package_id values both become Null. Interestingly, the user-populated field of “price” is correctly retained, and submitted to the SQL query. I have tried this debug print both before and after the initial db.commit (creating a new instance of Company), and on either side of this commit, I have the same problem.

I can only conclude that upon clicking submit, the data which I sent to the web page as part of the GET request, is being lost upon POST/form validation.

Any help is greatly appreciated.