用Python 3.4在MySQL中创建表

我使用Python 3.4作为cgi和Windows 10上的MySQL数据库服务器通过Apache运行服务器.当我尝试运行我的函数来创建数据库时,我得到错误:

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

用于创建数据库的功能

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()         

编辑:我将StudentNo更改为StudentID,现在得到错误:

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

解决方法:

创建StudentExam时,您引用的是不存在的Student.StudentId.看起来你想要引用Student.StudentNo.

编辑:

创建ExamSection时,引用Section表,该表尚不存在.将Section创建状态向上移动,以便在创建ExamSection之前运行并提交.

上一篇:如何设置Python CGI服务器?


下一篇:mysql – CGI语言选择