Counting Organizations This application will read the mailbox data (mbox.txt) count up the number email messages per organization (i.e. domain name of the email address) using a database with the following schema to maintain the counts.

Sqlite.py

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
print "Counts:"
for row in cur.execute(sqlstr) :
    print str(row[0]), row[1]

cur.close()

Can anyone help me getting the desired answer that is 536?

importar sqlite3
conn = sqlite3.connect ('emaildb2.sqlite')
cur = conn.cursor ()

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

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

fname = input ('Ingrese el nombre del archivo:')
if (len (fname) <1): fname = 'mbox.txt'
fh = open (fname)
list_1 = []
para la línea en fh:
si no es line.startswith (' De: '): continuar
piezas = line.split ()
correo electrónico = piezas [1]
dom = email.find (' @ ')
org = email [dom + 1: len (correo electrónico)]

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'

para la fila en cur.execute (sqlstr):
print (str (fila [0]), fila [1])

cur.close ()

El primer recuento es 536

it doesnt work

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 = 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()[1]
org = pieces.split('@')[1]
cur.execute('SELECT count FROM Counts WHERE org = ? ',
(org,)) # ? is a placeholder to avoid sql injection(destroying database using code injection)
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()

File "xxxxxxxxxxxxxxxxxxx", line 17, in
pieces = line.split()[1]

IndexError: list index out of range

I GET THIS ERROR
Why i shoul do?
Thanks

Hi, i run the code and i got the required answer but how or where do i get the sqlite.py file to upload.
Thanks in advance for your help

I have written the new and updated code which is working as of 8th July, 2020. Check it out at - https://github.com/rish-codes/py4e/blob/master/Course-4/emaildb.py
I'll be happy to help you guys out with any issues.

Life saver

Hi, thank you the code works and all but I was wondering where I would go to get the file to submit(the .sqlite file). i can not find it. Will be excited if you can walk me through the steps

…

On Tue, 10 Nov 2020 at 13:01, leiwen1994 ***@***.***> wrote: ***@***.**** commented on this gist. ------------------------------ I have written the new and updated code which is working as of 8th July, 2020. Check it out at - https://github.com/rish-codes/py4e/blob/master/Course-4/emaildb.py I'll be happy to help you guys out with any issues. Life saver — You are receiving this because you commented. Reply to this email directly, view it on GitHub <https://gist.github.com/4564251b5ba18b2de0c6bec72c715238#gistcomment-3522941>, or unsubscribe <https://github.com/notifications/unsubscribe-auth/ARTBH4B5UKMGQJVHS6XZ2YTSPF5WHANCNFSM4IS6SNSQ> .

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.

Hi, I have been struggling for the past few days because my code is not working. Anyone who can help, please do so!
( Given below are pics of my code and its output)

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: org

I 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

@likithnanaiah

@Nishith6
SQL Query Error: no such column: org
I 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

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()

Help why does it keep saying my data is incorrect even though it is correct!

Help why does it keep saying my data is incorrect even though it is correct!

I think the result should be like this

iupui.edu 536
umich.edu 491
indiana.edu 178
caret.cam.ac.uk 157
vt.edu 110
uct.ac.za 96
media.berkeley.edu 56
ufp.pt 28
gmail.com 25
et.gatech.edu 17

Thank you

Help why does it keep saying my data is incorrect even though it is correct!

I think the result should be like this

iupui.edu 536
umich.edu 491
indiana.edu 178
caret.cam.ac.uk 157
vt.edu 110
uct.ac.za 96
media.berkeley.edu 56
ufp.pt 28
gmail.com 25
et.gatech.edu 17

How do I run this code? I look at YouTube videos and I can get the answer based on this code, why not mine? Beg for help!

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

I'm getting the right result, but I also get this "Data is incorrect: emaildb.sqlite"

me too and i can't pass this task did u pass it?

Send me more details so that I can help you out.

…

