"""Run: python sqlite_demo.py. In-memory database, safe to repeat."""
import sqlite3

def main():
    con = sqlite3.connect(':memory:')
    try:
        con.execute('PRAGMA foreign_keys = ON')
        con.executescript('''
        CREATE TABLE students (
          id INTEGER PRIMARY KEY, name TEXT NOT NULL,
          team TEXT NOT NULL CHECK(team IN ('A','B')));
        CREATE TABLE scores (
          student_id INTEGER NOT NULL REFERENCES students(id),
          subject TEXT NOT NULL,
          score REAL NOT NULL CHECK(score BETWEEN 0 AND 100),
          PRIMARY KEY(student_id, subject));
        ''')
        with con:
            con.executemany('INSERT INTO students VALUES (?, ?, ?)',
                            [(1,'Lin','A'),(2,'Chen','A'),(3,'Wang','B'),(4,'Zhao','B')])
            con.executemany('INSERT INTO scores VALUES (?, ?, ?)',
                            [(1,'Python',80),(2,'Python',90),(3,'Python',70)])
        rows = con.execute('''SELECT s.team, COUNT(x.score), AVG(x.score)
          FROM students AS s LEFT JOIN scores AS x ON s.id=x.student_id
          GROUP BY s.team ORDER BY s.team''').fetchall()
        assert rows == [('A',2,85.0),('B',1,70.0)]
        print('team, graded students, average:', rows)
        before = con.execute('SELECT COUNT(*) FROM scores').fetchone()[0]
        try:
            with con:
                con.execute('INSERT INTO scores VALUES (4, ?, ?)', ('Python',88))
                con.execute('INSERT INTO scores VALUES (99, ?, ?)', ('Python',80))
        except sqlite3.IntegrityError:
            print('invalid foreign key: transaction rolled back')
        assert con.execute('SELECT COUNT(*) FROM scores').fetchone()[0] == before
    finally:
        con.close()

if __name__ == '__main__':
    main()