Database - Python

dir() - list capabilities of a class 
type() - type of a variable/object (will show "instance" only)

Inheritance class PartyAnimal:
class FootballFan(PartyAnimal):

CRUD SQL:

CREATE TABLE Users(name VARCHAR(128), email VARCHAR(128))
INSERT INTO Users(name, email) VALUES ('testname', 'testname@test.it')
DELETE FROM Users WHERE email='testname@test.it'
UPDATE Users SET name='realname' WHERE email='realname@test.it'
SELECT * FROM Users
SELECT * FROM Users WHERE email='realname@test.it'
SELECT * FROM Users ORDER BY email

Counting Email from org:

import sqlite3

conn = sqlite3.connect('emaildb.sqlite')
cur = conn.cursor()

cur.execute('''
DROP TABLE IF EXISTS Counts''')

cur.execute('''
CREATE TABLE Counts (org TEXT, count INTEGER)''')

fname = raw_input('Enter file name: ')
if ( len(fname) < 1 ) : fname = 'mbox-short.txt'
fh = open(fname)
for line in fh:
    if not line.startswith('From: ') : continue
    pieces = line.split()
    email = pieces[1]
    emailpcs = email.split('@')
    org = emailpcs[1]
    print org
    cur.execute('SELECT count FROM Counts WHERE org = ? ', (org, ))
    row = cur.fetchone()
    if row is None:
        cur.execute('''INSERT INTO Counts (org, count)
                VALUES ( ?, 1 )''', ( org, ) )
    else : 
        cur.execute('UPDATE Counts SET count=count+1 WHERE org = ?',
            (org, ))
    # This statement commits outstanding changes to disk each 
    # time through the loop - the program can be made faster 
    # by moving the commit so it runs only after the loop completes
    conn.commit()

# https://www.sqlite.org/lang_select.html
sqlstr = 'SELECT org, count FROM Counts ORDER BY count DESC LIMIT 10'

print
print "Counts:"
for row in cur.execute(sqlstr) :
    print str(row[0]), row[1]

cur.close()

Database Design:
Step1:
Think about the central attribute of the application: Track in Music db.

Step2:
Create the table from the most non-central attribute: Start from the end of the arrows.

Step3:
Insert data.

Join:

select Album.title, Artist.name from Album join Artist on Album.artist_id = Artist.id

If no "on", then do full outer join.

Many-to-many relationship
We need to add a "connection" table with two foreign keys. (Usually no primary key).
Table 1: Course (id, title)
Table 2: User(id, name, email)
Add table:
Member(course_id, user_id)

SELECT User.name, Member.role, Course.title
FROM User JOIN Member JOIN Course
ON Member.user_id = User.id AND Member.course_id = Course.id
ORDER BY Course.title, Member.role DESC, User.name