import sqlite3

# Connect to SQLite database (or create it if it doesn't exist)
conn = sqlite3.connect('emaildb.sqlite')
cur = conn.cursor()

# Drop table if exists
cur.execute('DROP TABLE IF EXISTS Counts')

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

# Prompt for the file name
fname = input('Enter file name: ')
if len(fname) < 1: fname = 'mbox.txt'
fh = open(fname)

# Read through the file
for line in fh:
    if not line.startswith('From: '): continue
    pieces = line.split()
    email = pieces[1]
    domain = email.split('@')[1]

    # Check if the domain exists in the table, if not, insert it with count 1
    cur.execute('SELECT count FROM Counts WHERE org = ? ', (domain,))
    row = cur.fetchone()
    if row is None:
        cur.execute('INSERT INTO Counts (org, count) VALUES (?, 1)', (domain,))
    else:
        # If the domain exists, update the count
        cur.execute('UPDATE Counts SET count = count + 1 WHERE org = ?', (domain,))

# Commit the changes
conn.commit()

# SQL query to retrieve data
sqlstr = 'SELECT org, count FROM Counts ORDER BY count DESC'

# Print the results
for row in cur.execute(sqlstr):
    print(str(row[0]), row[1])

# Close the cursor and the connection
cur.close()
-- create a table
CREATE TABLE students (
  id INTEGER PRIMARY KEY,
  name TEXT NOT NULL,
  gender TEXT NOT NULL
);
-- insert some values
INSERT INTO students VALUES (1, 'Ryan', 'M');
INSERT INTO students VALUES (2, 'Joanna', 'F');
-- fetch some values
SELECT * FROM students WHERE gender = 'F';