import sqlite3 #version 3.38 or higher

print('starting..')
cnn=None
filename = r"C:\dev\my_sqlite.db"
try:
    cnn = sqlite3.connect(filename)
    print('database connected..')
    cs = cnn.cursor()
    sql = ("Create Table MY_DATES ("
           " DateID int not null primary key,"
           " TextDate text,"
           " IntDate int,"
           " Comment text);")
    cs.execute(sql)
    print('created..')
    sql = ("Insert Into MY_DATES"
           " (DateID, TextDate, IntDate, Comment)"
           " Values (1, date('2022-08-15'),"
           " unixepoch('2022-08-20'), 'Example 1');")
    cs.execute(sql)
    sql = ("Insert Into MY_DATES"
           " (DateID, TextDate, IntDate, Comment)"
           " Values (2, date('2022-08-16 14:34'),"
           " unixepoch('2022-08-21 23:14'), 'Example 2');")
    cs.execute(sql)
    sql = ("Insert Into MY_DATES"
           " (DateID, TextDate, IntDate, Comment)"
           " Values (3, date('2022-08-22 14:55:01'),"
           " unixepoch('2022-09-30 01:45:14'), 'Example 3');")
    cs.execute(sql)
    sql = ("Insert Into MY_DATES"
           " (DateID, TextDate, IntDate, Comment)"
           " Values (4, datetime('2022-08-22 14:55:01'),"
           " unixepoch('2022-09-30 01:45:14'), 'Example 4');")
    cs.execute(sql)
    cnn.commit()
    print('inserted..')
    sql = 'Select * From MY_DATES;' #first look, then next statement
    #sql = "Select date(IntDate, 'unixepoch') MyUnixDate From MY_DATES;"
    recs = cs.execute(sql)
    for rec in recs:
        print(rec)
except Exception as e:
    print(e)
finally:
    if cnn:
        cnn.close()
        print('connection closed..')
print('done.')
    
           
