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 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),(row))) cur.close()