Practical 3 of python

Practical of python

import sqlite3

import matplotlib.pyplot as plt

import numpy as np

%matplotlib inline

#point to the testdb.sqlite

sqlite_file = ‘testdb.sqlite’

#create a connection

conn = sqlite3.connect(sqlite_file)

#define sql queries you wish to run

five_rows = ‘SELECT * FROM record LIMIT 5’

count = ‘SELECT COUNT(*) FROM record’

female_students_count = «SELECT COUNT(*) FROM RECORD WHERE SEX=’F’ «

male_students_who_failed = «»»

SELECT Sid, Sname, Mark from RECORD

WHERE Uid=’300581′ and mark < 50

female_student_who_scored_highest = «»»

select Sid, Sname from RECORD WHERE Uid= ‘300144’ and Sex=’F’

and mark = (select max(mark) from record where

Uid= ‘300144’ and Sex=’F’)»»»

female_student_who_scored_lowest = «»»

select Sid, Sname from RECORD WHERE Uid= ‘300144’ and Sex=’F’

and mark = (select min(mark) from record where

Uid= ‘300144’ and Sex=’F’)»»»

youngest_student = «»»SELECT Sid, Sname, DOB FROM RECORD

where DOB = (select MAX(DOB) FROM RECORD)

GROUP BY Sid «»»

oldest_student = «»»SELECT Sid, Sname, DOB FROM RECORD where

DOB = (select MIN(DOB) FROM RECORD) GROUP BY Sid «»»

top_5_students_average = «»»SELECT Sid,Sname, avg(mark)Average_Mark from record

group by Sid order by avg(mark) desc limit 5″»»

comparing_by_sex = «»»

SELECT Sex, AVG(Mark) FROM record GROUP BY Sex

def execute_query(sql):

Define a function to execute sql queries,

takes the sql statement as parameter,

prints the result after execution

#open a cursor object

c = conn.cursor()

c.execute(sql)

result = [[str(item) for item in results] for results in c.fetchall()]

for row in result:

print row

c.close()

conn.close()

def comparison(sql):

Define a function to execute sql queries,

#open a cursor object

c = conn.cursor()

c.execute(sql)

result = [[item for item in results] for results in c.fetchall()]

for row in result:

output.append(row[1:])

c.close()

conn.close()

return output

call the the function execute the various queries

#execute_query(five_rows)

#execute_query(count)

#execute_query(female_students_count)

#execute_query(male_students_who_failed)

#execute_query(female_student_who_scored_highly)

#execute_query(female_student_who_scored_lowest)

#execute_query(youngest_student)

#execute_query(oldest_student)