DEV Community

Durga Pokharel
Durga Pokharel

Posted on

Day 32 Of 100DaysOfcode: Learned More About Database on Python

Today is my 32 days of #100Daysofcode and #python. Today also continue to learned more about database on python with the help of coursera. Prepared some table. Tried to made some database to read data from file.

Here is one code which made database for file.
In the code below code start with import sqlite3 library. We make the connection with emailbd.sqlite. If there Counts table already exist then first drop it out and recreate that table. After there is loop for find number of counts of emails. If row is not exists then we insert value in that row .If row already exists then we update value in that row.

import sqlite3

conn = sqlite3.connect('emailbd.sqlite')
cur = conn.cursor()

cur.execute('DROP TABLE IF EXISTS Counts')

cur.execute('''CREATE TABLE Counts(email TEXT,count INTEGER)''')

fname = input('enter file name:')
if(len(fname)<1): fname = 'mbox-short.txt'
fh = open(fname)
for line in fh:
    if not line.startswith('From:'):continue
    pieces = line.split()
    email = pieces[1]
    cur.execute('SELECT count FROM Counts WHERE email = ?',(email,))
    row = cur.fetchone()
    if row is None:
        cur.execute('''INSERT INTO Counts(email,count)VALUES(?,1)''',(email,))
    else:
        cur.execute('''UPDATE Counts SET Count = Count + 1 WHERE email = ?''',(email,))

    conn.commit()


sqlstr = 'SELECT email, Count FROM Counts ORDER BY Count DESC LIMIT 10'
for row in cur.execute(sqlstr):
    print(str((row[0]),(row[1])))

cur.close()
Enter fullscreen mode Exit fullscreen mode

Day 32 of #100DayOfcode and #Python
learned More About Database And Made Few Tables.#100DaysOfCode ,#CodeNewbies ,#beginners pic.twitter.com/sfk25byLR5

— Durga Pokharel (@mathdurga) January 25, 2021

Top comments (1)

Collapse
 
otumianempire profile image
Michael Otu
if(len(fname)<1): fname = 'mbox-short.txt'
Enter fullscreen mode Exit fullscreen mode

You can check if the fname as in,

if not fname: fname = 'mbox-short.txt'
Enter fullscreen mode Exit fullscreen mode