1

I am currently learning how to modify data with python using visual studios and sqlite. My assignment is to count how many times emails are found in a file, organize them in a way that each email is then counted. Then I must input these into SQLite as a table named Counts with two rows (org,count). I have wrote a code that runs the program and outputs it onto the visual studios output screen but not the database.

this is my program:

import sqlite3

conn = sqlite3.connect('database3.db')
cur = conn.cursor()

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

cur.execute('''CREATE TABLE Counts (email TEXT, count INTEGER)''')
#cur.execute("INSERT INTO Counts Values('mlucygray@gmail.com',1)")

# Save (commit) the changes
conn.commit()
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,))

    cur.execute('SELECT * FROM Counts')
# https://www.sqlite.org/lang_select.html
sqlstr = 'SELECT email, count FROM Counts ORDER BY count DESC LIMIT 10'
conn.commit()
for row in cur.execute(sqlstr):
    print(str(row[0]), row[1])
    conn.commit()
cur.close()

click here for the link to the output of the above code

Thank you for any suggestions

Saveen
  • 4,120
  • 14
  • 38
  • 41
mehai
  • 11
  • 2
  • By the way, you said there would be two rows in your database table (email, count). But I think you meant "two columns". – Ann L. May 25 '18 at 00:50
  • It looks to me as if your program must be saving to the database. Otherwise, you wouldn't get any output from your `print` statement. What makes you think that it isn't writing to it? – Ann L. May 25 '18 at 00:54
  • Is there a reason why this code `'''INSERT INTO Counts (email, count) VALUES (?, 1)'''` has three single quotes around it, rather than one? I _believe_ (but haven't checked) that this would mean that this would prevent the `INSERT` statement from being interpreted as an `INSERT`. – Ann L. May 25 '18 at 00:56
  • @AnnL. to answer your first question, I know how to print it into a database, but I am not sure why it is not performing the inserts. Yes, You're right. I meant columns. I believe it isn't printing into the database because I can't see it in the database. – mehai May 25 '18 at 01:00
  • Try reducing the 3 single-quotes around the `INSERT` statement to just one at each end. That _might_ be the problem. – Ann L. May 25 '18 at 01:43
  • ''' or """ is used to write multi line strings.. @mehai are you able to insert any records at all ? try setting the level of autocommit to 1 – skybunk May 25 '18 at 04:52
  • check this https://stackoverflow.com/q/4699605/8872639 – skybunk May 25 '18 at 04:53
  • try using ipython or python -i script_name.py and check if its working manually, you can change your script accordingly after that – skybunk May 25 '18 at 04:56
  • You use the `conn.commit()` wrongly. For query like `INSERT...` and `UPDATE...`, you need to run `conn.commit()` after `cur.execute()`, which is not happening within the `for .. loop`. While for general retrieving data using `SELECT...`, `conn.commit()` is not required. – hcheung May 25 '18 at 05:41

1 Answers1

1

You need to commit changes with insert/update and DONT need to commit after executing select statements.

for line in fh:
    if not line.lower().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()
Max Jevachkin
  • 96
  • 6