[Python] Inserting value into MYSQL using FlaskAPI in Website

I keep having problem trying to insert value in my website form into Mysql.. I have also check all the table. AssessmentID seems to be not null. but keep getting the same error.

Problem occurs:

xxx.x.x.x - - [23/Mar/2025 14:57:14] "POST /testresult HTTP/1.1" 500 - Traceback (most recent call last): File "C:\Users\directory\HTML\venv\Lib\site-packages\mysql\connector\connection_cext.py", line 755, in cmd_query self._cmysql.query( _mysql_connector.MySQLInterfaceError: Column 'AssessmentID' cannot be null

The above exception was the direct cause of the following exception:
​

Picture of my ERD. after drop foreign key

app.py:

@app.route('/submit_form', methods=['POST'])
def submit_form():
try:
print("[DEBUG] submit_form called")
# Collect data from the form
NSP = request.form.get('NSP')
address = request.form.get('address')
contact_person = request.form.get('contact_person')
email = request.form.get('email')
test_datetime_local = request.form.get('Test_DatetimeLocal')
customer_segment = request.form.get('customer_segment')
ipv6_prefix = request.form.get('ipv6_prefix')
TypeOfService = request.form.get('TypeOfService')
ProductName = request.form.get('ProductName')
RegionName = request.form.get('RegionName')
StateName = request.form.get('StateName')
MethodOfAssignment = request.form.get('MethodOfAssignment')
RequestForAssignment = request.form.get('RequestForAssignment')
test_datetime_local = request.form.get('Test_DatetimeLocal')

print(f"[DEBUG] Form Data: NSP={NSP}, address={address}, contact_person={contact_person}, email={email}, test_datetime_local={test_datetime_local}, customer_segment={customer_segment}, ipv6_prefix={ipv6_prefix}, TypeOfService={TypeOfService}, ProductName={ProductName}, RegionName={RegionName}, StateName={StateName}, MethodOfAssignment={MethodOfAssignment}, RequestForAssignment={RequestForAssignment}")

# Insert into assessment
cursor.execute("""
INSERT INTO assessment (CustomerSegment, IPv6Prefix, TypeOfService, ProductName, MethodOfAssignment, RequestForAssignment, AssessmentDate)
VALUES (%s, %s, %s, %s, %s, %s, %s)
""", (customer_segment, ipv6_prefix, TypeOfService, ProductName, MethodOfAssignment, RequestForAssignment, test_datetime_local))

#assessment_id = cursor.lastrowid
#session['assessment_id'] = assessment_id
#print(f"[DEBUG] New assessment_id generated: {assessment_id}")

# Insert other related data
cursor.execute("INSERT INTO nsp (NSPName, Address) VALUES (%s, %s)", (NSP, address))
print("[DEBUG] Inserted into nsp")

cursor.execute("INSERT INTO staff (Telephone, Email) VALUES (%s, %s)", (contact_person, email))
print("[DEBUG] Inserted into staff")

cursor.execute("INSERT INTO sign_off (AssessmentID, Date) VALUES (%s, %s)", (assessment_id, test_datetime_local))
print("[DEBUG] Inserted into sign_off")

cursor.execute("INSERT INTO region (RegionName) VALUES (%s)", (RegionName,))
print("[DEBUG] Inserted into region")

cursor.execute("INSERT INTO state (StateName, RegionName) VALUES (%s, %s)", (StateName, RegionName))
print("[DEBUG] Inserted into state")

assessment_id = cursor.lastrowid
print(f"[DEBUG] Inserted assessment with ID: {assessment_id}")
session['assessment_id'] = assessment_id
print(f"[DEBUG] assessment_id stored in session: {session['assessment_id']}")

db.commit()
print("[DEBUG] All inserts committed successfully")
return redirect('/testresult')

except Exception as e:
db.rollback()
print(f"[ERROR] An error occurred during submit_form: {e}")
return f"An error occurred: {e}"

@app.route('/testresult', methods=['GET', 'POST'])
def testresult():
assessment_id = session.get('assessment_id')
print(f"[DEBUG] testresult page loaded for assessment_id: {assessment_id}")
if assessment_id is None:
return "ERROR LAGI HAHAHA", 400

if request.method == 'POST':
domains = ['Google', 'Facebook', 'Youtube']
for domain in domains:
cursor.execute("SELECT Domain_ID FROM domain WHERE DomainName = %s", (domain,))
domain_row = cursor.fetchone()
if domain_row is None:
print(f"[ERROR] Domain {domain} not found in database.")
return f"Domain {domain} not found in database."
domain_id = domain_row[0]

ipv4_result = request.form.get(f'ping_ipv4_{domain.lower()}')
ipv6_result = request.form.get(f'ping_ipv6_{domain.lower()}')
print(f"[DEBUG] Domain: {domain}, IPv4: {ipv4_result}, IPv6: {ipv6_result}")

cursor.execute("""
INSERT INTO dual_stack_ping (Domain_ID, AssessmentID, IPv4_Result, IPv6_Result, Status)
VALUES (%s, %s, %s, %s, %s)
""", (domain_id, assessment_id, ipv4_result, ipv6_result, 'Completed'))

db.commit()
print("[DEBUG] testresult form submitted and committed")
return redirect('/dashboard')

return render_template('testresult.html', assessment_id=assessment_id)

@app.route('/dashboard')
def dashboard():
cursor.execute("SELECT * FROM assessment ORDER BY AssessmentID DESC")
assessments = cursor.fetchall()
print(f"[DEBUG] Loaded dashboard with {len(assessments)} assessments")
return render_template('dashboard.html', assessments=assessments)

if __name__ == '__main__':
app.run(debug=True)


Testresult.html:

<!DOCTYPE html>
<html lang="en">
<head>
<meta charset="UTF-8">
<meta name="viewport" content="width=device-width, initial-scale=1.0">
<title>Document</title>

<link rel="stylesheet" href="static/style.css">
</head>
<body>
<!-- write code here -->
<h1>Fill in your Test result here</h1>
<section class="wrapper-main">
<form action="/testresult" method="post">
<input type="hidden" name="assessment_id" value="{{ assessment_id }}">
<h2>E. Test Results</h2>

<h3>Dual-Stack Ping Test</h3>
<table>
<tr>
<th>Domain</th>
<th>IPv4</th>
<th>IPv6</th>
</tr>
<tr>
<td>Google</td>
<td>
<label><input type="radio" name="ping_ipv4_google" value="<20ms"> < 20ms</label>
<label><input type="radio" name="ping_ipv4_google" value="20-100ms"> 20-100ms</label>
<label><input type="radio" name="ping_ipv4_google" value=">100ms"> > 100ms</label>
</td>

<td>
<label><input type="radio" name="ping_ipv6_google" value="<20ms"> < 20ms</label>
<label><input type="radio" name="ping_ipv6_google" value="20-100ms"> 20-100ms</label>
<label><input type="radio" name="ping_ipv6_google" value=">100ms"> > 100ms</label>
</td>
</tr>
<tr>
<td>Facebook</td>
<td>
<label><input type="radio" name="ping_ipv4_facebook" value="<20ms"> < 20ms</label>
<label><input type="radio" name="ping_ipv4_facebook" value="20-100ms"> 20-100ms</label>
<label><input type="radio" name="ping_ipv4_facebook" value=">100ms"> > 100ms</label>
</td>
<td>
<label><input type="radio" name="ping_ipv6_facebook" value="<20ms"> < 20ms</label>
<label><input type="radio" name="ping_ipv6_facebook" value="20-100ms"> 20-100ms</label>
<label><input type="radio" name="ping_ipv6_facebook" value=">100ms"> > 100ms</label>
</td>
</tr>
<tr>
<td>Youtube</td>
<td>
<label><input type="radio" name="ping_ipv4_youtube" value="<20ms"> < 20ms</label>
<label><input type="radio" name="ping_ipv4_youtube" value="20-100ms"> 20-100ms</label>
<label><input type="radio" name="ping_ipv4_youtube" value=">100ms"> > 100ms</label>
</td>
<td>
<label><input type="radio" name="ping_ipv6_youtube" value="<20ms"> < 20ms</label>
<label><input type="radio" name="ping_ipv6_youtube" value="20-100ms"> 20-100ms</label>
<label><input type="radio" name="ping_ipv6_youtube" value=">100ms"> > 100ms</label>
</td>
</tr>
</table>


<h3>Web-Based IPv6 Accessibility & Connectivity Test</h3>
<table>
<tr>
<th>Domain</th>
<th>Pass</th>
<th>Fail</th>
</tr>
<tr>
<td>test-ipv6.com</td>
<td><input type="radio" name="web_ipv6_test_testipv6" value="Pass"></td>
<td><input type="radio" name="web_ipv6_test_testipv6" value="Fail"></td>
</tr>
<tr>
<td>ipv6test.google.com</td>
<td><input type="radio" name="web_ipv6_test_ipv6test" value="Pass"></td>
<td><input type="radio" name="web_ipv6_test_ipv6test" value="Fail"></td>
</tr>
<tr>
<td>https://www.ipv6forum.com/test_ipv6.php</td>
<td><input type="radio" name="web_ipv6_test_ipv6forum" value="Pass"></td>
<td><input type="radio" name="web_ipv6_test_ipv6forum" value="Fail"></td>
</tr>
<tr>
<td>http://he.test-ipv6.com/</td>
<td><input type="radio" name="web_ipv6_test_ipv6.com" value="Pass"></td>
<td><input type="radio" name="web_ipv6_test_ipv6.com" value="Fail"></td>
</tr>
</table>

<h3>Dual-Stack Website Connectivity Test</h3>
<table>
<tr>
<th>Domain</th>
<th>Connected</th>
</tr>
<tr>
<td>https://www.lacnic.net/</td>
<td><input type="checkbox" name="dual_stack_web_test_lacnic"></td>
</tr>
<tr>
<td>https://www.ripe.net/</td>
<td><input type="checkbox" name="dual_stack_web_test_ripe"></td>
</tr>
<tr>
<td>https://www.afrinic.net/</td>
<td><input type="checkbox" name="dual_stack_web_test_ripe"></td>
</tr>
<tr>
<td>https://www.apnic.net/</td>
<td><input type="checkbox" name="dual_stack_web_test_ripe"></td>
</tr>
<tr>
<td>https://www.arin.net/</td>
<td><input type="checkbox" name="dual_stack_web_test_ripe"></td>
</tr>
</table>


<h3>Dual-Stack Website Access Latency Test</h3>
<table>
<tr>
<th>Domain</th>
<th>IPv4 Load Time</th>
<th>IPv6 Load Time</th>
</tr>
<tr>
<td>Google</td>
<td>
<label><input type="radio" name="latency_ipv4_google" value="<2"> <2s</label>
<label><input type="radio" name="latency_ipv4_google" value="2-5"> 2-5s</label>
<label><input type="radio" name="latency_ipv4_google" value=">5"> >5s</label>
</td>
<td>
<label><input type="radio" name="latency_ipv6_google" value="<2"> <2s</label>
<label><input type="radio" name="latency_ipv6_google" value="2-5"> 2-5s</label>
<label><input type="radio" name="latency_ipv6_google" value=">5"> >5s</label>
</td>
</tr>
<tr>
<td>Facebook</td>
<td>
<label><input type="radio" name="latency_ipv4_facebook" value="<2"> <2s</label>
<label><input type="radio" name="latency_ipv4_facebook" value="2-5"> 2-5s</label>
<label><input type="radio" name="latency_ipv4_facebook" value=">5"> >5s</label>
</td>
<td>
<label><input type="radio" name="latency_ipv6_facebook" value="<2"> <2s</label>
<label><input type="radio" name="latency_ipv6_facebook" value="2-5"> 2-5s</label>
<label><input type="radio" name="latency_ipv6_facebook" value=">5"> >5s</label>
</td>
</tr>
<tr>
<td>Youtube</td>
<td>
<label><input type="radio" name="latency_ipv4_youtube" value="<2"> <2s</label>
<label><input type="radio" name="latency_ipv4_youtube" value="2-5"> 2-5s</label>
<label><input type="radio" name="latency_ipv4_youtube" value=">5"> >5s</label>
</td>
<td>
<label><input type="radio" name="latency_ipv6_youtube" value="<2"> <2s</label>
<label><input type="radio" name="latency_ipv6_youtube" value="2-5"> 2-5s</label>
<label><input type="radio" name="latency_ipv6_youtube" value=">5"> >5s</label>
</td>
</tr>
</table>

<h3>Dual-Stack DNS Resolving Test</h3>
<table>
<tr>
<th>Domain</th>
<th>IPv4 Resolve Time</th>
<th>IPv6 Resolve Time</th>
</tr>
<tr>
<td>Google</td>
<td>
<label><input type="radio" name="dns_ipv4_google_ipv4" value="<100"> <100ms</label>
<label><input type="radio" name="dns_ipv4_google_ipv4" value="100-250"> 100-250ms</label>
<label><input type="radio" name="dns_ipv4_google_ipv4" value=">250"> >250ms</label>
</td>
<td>
<label><input type="radio" name="dns_ipv6_google_ipv6" value="<100"> <100ms</label>
<label><input type="radio" name="dns_ipv6_google_ipv6" value="100-250"> 100-250ms</label>
<label><input type="radio" name="dns_ipv6_google_ipv6" value=">250"> >250ms</label>
</td>
</tr>
<tr>
<td>Facebook</td>
<td>
<label><input type="radio" name="dns_ipv4_Facebook_ipv4" value="<100"> <100ms</label>
<label><input type="radio" name="dns_ipv4_Facebook_ipv4" value="100-250"> 100-250ms</label>
<label><input type="radio" name="dns_ipv4_Facebook_ipv4" value=">250"> >250ms</label>
</td>
<td>
<label><input type="radio" name="dns_ipv6_Facebook_ipv6" value="<100"> <100ms</label>
<label><input type="radio" name="dns_ipv6_Facebook_ipv6" value="100-250"> 100-250ms</label>
<label><input type="radio" name="dns_ipv6_Facebook_ipv6" value=">250"> >250ms</label>
</td>
</tr>
<tr>
<td>Youtube</td>
<td>
<label><input type="radio" name="dns_ipv4_Youtube_ipv4" value="<100"> <100ms</label>
<label><input type="radio" name="dns_ipv4_Youtube_ipv4" value="100-250"> 100-250ms</label>
<label><input type="radio" name="dns_ipv4_Youtube_ipv4" value=">250"> >250ms</label>
</td>
<td>
<label><input type="radio" name="dns_ipv6_Youtube_ipv6" value="<100"> <100ms</label>
<label><input type="radio" name="dns_ipv6_Youtube_ipv6" value="100-250"> 100-250ms</label>
<label><input type="radio" name="dns_ipv6_Youtube_ipv6" value=">250"> >250ms</label>
</td>
</tr>
</table>


<h3>Dual-Stack Traceroute Test</h3>
<table>
<tr>
<th>Domain</th>
<th>IPv4 (hops)</th>
<th>IPv6 (hops)</th>
</tr>
<tr>
<td>Google</td>
<td>
<label><input type="radio" name="traceroute_ipv4_google_ipv4" value="<15"> <15</label>
<label><input type="radio" name="traceroute_ipv4_google_ipv4" value=">15"> >15</label>
</td>
<td>
<label><input type="radio" name="traceroute_ipv6_google_ipv6" value="<15"> <15</label>
<label><input type="radio" name="traceroute_ipv6_google_ipv6" value=">15"> >15</label>
</td>
</tr>
<tr>
<td>Facebook</td>
<td>
<label><input type="radio" name="traceroute_ipv4_Facebook_ipv4" value="<15"> <15</label>
<label><input type="radio" name="traceroute_ipv4_Facebook_ipv4" value=">15"> >15</label>
</td>
<td>
<label><input type="radio" name="traceroute_ipv6_Facebook_ipv6" value="<15"> <15</label>
<label><input type="radio" name="traceroute_ipv6_Facebook_ipv6" value=">15"> >15</label>
</td>
</tr>
<tr>
<td>Youtube</td>
<td>
<label><input type="radio" name="traceroute_ipv4_Youtube_ipv4" value="<15"> <15</label>
<label><input type="radio" name="traceroute_ipv4_Youtube_ipv4" value=">15"> >15</label>
</td>
<td>
<label><input type="radio" name="traceroute_ipv6_Youtube_ipv6" value="<15"> <15</label>
<label><input type="radio" name="traceroute_ipv6_Youtube_ipv6" value=">15"> >15</label>
</td>
</tr>
<tr>
<td>download.thinkbroadband.com</td>
<td>
<label><input type="radio" name="traceroute_ipv4_google_ipv4" value="<15"> <15</label>
<label><input type="radio" name="traceroute_ipv4_google_ipv4" value=">15"> >15</label>
</td>
<td>
<label><input type="radio" name="traceroute_ipv6_google_ipv6" value="<15"> <15</label>
<label><input type="radio" name="traceroute_ipv6_google_ipv6" value=">15"> >15</label>
</td>
</tr>
</table>
<button2 type="button2" id="backButton" formaction="/submit_form">Back</button2>
<script>
document.getElementById("backButton").addEventListener("click", function() {
window.location.href = "/";
});
</script>
<br><br>

<button type="submit">Submit Self-Assessment</button>
</form>

Continue reading...