'''
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. create a list of commands to use a loop to execute these commands
'''
import sqlite3

cmd_list = ["CREATE TABLE stocks (date_text, trans_text, symbol_text, qty_real, price_real)", \
            "INSERT INTO stocks VALUES ('2006-01-05','BUY','RHAT',100,35.14)", \
            "INSERT INTO stocks VALUES ('2006-03-28', 'BUY', 'IBM', 1000, 45.0)", \
            "INSERT INTO stocks VALUES ('2006-04-06', 'SELL', 'IBM', 500, 53.0)", \
           "INSERT INTO stocks VALUES ('2006-04-05', 'BUY', 'MSFT', 1000, 72.0)"];
def create_table(cur):

    for cmd in cmd_list:
        cur.execute(cmd)

con = sqlite3.connect(':memory:')
cur = con.cursor()

create_table(cur)

# in the following line, the reference 'type' is replacing the field
# 'trans_text' in the table 'stocks'
cur.execute("select * from stocks where trans_text=:type", {"type":"BUY"})

print(cur.fetchall())

# 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()


# the remaining lines of code are left as reference, which
# came originaly from the above website
#cur.execute("create table lang (name, first_appeared)")

# This is the qmark style:
#cur.execute("insert into lang values (?, ?)", ("C", 1972))

'''
# The qmark style used with executemany():
lang_list = [
    ("Fortran", 1957),
    ("Python", 1991),
    ("Go", 2009),
]
cur.executemany("insert into lang values (?, ?)", lang_list)

# And this is the named style:
cur.execute("select * from lang where first_appeared=:year", {"year": 1972})

'''