In [19]:
import sqlite3
my_conn = sqlite3.connect('my_db')
print("Connected to database successfully")

Connected to database successfully
In [ ]:
try:
  my_conn.execute('''DROP table student;''')
  print("student table deleted")
except sqlite3.Error as my_error:
  print("error: ",my_error)
In [50]:
try:
    my_conn.execute('''
        CREATE TABLE IF NOT EXISTS student(id integer primary key, 
                      name text, 
                      class text, 
                      mark integer, 
                      sex text 
                      );''')
    my_conn.commit()
    print("Student Table created successfully")
except sqlite3.Error as my_error:
  print("error: ",my_error)     

Student Table created successfully
In [3]:
r_set=my_conn.execute('''INSERT INTO `student` (`id`, `name`, `class`, `mark`, `sex`) VALUES
(1, 'John Deo', 'Four', 75, 'female'),
(2, 'Max Ruin', 'Three', 85, 'male'),
(3, 'Arnold', 'Three', 55, 'male'),
(4, 'Krish Star', 'Four', 60, 'female'),
(5, 'John Mike', 'Four', 60, 'female'),
(6, 'Alex John', 'Four', 55, 'male'),
(7, 'My John Rob', 'Five', 78, 'male'),
(8, 'Asruid', 'Five', 85, 'male'),
(9, 'Tes Qry', 'Six', 78, 'male'),
(10, 'Big John', 'Four', 55, 'female'),
(11, 'Ronald', 'Six', 89, 'female'),
(12, 'Recky', 'Six', 94, 'female'),
(13, 'Kty', 'Seven', 88, 'female'),
(14, 'Bigy', 'Seven', 88, 'female'),
(15, 'Tade Row', 'Four', 88, 'male'),
(16, 'Gimmy', 'Four', 88, 'male'),
(17, 'Tumyu', 'Six', 54, 'male'),
(18, 'Honny', 'Five', 75, 'male'),
(19, 'Tinny', 'Nine', 18, 'male'),
(20, 'Jackly', 'Nine', 65, 'female'),
(21, 'Babby John', 'Four', 69, 'female'),
(22, 'Reggid', 'Seven', 55, 'female'),
(23, 'Herod', 'Eight', 79, 'male'),
(24, 'Tiddy Now', 'Seven', 78, 'male'),
(25, 'Giff Tow', 'Seven', 88, 'male'),
(26, 'Crelea', 'Seven', 79, 'male'),
(27, 'Big Nose', 'Three', 81, 'female'),
(28, 'Rojj Base', 'Seven', 86, 'female'),
(29, 'Tess Played', 'Seven', 55, 'male'),
(30, 'Reppy Red', 'Six', 79, 'female'),
(31, 'Marry Toeey', 'Four', 88, 'male'),
(32, 'Binn Rott', 'Seven', 90, 'female'),
(33, 'Kenn Rein', 'Six', 96, 'female'),
(34, 'Gain Toe', 'Seven', 69, 'male'),
(35, 'Rows Noump', 'Six', 88, 'female');''')
my_conn.commit()
In [33]:
r_set=my_conn.execute('''SELECT * from student''');
for row in r_set:
    print(row)
In [5]:
r_set=my_conn.execute('''select name from sqlite_master where type = 'table' ''')
for row in r_set:
    print(row)

('student',)
In [20]:
r_set=my_conn.execute('''select count(*) from student ''')
for row in r_set:
    print(row)

(35,)
In [18]:
q="SELECT  `id` ,  `name` ,  `class` ,  `mark` ,  `sex` , \
CASE class \
WHEN  'Four' THEN   '1st floor' \
WHEN  'Five' THEN    '2nd floor' \
WHEN  'Three' THEN    '2nd floor' \
WHEN  'Two' THEN    '1st floor' \
ELSE 'Ground floor' \
END AS  location \
FROM  `student` "

try:
    my_cursor=my_conn.execute(q)
    
    for row in my_cursor:
        print(row[0],row[1],row[2],row[3],row[4],'-',row[5])
except sqlite3.Error as my_error:
  print("error: ",my_error)

