在sqlite3 for python中使用外键插入

Question

我在一个名为soccer.db的数据库中有两个表，其中两个表分别是team和game

我使用DB浏览器创建数据库。我的sqlite3版本是3.30.0。我还确认启用/检查了外键pragma

CREATE TABLE "team" (
    "id"    INTEGER UNIQUE,
    "opponent_name" TEXT,
    "rank_when played"  INTEGER,
    "date_added"    TEXT,
    PRIMARY KEY("id" AUTOINCREMENT)
);

CREATE TABLE "game" (
    "id"    INTEGER,
    "opponent_id"   INTEGER,
    "goals_for" INTEGER,
    "goals_against" INTEGER,
    "date_added"    TEXT,
    PRIMARY KEY("id" AUTOINCREMENT),
    FOREIGN KEY("opponent_id") REFERENCES "team"("id")
);

现在，我正尝试使用以下代码，使用jupyter笔记本中的sqlite3将每周游戏信息插入游戏数据库

conn = sqlite3.connect('soccer.db')
c = conn.cursor()

c.execute('INSERT INTO team (opponent_name, rank_when_played, date_added) VALUES (?,?,?)', ('Manchester_City', 4, '04/22/2018')

c.execute('INSERT INTO game (opponent_id, goals_for, goals_against, date_added) VALUES (?,?,?,?)', ((SELECT id FROM team WHERE opponent_name = 'Machester City'), 3, 2, '04/22/2018')

conn.commit()
c.close()

当我试图运行代码时，我得到以下错误。我确认第一条insert语句有效，所以我假设错误是指第二条insert语句

SyntaxError: invalid syntax

我做错了什么

Answer 1

您应该使用光标的lastrowid，如中所示：

c = conn.cursor()

c.execute('INSERT INTO team (opponent_name, rank_when_played, date_added) VALUES (?,?,?)', 
          ('Manchester_City', 4, '04/22/2018'))

c.execute('INSERT INTO game (opponent_id, goals_for, goals_against, date_added) VALUES (?,?,?,?)', 
          (c.lastrowid, 3, 2, '04/22/2018'))
          # ==========

conn.commit()

另一方面，如果您希望使用现有团队，则可以执行以下操作：

c = conn.cursor()
c.execute("Select id from team where opponent_name = ?", ('Manchester_City', ))
res = c.fetchone()
team_id = res[0]

c.execute('INSERT INTO game (opponent_id, goals_for, goals_against, date_added) VALUES (?,?,?,?)', 
          (team_id, 3, 2, '07/23/2018'))
conn.commit()