-
-
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() |
acheampomaa
commented
Nov 10, 2020
via email
I have the error below after running this code on Pycharm:
fh = open(fname)
FileNotFoundError: [Errno 2] No such file or directory: "'mbox.txt'"
file should in the same directory where you have python file located
import sqlite3
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 = 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("@")
org = pieces[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()
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()
Hi manichabba
Could you tell me what the [1] are for in line 20 and 21 of your code?
1-Sure I will help you, you can see that split put saprate words by default space and put them in List as you can see list have two item start from 0 and end with 1.
pieces = line.split()
print(pieces)
['From:', 'stephen.marquard@uct.ac.za']
2- so when she select 1 she select the second item [stephen.marquard@uct.ac.za] usin [1] in list
pieces = line.split()[1]
['stephen.marquard@uct.ac.za']
3- after that she split with respect to @ not space so this will be the output['stephen.marquard' , 'uct.ac.za'] as you see she choose [1] in list
org = pieces.split('@')[1]
uct.ac.za
I hope that my reply help you.
Counting Organizations
This application will read the mailbox data (mbox.txt) and count the number of email messages per organization (i.e. domain name of the email address) using a database with the following schema to maintain the counts.
CREATE TABLE Counts (org TEXT, count INTEGER)
When you have run the program on mbox.txt upload the resulting database file above for grading.
If you run the program multiple times in testing or with dfferent files, make sure to empty out the data before each run.
You can use this code as a starting point for your application: http://www.py4e.com/code3/emaildb.py.
The data file for this application is the same as in previous assignments: http://www.py4e.com/code3/mbox.txt.
Can someone please help us solve the problem above?
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!!
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?