1 John Deo Four 75 female - 1st floor
2 Max Ruin Three 85 male - 2nd floor
3 Arnold Three 55 male - 2nd floor
4 Krish Star Four 60 female - 1st floor
5 John Mike Four 60 female - 1st floor
6 Alex John Four 55 male - 1st floor
7 My John Rob Five 78 male - 2nd floor
8 Asruid Five 85 male - 2nd floor
9 Tes Qry Six 78 male - Ground floor
10 Big John Four 55 female - 1st floor
11 Ronald Six 89 female - Ground floor
12 Recky Six 94 female - Ground floor
13 Kty Seven 88 female - Ground floor
14 Bigy Seven 88 female - Ground floor
15 Tade Row Four 88 male - 1st floor
16 Gimmy Four 88 male - 1st floor
17 Tumyu Six 54 male - Ground floor
18 Honny Five 75 male - 2nd floor
19 Tinny Nine 18 male - Ground floor
20 Jackly Nine 65 female - Ground floor
21 Babby John Four 69 female - 1st floor
22 Reggid Seven 70 female - Ground floor
23 Herod Eight 79 male - Ground floor
24 Tiddy Now Seven 78 male - Ground floor
25 Giff Tow Seven 88 male - Ground floor
26 Crelea Seven 79 male - Ground floor
27 Big Nose Three 81 female - 2nd floor
28 Rojj Base Seven 86 female - Ground floor
29 Tess Played Seven 55 male - Ground floor
30 Reppy Red Six 79 female - Ground floor
31 Marry Toeey Four 88 male - 1st floor
32 Binn Rott Seven 90 female - Ground floor
33 Kenn Rein Six 96 female - Ground floor
34 Gain Toe Seven 69 male - Ground floor
35 Rows Noump Six 88 female - Ground floor
In [21]:
q="SELECT  `id` ,  `name` ,  `class` ,  `mark` ,  `sex` , \
CASE  \
WHEN mark >= 90 THEN   'A' \
WHEN mark >= 80 THEN    'B' \
WHEN mark >= 70 THEN   'C' \
ELSE 'FAIL' \
END AS  grade \
FROM  `student` "
try:
    my_cursor=my_conn.execute(q)
    
    for row in my_cursor:
        print(row[0],row[1],row[2],row[3],row[4],'-',row[5])
except sqlite3.Error as my_error:
  print("error: ",my_error)

1 John Deo Four 75 female - C
2 Max Ruin Three 85 male - B
3 Arnold Three 55 male - FAIL
4 Krish Star Four 60 female - FAIL
5 John Mike Four 60 female - FAIL
6 Alex John Four 55 male - FAIL
7 My John Rob Five 78 male - C
8 Asruid Five 85 male - B
9 Tes Qry Six 78 male - C
10 Big John Four 55 female - FAIL
11 Ronald Six 89 female - B
12 Recky Six 94 female - A
13 Kty Seven 88 female - B
14 Bigy Seven 88 female - B
15 Tade Row Four 88 male - B
16 Gimmy Four 88 male - B
17 Tumyu Six 54 male - FAIL
18 Honny Five 75 male - C
19 Tinny Nine 18 male - FAIL
20 Jackly Nine 65 female - FAIL
21 Babby John Four 69 female - FAIL
22 Reggid Seven 70 female - C
23 Herod Eight 79 male - C
24 Tiddy Now Seven 78 male - C
25 Giff Tow Seven 88 male - B
26 Crelea Seven 79 male - C
27 Big Nose Three 81 female - B
28 Rojj Base Seven 86 female - B
29 Tess Played Seven 55 male - FAIL
30 Reppy Red Six 79 female - C
31 Marry Toeey Four 88 male - B
32 Binn Rott Seven 90 female - A
33 Kenn Rein Six 96 female - A
34 Gain Toe Seven 69 male - FAIL
35 Rows Noump Six 88 female - B
In [9]:
q="SELECT  `id` ,  `name` ,  `class` ,  `mark` ,  `sex` ,\
CASE WHEN mark BETWEEN  90  AND 100 THEN   'A'\
WHEN mark BETWEEN  80  AND 89 THEN    'B'\
WHEN mark BETWEEN  70  AND 79 THEN   'C'\
ELSE 'FAIL' \
END AS  grade FROM  `student`"
try:
    my_cursor=my_conn.execute(q)
    
    for row in my_cursor:
        print(row[0],row[1],row[2],row[3],row[4],row[5])
