6_Search_by_condition_where_clause.py

Sign up Free. Don't forget to check out our challenges, lessons, solve and learn series and more ...

Code Snippet

``````import sqlite3

#Here we attempt to SEARCH FOR SOMETHING SPECIFIC
#In this instance, we want to return the results of all students who have points above 19,000 (this should return two users)
#Note you can have multiple conditions such as: sqlite> WHERE NAME="Pigachee" AND POINTS >= 65000;
def the_where_clause():
conn=sqlite3.connect('test.db')
myrows=conn.execute("SELECT * from STUDENT WHERE POINTS >= 19000;")
#conn.commit()
print("The students who have achieved points above 19000 are:")
print("=======================================")
for row in myrows:
print(row)
conn.close()

def deleting():
conn=sqlite3.connect('test.db')
conn.execute("DELETE from STUDENT where ID = 3;")
conn.commit()
print("Total number of rows deleted :", conn.total_changes)

cursor = conn.execute("SELECT id, name, comment, points from STUDENT")
for row in cursor:
print("ID = ", row[0])
print("NAME = ", row[1])
print("POINTS = ", row[3], "\n")
print("====Your request to delete has been completed=====")
conn.close()

def update_database():
conn = sqlite3.connect('test.db')
conn.execute("UPDATE STUDENT set POINTS = 99000.00 where ID = 4")
conn.commit
print("Total number of rows updated :", conn.total_changes)

cursor = conn.execute("SELECT id, name, comment, points from STUDENT")
for row in cursor:
print ("ID = ", row[0])
print ("NAME = ", row[1])
print ("COMMENT = ", row[2])
print ("POINTS = ", row[3], "\n")

print("===Request complete===")
conn.close()

def create_table():
conn = sqlite3.connect('test.db')
print("Opened database")
conn.execute('''CREATE TABLE STUDENT
(ID INT PRIMARY KEY     NOT NULL,
NAME           TEXT    NOT NULL,
AGE            INT     NOT NULL,
COMMENT        CHAR(50),
POINTS         REAL);''')
print("A database table has been created now")
conn.close()

conn=sqlite3.connect('test.db')

conn.execute("INSERT INTO STUDENT (ID,NAME,AGE,COMMENT,POINTS) \
VALUES (1, 'Jonathan', 14, 'One of our top students!', 20000.00 )");

conn.execute("INSERT INTO STUDENT (ID,NAME,AGE,COMMENT,POINTS) \
VALUES (2, 'Ruth', 15, 'An all rounder - wonderful!', 15000.00 )");

conn.execute("INSERT INTO STUDENT (ID,NAME,AGE,COMMENT,POINTS) \
VALUES (3, 'Nick', 14, 'Lacks drive and motivation. Rude.', 2.00 )");

conn.execute("INSERT INTO STUDENT (ID,NAME,AGE,COMMENT,POINTS) \
VALUES (4, 'Pigachee', 15, 'No.1 at everything.Simply Amazing!', 65000.00 )");

conn.commit()
print("Records have been created")
conn.close()

def fetch_display():
conn = sqlite3.connect('test.db')
cursor = conn.execute("SELECT id, name, comment, points from STUDENT")
for row in cursor:
print ("ID=" ,row[0])
print ("NAME = ", row[1])
print ("COMMENT = ", row[2])
print ("POINTS = ", row[3], "\n")

print ("Operation has been completed as per your request")
conn.close()

the_where_clause()

``````