'''
Various routines working with sqlite3 database.
From:

http://www.tutorialspoint.com/sqlite/

https://www.tutorialspoint.com/sqlite/sqlite_python.htm

Xiannong Meng
2017-06-10

Revised
2018-01-30
'''
import sqlite3

def connDB( dbName ):
    """Connecting to sqlite3 database with a file name,
    returning the handle to the calller.
    dbName: the file name where the database is stored."""

    conn = sqlite3.connect(dbName)
    print('Connected to sqlite3 database successfully!')
    return conn

def createTable( conn ):
    """Creating a database from the connector 'conn'
    with the fixed attributes"""

    conn.execute("CREATE TABLE COMPANY\
         (ID INT PRIMARY KEY     NOT NULL,\
         NAME           TEXT    NOT NULL,\
         AGE            INT     NOT NULL,\
         ADDRESS        CHAR(50),\
         SALARY         REAL);")
    print("Table created successfully")

def insertRecs( conn ):
    """Inserting a fixed number of static records into the database"""

    conn.execute("INSERT INTO COMPANY (ID,NAME,AGE,ADDRESS,SALARY) \
    VALUES (1, 'Paul', 32, 'California', 20000.00 )");

    conn.execute("INSERT INTO COMPANY (ID,NAME,AGE,ADDRESS,SALARY) \
    VALUES (2, 'Allen', 25, 'Texas', 15000.00 )");

    conn.execute("INSERT INTO COMPANY (ID,NAME,AGE,ADDRESS,SALARY) \
    VALUES (3, 'Teddy', 23, 'Norway', 20000.00 )");

    conn.execute("INSERT INTO COMPANY (ID,NAME,AGE,ADDRESS,SALARY) \
    VALUES (4, 'Mark', 25, 'Rich-Mond ', 65000.00 )");

    conn.commit()
    print ("Records created successfully")

def selectRecs( conn ):
    """Executing SQL SFW command"""
    
    cursor = conn.execute("SELECT id, name, address, salary from COMPANY")
    print('ID\tName\tAddress\t\tSalary')
    print('-----------------------------------------')
    for row in cursor:
        print(row[0], '\t', row[1], '\t', row[2], '\t', row[3])
    print("Selection done successfully")

def updateRecs( conn ):
    """Updating a record"""

    conn.execute("UPDATE COMPANY set SALARY = 25000.00 where ID = 1")
    conn.commit()
    print('Total number of rows updated :', conn.total_changes)

def deleteRecs( conn ):
    """Deleting a record"""

    conn.execute("DELETE from COMPANY where ID = 2;")
    conn.commit()
    print('Total number of rows deleted :', conn.total_changes)

def testCursor( conn ):
    """Testing database cursor"""

    cursor = conn.execute("SELECT * from COMPANY;")
    for row in cursor:
        print('++++++')
        print('ID = ', row[0])
        print('Name = ', row[1])
        print('Address = ', row[2])
        print('Salary = ', row[3])
    print('Testing cursor done successfully')

def main():

    db_name = input('Enter a new database name : ')
    print('Connecting to DB...')
    conn = connDB(db_name)
    
    print('Creating a table ...')
    createTable(conn)

    print('Inserting records ...')
    insertRecs(conn)

    print('Selecting records ...')
    selectRecs(conn)

    print('Updating records ...')
    updateRecs(conn)

    print('Deleting records ...')
    deleteRecs(conn)

    print('Selecting records ...')
    selectRecs(conn)

    print('Testing cursor (multiple SQL commands) ...')
    testCursor(conn)

    print('Closing DB ...')
    conn.close()

main()