'''
Xiannong Meng
2021-10-25
https://docs.python.org/3/library/sqlite3.html
with minor modification to 
1. define and use a function create_table()
2. define and use a function query_db()
3. define and use a function load_db() that can load either from disk or memory

NOTE: We need to investigte how to run sql query securely (in-memory).
The above post has an example to query from ":memory:" but haven't been
successfully using it yet.

Resolved: See the program in 'python-sqlite-mem.py'
'''
import sqlite3

def create_table(cur):
    # Create table
    cur.execute("CREATE TABLE stocks (date_text, trans_text, symbol_text, qty_real, price_real)")

    # Insert a row of data
    cur.execute("INSERT INTO stocks VALUES ('2006-01-05','BUY','RHAT',100,35.14)")
    cur.execute("INSERT INTO stocks VALUES ('2006-03-28', 'BUY', 'IBM', 1000, 45.0)")
    cur.execute("INSERT INTO stocks VALUES ('2006-04-06', 'SELL', 'IBM', 500, 53.0)")
    cur.execute("INSERT INTO stocks VALUES ('2006-04-05', 'BUY', 'MSFT', 1000, 72.0)")


def create_db(fname):
    con = sqlite3.connect(fname)
    cur = con.cursor()

    create_table(cur)

    # Save (commit) the changes
    con.commit()

    # We can also close the connection if we are done with it.
    # Just be sure any changes have been committed or they will be lost.
    con.close()

def load_db(where):
    '''
    where == ":memory" or
    where == disk file name
    e.g., load_db("example.db") creates a disk file
    load_db(":memory") creates a memory copy only
    '''
    con = sqlite3.connect(where)
    return con

def query_db(where):
    '''
    where == ":memory:" or
    where == disk file name
    e.g., load_db("example.db") creates a disk file
    load_db(":memory") creates a memory copy only
    note that running execute() agains a db in disk file presents security risk
    '''
    con = load_db(where)
    cur = con.cursor()
    cur.execute("select * from stocks")
    print(cur.fetchall())
    con.close()

create_db('example.db')  # should run once only to create the db
query_db('example.db')
'''
create_db(':memory:')  # should run once only to create the db
query_db(':memory:')
'''