chapter15-using-database-and-SQL

Create, Read, Update, Delete (Single Table)
SQLite - (Oracle/MySql/Access,API, Developer)- database schema/model
www.sqlitebrowser.org
table is also called relation; tuple means row, which is an 'object' and a set of fields; attribute means column or fields
relation is a set of tuples; first row is meta data
SQL can only handle STRUCTURED/cleaned/not flexible data; DBA
- Create a table: 
CREATE TABLE Users (
	name VARCHAR(128),
	email VARCHAR(128)
)
- Retrieve data: SELECT * FROM Users WHERE... ORDER BY name
SELECT COUNT(*) FROM Users - # Number of rows
- Insert data:INSERT INTO Users (name, email) VALUES ('Kristin','kri@umiche.edu')
- Delete data:DELETE FROM Users WHERE name='Kristin'; DELETE FROM Users
- Update data:UPDATE ssers SET name = 'Charles' WHERE email = 'sal@umiche.edu'

* using database with python
import sqlite3
conn = sqlite.connect('C:\Users\Sharon\Desktop\Python\code\emaildb2.sqlite3')
#store database into emaildb2.sqlite3 (create new db)
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 = 'C:\Users\Sharon\Desktop\Python\code\mbox.txt'
handle = open(fname)
for line in handle:
    if not line.startswith('From '): continue
    pieces = line.split()
    email = pieces[1]
    emailsplit = email.split('@')
    org = emailsplit[1]
    cur.execute('SELECT count FROM Counts WHERE org = ?', (org, ))
    # so far count is not a variable, but just a field
    try:
        count = cur.fetchone()[0] #Using fetchone to get rowid or None
        # so far cont is defined
        cur.execute('UPDATE Counts SET count=count+1 WHERE org = ?',(org, ))
    except:
        cur.execute('''INSERT INTO Counts(org,count)
                    VALUES (?,1)''',
                    (org, ))
    conn.commit()

#https://www.sqlite.org/lang_select.html
sqlstr = 'SELECT org, count FROM Counts ORDER BY count DESC LIMIT 10'
for row in cur.execute(sqlstr):
    print str(row[0]),row[1]
cur.close()