Creating tables in MySQL in Python 3.4

Question

I am running a server through Apache using Python 3.4 as a cgi and a MySQL database server on Windows 10. When I try to run my function for creating the database I get the error:

DatabaseError: 1005 (HY000): Can't create table `testdb`.`studentexam` (errno: 150 
"Foreign key constraint is incorrectly formed") 

The function for creating the database

import mysql.connector as conn

#connect to server
db=conn.connect(host="localhost",user="root",password="")
cursor=db.cursor()
#create database
cursor.execute("""CREATE DATABASE IF NOT EXISTS Testdb""")
db.commit()
#use database
cursor.execute("""USE Testdb""")
db.commit()
#create Teacher table
cursor.execute("""CREATE TABLE IF NOT EXISTS Teacher(
    TeacherUsername VARCHAR(255) PRIMARY KEY,
    TeacherPassword TEXT)""")
db.commit()
#create student table
cursor.execute("""CREATE TABLE IF NOT EXISTS Student(
    StudentNo INT PRIMARY KEY,
    StudentSurname TEXT,
    StudentForename TEXT,
    StudentTeacher VARCHAR(255),
    StudentPassword TEXT,
    FOREIGN KEY(StudentTeacher) REFERENCES Teacher(TeacherUsername))""")
db.commit()
#create exam table
cursor.execute("""CREATE TABLE IF NOT EXISTS Exam(
    TestName VARCHAR(255) PRIMARY KEY,
    TestTotalMarks TEXT,
    Teacher VARCHAR(255),
    FOREIGN KEY(Teacher) REFERENCES Teacher(TeacherUsername))""")
db.commit()
#create StudentExam table
cursor.execute("""CREATE TABLE IF NOT EXISTS StudentExam(
    TestName VARCHAR(255),
    StudentID INT,
    StudentTotalMarks INT,
    PRIMARY KEY(TestName,StudentID),
    FOREIGN KEY(TestName) REFERENCES Exam(TestName),
    FOREIGN KEY(StudentID) REFERENCES Student(StudentID))""")
db.commit()
#create ExamSection table
cursor.execute("""CREATE TABLE IF NOT EXISTS ExamSection(
    TestName VARCHAR(255),
    SectionID INT,
    PRIMARY KEY(TestName,SectionID),
    FOREIGN KEY(TestName) REFERENCES Exam(TestName),
    FOREIGN KEY(SectionID) REFERENCES Section(SectionID))""")
db.commit()
#create Section table
cursor.execute("""CREATE TABLE IF NOT EXISTS Section(
    SectionID INT PRIMARY KEY,
    SectionName TEXT,
    SectionTotalMarks INT)""")
db.commit()
#create Question table
cursor.execute("""CREATE TABLE IF NOT EXISTS Question(
    QuestionID INT PRIMARY KEY,
    SectionID VARCHAR(255),
    Image TEXT,
    Question TEXT,
    PossibleAnswer TEXT,
    CorrectAnswer TEXT,
    QuestionType TEXT,
    FOREIGN KEY(SectionID) REFERENCES Section(SectionID))""")
db.commit()
#create QuestionResults Table
cursor.execute("""CREATE TABLE IF NOT EXISTS QuestionResults(
    QuestionID INT,
    StudentID INT,
    SectionID VARCHAR(255),
    StudentAnswer TEXT,
    PRIMARY KEY(QuestionID,StudentID),
    FOREIGN KEY(QuestionID) REFERENCES Question(QuestionID)
    FOREIGN KEY(StudentID) REFERENCES Student(StudentID))""")
db.commit()
#create Revision table
cursor.execute("""CREATE TABLE IF NOT EXISTS Revision(
    RevisionID INT PRIMARY KEY,
    RevisionSheet TEXT,
    TeacherUsername VARCHAR(255),
    FOREIGN KEY(TeacherUsername) REFERENCES Teacher(TeacherUsername))""")
db.commit()
#create StudentRevition table
cursor.execute("""CREATE TABLE IF NOT EXISTS StudentRevision(
    RevisionID INT,
    StudentID INT,
    PRIMARY KEY(RevisionID,StudentID),
    FOREIGN KEY(RevisionID) REFERENCES Revision(RevisionID),
    FOREIGN KEY(StudentID) REFERENCES Student(StudentID))""")
db.commit()
#create StudentResults table
cursor.execute("""CREATE TABLE IF NOT EXISTS StudentResults(
    SectionID VARCHAR(255),
    StudentID INT,
    StudentSectionMarks INT,
    PRIMARY KEY(SectionID,StudentID),
    FOREIGN KEY(SectionID) REFERENCES Section(SectionID),
    FOREIGN KEY(StudentID) REFERENCES Student(StudentID))""")
db.commit()
cursor.close()         

EDIT: I Changed StudentNo to StudentID and now get the error:

DatabaseError: 1005 (HY000): Can't create table `testdb`.`examsection` (errno: 150
"Foreign key constraint is incorrectly formed") 

Solution 1

When creating StudentExam, you are referencing Student.StudentId which doesn't exist. It looks like you rather want to reference Student.StudentNo.

edit:

When you create ExamSection, you reference the Section table, which doesn't exist yet. Move the Section creation state up so that it runs and commits before you create ExamSection.

Solution 2

Change this:

#create student table
cursor.execute("""CREATE TABLE IF NOT EXISTS Student(
StudentNo INT PRIMARY KEY,
StudentSurname TEXT,
StudentForename TEXT,
StudentTeacher VARCHAR(255),
StudentPassword TEXT,
FOREIGN KEY(StudentTeacher) REFERENCES Teacher(TeacherUsername))""")
db.commit()

to this:

#create student table
cursor.execute("""CREATE TABLE IF NOT EXISTS Student(
StudentID INT PRIMARY KEY,
StudentSurname TEXT,
StudentForename TEXT,
StudentTeacher VARCHAR(255),
StudentPassword TEXT,
FOREIGN KEY(StudentTeacher) REFERENCES Teacher(TeacherUsername))""")
db.commit()