DEV Community

loading...

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

Durga Pokharel
A mathematics student learning to code.
・4 min read

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()
Enter fullscreen mode Exit fullscreen mode

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()
Enter fullscreen mode Exit fullscreen mode

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')

Enter fullscreen mode Exit fullscreen mode

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()
Enter fullscreen mode Exit fullscreen mode

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')
Enter fullscreen mode Exit fullscreen mode

Discussion (0)