except sqlite3.Error as my_error:
  print("error: ",my_error)

1 John Deo Four 75 female C
2 Max Ruin Three 85 male B
3 Arnold Three 55 male FAIL
4 Krish Star Four 60 female FAIL
5 John Mike Four 60 female FAIL
6 Alex John Four 55 male FAIL
7 My John Rob Five 78 male C
8 Asruid Five 85 male B
9 Tes Qry Six 78 male C
10 Big John Four 55 female FAIL
11 Ronald Six 89 female B
12 Recky Six 94 female A
13 Kty Seven 88 female B
14 Bigy Seven 88 female B
15 Tade Row Four 88 male B
16 Gimmy Four 88 male B
17 Tumyu Six 54 male FAIL
18 Honny Five 75 male C
19 Tinny Nine 18 male FAIL
20 Jackly Nine 65 female FAIL
21 Babby John Four 69 female FAIL
22 Reggid Seven 70 female C
23 Herod Eight 79 male C
24 Tiddy Now Seven 78 male C
25 Giff Tow Seven 88 male B
26 Crelea Seven 79 male C
27 Big Nose Three 81 female B
28 Rojj Base Seven 86 female B
29 Tess Played Seven 55 male FAIL
30 Reppy Red Six 79 female C
31 Marry Toeey Four 88 male B
32 Binn Rott Seven 90 female A
33 Kenn Rein Six 96 female A
34 Gain Toe Seven 69 male FAIL
35 Rows Noump Six 88 female B
In [15]:
my_data=(90,100,80,89,70,79)
q="SELECT  `id` ,  `name` ,  `class` ,  `mark` ,  `sex` ,\
CASE WHEN mark BETWEEN  ?  AND ? THEN   'A'\
WHEN mark BETWEEN  ?  AND ? THEN    'B'\
WHEN mark BETWEEN  ?  AND ? THEN   'C'\
ELSE 'FAIL' \
END AS  grade FROM  `student`"
try:
    my_cursor=my_conn.execute(q,my_data)
    
    for row in my_cursor:
        print(row[0],row[1],row[2],row[3],row[4],row[5])
except sqlite3.Error as my_error:
  print("error: ",my_error)

1 John Deo Four 75 female C
2 Max Ruin Three 85 male B
3 Arnold Three 55 male FAIL
4 Krish Star Four 60 female FAIL
5 John Mike Four 60 female FAIL
6 Alex John Four 55 male FAIL
7 My John Rob Five 78 male C
8 Asruid Five 85 male B
9 Tes Qry Six 78 male C
10 Big John Four 55 female FAIL
11 Ronald Six 89 female B
12 Recky Six 94 female A
13 Kty Seven 88 female B
14 Bigy Seven 88 female B
15 Tade Row Four 88 male B
16 Gimmy Four 88 male B
17 Tumyu Six 54 male FAIL
18 Honny Five 75 male C
19 Tinny Nine 18 male FAIL
20 Jackly Nine 65 female FAIL
21 Babby John Four 69 female FAIL
22 Reggid Seven 70 female C
23 Herod Eight 79 male C
24 Tiddy Now Seven 78 male C
25 Giff Tow Seven 88 male B
26 Crelea Seven 79 male C
27 Big Nose Three 81 female B
28 Rojj Base Seven 86 female B
29 Tess Played Seven 55 male FAIL
30 Reppy Red Six 79 female C
31 Marry Toeey Four 88 male B
32 Binn Rott Seven 90 female A
33 Kenn Rein Six 96 female A
34 Gain Toe Seven 69 male FAIL
35 Rows Noump Six 88 female B
In [24]:
my_conn.close()