Day 30 Of 100DaysOfCode: Done a Dummy Project On SQL
Tags: 100daysofcode, codenewbie, beginners, python
Published: 2021-01-23 16:24:43

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.

```python
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.
```python
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

```python
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')
```
<blockquote class="twitter-tweet"><p lang="en" dir="ltr">Day 30 of <a href="https://twitter.com/hashtag/100DaysOfCode?src=hash&amp;ref_src=twsrc%5Etfw">#100DaysOfCode</a> and <a href="https://twitter.com/hashtag/Python?src=hash&amp;ref_src=twsrc%5Etfw">#Python</a> <br>* More About Database On Python<br>* Tried To Create Database for text file <br>* Done a Dummy Project on SQL <a href="https://t.co/kcvG1SIScq">pic.twitter.com/kcvG1SIScq</a></p>&mdash; Durga Pokharel (@mathdurga) <a href="https://twitter.com/mathdurga/status/1353009842215149573?ref_src=twsrc%5Etfw">January 23, 2021</a></blockquote> <script async src="https://platform.twitter.com/widgets.js" charset="utf-8"></script>