#!/usr/bin/env python
# Partial reimplementation of googlepbxcontacts, using GScript/IMAP to 'sidestep' OAUTH 2.0 (which interferes with direct scraping)
#
# This app looks in the mailbox EMAIL_ACCOUNT in the folder EMAIL_FOLDER for email from a GScript that sends contact information as a
# 3-column CSV (columns are name, label, number)
# Only email sent from SENT_FROM with a title containing SENT_TITLE** are accepted, lines without 3 columns are ignored.
# 05/04/2015 initial coding Bob Koure
#
import sys
import imaplib
import email
import datetime
import StringIO
import csv
import re
import MySQLdb
import unicodedata
EMAIL_ACCOUNT = "[email protected]" # account you are sending contacts updates TO
EMAIL_PASSWD = "passw0rd"
EMAIL_FOLDER = "INBOX" # you can use a folder(tag) instead - e.g. "contacts", "pbx/contact updates" -- Use a GMail filter to move incoming folder updates there
SENT_FROM = '[email protected]' # the account you are sending contacts FROM (the one you login to to run the emailphonecontacts google script (also useful if we ever support updates from multiple users)
SENT_TITLE = 'phone contacts' # must be in the subject line - feel free to change, but also change in emailphonecontacts google script
IMAP_SERVER = 'imap.gmail.com' # no need to ever change this
# mysql database connection settings
dbhost = "localhost";
dbpass = "passw0rd"; # passw0rd for non raspberry PIAF
dbuser = "root";
dbname = "asteridex";
dbtable = "user1" # user1 for filling the original Asteridex table,
#dbtable = "pbxcontacts" # or your own table name for example to create test tables.
# global variables, other variables are defined under main()
g_replacecontacts = 0 # 0 = delete data in dbtable 1 = remove/re-insert contacts from CSV
g_printtraceinfo = 0 # 1 to trace and print throughout the program, 2 to print more detail
def strip_accents(s):
# remove accents from characters in a name (note: gscript mailapp uses iso8859.1 _in_the_US)
return ''.join(c for c in unicodedata.normalize('NFD', s.decode('iso-8859-1'))
if unicodedata.category(c) != 'Mn')
def process_CSV(scsv):
global g_replacecontacts
icount = 0
f = StringIO.StringIO(scsv)
contacts = list(csv.reader(f, delimiter=',')) # sidestep renitializing CSV iterators
print len(contacts), " contacts in CSV"
if len(contacts) == 0: return
try:
# Open database connection and prepare a cursor object using cursor() method
db = MySQLdb.connect(dbhost, dbuser, dbpass, dbname )
cursor = db.cursor()
# execute SQL query using execute() method.
cursor.execute("SELECT VERSION()")
# Fetch a single row using fetchone() method.
data = cursor.fetchone()
print "Main: Database version : %s " % data
#except MySQLError:
except:
print "Error opening database ", dbname
return
# Clean up database table, unless user wants to replace contacts that are already in database
if g_replacecontacts :
print "Replacing existing contact group in the %s database." % (dbname)
else:
# delete all of our contacts before we refetch them, this will propagate deletions
print "Deleting existing table %s in the %s database." % (dbtable, dbname)
cursor.execute("DELETE FROM %s" % dbtable)
cursor.close()
# re-instantiate the cursor
cursor = db.cursor()
# for each contact
for contact in contacts:
if len(contact) >=3:
name = strip_accents(contact[0])
label = contact[1]
number = contact[2]
nameandlabel = name + ' ' + '('+ label + ')'
print name, label, number
if g_replacecontacts: # if replacing contacts, delete matching entry (TO DO replace with select where / update)
sql = "DELETE FROM `%s`" % dbtable
sql += "WHERE `name` REGEXP '^%s'" % nameandlabel # name (label): ^ starts with name
try:
cursor.execute(sql)
db.commit()
if g_printtraceinfo > 0: print "Main: name `%s` DELETED from db" % normalised_name
except:
db.rollback()
# punting on formatting numbers
contact_outnumber = number
# punting on dialcode for now
contact_group = "*"
contact_dialcode = ""
contact_email = "not set" # WHY is this column here? Easy enough to pass 'em in if there's aneed/use
sql = "INSERT INTO `%s`" % dbtable
sql += "(`name`, `in`, `out`, `dialcode`, `email`) VALUES ('%s', '%s', '%s', '%s', '%s' )" % \
(nameandlabel, contact_group, contact_outnumber, contact_dialcode, contact_email)
try:
cursor.execute(sql)
db.commit()
if g_printtraceinfo > 0: print "Main: name `%s` INSERTED into db" % contact_name
except:
db.rollback()
icount += 1
print "inserted",icount,"contacts of", len(contacts)
def process_mailbox(M):
rv, data = M.search(None, "ALL") # search gets a list of message sequence numbers
if rv != 'OK':
print "No messages found"
return
for num in data[0].split():
rv, data = M.fetch(num, "(BODY[HEADER.FIELDS (FROM)])")
if rv != 'OK': continue
#print "From: ", data[0][1].strip('\r\n')
if not SENT_FROM in data[0][1]: continue
rv, data = M.fetch(num, "(BODY[HEADER.FIELDS (SUBJECT)])")
if rv != 'OK': continue
#print "Subject: ", data[0][1].strip('\r\n')
if not SENT_TITLE in data[0][1]: continue
# sender and subject indicate a contacts update, get the entire email
rv, data = M.fetch(num, '(RFC822)')
if rv != 'OK': continue
msg = email.message_from_string(data[0][1])
date_tuple = email.utils.parsedate_tz(msg['Date'])
if date_tuple:
local_date = datetime.datetime.fromtimestamp(
email.utils.mktime_tz(date_tuple))
print "Local Date:", \
local_date.strftime("%a, %d %b %Y %H:%M:%S")
if msg.is_multipart(): continue # sent from GScript MailApp as single part email
type = msg.get_content_type()
print type
if type != 'text/plain': continue
csv = msg.get_payload(None, True)
process_CSV(csv)
# done with this email, delete* it
M.store(num, '+FLAGS', '\\Deleted')
M.expunge()
def main():
M = imaplib.IMAP4_SSL(IMAP_SERVER)
M.login(EMAIL_ACCOUNT, EMAIL_PASSWD)
rv, data = M.select(EMAIL_FOLDER)
if rv == 'OK':
print "Processing mailbox: ", EMAIL_FOLDER
process_mailbox(M)
M.close()
else:
print "ERROR: Unable to open mailbox ", rv
M.logout()
if __name__ == "__main__":
main()