import sqlite3
my_conn = sqlite3.connect('my_db')
print("Connected to database successfully")
try:
my_conn.execute('''DROP table student;''')
print("student table deleted")
except sqlite3.Error as my_error:
print("error: ",my_error)
try:
my_conn.execute('''
CREATE TABLE IF NOT EXISTS student(id integer primary key,
name text,
class text,
mark integer,
sex text,
dot text
);''')
my_conn.commit()
print("Student Table created successfully")
except sqlite3.Error as my_error:
print("error: ",my_error)
r_set=my_conn.execute('''INSERT INTO `student` (`id`, `name`, `class`, `mark`, `sex`,`dot`) VALUES
(1, 'John Deo', 'Four', 75, 'female','2020-12-20'),
(2, 'Max Ruin', 'Three', 85, 'male','2020-12-21'),
(3, 'Arnold', 'Three', 55, 'male','2019-11-20'),
(4, 'Krish Star', 'Four', 60, 'female','2020-08-16'),
(5, 'John Mike', 'Four', 60, 'female','2020-09-16'),
(6, 'Alex John', 'Four', 55, 'male','2020-10-16'),
(7, 'My John Rob', 'Five', 78, 'male','2020-11-16'),
(8, 'Asruid', 'Five', 85, 'male','2020-12-16'),
(9, 'Tes Qry', 'Six', 78, 'male','2020-08-16'),
(10, 'Big John', 'Four', 55, 'female','2020-08-16'),
(11, 'Ronald', 'Six', 89, 'female','2020-10-18'),
(12, 'Recky', 'Six', 94, 'female','2020-11-18'),
(13, 'Kty', 'Seven', 88, 'female','2020-10-28'),
(14, 'Bigy', 'Seven', 88, 'female','2020-11-18'),
(15, 'Tade Row', 'Four', 88, 'male','2020-12-18'),
(16, 'Gimmy', 'Four', 88, 'male', '2020-12-10'),
(17, 'Tumyu', 'Six', 54, 'male','2020-10-18'),
(18, 'Honny', 'Five', 75, 'male','2020-10-14'),
(19, 'Tinny', 'Nine', 18, 'male','2020-12-18'),
(20, 'Jackly', 'Nine', 65, 'female','2020-10-11'),
(21, 'Babby John', 'Four', 69, 'female','2020-10-13'),
(22, 'Reggid', 'Seven', 70, 'female','2020-05-18'),
(23, 'Herod', 'Eight', 79, 'male','2020-04-18'),
(24, 'Tiddy Now', 'Seven', 78, 'male','2020-10-23'),
(25, 'Giff Tow', 'Seven', 88, 'male','2020-10-12'),
(26, 'Crelea', 'Seven', 79, 'male','2020-12-18'),
(27, 'Big Nose', 'Three', 81, 'female','2020-07-18'),
(28, 'Rojj Base', 'Seven', 86, 'female','2020-07-19'),
(29, 'Tess Played', 'Seven', 55, 'male','2020-10-08'),
(30, 'Reppy Red', 'Six', 79, 'female','2020-12-08'),
(31, 'Marry Toeey', 'Four', 88, 'male','2020-11-28'),
(32, 'Binn Rott', 'Seven', 90, 'female','2020-11-17'),
(33, 'Kenn Rein', 'Six', 96, 'female','2020-10-25'),
(34, 'Gain Toe', 'Seven', 69, 'male','2020-11-14'),
(35, 'Rows Noump', 'Six', 88, 'female','2020-10-18');''')
my_conn.commit()
r_set=my_conn.execute('''SELECT * from student''');
for row in r_set:
pass
#print(row)
r_set=my_conn.execute('''select name from sqlite_master where type = 'table' ''')
for row in r_set:
print(row)
r_set=my_conn.execute('''select count(*) from student ''')
for row in r_set:
print(row)
my_data=('2020-05-01','2020-07-20')
q="SELECT * FROM student WHERE dot between ? and ? "
try:
my_cursor=my_conn.execute(q,my_data)
for row in my_cursor:
print(row[0],row[1],row[3],row[2],row[4],row[5])
except sqlite3.Error as my_error:
print("error: ",my_error)
my_data=('2020-04-01','2020-06-30','2020-07-01','2020-09-30',
'2020-10-01','2020-12-31','2021-01-01','2021-03-31')
q="SELECT id,name,mark,class,dot,\
CASE WHEN dot BETWEEN ? AND ? THEN 'Q1'\
WHEN dot BETWEEN ? AND ? THEN 'Q2'\
WHEN dot BETWEEN ? AND ? THEN 'Q3'\
WHEN dot BETWEEN ? AND ? THEN 'Q4'\
ELSE 'Outside FY' \
END AS Date FROM `student`"
try:
my_cursor=my_conn.execute(q,my_data)
for row in my_cursor:
print(row[0],row[1],row[3],row[2],row[4],'-',row[5])
except sqlite3.Error as my_error:
print("error: ",my_error)
my_data=('2020-04-01','2020-06-30','2020-07-01','2020-09-30','2020-10-01','2020-12-31','2021-01-01','2021-03-31')
q="SELECT id,name,mark,class,dot,\
CASE WHEN dot BETWEEN ? AND ? THEN 'Q1'\
WHEN dot BETWEEN ? AND ? THEN 'Q2'\
WHEN dot BETWEEN ? AND ? THEN 'Q3'\
WHEN dot BETWEEN ? AND ? THEN 'Q4'\
ELSE 'Outside FY' \
END AS Date FROM `student`"
try:
my_cursor=my_conn.execute(q,my_data)
for row in my_cursor:
print(row[0],row[1],row[3],row[2],row[4],'-',row[5])
except sqlite3.Error as my_error:
print("error: ",my_error)
my_conn.close()