#!/usr/bin/env python
# Author: Tashfeen Bhimdi
# Version: 2014-07-18a
#
# Modification of code by John Baab (googlecontacts.py v0.1)
# http://pbxinaflash.com/community/index.php?threads/google-contacts-to-asterisk-phonebook.10943/
#
# Purpose: syncs contacts from google to a MySQL database
# Requirements: python, MySQL, gdata python module, mysql.connector python module
# Notes: This is READ-ONLY from Google script:
# -Contacts are pulled from Google
# -Local database is purged
# -Pulled contacts are stored in local database
#
# Debian:
# apt-get install python-gdata
# apt-get install python-mysql.connector
#
# License:
#
# This Package is free software; you can redistribute it and/or
# modify it under the terms of the GNU General Public
# License as published by the Free Software Foundation; either
# version 3 of the License, or (at your option) any later version.
#
# This package is distributed in the hope that it will be useful,
# but WITHOUT ANY WARRANTY; without even the implied warranty of
# MERCHANTABILITY or FITNESS FOR A PARTICULAR PURPOSE. See the GNU
# General Public License for more details.
#
# You should have received a copy of the GNU General Public
# License along with this package; if not, write to the Free Software
# Foundation, Inc., 51 Franklin St, Fifth Floor, Boston, MA 02110-1301 USA
#
# On Debian & Ubuntu systems, a complete copy of the GPL can be found under
# /usr/share/common-licenses/GPL-3, or (at your option) any later version
import re
import mysql.connector
import gdata.contacts.client
def Drive():
# Change if not in the US
# Prefixes are removed from contact numbers before inserted into MySQL database
# Use "" if you don't want a prefix removed
countryCode = "1"
internationalDialingCode = "011"
# countryCode and internationalDialingCode are removed because
# the MySQL lookup query checks the latter portion of the phone number in the database
# e.g. Calling Numbers 12345678999 and 2345678999 will both match 2345678999
# in the MySQL database using this query in FreePBX/MySQL CNAM Lookup:
#
# SELECT name FROM phonebook WHERE '[NUMBER]' REGEXP CONCAT(number,'$') ORDER BY CHAR_LENGTH(number) LIMIT 1
#
# REGEXP means a regular expression search
# CONCAT adds "$" to the end of the number in the db, signifying end of string
# So a number in the database must match all or just the end of the incoming number
# ORDER BY CHAR_LENGTH tells the db to sort the record with the shortest db number first
# This should take care of an incoming number 6789 matching both 16789 and 3456789
# in the db (16789 is the "best match" in this case since it has the largest overlap)
# LIMIT 1 tells MySQL to only return 1 result
# Google login info
# PLEASE use app specific passwords
# If only using one account, make sure variable is stored in this format:
# googleUserAccounts = (("[email protected]", "mypassword"))
googleUserAccounts = (("[email protected]", "mypassword"),
("[email protected]", "mypassword"))
# MySQL login info
mysqlUser = "username"
mysqlPw = "password"
mysqlHost = "servername"
mysqlDatabase = "databasename"
################################
rx = re.compile('\W+')
allGoogleContacts = []
for email, password in googleUserAccounts:
# Login to Google
gd_client = gdata.contacts.client.ContactsClient(source='googlecontacts_mysql')
gd_client.ClientLogin(email, password, gd_client.source)
# Retrieve contacts
query = gdata.contacts.client.ContactsQuery()
query.max_results = 10000
feed = gd_client.GetContacts(q = query)
for entry in feed.entry:
# If contact has a name
# If contact has a phone number
# If contact is part of a group ("Other Contacts" autogroup is skipped this way)
if entry.name is not None and len(entry.phone_number) > 0 and len(entry.group_membership_info) > 0:
# Clean up characters in contact name; replace all non-alphanumerics with spaces
fullName = entry.name.full_name.text
fullName = rx.sub(' ', fullName).strip()
for rawPhoneNumber in entry.phone_number:
# Remove non-numeric characters from the phone number
phoneNumber = re.sub("[^0-9]", "", rawPhoneNumber.text)
# Remove country code from the phone number
if countryCode != "" and phoneNumber.startswith(countryCode):
phoneNumber = phoneNumber[len(countryCode):]
# Remove international dialing code from the phone number
if internationalDialingCode != "" and phoneNumber.startswith(internationalDialingCode):
phoneNumber = phoneNumber[len(internationalDialingCode):]
# Save contact for later insert
allGoogleContacts.append((fullName, phoneNumber))
# Remove NAME+NUMBER duplicates (if an account has duplicates or multiple accounts have the same contact)
allGoogleContacts = tuple(set(allGoogleContacts))
# Connect to MySQL
cnx = mysql.connector.connect(user=mysqlUser,
password=mysqlPw,
host=mysqlHost,
database=mysqlDatabase)
cursor = cnx.cursor()
# Remove the contacts currently in the database
cursor.execute("DROP TABLE IF EXISTS phonebook") # autocommit
ddl = """
CREATE TABLE phonebook (
contact_id int(6) NOT NULL AUTO_INCREMENT,
name varchar(32) NOT NULL,
number varchar(32) NOT NULL,
PRIMARY KEY (contact_id)
)
"""
cursor.execute(ddl) # autocommit
# Insert all contacts into the database
insertSql = """
INSERT INTO phonebook
(name, number)
VALUES
(%s, %s)
"""
cursor.executemany(insertSql, allGoogleContacts)
# Commit insert and close MySQL connection
cnx.commit()
cursor.close()
cnx.close()
if __name__ == "__main__":
Drive()