[FIXED] Error committing multiple items to one table in SQLite DB model with flask-sqlalchemy

Issue

When adding one commit - db.session.add(new_professor) the program executes properly, when adding a second commit db.session.add(prof_hindex) this is the error:

sqlalchemy.exc.IntegrityError: (sqlite3.IntegrityError) NOT NULL constraint failed: professors.name [SQL: INSERT INTO professors (name, hindex, date_created) VALUES (?, ?, ?)]
[parameters: (None, 25, '2022-03-20 21:14:39.624208')]

it seems once I try and commit two items to the table, name = db.Column(db.String(100), nullable=False) is null.

DB MODEL

class Professors(db.Model):
id = db.Column(db.Integer, primary_key=True)
name = db.Column(db.String(100), nullable=False)
hindex = db.Column(db.Integer)
date_created = db.Column(db.DateTime, default=datetime.utcnow)

# Return a string when adding to DB
def __repr__(self):
    return f"Professors('{self.name}','{self.hindex}')"

POST ROUTE

name = request.form.get("name")
if not name:
    return render_template("failure.html")

# Google Scholar API
search_query = scholarly.search_author(name)
author = next(search_query)
indices_list = (scholarly.fill(author, sections=['indices']))
hindex_int = indices_list['hindex']
# Passed variables to DB
new_professor = Professors(name=name)
prof_hindex = Professors(hindex=hindex_int)

db.session.add(new_professor)
db.session.add(prof_hindex)
db.session.commit()
return redirect("/registrants")

Unsure why the parameters in above show null when adding two items in the table? How would you go about adding multiple items per class?

Solution

You've got a bit of nomenclature mixed up. In your case saying "I want to add multiple items" means "I want to add multiple Professor rows". But looking at your code, it would appear you're actually asking "how do I add multiple attributes when adding a single Professor row".

To add a single Professor with multiple attributes:

...
# Passed variables to DB
new_professor = Professors(
    name=name,
    hindex=hindex_int)

db.session.add(new_professor)
db.session.commit()
return redirect("/registrants")

To add multiple Professors (rows):

professorA = Professors(
    name=nameA,
    hindex=hindex_intA)

professorB = Professors(
    name=nameB,
    hindex=hindex_intB)

db.session.add(professorA)
db.session.add(professorB)
db.session.commit()
return redirect("/registrants")

You were getting an error, because you were trying to add a second Professor (row) prof_hindex = Professors(hindex=hindex_int) but you weren't providing a name attribute, which is a required field due to nullable=False.

Answered By - PGHE