Day 31 Of 100Dayofcode : Toy Project Using Python And Sqlite3

Today also continue to learn database on python. Tried to write more database to read data from file. Did some code related to python function with the help of goggle. Learned more about database with the help of friend.

I try to right simple project for the school using python. Which manage the student data.

Project Using Python And Sqlite3

My code is start with importing sqlite3. If there is already Students table then drop out that table and recreate new Students table. Read the data from student_data.txt file. And there is list of house. This code distribute students to these houses.

import sqlite3

conn = sqlite3.connect('school.sqlite')
cur = conn.cursor()
cur.execute('''DROP TABLE IF EXISTS Students''')
cur.execute('''CREATE TABLE Students (id INTEGER NOT NULL PRIMARY KEY AUTOINCREMENT UNIQUE,
                name TEXT, dob INTEGER,gender TEXT,House TEXT)''')

lines = open('student_data.txt').read().splitlines()
houses = ['Sagarmatha','Maxapuchre','Annapurna','Gaurishankhar']
for i,data in enumerate(lines):
    data =data.split(',')
    house = houses[i%len(houses)]


    cur.execute('''INSERT INTO Students(name,dob,gender,House)VALUES(?,?,?,?)''',(data[0],data[2],data[3],house))

cur.execute('SELECT name, dob,gender,House FROM Students')
for row in cur:
    print(row)
conn.commit()

cur.close()

Below code collect the students who belong to the particular house. For this I used SELECT * FROM

cur = conn.cursor()
dep = ['Sagarmatha']
cur.execute('''SELECT * FROM Students WHERE House = (?)''',(dep))
for data in cur:
    print(data)
cur.close()

When run the above code we can find following output.

(1, 'Durga Pokharel', 2055, 'Male', 'Sagarmatha')
(5, 'Somu Gyawali', 2054, 'Male', 'Sagarmatha')
(9, 'Shirjana Dangal', 2057, 'Female', 'Sagarmatha')
(13, 'Shreeram Pokharel', 2056, 'Male', 'Sagarmatha')
(17, 'Sombhu Lama', 2058, 'Male', 'Sagarmatha')
(21, 'Dhurba Parajuli', 2052, 'Male', 'Sagarmatha')
(25, 'Ram Parajuli', 2055, 'Male', 'Sagarmatha')
(29, 'Goma Sinor', 2050, 'Female', 'Sagarmatha')
(33, 'Suresh Cena', 2030, 'Male', 'Sagarmatha')
(37, 'Devi Bhattarai', 2018, 'Male', 'Sagarmatha')
(41, 'Naran Lama', 2037, 'Male', 'Sagarmatha')
(45, 'Naran Pokharel', 2034, 'Male', 'Sagarmatha')
(49, 'Naran Bogati', 2044, 'Male', 'Sagarmatha')
(53, 'Naran Hajari', 2030, 'Male', 'Sagarmatha')
(57, 'Naran Bhatta', 2051, 'Male', 'Sagarmatha')
(61, 'Naran Bhattarai', 2050, 'Male', 'Sagarmatha')
(65, 'Reaham Bahadur Phuyal', 57, 'Male', 'Sagarmatha')
(69, 'Hari Bahadur Phuyal', 2057, 'Male', 'Sagarmatha')
(73, 'Himal Bahadur Timilsina', 2057, 'Male', 'Sagarmatha')
(77, ' Vima Bahadur Hajari', 2052, 'Male', 'Sagarmatha')
(81, 'Umesh Bahadur Bhatta', 2059, 'Male', 'Sagarmatha')
(85, 'Tila Thapa', 2020, 'Female', 'Sagarmatha')
(89, 'Torpe Pokharel', 2039, 'Female', 'Sagarmatha')
(93, 'Torpe Syangbo', 2036, 'Male', 'Sagarmatha')
(97, 'Lili Syangbo', 2036, 'Female', 'Sagarmatha')
(101, 'Devi Rana', 2055, 'Female', 'Sagarmatha')
(105, 'Silu Darji', 2055, 'Female', 'Sagarmatha')
(109, 'Shyam Garama', 2060, 'Male', 'Sagarmatha')
(113, 'Saru Phuyal', 2047, 'Femal', 'Sagarmatha')
(117, 'Ramesh Parajuli', 2042, 'Male', 'Sagarmatha')
(121, 'Anupam Parajuli', 2050, 'Male', 'Sagarmatha')
(125, 'Manisha Poudel', 2049, 'Female', 'Sagarmatha')
(129, 'Lila Hajari', 2022, 'Female', 'Sagarmatha')
(133, 'Devi Bhatta', 2020, 'Male', 'Sagarmatha')
(137, 'Naran Phuyal', 2029, 'Male', 'Sagarmatha')
(141, 'Naran Pokharel', 2031, 'Male', 'Sagarmatha')
(145, 'Naran Bogati', 2057, 'Male', 'Sagarmatha')
(149, 'Naran Sinor', 2050, 'male', 'Sagarmatha')
(153, 'Naran Silpakar', 2056, 'Male', 'Sagarmatha')
(157, 'Naran Bhattarai', 2045, 'Male', 'Sagarmatha')
(161, 'Shyam Bahadur Phuyal', 2067, 'Male', 'Sagarmatha')
(165, 'Siva Bahadur Phuyal', 2056, 'Male', 'Sagarmatha')
(169, 'Sila Bahadur Bogati', 2035, 'Male', 'Sagarmatha')
(173, 'Shyam Bahadur Sinor', 2042, 'Male', 'Sagarmatha')
(177, 'Bishnu Bahadur Silpakar', 2053, 'Male', 'Sagarmatha')
(181, 'Torpe Kaji', 2051, 'Male', 'Sagarmatha')
(185, 'Torpe Parajuli', 2022, 'Male', 'Sagarmatha')
(189, 'Torpe Timilsina', 2018, 'Male', 'Sagarmatha')
(193, 'Risi Syangbo', 2067, 'Male', 'Sagarmatha')

