Python-Flask Application issue

Question

I am building student management system using flask for this i want to make student fee form based on below criteria

1. Select the course and then relative subcourse and then list of student (name, father name, cnic, phone number, email address) appeared which are enrolled in subcourse - course with enter fee and edit fee button

2. When I click on enter fee button then new form open with fee details as following

   a. Date bydefault today
   b. Admission fee
   c. Admission discount
   d. Net admission fee (formula is admission fee – admission discount)
   e. Installment type (one time, monthly)
   f. Fee amount
   g. Fee discount (absolute)
   h. Fee % discount (show the discount amount as (fee amount * fee % dicsount)
   i. Other charges
   j. Net fee payable, formulta is (fee amount – fee discount – fee % discount- other charges)
   k. In case of monthly then appeared (month and year)
   l. Payment mode (Cash, Online transfer, credit card)
   m. Fee Paid
   n. Outstanding Fee amount (show the balance amount formula is (net fee payabale – fee paid)

3. I case of edit fee then new form of edit fee appeared against students and we can edit / add fees of students.

I am facing error which is facing error of

sqlalchemy.exc.IntegrityError: (pymysql.err.IntegrityError) 
(1048, "Column 'subcourse_id' cannot be null")

My db code is

class Fee(db.Model):

    id = db.Column(db.Integer, primary_key=True)
    student_id = db.Column(db.Integer, db.ForeignKey('student.id'), nullable=False)
    subcourse_id = db.Column(db.Integer, db.ForeignKey('sub_course.id'), nullable=False)
    course_id = db.Column(db.Integer, db.ForeignKey('course.id'), nullable=False)
    fee_amount = db.Column(db.Float, nullable=False)
    discount_amount = db.Column(db.Float, nullable=True)
    discount_percentage = db.Column(db.Float, nullable=True)
    net_fee = db.Column(db.Float, nullable=False)
    installment_type = db.Column(db.String(10), nullable=False)
    year = db.Column(db.String(10), nullable=True)
    month = db.Column(db.String(10), nullable=True)
    payment_made = db.Column(db.Float, nullable=True)
    other_charges = db.Column(db.Float, nullable=True)
    date = db.Column(db.Date, default=datetime.utcnow, nullable=False)
    admission_fee = db.Column(db.Float, nullable=True)
    admission_discount = db.Column(db.Float, nullable=True)
    balance_admission_fee = db.Column(db.Float, nullable=True)

and my function is

@app.route('/get_subcourses/<int:course_id>')
def get_subcourses(course_id):
    subcourses = SubCourse.query.filter_by(course_id=course_id).all()
    subcourses_list = [{'id': sc.id, 'name': sc.name} for sc in subcourses]
    return jsonify({'subcourses': subcourses_list})

@app.route('/get_students/<int:subcourse_id>')
def get_students(subcourse_id):
    students = Student.query.filter_by(subcourse_id=subcourse_id).all()
    students_list = [{'id': s.id, 'name': s.name, 'father_name': s.father_name, 'cnic': s.cnic, 'mobile': s.mobile} for s in students]
    return jsonify({'students': students_list})

# Route to handle the fee entry form submission
@app.route('/fee_selection', methods=['GET', 'POST'])
def fee_selection():
    courses = Course.query.all()
    subcourses = SubCourse.query.all()
    return render_template('fee_selection.html', courses=courses, subcourses=subcourses)

@app.route('/fee_form', methods=['GET', 'POST'])
def fee_form():
    if request.method == 'POST':
        student_id = request.form['student_id']
        subcourse_id = request.form['subcourse_id']
        course_id = request.form['course_id']
        date = request.form['date']
        admission_fee = request.form['admission_fee']
        admission_discount = request.form['admission_discount']
        balance_admission_fee = request.form['balance_admission_fee']
        installment_type = request.form['installment_type']
        fee_amount = request.form['fee_amount']
        discount_amount = request.form['discount_amount']
        discount_percentage = request.form['discount_percentage']
        other_charges = request.form['other_charges']
        
        month = request.form.get('month')
        year = request.form.get('year')
        payment_mode = request.form['payment_mode']
        payment_made = request.form['payment_made']
        net_fee = request.form['net_fee']

        
        fee = Fee(
                student_id=student_id,
                # subcourse_id=subcourse_id,
                # course_id=course_id,
                date=date,
                fee_amount=fee_amount,
                discount_amount=discount_amount,
                discount_percentage=discount_percentage,
                net_fee=net_fee,
                installment_type=installment_type,
                year=year,
                month=month,
                payment_made=payment_made,
                
                other_charges=other_charges,
                
                admission_fee=admission_fee,
                admission_discount=admission_discount,
                balance_admission_fee=balance_admission_fee
            )

        db.session.add(fee)
        db.session.commit()
        return redirect(url_for('fee_selection'))

    courses = Course.query.all()
    subcourses = SubCourse.query.all()
    fees = Fee.query.all()
    today = datetime.today().strftime('%Y-%m-%d')
    return render_template('fee_form.html',fees=fees, courses=courses, subcourses=subcourses,  today=today)


# Route to fetch existing fee details for editing
@app.route('/get_fee/<int:student_id>', methods=['GET'])
def get_fee(student_id):
    fee = Fee.query.filter_by(student_id=student_id).first()
    if fee:
        fee_data = {
            'id': fee.id,
            'fee_amount': fee.fee_amount,
            'discount_amount': fee.discount_amount,
            'discount_percentage': fee.discount_percentage,
            'net_fee': fee.net_fee,
            'installment_type': fee.installment_type,
            'year': fee.year,
            'month': fee.month,
            'payment_made': fee.payment_made,
            'other_charges': fee.other_charges,
            'date': fee.date.strftime('%Y-%m-%d'),
            'admission_fee': fee.admission_fee,
            'admission_discount': fee.admission_discount,
            'balance_admission_fee': fee.balance_admission_fee
        }
        return jsonify(fee_data)
    return jsonify(None)

Here is my html codes including js codes

fee_selection.html code

{% extends "base.html" %}

{% block title %}Fees{% endblock %}


{% block content %}
    <h2>Add Fee</h2>
    <div class="container mt-5">
        <h2>Fee Form</h2>
        <div class="form-group">
            <label for="course_id">Course:</label>
            <select class="form-control" id="course_id" name="course_id" required>
                <option value="">Select Course</option>
                {% for course in courses %}
                    <option value="{{ course.id }}">{{ course.name }}</option>
                {% endfor %}
            </select>
        </div>
        <div class="form-group">
            <label for="subcourse_id">Subcourse:</label>
            <select class="form-control" id="subcourse_id" name="subcourse_id" required>
                <option value="">Select Subcourse</option>
            </select>
        </div>
        <div class="form-group">
            <label for="student_id">Student:</label>
            <div id="student_list">
                <table border="1" id="students-table" style="display:none;">
                    <thead>
                        <tr>
                            <th>Student Name</th>
                            <th>Father Name</th>
                            <th>CNIC</th>
                            <th>Mobile Number</th>
                            <th>Actions</th>
                        </tr>
                    </thead>
                    <tbody id="students-tbody">
                        <!-- Student rows will be appended here by JavaScript -->
                    </tbody>
                </table><br>
            </div>
        </div>
    

    <script>
        
        document.getElementById('course_id').addEventListener('change', function() {
    const courseId = this.value;
    fetch(`/get_subcourses/${courseId}`)
        .then(response => response.json())
        .then(data => {
            const subcourseSelect = document.getElementById('subcourse_id');
            subcourseSelect.innerHTML = '<option value="">Select Subcourse</option>';
            data.subcourses.forEach(subcourse => {
                subcourseSelect.innerHTML += `<option value="${subcourse.id}">${subcourse.name}</option>`;
            });
        });
});
document.getElementById('subcourse_id').addEventListener('change', function() {
        const subcourseId = this.value;
        fetch(`/get_students/${subcourseId}`)
            .then(response => response.json())
            .then(data => {
                const studentsTable = document.getElementById('students-table');
                const studentsTbody = document.getElementById('students-tbody');
                studentsTbody.innerHTML = '';
                if (data.students.length > 0) {
                    studentsTable.style.display = 'block';
                    data.students.forEach(student => {
                        studentsTbody.innerHTML += `
                            <tr>
                                <td>${student.name}</td>
                                <td>${student.father_name}</td>
                                <td>${student.cnic}</td>
                                <td>${student.mobile}</td>
                                <td>
                                <button onclick="enterFee(${student.id})">Enter Fee</button>
                                <button onclick="editFee(${student.id})">Edit Fee</button>
                               </td>
                        
                            </tr>
                        `;
                    });
                } else {
                    studentsTable.style.display = 'none';
                }
            });
    });             
                        

    function enterFee(studentId) {
        window.location.href = '/fee_form?student_id=' + studentId ;
        
    }

        function editFee(studentId) {
            window.location.href = '/fee_form?student_id=' + studentId + '&edit=true';
        }

        
    </script>
{% endblock %}

and fee_form.html code is

{% extends "base.html" %}

{% block title %}Fees{% endblock %}

{% block content %}
<h1>Fee Form</h1>
    <form method="POST" action="/fee_form">
        <input type="hidden" name="fee_id" id="fee_id">
        <input type="number" name="student_id" id="student_id" value="{{ 'student_id' }}">
        
        <input type="number" name="course_id" id = "course_id" value="{{ 'student.course_id' }}">
        <input type="number" name="subcourse_id" value="{{ subcourse_id }}">
        
        <label for="date">Date:</label>
        <input type="date" id="date" name="date" value="{{ today }}"><br>
        
        <label for="admission_fee">Admission Fee:</label>
        <input type="number" id="admission_fee" name="admission_fee"><br>
        
        <label for="admission_discount">Admission Discount:</label>
        <input type="number" id="admission_discount" name="admission_discount"><br>
        
        <label for="balance_admission_fee">Net Admission Fee:</label>
        <input type="number" id="balance_admission_fee" name="balance_admission_fee" readonly><br>
        
        <label for="installment_type">Installment Type:</label>
        <select id="installment_type" name="installment_type">
            <option value="one_time">One Time</option>
            <option value="monthly">Monthly</option>
        </select><br>
        <div id="monthly_fields" style="display:none;">
            <label for="year">Year:</label>
            <input type="text" id="year" name="year"><br>
            
            <label for="month">Month:</label>
            <input type="text" id="month" name="month"><br>
        </div>
        
        <label for="fee_amount">Fee Amount:</label>
        <input type="number" id="fee_amount" name="fee_amount"><br>
        
        <label for="discount_amount">Discount Amount:</label>
        <input type="number" id="discount_amount" name="discount_amount"><br>
        
        <label for="discount_percentage">Discount Percentage:</label>
        <input type="number" id="discount_percentage" name="discount_percentage"><br>
        
        <label for="other_charges">Other Charges:</label>
        <input type="number" id="other_charges" name="other_charges"><br>
        
        
        <label for="net_fee">Net Fee:</label>
        <input type="number" id="net_fee" name="net_fee" readonly><br>
        
        
       
        
        
        <label for="payment_mode">Payment Mode:</label>
        <select id="payment_mode" name="payment_mode">
            <option value="cash">Cash</option>
            <option value="online_transfer">Online Transfer</option>
            <option value="credit_card">Credit Card</option>
        </select><br>
        
        <label for="payment_made">Payment Made:</label>
        <input type="number" id="payment_made" name="payment_made"><br>
        
        <label for="outstanding_fee">Balance Fee:</label>
        <input type="number" id="outstanding_fee" name="outstanding_fee" readonly><br>
        
        <button type="submit">Submit</button>
    </form>

    <script>
        document.getElementById('installment_type').addEventListener('change', function() {
            var monthlyFields = document.getElementById('monthly_fields');
            if (this.value === 'monthly') {
                monthlyFields.style.display = 'block';
            } else {
                monthlyFields.style.display = 'none';
            }
        });

        document.getElementById('admission_fee').addEventListener('input', calculateNetAdmissionFee);
        document.getElementById('admission_discount').addEventListener('input', calculateNetAdmissionFee);
        document.getElementById('fee_amount').addEventListener('input', calculateNetFee);
        document.getElementById('discount_amount').addEventListener('input', calculateNetFee);
        document.getElementById('discount_percentage').addEventListener('input', calculateNetFee);
        document.getElementById('other_charges').addEventListener('input', calculateNetFee);
        document.getElementById('payment_made').addEventListener('input', calculateOutstandingFee);

        function calculateNetAdmissionFee() {
            var admissionFee = parseFloat(document.getElementById('admission_fee').value) || 0;
            var admissionDiscount = parseFloat(document.getElementById('admission_discount').value) || 0;
            var netAdmissionFee = admissionFee - admissionDiscount;
            document.getElementById('balance_admission_fee').value = netAdmissionFee;
            calculateNetFee();
        }

        function calculateNetFee() {
            var feeAmount = parseFloat(document.getElementById('fee_amount').value) || 0;
            var discountAmount = parseFloat(document.getElementById('discount_amount').value) || 0;
            var discountPercentage = parseFloat(document.getElementById('discount_percentage').value) || 0;
            var netAddmissionFee = parseFloat(document.getElementById('balance_admission_fee').value) || 0;
            var otherCharges = parseFloat(document.getElementById('other_charges').value) || 0;
            var feePercentageDiscount = feeAmount * (discountPercentage / 100);
            var netFeePayable = feeAmount - discountAmount - feePercentageDiscount + otherCharges + netAddmissionFee;
            document.getElementById('net_fee').value = netFeePayable;
            calculateOutstandingFee();
        }

        function calculateOutstandingFee() {
            var netFeePayable = parseFloat(document.getElementById('net_fee').value) || 0;
            var paymentMade = parseFloat(document.getElementById('payment_made').value) || 0;
            var outstandingFee = netFeePayable - paymentMade;
            document.getElementById('outstanding_fee').value = outstandingFee;
        }

       
        // {% if request.args.get('edit') %}
        // fetch('/get_fee/' + {{ request.args.get('student_id') }})
        //     .then(response => response.json())
        //     .then(data => {
        //         if (data) {
        //             document.getElementById('fee_id').value = data.id;
        //             document.getElementById('fee_amount').value = data.fee_amount;
        //             document.getElementById('discount_amount').value = data.discount_amount;
        //             document.getElementById('discount_percentage').value = data.discount_percentage;
        //             document.getElementById('net_fee').value = data.net_fee;
        //             document.getElementById('installment_type').value = data.installment_type;
        //             document.getElementById('year').value = data.year;
        //             document.getElementById('month').value = data.month;
        //             document.getElementById('payment_made').value = data.payment_made;
        //             document.getElementById('other_charges').value = data.other_charges;
        //             document.getElementById('date').value = data.date;
        //             document.getElementById('admission_fee').value = data.admission_fee;
        //             document.getElementById('admission_discount').value = data.admission_discount;
        //             document.getElementById('balance_admission_fee').value = data.balance_admission_fee;
        //             calculateNetFee();
        //             calculateOutstandingFee();
        //         }
        //     });
        // {% endif %}

    </script>
    
{% endblock %}

Answer 1

Error "Column 'subcourse_id' cannot be null should explain everything.

In some place you forgot to and value for subcourse_id when you INSERT data.

In code I see

Fee(
   ...
   # subcourse_id=subcourse_id,
   ...

so you create Fee without subcourse_id and problably this is your problem.

You defined model Fee with subcourse_id = db.Column(.., nullable=False)
and it needs value but you don't set it.

You have to set this value when you create object Fee()
or you have to remove nullable=False from model.