Day 30 Of 100DaysOfCode: Done a Dummy Project On SQL

This is my 30th day of #100Daysofcode and #python. Continuing to learned database on python from coursera and the help of goggle. Tried to build database for some text file.

I tried to write a simply project on database. Which take the data from department file and teacher file. If teacher are in valid department then it add that teacher in the database otherwise it ignore that teacher name. My code is given below.

Dummy Project On SQL

My code start with importing the sqlite3. If there is already department table then it drop out that table and then create new table. After that it read the data from department.txt file. Then insert the value from the department.txt. I made empty list to add all the name of department from department.txt file.

import sqlite3

conn = sqlite3.connect('school.sqlite')
cur = conn.cursor()
cur.execute('''DROP TABLE IF EXISTS Department''')
cur.execute('CREATE TABLE Department(code PRIMARY KEY,department TEXT)')
lines = open('department.txt').read().splitlines()
for data in lines:
    data = data.split(',')
    cur.execute('''INSERT INTO Department(code,department)VALUES(?,?)''',(data[0],data[1]))

conn.commit()
department = []
cur.execute('''SELECT code,department FROM Department''')
for row in cur:
        department.append(row[1])
cur.close(

In below code if teacher table already exists at first drop out that table and create new table. Read the data from teacher.txt file. Insert the value from teacher.txt to the teacher database. If department name which are in teacher.txt are in specified department list which was obtained from above code then this teacher is in valid department. It kept that teacher in the database otherwise not.

cur = conn.cursor()
cur.execute('''DROP TABLE IF EXISTS Teacher''')
cur.execute('CREATE TABLE Teacher(name TEXT,dob INTEGER, gender TEXT,department TEXT)''')
lines = open('teacher.txt').read().splitlines()
for data in lines:
    data = data.split(',')

    if data[3] in department:
        cur.execute('''INSERT INTO Teacher(name,dob,gender,department)VALUES(?,?,?,?)''',(data[0],data[1],data[2],data[3]))
    else:
        print(f'Department {data[3]} name is not found.' )

conn.commit()
cur.execute('''SELECT name,dob,gender,department FROM Teacher''')
for row in cur:
        print(row)
cur.close()

After running this code we obtain following output

Department Education name is not found.
Department Account name is not found.
Department Population name is not found.
Department English name is not found.
('Tulsi prased Nepal', 2024, 'Male', 'Mathematics')
('Viper Kaka', 2055, 'Male', 'It')
('Naran Gautum', 2044, 'Male', 'Physics')
('Bishnu Gyawali', 2045, 'Male', 'Statictits')
('Sarmila Pandey', 2053, 'Female', 'Chemistry')
('Xabilal Bhusal', 2033, 'Male', 'Statictits')
('Tilak Sapkota', 2034, 'Male', 'Nepali')
('Sarad Pandey', 2047, 'Male', 'Physics')
('Kusal Rana', 2052, 'Male', 'Biology')
('Simran Nepal', 2024, 'Male', 'Computer')
('Sisir Nepal', 2050, 'Male', 'Zoology')

Day 30 of #100DaysOfCode and #Python
* More About Database On Python
* Tried To Create Database for text file
* Done a Dummy Project on SQL pic.twitter.com/kcvG1SIScq

— Durga Pokharel (@mathdurga) January 23, 2021

Comments

[Michael Otu]

In here, the ''' was not used properly...

cur.execute('CREATE TABLE Department(code PRIMARY KEY,department TEXT)''')

to

query = '''CREATE TABLE Department(code PRIMARY KEY,department TEXT)'''
cur.execute(query)

[Durga Pokharel]

I ll try it.