Inserting with Foreign Key in sqlite3 for python

Question

I have a database called soccer.db with two tables team and game

I used DB Browser to create the database. My sqlite3 version is 3.30.0. I also confirmed that the foreign keys pragma is enabled/checked.

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")
);

Now, I am trying to insert weekly game information into the game database using sqlite3 in a jupyter notebook using the following code.

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

When I try to run the code, I get the following error. I confirmed that the first insert statement worked, so I assume that the error is referring to the second insert statement.

SyntaxError: invalid syntax

What am I doing wrong?

Answer 1

You should use the lastrowid of the cursor, as in:

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

If, on the other hand, you'd like to use an exiting team, you can do the following:

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