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

Connected to database successfully
In [2]:
try:
  my_conn.execute('''DROP table student;''')
  print("student table deleted")
except sqlite3.Error as my_error:
  print("error: ",my_error) 

student table deleted
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 [3]:
r_set=my_conn.execute('''select count(*) from student ''')
for row in r_set:
    print(row)

(35,)
In [4]:
q="SELECT * FROM  student WHERE name like '%John'"
try:
    my_cursor=my_conn.execute(q)
    
    for row in my_cursor:
        print(row[0],row[1],row[2],row[3],row[4])
except sqlite3.Error as my_error:
  print("error: ",my_error)

6 Alex John Four 55 male
10 Big John Four 55 female
21 Babby John Four 69 female
In [67]:
q="SELECT * FROM  student WHERE name like '%John%'"
#q="SELECT * FROM  student WHERE name like '%John%' or name like '%ow%'"
try:
    my_cursor=my_conn.execute(q)
    
    for row in my_cursor:
        print(row[0],row[1],row[3],row[2],row[4])
except sqlite3.Error as my_error:
  print("error: ",my_error)

1 John Deo 75 Four female
5 John Mike 60 Four female
6 Alex John 55 Four male
7 My John Rob 78 Five male
10 Big John 55 Four female
21 Babby John 69 Four female
In [5]:
my_data=('%john',)
q="SELECT * FROM  student WHERE name like ?"
#q="SELECT * FROM  student WHERE name like '%John%' or name like '%ow%'"
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])
except sqlite3.Error as my_error:
  print("error: ",my_error)

6 Alex John 55 Four male
10 Big John 55 Four female
21 Babby John 69 Four female
In [29]:
my_data=('b%n',) # starting with b and ending with n 
q="SELECT * FROM  student WHERE name like ?"

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

10 Big John 55 Four female
21 Babby John 69 Four female
In [6]:
my_data=('%john%','%ow%')
q="SELECT * FROM  student WHERE name like ? or name like ?"
try:
    my_conn.execute('PRAGMA case_sensitive_like = OFF')
    my_cursor=my_conn.execute(q,my_data)
    
    for row in my_cursor:
        print(row[0],row[1],row[3],row[2],row[4])
except sqlite3.Error as my_error:
  print("error: ",my_error)

1 John Deo 75 Four female
5 John Mike 60 Four female
6 Alex John 55 Four male
7 My John Rob 78 Five male
10 Big John 55 Four female
15 Tade Row 88 Four male
21 Babby John 69 Four female
24 Tiddy Now 78 Seven male
25 Giff Tow 88 Seven male
35 Rows Noump 88 Six female
In [82]:
my_data=('k%',) # starting  with k
#my_data=('ke%',) # starting  with ke
q="SELECT * FROM  student WHERE name like ?"

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])
except sqlite3.Error as my_error:
  print("error: ",my_error)

4 Krish Star 60 Four female
13 Kty 88 Seven female
33 Kenn Rein 96 Six female
In [83]:
my_data=('9_',) # mark in equal or above 90
#my_data=('ke%',) # starting  with ke
q="SELECT * FROM  student WHERE mark like ?"

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])
except sqlite3.Error as my_error:
  print("error: ",my_error)

12 Recky 94 Six female
32 Binn Rott 90 Seven female
33 Kenn Rein 96 Six female
In [8]:
my_data=('b%n',) # starting with b and ending with n 
q="SELECT * FROM  student WHERE name like ?"

try:
    my_conn.execute('PRAGMA case_sensitive_like = ON') # change this to OFF
    my_cursor=my_conn.execute(q,my_data)
    
    for row in my_cursor:
        print(row[0],row[1],row[3],row[2],row[4])
except sqlite3.Error as my_error:
  print("error: ",my_error)
In [53]:
my_conn.close()