###################################################################
# SQLITE - Insert into tables multiple rows
###################################################################
import sqlite3

conn  = sqlite3.connect('/home/sultans/data/sqlite/sqlite.db')      #server database
#conn = sqlite3.connect('/sqlite/sqlite.db')                        #local database 
curs  = conn.cursor( )

### Insert into course table ########################################
sql = """
    INSERT into course (course_id,description,price)
    VALUES (?,?,?) 
"""
rows = [('X52-9272','SQL Programming Language',540),
        ('X52-9759','XML for Web Development',1095),
        ('X52-9740','Web Page Development with HTML',1095),
        ('X52-9238','Introduction to Java',1095),
        ('X52-9742','Intensive Web Development',3995),
        ('X52-9755','JavaScript',1095),
        ('X52-9267','Object Oriented Analysis and Design',995),
        ('X52-9562','Java Web Services',1095) ]
                      
curs.executemany(sql, rows)


### Insert into instructor table #####################################
sql = """
    INSERT into instructor (lname,fname,ssn,sex)
    VALUES (?,?,?,?) 
"""
rows = [('Sultan','Sam',    '000-02-0001','M'),
        ('Pefanis','George','000-02-0002','M'),
        ('martin','susan',  '000-02-0003','F'),
        ('Paller','Marc',   '000-02-0004','M'),
        ('O''Brien','Mary', '000-02-0005','F'),
        ('Katz','Eric',     '000-02-0006','M')]
                       
curs.executemany(sql, rows)


### Insert into student table #######################################
sql = """
    INSERT into student (lname,fname,ssn,sex)
    VALUES (?,?,?,?) 
"""
rows = [('Burns','Barbara',  '000-01-0001','F'),
        ('Cambria','Vincent','000-01-0002','M'),
        ('Davidson','Duncan','000-01-0003','M'),
        ('Smith','David',    '000-01-0004','M'),
        ('Thomas','Eugene',  '000-01-0005','M'),
        ('Owens','Cynthia',  '000-01-0006','F'),
        ('Willis','Eileen',  '000-01-0007','F'),
        ('Myers','Rick',     '000-01-0008','M'),
        ('Ryan','Natasha',   '000-01-0009','F'),
        ('Stack','Patrick',  '000-01-0010','M'),
        ('Tobias','Wayne',   '000-01-0011','M'),
        ('Race','Joseph',    '000-01-0012','M'),
        ('Nelson','Colette', '000-01-0013','F'),
        ('Brinson','Angel',  '000-01-0014','F'),
        ('Soley','John',     '000-01-0015','M'),
        ('Grace','Robert',   '000-01-0016','M'),
        ('Tok','Kathy',      '000-01-0017','F'),
        ('miller','janet',   '000-01-0018','F'),
        ('Austin','Maria',   '000-01-0019','F'),
        ('Teagan','Edward',  '000-01-0020','M'),
        ('Milgrom','Anya',   '000-01-0021','F'),
        ('Vasquez','Lillian','000-01-0022','F'),
        ('Chan','David',     '000-01-0023','M'),
        ('James','Phyllis',  '000-01-0024','F'),
        ('Sultan','Sam',     '000-02-0001','M')]
                      
curs.executemany(sql, rows)


