创建带外键的表

Question

我写了一个函数来创建一个表格：

def createSQLCreateTableCommand(tableName, columns, foreignKeys):
    columns = ["%s"%(c) for c in columns]
    foreignKeys = ["FOREIGN KEY (%s) REFERENCES %s(%s)"%(fk[0],fk[1],fk[2]) for fk in foreignKeys]
    cmd = """CREATE TABLE IF NOT EXISTS %s (%s)"""%(tableName,','.join(columns+foreignKeys))
    return cmd

我想测试我的函数，于是写了以下代码。“visit”和“test”只是有一些列的表格。在“test_result”表中，它包含一个外键，指向“visit”表中的rowid，还有一个外键，指向“test”表中的rowid。我的代码在“visit”和“test”表上运行得很好，但在展示“test_result”表时却失败了。我不确定自己是否正确表达了外键的参数。有没有人能帮我修复我的代码？

conn = sqlite.connect(":memory:")
cursor = conn.cursor()

sqlVisits = createSQLCreateTableCommand("visit",["visitid text"],[])
sqlTests = createSQLCreateTableCommand("test",["name text","unit text","low text","high text"],[])
sqlTestResults = createSQLCreateTableCommand("test_result",["value text","time_date text"],["visit int","test int"])    #this is not correct

cursor.execute(sqlVisits)
cursor.execute(sqlTests)
cursor.execute(sqlTestResults)
conn.commit()

cursor.execute("""SELECT tbl_name FROM sqlite_master WHERE type = 'table'""")
print cursor.fetchall()

Answer 1

当你打印出这个函数的实际输出时：

>>> createSQLCreateTableCommand("test_result",["value text","time_date text"],["visit int","test int"])
CREATE TABLE IF NOT EXISTS test_result (
    value text,
    time_date text,
    FOREIGN KEY (v) REFERENCES i(s),
    FOREIGN KEY (t) REFERENCES e(s)
)

你会看到像 fk[0] 这样的表达式是用来从字符串中提取单个字符的。

参数 foreignKeys 必须是一个数组，里面包含三条字符串：子表的列名、父表的表名和父表的列名：

>>> print createSQLCreateTableCommand("test_result",
...                                   ["value text", "time_date text"],
...                                   [("visitid", "visit", "visitid"),
...                                    ("testname", "test", "name")])
CREATE TABLE IF NOT EXISTS test_result (
    value text,
    time_date text,
    FOREIGN KEY (visitid) REFERENCES visit(visitid),
    FOREIGN KEY (testname) REFERENCES test(name)
)

不过，这样写是不合法的 SQL，因为外键约束必须指向子表中已经存在的列。

要解决这个问题，需要修改 createSQLCreateTableCommand 函数，以便创建一个带有外键约束的列：

>>> def createSQLCreateTableCommand(tableName, columns, foreignKeys):
...     columns = ["%s"%(c) for c in columns]
...     foreignKeys = ["%s REFERENCES %s(%s)"%(fk[0],fk[1],fk[2]) for fk in foreignKeys]
...     cmd = """CREATE TABLE IF NOT EXISTS %s (%s)"""%(tableName,','.join(columns+foreignKeys))
...     return cmd
...
>>> print createSQLCreateTableCommand("test_result",
...                                   ["value text", "time_date text"],
...                                   [("visitid text", "visit", "visitid"),
...                                    ("testname text", "test", "name")])
CREATE TABLE IF NOT EXISTS test_result (
    value text,
    time_date text,
    visitid text REFERENCES visit(visitid),
    testname text REFERENCES test(name)
)