import sqlite3
my_conn = sqlite3.connect('test_db')
print("Connected to database successfully")
r_set=my_conn.execute("SELECT datetime('NOW')")
for row in r_set:
print(row)
try:
my_conn.execute("CREATE TABLE IF NOT EXISTS my_dt(dt1 text, \
dt2 text,\
CONSTRAINT my_Check CHECK(dt1 <= date('now')))")
my_conn.commit()
print("my_dt Table created successfully")
except sqlite3.Error as my_error:
print("error: ",my_error)
my_conn.commit()
try:
my_conn.execute("INSERT INTO my_dt(dt1,dt2) VALUES \
('2023-08-18','2020-12-13')")
my_conn.commit()
print("Row added successfully")
except sqlite3.Error as my_error:
print("error: ",my_error)
try:
my_conn.execute("INSERT INTO my_dt(dt1,dt2) VALUES \
(date('now','+1 day'),'2020-12-13')")
my_conn.commit()
print("Row added successfully")
except sqlite3.Error as my_error:
print("error: ",my_error)
r_set=my_conn.execute('''SELECT * from my_dt''');
for row in r_set:
print(row)
# Changing CHECK constraint by creating new table
# Copying data from old table to new table
# Deleting old table
# Renaming new table to old table.
try:
my_conn.execute("BEGIN")
my_conn.execute("CREATE TABLE IF NOT EXISTS \
my_dt_new(dt1 text, \
dt2 text, \
CONSTRAINT my_Check CHECK(dt1 <= date('now')),\
CONSTRAINT my_check2 CHECK(date(dt2) >= date(dt1)))")
my_conn.execute("INSERT INTO my_dt_new SELECT * FROM my_dt")
my_conn.execute("DROP table my_dt")
my_conn.execute("ALTER TABLE my_dt_new RENAME TO my_dt")
my_conn.commit()
print("Date constraint updated successfully")
except sqlite3.Error as my_error:
print("error: ",my_error)
my_conn.commit()
try:
my_conn.execute("INSERT INTO my_dt(dt1,dt2) VALUES \
(date('now','-1 day'),date('now','-2 day'))")
my_conn.commit()
print("Row added successfully")
except sqlite3.Error as my_error:
print("error: ",my_error)
# understanding 'now'
q="SELECT date('now','-1 day')"
q="SELECT date('now','-1 month')"
q="SELECT date('now','1 year')"
q="SELECT date('now','1 year','2 month')"
q="SELECT date('now','1 year','2 month','-2 month')"
q="SELECT date('now','1 year','2 month','-2 month','3 day')"
q="SELECT date('now')"
q="SELECT date('now','-1 day')"
r_set=my_conn.execute(q)
data_row=r_set.fetchone()
print(data_row[0])
r_set=my_conn.execute("SELECT date('now','start of month','+1 month','-1 day');")
for row in r_set:
print(row)