### Insert into class table #######################################
sql = """
    INSERT into class (course_id,session_id,inst_ssn,stu_ssn)
    VALUES (?,?,?,?) 
"""
rows = [('X52-9272',1,'000-02-0001','000-01-0002'),
        ('X52-9272',1,'000-02-0001','000-01-0004'),
        ('X52-9272',1,'000-02-0001','000-01-0006'),
        ('X52-9272',1,'000-02-0001','000-01-0008'),
        ('X52-9272',1,'000-02-0001','000-01-0010'),
        ('X52-9272',1,'000-02-0001','000-01-0012'),
        ('X52-9272',1,'000-02-0001','000-01-0014'),
        ('X52-9272',1,'000-02-0001','000-01-0016'),
        ('X52-9272',1,'000-02-0001','000-01-0018'),
        ('X52-9272',1,'000-02-0001','000-01-0020'),
        ('X52-9272',1,'000-02-0001','000-01-0022'),

        ('X52-9759',1,'000-02-0001','000-01-0001'),
        ('X52-9759',1,'000-02-0001','000-01-0003'),
        ('X52-9759',1,'000-02-0001','000-01-0005'),
        ('X52-9759',1,'000-02-0001','000-01-0007'),
        ('X52-9759',1,'000-02-0001','000-01-0008'),
        ('X52-9759',1,'000-02-0001','000-01-0010'),
        ('X52-9759',1,'000-02-0001','000-01-0013'),
        ('X52-9759',1,'000-02-0001','000-01-0015'),
        ('X52-9759',1,'000-02-0001','000-01-0017'),
        ('X52-9759',1,'000-02-0001','000-01-0019'),

        ('X52-9740',1,'000-02-0002','000-01-0001'),
        ('X52-9740',1,'000-02-0002','000-01-0002'),
        ('X52-9740',1,'000-02-0002','000-01-0003'),
        ('X52-9740',1,'000-02-0002','000-01-0004'),
        ('X52-9740',1,'000-02-0002','000-01-0005'),
        ('X52-9740',1,'000-02-0002','000-01-0006'),
        ('X52-9740',1,'000-02-0002','000-01-0007'),
        ('X52-9740',1,'000-02-0002','000-01-0008'),
        ('X52-9740',1,'000-02-0002','000-01-0009'),
        ('X52-9740',1,'000-02-0002','000-01-0010'),

        ('X52-9740',2,'000-02-0003','000-01-0011'),
        ('X52-9740',2,'000-02-0003','000-01-0012'),
        ('X52-9740',2,'000-02-0003','000-01-0013'),
        ('X52-9740',2,'000-02-0003','000-01-0014'),
        ('X52-9740',2,'000-02-0003','000-01-0015'),
        ('X52-9740',2,'000-02-0003','000-01-0016'),
        ('X52-9740',2,'000-02-0003','000-01-0017'),
        ('X52-9740',2,'000-02-0003','000-01-0018'),
        ('X52-9740',2,'000-02-0003','000-01-0019'),
        ('X52-9740',2,'000-02-0003','000-01-0020'),
        ('X52-9740',2,'000-02-0003','000-01-0021'),
        ('X52-9740',2,'000-02-0003','000-01-0022'),

        ('X52-9755',1,'000-02-0006','000-01-0003'),
        ('X52-9755',1,'000-02-0006','000-01-0006'),
        ('X52-9755',1,'000-02-0006','000-01-0009'),
        ('X52-9755',1,'000-02-0006','000-01-0012'),
        ('X52-9755',1,'000-02-0006','000-01-0015'),
        ('X52-9755',1,'000-02-0006','000-01-0018'),
        ('X52-9755',1,'000-02-0006','000-01-0021')]

curs.executemany(sql, rows)

### Insert into payment table ########################################
sql = """
    INSERT into payment (vendor,amount,description,pay_date)
    VALUES (?,?,?,?) 
"""
rows = [('Con Edison',         125.15,'Electric',        '2017-01-31'),  
        ('Verizon',             54.79,'Home Phone',      '2017-02-28'),  
        ('Allstate',          1240.50,'Home Insurance',  '2017-03-01'),  
        ('Verizon',             49.95,'Cell Phone',      '2017-03-02'),  
        ('Verizon',             39.95,'DSL Line',        '2017-03-03'), 
        ('Con Edison',          59.63,'Electric',        '2017-03-04'),  
        ('Department of Water',155.43,'Water and Sewers','2017-03-05'),  
        ('Allstate',          1550.25,'Car Insurance',   '2017-03-06') ]
                      
curs.executemany(sql, rows)


conn.commit()
conn.close()