Today, we’re gonna work on one of the interesting project based on Class concept in SQLite :-)
I hope you’re aware of what sqlite is and how does it works. If not do refer my blog https://medium.com/@uec18332/sqlite-the-lightweight-and-quick-response-database-152b4face0e3 ✨
Problem:
Write a program for implementing ‘CovidVaccineManagement’
a) Viewing the vaccine status of a person
b)Getting the vaccine s1lot availability
c)Viewing the vaccine status of a particular place
d)Getting the count of vaccinated by gender of a place.
Note: Only class concept has to be used along with sqlite for database
Solution:
✔ First Import Sqlite
✔ To use the module, start by creating a Connection object that represents the database. Here the data will be stored in the vaccine.db file.
✔ Once a Connection has been established, create a Cursor object and call its execute() method to perform SQL commands.
🎈 Creating a table for Vaccine status using name, gender, city and vaccine status
cur.execute(‘’’CREATE TABLE VacStatu(name text,gender text,city text,vacstatus text)’’’)
🎈 Creating another table for Vaccine slot availability based on place, total available slot and total slot
cur.execute(‘’’CREATE TABLE VacSl(place text,TotalSlot integer,AvaSlot integer)’’’)
🎈 Inserting a row of data(values) into vaccine slot table
cur.execute(‘’’INSERT INTO VacSl Values(‘Mussoorie’,100,58),(‘Delhi’,150,35),(‘Kerala’,124,67)’’’)
🎈 Creating a class for assigning a value in a table
class CovVac:
🎈 Creating a user-defined function named vaccinedata
def vaccinedata(self,n):
🎈Defining the variables to get input from the user
for i in range(n):
name=input(“Enter your name: “)
city=input(“Enter your city: “)
gender=input(“Enter your gender: “)
vac=input(“Got vaccinated? (Yes/No)”)
🎈 Inserting values like name, gender, city, vac into VacStatu
cur.execute(‘’’INSERT INTO VacStatu Values(?,?,?,?)’’’,(name,gender,city,vac))
🎈 Defining data's and getting VacStatus data in the Output
def datas(self):
cur.execute(‘’’SELECT * FROM VacStatu’’’)
output1=cur.fetchall()
print(output1)
🎈Defining sta_of_vac and getting name and vacstatus of VacStatu as the output
def sta_of_vac(self):
cur.execute(‘’’SELECT name,vacstatus FROM VacStatu’’’)
output2=cur.fetchall()
print(output2)
🎈 Defining ava_of_slot and getting data from VacSl then priniting it as output
def gen_count(self):
cur.execute(‘’’SELECT city,gender,count(vacstatus) FROM VacStatu WHERE vacstatus==’Yes’ GROUP BY gender,city ‘’’)
output7=cur.fetchall()
print(output7)
🎈Defining mus(mussoorie) to get data like name and vacstatus from VacStatus and print as output
def mus(self):
cur.execute(‘’’SELECT name,vacstatus from VacStatu where city=’Mussoorie’ ‘’’)
output4=cur.fetchall()
print(output4)
🎈 Defining delhi to get data like name and vacstatus from VacStatus and print as output
def delhi(self):
cur.execute(‘’’SELECT name,vacstatus from VacStatu where city=’Delhi’ ‘’’)
output5=cur.fetchall()
print(output5)
🎈 Defining ker(kerala) to get data like name and vacstatus from VacStatus and print as output
def ker(self):
cur.execute(‘’’SELECT name,vacstatus from VacStatu where city=’Kerala’ ‘’’)
output6=cur.fetchall()
print(output6)
🎈 Assigning a variable “n” to get input for No.of.datas you want to enter
n=int(input(“Enter No.Of.Datas you want to enter: “))
🎈Calling CovVac function
cov=CovVac()
cov.vaccinedata(n)
🎈 Viewing the vaccine status of a person by calling the function
cov.sta_of_vac()
🎈 Getting the vaccine slot availability by calling the function
cov.ava_of_slot()
🎈 Viewing the vaccine status of a particular place by calling the functions
cov.mus()
cov.delhi()
cov.ker()
🎈Getting the count of vaccinated by gender of a place by calling the function
cov.gen_count()
⛳Code of Covid Vaccination Management
import sqlite3
con=sqlite3.connect(‘CovidVaccine.db’)
cur=con.cursor()
cur.execute(‘’’CREATE TABLE VacStatu(name text,gender text,city text,vacstatus text)’’’)
cur.execute(‘’’CREATE TABLE VacSl(place text,TotalSlot integer,AvaSlot integer)’’’)
cur.execute(‘’’INSERT INTO VacSl Values(‘Mussoorie’,100,58),(‘Delhi’,150,35),(‘Kerala’,124,67)’’’)
class CovVac:
def vaccinedata(self,n):
for i in range(n):
name=input(“Enter your name: “)
city=input(“Enter your city: “)
gender=input(“Enter your gender: “)
vac=input(“Got vaccinated? (Yes/No)”)
cur.execute(‘’’INSERT INTO VacStatu Values(?,?,?,?)’’’,(name,gender,city,vac))
def datas(self):
cur.execute(‘’’SELECT * FROM VacStatu’’’)
output1=cur.fetchall()
print(output1)
def sta_of_vac(self):
cur.execute(‘’’SELECT name,vacstatus FROM VacStatu’’’)
output2=cur.fetchall()
print(output2)
def gen_count(self):
cur.execute(‘’’SELECT city,gender,count(vacstatus) FROM VacStatu WHERE vacstatus==’Yes’ GROUP BY gender,city ‘’’)
output7=cur.fetchall()
print(output7)
def ava_of_slot(self):
cur.execute(‘’’SELECT * FROM VacSl’’’)
output3=cur.fetchall()
print(output3)
def mus(self):
cur.execute(‘’’SELECT name,vacstatus from VacStatu where city=’Mussoorie’ ‘’’)
output4=cur.fetchall()
print(output4)
def delhi(self):
cur.execute(‘’’SELECT name,vacstatus from VacStatu where city=’Delhi’ ‘’’)
output5=cur.fetchall()
print(output5)
def ker(self):
cur.execute(‘’’SELECT name,vacstatus from VacStatu where city=’Kerala’ ‘’’)
output6=cur.fetchall()
print(output6)
n=int(input(“Enter No.Of.Datas you want to enter: “))
cov=CovVac()
cov.vaccinedata(n)
✨Output
a) Viewing the vaccine status of a person
cov.sta_of_vac()
Output:
[(‘name1’, ‘Yes’), (‘name2’,’No’), (‘name3’, ‘Yes’)]
b) Getting the vaccine slot availability
cov.ava_of_slot()
Output:
[(‘Mussoorie’, 100, 58), (‘Delhi’, 150, 35), (‘Kerala’, 124, 67)]
c) Viewing the vaccine status of a particular place
cov.mus()
Output:
[(‘name3’, ‘Yes’)]
cov.delhi()
Output:
[(‘name1’, ‘Yes’)]
Cov.ker()
Output:
[(‘name2’, ‘No’)]
d) Getting the count of vaccinated by gender of a place
cov.gen_count()
Output:
[(‘Mussoorie’, ‘female’, 1), (‘Delhi’, ‘male’, 1), (‘Kerala’, ’female’, 1)]
