-
-
Save manichabba/4564251b5ba18b2de0c6bec72c715238 to your computer and use it in GitHub Desktop.
import re | |
import sqlite3 | |
conn = sqlite3.connect('emaildb.sqlite') | |
cur = conn.cursor() | |
cur.execute(''' | |
DROP TABLE IF EXISTS Counts''') | |
cur.execute(''' | |
CREATE TABLE Counts (org TEXT, count INTEGER)''') | |
fname = raw_input('Enter file name: ') | |
if ( len(fname) < 1 ) : fname = 'mbox.txt' | |
fh = open(fname) | |
for line in fh: | |
if not line.startswith('From: ') : continue | |
pieces = line.split()[1] | |
org = pieces.split('@')[1] | |
print org | |
cur.execute('SELECT count FROM Counts WHERE org = ? ', (org, )) | |
row = cur.fetchone() | |
if row is None: | |
cur.execute('''INSERT INTO Counts (org, count) | |
VALUES ( ?, 1 )''', ( org, ) ) | |
else : | |
cur.execute('UPDATE Counts SET count=count+1 WHERE org = ?', | |
(org, )) | |
conn.commit() | |
sqlstr = 'SELECT org, count FROM Counts ORDER BY count DESC LIMIT 10' | |
print "Counts:" | |
for row in cur.execute(sqlstr) : | |
print str(row[0]), row[1] | |
cur.close() |
okay, read slowly:
First: Create a folder on the desktop (doesn't matter the name of the folder).
In the folder, save the text: http://www.py4e.com/code3/mbox.txt as TXT.
Create a Python file and copy the code below:
''' import sqlite3
conn = sqlite3.connect('emaildb.sqlite')
cur = conn.cursor()cur.execute('DROP TABLE IF EXISTS Counts')
cur.execute('''
CREATE TABLE Counts (org TEXT, count INTEGER)''')fname = 'mbox.txt'
if (len(fname) < 1): fname = 'mbox.txt'
fh = open(fname)
for line in fh:
if not line.startswith('From: '): continue
pieces = line.split()
email = pieces[1]
parts = email.split('@')
org = parts[1]
cur.execute('SELECT count FROM Counts WHERE org = ? ', (org,))
row = cur.fetchone()
if row is None:
cur.execute('''INSERT INTO Counts (org, count)
VALUES (?, 1)''', (org,))
else:
cur.execute('UPDATE Counts SET count = count + 1 WHERE org = ?',
(org,))
conn.commit()sqlstr = 'SELECT org, count FROM Counts ORDER BY count DESC LIMIT 10'
for row in cur.execute(sqlstr):
print(str(row[0]), row[1])cur.close() '''
Execute the code, instantly will create a file .sqlite in the folder that you created on the desktop.
Upload the file sqlite.
Good Luck!!
Thanks so much for your concern. But the code isn't work.
okay, read slowly:
First: Create a folder on the desktop (doesn't matter the name of the folder).
In the folder, save the text: http://www.py4e.com/code3/mbox.txt as TXT.
Create a Python file and copy the code below:
''' import sqlite3
conn = sqlite3.connect('emaildb.sqlite')
cur = conn.cursor()
cur.execute('DROP TABLE IF EXISTS Counts')
cur.execute('''
CREATE TABLE Counts (org TEXT, count INTEGER)''')
fname = 'mbox.txt'
if (len(fname) < 1): fname = 'mbox.txt'
fh = open(fname)
for line in fh:
if not line.startswith('From: '): continue
pieces = line.split()
email = pieces[1]
parts = email.split('@')
org = parts[1]
cur.execute('SELECT count FROM Counts WHERE org = ? ', (org,))
row = cur.fetchone()
if row is None:
cur.execute('''INSERT INTO Counts (org, count)
VALUES (?, 1)''', (org,))
else:
cur.execute('UPDATE Counts SET count = count + 1 WHERE org = ?',
(org,))
conn.commit()
sqlstr = 'SELECT org, count FROM Counts ORDER BY count DESC LIMIT 10'
for row in cur.execute(sqlstr):
print(str(row[0]), row[1])
cur.close() '''
Execute the code, instantly will create a file .sqlite in the folder that you created on the desktop.
Upload the file sqlite.
Good Luck!!Thanks so much for your concern. But the code isn't work.
Hi! thank you for the reply. I have repeated my code, and it worked normally. Try to follow step by step as my suggestions above. If you are not able to solve it, please, come back here again.
Can anyone help me getting the desired answer that is 536?
just use curl yours_mbox_link_here -o mbox.txt
and use mbox.txt.
in this case you should use urllib or request to download mailbox.txt.
@Nishith6
SQL Query Error: no such column: orgI am getting the above error. Please help, I am stuck here for about a week now
Did you get the answer?? PLEASE tell me aslo i am aslo stuck in the same problem
@Nishith6
SQL Query Error: no such column: org
I am getting the above error. Please help, I am stuck here for about a week nowDid you get the answer?? PLEASE tell me aslo i am aslo stuck in the same problem
First you have to copy http://www.py4e.com/code3/mbox.txt this link content and save with 'mobx.txt' name
and after this you have to run above code (in above code is in python 2 you have to convert in python 3 )
then you show that one sqlite file is created you have to upload this sqlite file (make sure your mobx.txt file is in same folder ).
I am doing everything mentioned, Even the database file is being created in the proper structure, but there is no data in the database. Why is it happening ?
Bro even i am facing the same problem have u got any solution
Everything is fine, except that org should be like this:
org = pieces[1].split("@")[1] on line 21
Because split() is performed only on strings and pieces is a list. You will get an attribute error
@rish codes
https://github.com/rish-codes/py4e/blob/master/Course-4/emaildb.py
open the above link
copy the code and execute it
or copy the below code also
import sqlite3
conn = sqlite3.connect('emaildb.sqlite')
cur = conn.cursor()
cur.execute('DROP TABLE IF EXISTS Counts')
cur.execute('CREATE TABLE Counts(org TEXT, count INTEGER)')
fname = 'mbox.txt'
fh = open(fname)
for line in fh:
if not line.startswith('From: '):
continue
pieces = line.split()
email = pieces[1]
org = email.split('@')[1]
cur.execute('SELECT count FROM Counts WHERE org = ? ', (org,))
row = cur.fetchone()
if row is None:
cur.execute('''INSERT INTO Counts (org, count)
VALUES (?, 1)''', (org,))
else:
cur.execute('UPDATE Counts SET count = count + 1 WHERE org = ?',
(org,))
conn.commit()
sqlstr = 'SELECT org, count FROM Counts ORDER BY count DESC LIMIT 10'
for row in cur.execute(sqlstr):
print(str(row[0]), row[1])
cur.close()
make sure you downloaded the mbox text
once you execute the python code the emaildb.SQLite is generated .you need to apply the uploaded that code
Here is the solution that works for me :
import re
conn = sqlite3.connect('orgdb.sqlite')
cur = conn.cursor()
cur.execute('DROP TABLE IF EXISTS Counts')
cur.execute('''
CREATE TABLE Counts (org TEXT, count INTEGER)''')
fname = 'mbox.txt'
fh = open(fname)
for line in fh:
if not line.startswith('From: '): continue
org = re.findall("@(.*)", line)
cur.execute('SELECT count FROM Counts WHERE org = ? ', (org[0],))
row = cur.fetchone()
if row is None:
cur.execute('''INSERT INTO Counts (org, count)
VALUES (?, 1)''', (org[0],))
else:
cur.execute('UPDATE Counts SET count = count + 1 WHERE org = ?',
(org[0],))
conn.commit()
# https://www.sqlite.org/lang_select.html
sqlstr = 'SELECT org, count FROM Counts ORDER BY count DESC LIMIT 10'
for row in cur.execute(sqlstr):
print(str(row[0]), row[1])
cur.close()
Non of the codes for me is actually counting orgs, maximum i get is 1? what am i doing wrong , i already tried all the codes here ans mine which were similar and trying to correct where i think mistake can be , and yes i do have mbox.txt saved in same directory and i do have created db.sqlite files but they either empty or counting 1, please help
me too and i can't pass this task did u pass it?
okay, read slowly:
First: Create a folder on the desktop (doesn't matter the name of the folder).
In the folder, save the text: http://www.py4e.com/code3/mbox.txt as TXT.
Create a Python file and copy the code below:
''' import sqlite3
conn = sqlite3.connect('emaildb.sqlite')
cur = conn.cursor()
cur.execute('DROP TABLE IF EXISTS Counts')
cur.execute('''
CREATE TABLE Counts (org TEXT, count INTEGER)''')
fname = 'mbox.txt'
if (len(fname) < 1): fname = 'mbox.txt'
fh = open(fname)
for line in fh:
if not line.startswith('From: '): continue
pieces = line.split()
email = pieces[1]
parts = email.split('@')
org = parts[1]
cur.execute('SELECT count FROM Counts WHERE org = ? ', (org,))
row = cur.fetchone()
if row is None:
cur.execute('''INSERT INTO Counts (org, count)
VALUES (?, 1)''', (org,))
else:
cur.execute('UPDATE Counts SET count = count + 1 WHERE org = ?',
(org,))
conn.commit()
sqlstr = 'SELECT org, count FROM Counts ORDER BY count DESC LIMIT 10'
for row in cur.execute(sqlstr):
print(str(row[0]), row[1])
cur.close() '''
Execute the code, instantly will create a file .sqlite in the folder that you created on the desktop.
Upload the file sqlite.
Good Luck!!