DEV Community

Durga Pokharel
Durga Pokharel

Posted on • Edited on

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

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

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

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

Do your career a big favor. Join DEV. (The website you're on right now)

It takes one minute, it's free, and is worth it for your career.

Get started

Community matters

Top comments (2)

Collapse
 
otumianempire profile image
Michael Otu

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

cur.execute('CREATE TABLE Department(code PRIMARY KEY,department TEXT)''')
Enter fullscreen mode Exit fullscreen mode

to

query = '''CREATE TABLE Department(code PRIMARY KEY,department TEXT)'''
cur.execute(query)
Enter fullscreen mode Exit fullscreen mode
Collapse
 
iamdurga profile image
Durga Pokharel

I ll try it.

👋 Kindness is contagious

Discover a treasure trove of wisdom within this insightful piece, highly respected in the nurturing DEV Community enviroment. Developers, whether novice or expert, are encouraged to participate and add to our shared knowledge basin.

A simple "thank you" can illuminate someone's day. Express your appreciation in the comments section!

On DEV, sharing ideas smoothens our journey and strengthens our community ties. Learn something useful? Offering a quick thanks to the author is deeply appreciated.

Okay