From this code we can get the student who is bourn in 2060 or later 2060. Like this we can find any age groups students.

cur = conn.cursor()
dob = ['2060']
cur.execute('''SELECT * FROM Students WHERE dob >=  (?)''',(dob))
for data in cur:
    print(data)
cur.close()

When this code run we can find folloing output.

(12, 'Shyam Garama', 2060, 'Male', 'Gaurishankhar')
(18, 'Silu Parajuli', 2062, 'Female', 'Maxapuchre')
(22, 'Milan Parajuli', 2062, 'Male', 'Maxapuchre')
(62, 'Naran Bhattarai', 2061, 'Male', 'Maxapuchre')
(64, 'Shyam Bahadur Phuyal', 2067, 'Male', 'Gaurishankhar')
(66, 'Sureah Bahadur Phuyal', 2064, 'Male', 'Maxapuchre')
(94, 'Torpe Syangbo', 2067, 'Male', 'Maxapuchre')
(96, 'Risi Syangbo', 2067, 'Male', 'Gaurishankhar')
(109, 'Shyam Garama', 2060, 'Male', 'Sagarmatha')
(115, 'Silu Parajuli', 2062, 'Female', 'Annapurna')
(119, 'Milan Parajuli', 2062, 'Male', 'Annapurna')
(159, 'Naran Bhattarai', 2061, 'Male', 'Annapurna')
(161, 'Shyam Bahadur Phuyal', 2067, 'Male', 'Sagarmatha')
(163, 'Sureah Bahadur Phuyal', 2064, 'Male', 'Annapurna')
(191, 'Torpe Syangbo', 2067, 'Male', 'Annapurna')
(193, 'Risi Syangbo', 2067, 'Male', 'Sagarmatha')

Day 31 of #100DaysOfCode and #Python
* Continue To Learn Database On Python
* Create Some Database For a File.
* Toy Project Using Python And Sqlite3 #100DaysOfCode , #CodeNewbie , #beginner pic.twitter.com/k2clGjOAv0

— Durga Pokharel (@mathdurga) January 24, 2021