Solved

Python: how to check mail status

Posted on 2009-05-11
3
517 Views
Last Modified: 2012-05-06
Hi experts,

I am going to write a Python code to upload new e-mails from a specific user account into MySQL database. This Python code is scheduled to run every miniute.

This user account is generic so nobody will come to check e-mails except the running code. Whenever the code runs, it should only upload NEW e-mails into database. In other words, the code can not upload a e-mail content if this e-mail is in the database already. Therefore I need to know how to distinguish if a e-mail has been "read" by the code or not.

Comparing "From", "To" and "Body" of a e-mail with corresponding parts in the database is certainly a way to find if that e-mail has been loaded or not, however this method is too clumsy to use. Is there any smart way to find this?

Thanks so much.
0
Comment
Question by:davidw88
  • 2
3 Comments
 
LVL 3

Expert Comment

by:ilalopoulos
ID: 24364604
You will use the database for the duplicate checking, database engines are optimised for this kind of work.

So:

1. Decide what do you mean by unique, emails headers give you at minimum the from, to, subject, date fields so some of the combinations you can use to define uniqueness are the following:

to, from subject, date
to, from, subject, date, and part of the body
date and a hash of to,from,body
etc.

2. You will define the above as primary keys in the table but take care that depending on the MySQL engine that you will use, keys have a limit in size, so maybe the most effective way to use is the 3d option (a field and a hash of the rest)

In this example I will use subject and date which will be defined as the primary key for the table:

CREATE TABLE `emails` (
  `to` varchar(255) NOT NULL,
  `from` varchar(255) NOT NULL,
  `subject` varchar(255) NOT NULL,
  `date` datetime NOT NULL,
  `body` text NOT NULL,
  PRIMARY KEY  (`subject`,`date`)
);

3. The test for duplicates is done by the database engine, I will give you two approaches:

a) Use the insert query with ON DUPLICATE KEY and a dummy update part - in case that the entry exist the database will not complain nor do anything else - this is the silent approach.

"""INSERT INTO emails (e_to, e_from, e_subject, e_date, e_body) VALUES ('%s', '%s', '%s', '%s', '%s')""" % (e_to, e_from, e_subject, e_date, e_body)

more info for ON DUPLICATE KEY at: http://dev.mysql.com/doc/refman/5.0/en/insert-on-duplicate.html

b) Use a try/except clause to catch duplicate key errors from MySQL:
q = """INSERT INTO emails (e_to, e_from, e_subject, e_date, e_body) VALUES ('%s', '%s', '%s', '%s', '%s')""" % (e_to, e_from, e_subject, e_date, e_body)
 
try:
    
    cursor.execute(q)
    conn.commit()
 
except MySQLdb.IntegrityError, message:
    if message[0] == 1062:
        #Do whatever you want here for the duplicate
        print "duplicate %s %s" % (e_date, e_subject)
    else:
        raise	# Not a duplicate key error

Open in new window

0
 
LVL 3

Accepted Solution

by:
ilalopoulos earned 125 total points
ID: 24364740
In the above post I have ommited the ON DUPLICATE KEY part in the first approach (3a).

So the complete 3a answer is:

a) Use the insert query with ON DUPLICATE KEY and a dummy update part - in case that the entry exist the database will not complain nor do anything else - this is the silent approach.

"""INSERT INTO emails (e_to, e_from, e_subject, e_date, e_body) VALUES ('%s', '%s', '%s', '%s', '%s') ON DUPLICATE KEY UPDATE e_date = e_date""" % (e_to, e_from, e_subject, e_date, e_body)

more info for ON DUPLICATE KEY at: http://dev.mysql.com/doc/refman/5.0/en/insert-on-duplicate.html

*Also the table create has different fields from the ones I use later on the examples but this is trivial to change.
0
 

Author Comment

by:davidw88
ID: 24365804
I see. Thanks ilalopoulos for your two replies.

I will follow your idea to test and let you know how it works later.

thanks again.
0

Featured Post

Secure Your Active Directory - April 20, 2017

Active Directory plays a critical role in your company’s IT infrastructure and keeping it secure in today’s hacker-infested world is a must.
Microsoft published 300+ pages of guidance, but who has the time, money, and resources to implement? Register now to find an easier way.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Variable is a place holder or reserved memory locations to store any value. Which means whenever we create a variable, indirectly we are reserving some space in the memory. The interpreter assigns or allocates some space in the memory based on the d…
Flask is a microframework for Python based on Werkzeug and Jinja 2. This requires you to have a good understanding of Python 2.7. Lets install Flask! To install Flask you can use a python repository for libraries tool called pip. Download this f…
Learn the basics of lists in Python. Lists, as their name suggests, are a means for ordering and storing values. : Lists are declared using brackets; for example: t = [1, 2, 3]: Lists may contain a mix of data types; for example: t = ['string', 1, T…
Learn the basics of if, else, and elif statements in Python 2.7. Use "if" statements to test a specified condition.: The structure of an if statement is as follows: (CODE) Use "else" statements to allow the execution of an alternative, if the …

735 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question