sqllite-example.py

db = 'test.db'
table = 'Table'

releaseLabel = "releaseLabel"
applicationString = "applicationString"
account = "account"
creationDate = "creationDate"

def create_table(overwrite: bool):
  if overwrite and table_exists(table):
    with sqlite3.connect(db) as conn:
      conn.execute("drop table {}".format(table))

  with sqlite3.connect(db) as conn:
    conn.execute('''create table JobFlow(
       accountId text,
       releaseLabel text,
       applicationString text,
       creationDateTime text
     )''')

def table_exists(table: str) -> bool:
  with sqlite3.connect(db) as conn:
    try:
      conn.execute("select * from {} limit 1".format(table))
      print("Table DOES exists ")
      return True
    except sqlite3.OperationalError as e:
      print("Table DOES NOT exist")
      return False

def populate_table():
  values = [('12345', 'emr-5.10.0',json.dumps([{"Name": "Hive", "Version": "1.2.1"}]),"2019-01-01 00:00:01Z"),
            ('12345', 'emr-5.11.0',json.dumps([{"Name": "Spark", "Version": "1.2.2"}]),"2019-01-01 00:00:05Z"),
            ('12345', 'emr-5.12.0',json.dumps([{"Name": "Hbase", "Version": "1.2.2"},{"Name": "Hive", "Version": "1.2.1"}]),"2019-01-01 00:00:15Z"),
            ('12345', 'emr-5.10.0',json.dumps([{"Name": "Hive", "Version": "1.2.3"}]),"2019-01-01 00:00:29Z")]
  with sqlite3.connect(db) as conn:
    conn.executemany("insert into {} values(?,?, ?, ?)".format(table),values)

def query_table():
  with sqlite3.connect(db) as conn:
   for row in conn.execute("select * from {}".format(table)):
    print(row)
   for row in conn.execute("select count(*), releaseLabel from {} group by releaseLabel".format(table)):
     print(row)
   for row in conn.execute("select releaseLabel from {}".format(table)):
     print(row)
   for row in conn.execute("select accountId, count(*) from {} group by accountId".format(table)):
     print(row)