ich habe folgende sqlite DB erstellt:
Code: Alles auswählen
CREATE TABLE IF NOT EXISTS game_group (
group_id integer UNIQUE PRIMARY KEY AUTOINCREMENT NOT NULL,
name text UNIQUE NOT NULL,
created_date timestamp NOT NULL DEFAULT CURRENT_TIMESTAMP
);
CREATE TABLE IF NOT EXISTS players (
player_id integer UNIQUE PRIMARY KEY AUTOINCREMENT NOT NULL,
name text UNIQUE NOT NULL,
created_date timestamp NOT NULL DEFAULT CURRENT_TIMESTAMP
);
CREATE TABLE IF NOT EXISTS game_group_members (
added_date timestamp NOT NULL DEFAULT CURRENT_TIMESTAMP,
group_id integer,
player_id integer,
PRIMARY KEY (group_id, player_id),
CONSTRAINT fk_game_group
FOREIGN KEY (group_id) REFERENCES game_group(group_id)
ON DELETE CASCADE,
CONSTRAINT fk_players
FOREIGN KEY (player_id) REFERENCES players(player_id)
ON DELETE CASCADE
);
Ich lege die DB und Test-Einträge wie folgt an:
Code: Alles auswählen
conn = sqlite3.connect('test_db.sqlite')
cur = conn.cursor()
cur.executescript(init_sql)
# Create a player
query = "INSERT INTO players (name) VALUES (?)"
cur.execute(query, ('TestUser',))
# Create a group
query = "INSERT INTO game_group (name) VALUES (?)"
cur.execute(query, ('TestGroup',))
# Display tables
query = "SELECT * FROM players"
cur.execute(query).fetchall() # Liefert: [(1, 'TestUser', '2020-12-18 08:35:09')]
query = "SELECT * FROM game_group"
cur.execute(query).fetchall() # Liefert: [(1, 'TestGroup', '2020-12-18 08:42:43')]
# Add the player to the group
query = "INSERT INTO game_group_members (group_id, player_id) VALUES (?, ?)"
cur.execute(query, (1, 1))
# Show the group assignment
query = "SELECT * FROM game_group_members"
cur.execute(query).fetchall() # Liefert: [('2020-12-18 08:48:15', 1, 1)]
# Delete the player
query = "DELETE FROM players WHERE player_id = ?"
cur.execute(query, (1,))
conn.commit()
# Show table 'players'
query = "SELECT * FROM players"
cur.execute(query).fetchall() # Liefert: []
# Show the group assignments
query = "SELECT * FROM game_group_members"
cur.execute(query).fetchall() # Liefert: [('2020-12-18 08:48:15', 1, 1)]