In [1]:
import sqlite3
my_conn = sqlite3.connect('test_db')
print("Connected to database successfully")
Connected to database successfully
In [7]:
r_set=my_conn.execute("SELECT datetime('NOW')")
for row in r_set:
    print(row)
('2020-08-18 17:14:43',)
In [2]:
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()
my_dt  Table created successfully
In [3]:
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)
error:  CHECK constraint failed: my_Check
In [4]:
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)
error:  CHECK constraint failed: my_Check
In [31]:
r_set=my_conn.execute('''SELECT * from my_dt''');
for row in r_set:
    print(row)
('2020-08-18', '2020-12-13')
('2020-08-18', '2020-12-13')
('2020-08-17', '2020-12-13')
In [11]:
# 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()
Date constraint updated successfully
In [19]:
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)
error:  CHECK constraint failed: my_check2
In [16]:
# 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])
2020-08-18
In [8]:
r_set=my_conn.execute("SELECT date('now','start of month','+1 month','-1 day');")
for row in r_set:
    print(row)
('2020-08-31',)