DEV Community

loading...

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

iamdurga profile image Durga Pokharel ・2 min read

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

Discussion (1)

pic
Editor guide
Collapse
otumianempire profile image
Otu Michael
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