Vitali Kremez
  • Home
  • About
  • Contact
  • Cyber Security
  • Cyber Intel
  • Programming
  • Reverse Engineering
  • Exploit Development
  • Penetration Test
  • WIN32 Assembly
  • On Writing
    • Blog
    • LSAT
    • Photo
  • Honeypot
  • Forum

Python SQL Database Constructor

12/28/2015

1 Comment

 
#This is my solution for University of Michigan's challenge on Python CRUD (Create, Read, Update, #Delete) SQL Database

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-short.txt'
fh = open(fname)
for line in fh:
    if not line.startswith('From: ') : continue
    pieces = line.split()
    email = pieces[1]
    oemail = email.split('@')
    cemail = oemail[1]
    print(cemail)
    cur.execute('SELECT count FROM Counts WHERE org = ? ', (cemail, ))
    row = cur.fetchone()
    if row is None:
        cur.execute('''INSERT INTO Counts (org, count) 
                VALUES ( ?, 1 )''', ( cemail, ) )
    else : 
        cur.execute('UPDATE Counts SET count=count+1 WHERE org = ?', 
            (cemail, ))
    # This statement commits outstanding changes to disk each 
    # time through the loop - the program can be made faster 
    # by moving the commit so it runs only after the loop completes
    conn.commit()

# https://www.sqlite.org/lang_select.html
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()
1 Comment
rony
1/2/2016 09:12:16 am

life saving thanks

Reply



Leave a Reply.

    Author

    Vitali Kremez
    The Coder

    Archives

    January 2016
    December 2015
    November 2015
    October 2015
    September 2015

    Categories

    All

    RSS Feed

Powered by Create your own unique website with customizable templates.
  • Home
  • About
  • Contact
  • Cyber Security
  • Cyber Intel
  • Programming
  • Reverse Engineering
  • Exploit Development
  • Penetration Test
  • WIN32 Assembly
  • On Writing
    • Blog
    • LSAT
    • Photo
  • Honeypot
  • Forum