On Mon, Jun 17, 2024 at 6:36 PM Bssmmn ***@***.***> wrote: ***@***.**** commented on this gist. ------------------------------ me too and i can't pass this task did u pass it? — Reply to this email directly, view it on GitHub <https://gist.github.com/manichabba/4564251b5ba18b2de0c6bec72c715238#gistcomment-5091503> or unsubscribe <https://github.com/notifications/unsubscribe-auth/AQLNHCJGL5QWWWJJFDGMFQTZH3NODBFKMF2HI4TJMJ2XIZLTSKBKK5TBNR2WLJDUOJ2WLJDOMFWWLO3UNBZGKYLEL5YGC4TUNFRWS4DBNZ2F6YLDORUXM2LUPGBKK5TBNR2WLJDHNFZXJJDOMFWWLK3UNBZGKYLEL52HS4DFVRZXKYTKMVRXIX3UPFYGLK2HNFZXIQ3PNVWWK3TUUZ2G64DJMNZZDAVEOR4XAZNEM5UXG5FFOZQWY5LFVAZTSMJRGA3DGNNHORZGSZ3HMVZKMY3SMVQXIZI> . You are receiving this email because you commented on the thread. Triage notifications on the go with GitHub Mobile for iOS <https://apps.apple.com/app/apple-store/id1477376905?ct=notification-email&mt=8&pt=524675> or Android <https://play.google.com/store/apps/details?id=com.github.android&referrer=utm_campaign%3Dnotification-email%26utm_medium%3Demail%26utm_source%3Dgithub> .

Yes now i passed all of them

________________________________ From: Kartik Nanneboina ***@***.***> Sent: 17 June 2024 14:27 To: kartik-nv ***@***.***> Cc: Comment ***@***.***> Subject: Re: manichabba/Sqlite.py @kartik-nv commented on this gist.

________________________________ Send me more details so that I can help you out.

On Mon, Jun 17, 2024 at 6:36 PM Bssmmn ***@***.***> wrote: ***@***.**** commented on this gist. ------------------------------ me too and i can't pass this task did u pass it? — Reply to this email directly, view it on GitHub <https://gist.github.com/manichabba/4564251b5ba18b2de0c6bec72c715238#gistcomment-5091503> or unsubscribe <https://github.com/notifications/unsubscribe-auth/AQLNHCJGL5QWWWJJFDGMFQTZH3NODBFKMF2HI4TJMJ2XIZLTSKBKK5TBNR2WLJDUOJ2WLJDOMFWWLO3UNBZGKYLEL5YGC4TUNFRWS4DBNZ2F6YLDORUXM2LUPGBKK5TBNR2WLJDHNFZXJJDOMFWWLK3UNBZGKYLEL52HS4DFVRZXKYTKMVRXIX3UPFYGLK2HNFZXIQ3PNVWWK3TUUZ2G64DJMNZZDAVEOR4XAZNEM5UXG5FFOZQWY5LFVAZTSMJRGA3DGNNHORZGSZ3HMVZKMY3SMVQXIZI> . You are receiving this email because you commented on the thread. Triage notifications on the go with GitHub Mobile for iOS <https://apps.apple.com/app/apple-store/id1477376905?ct=notification-email&mt=8&pt=524675> or Android <https://play.google.com/store/apps/details?id=com.github.android&referrer=utm_campaign%3Dnotification-email%26utm_medium%3Demail%26utm_source%3Dgithub> .

— Reply to this email directly, view it on GitHub<https://gist.github.com/manichabba/4564251b5ba18b2de0c6bec72c715238#gistcomment-5091514> or unsubscribe<https://github.com/notifications/unsubscribe-auth/BFE7DXNN6FAU7AAWIP2NLWTZH3P57BFKMF2HI4TJMJ2XIZLTSKBKK5TBNR2WLJDUOJ2WLJDOMFWWLO3UNBZGKYLEL5YGC4TUNFRWS4DBNZ2F6YLDORUXM2LUPGBKK5TBNR2WLJDHNFZXJJDOMFWWLK3UNBZGKYLEL52HS4DFVRZXKYTKMVRXIX3UPFYGLK2HNFZXIQ3PNVWWK3TUUZ2G64DJMNZZDAVEOR4XAZNEM5UXG5FFOZQWY5LFVAZTSMJRGA3DGNNHORZGSZ3HMVZKMY3SMVQXIZI>. You are receiving this email because you commented on the thread. Triage notifications on the go with GitHub Mobile for iOS<https://apps.apple.com/app/apple-store/id1477376905?ct=notification-email&mt=8&pt=524675> or Android<https://play.google.com/store/apps/details?id=com.github.android&referrer=utm_campaign%3Dnotification-email%26utm_medium%3Demail%26utm_source%3Dgithub>.