Case Study: Pokemon

pokemon.py

import sqlite3
import os
if os.path.exists("pokemania.db"):
  os.remove("pokemania.db")
db = sqlite3.connect('pokemania.db')
db.cursor().executescript("""
  
  CREATE TABLE IF NOT EXISTS pokemon (
    pname TEXT NOT NULL PRIMARY KEY UNIQUE,
    rarity TEXT NOT NULL
  );

  CREATE TABLE IF NOT EXISTS collections (
    user TEXT NOT NULL,
    pname TEXT NOT NULL,
    PRIMARY KEY (user, pname)
  );

  INSERT INTO pokemon (pname, rarity) VALUES ("Pikachu", "GOLD");
  INSERT INTO pokemon (pname, rarity) VALUES ("Bobosaur", "GOLD");
  INSERT INTO pokemon (pname, rarity) VALUES ("Squirtle", "RARE");
  INSERT INTO collections (user, pname) VALUES ("Jared", "Pikachu");
  INSERT INTO collections (user, pname) VALUES ("Thommo", "Pikachu");
  INSERT INTO collections (user, pname) VALUES ("Thommo", "Squirtle");
""")

sql = """
  SELECT *
  FROM collections JOIN pokemon ON collections.pname = pokemon.pname
"""

print(db.cursor().execute(sql).fetchall())