Python MySQL Example

#!/usr/bin/env python
import csv
import MySQLdb

db_settings = {
    'hostname': 'migration-tml-learning-aws1-prod.cykwvsam5lbv.sa-east-1.rds.amazonaws.com',
    'username': 'migration',
    'password': '5xmo4gtc',
    'database': 'tlc',
}

db = MySQLdb.connect(**db_settings)
cursor = db.cursor()
count_messages_template = """SELECT (MT.totalcount + MO.totalcount ) AS total_%(phonenumber)s
FROM
    (SELECT COUNT(TML_MT.created_date) AS totalcount
     FROM mobile_learning.mobile_terminated AS `TML_MT`
     WHERE TML_MT.phone_number = '%(phonenumber)s'
        AND TML_MT.created_date >= (now()- INTERVAL 90 DAY)) AS `MT`,
    (SELECT COUNT(TML_MO.created_date) AS totalcount
     FROM mobile_learning.mobile_originated AS `TML_MO`
     WHERE TML_MO.phone_number = '%(phonenumber)s'
        AND TML_MO.created_date >= (now()- INTERVAL 90 DAY)) AS `MO` UNION ALL SELECT count(TLC_MSG.created_date) AS total
    FROM tlc.message AS `TLC_MSG`
    WHERE TLC_MSG.msisdn = CONCAT('52', '%(phonenumber)s')
        AND TLC_MSG.created_date >= (now()- INTERVAL 90 DAY);"""

query = lambda q: (lambda q: cursor.query(q))()

def compare(phone):
    cursor.query(count_messages_template % {'phonenumber': phone})
    tml, tlc = cursor.fetchall()
    print 'MSISDN: 52%s, TLC: %i; TML: %s' % (phone, tml, tlc)

if __name__ == '__main__':
    with csv.open('numbers.csv', 'r') as phone:
        for phonenumber in phone:
            compare(phone)