Thana Summary Script

import MySQLdb


def dropThanaSummaryTable(db):
    cursor = db.cursor()
    deleteQuery = 'DELETE FROM `thana_summary` WHERE 1'

    try:
        cursor.execute(deleteQuery)
        db.commit()
        print 'Successfully cleared the table'

    except Exception as inst:
        print type(inst)  # the exception instance
        print inst.args  # arguments stored in .args
        print inst  # __str__ allows args to be printed directly
        x, y = inst.args
        print 'x =', x
        print 'y =', y


def insertByBatchQuery(db, upperLimit, lowerLimit):
    batchInsertQuery = """ insert into thana_summary (District_name,
                                         District_id,
                                         Thana_name,
                                         TCode,
                                         Total_school,
                                         Total_Amount,
                                         Total_Amount_Mobile,
                                         Total_parents,
                                         Beneficiary,
                                         Disbursement_through_mobile_banking,
                                         NoOfStd,
                                         Beneficiary_NoOnStd,
                                         Mobile_Beneficiary_Std)
                            SELECT
                                dis.`name` AS 'District_name',
                                dis.id as 'District_id',
                                t.`name` AS 'Thana name',
                                t.`thana_code` AS TCode,
                                COUNT(DISTINCT (d.`school_code`)) AS 'Total_school',
                                SUM(CASE
                                    WHEN d.`amount` <= 1350 AND d.`amount` > 0 THEN d.`amount`
                                END) AS 'Total_Amount',
                                SUM(CASE
                                    WHEN d.`is_valid` = 1 THEN d.`amount`
                                END) AS 'Total_Amount_Mobile',
                                COUNT(d.`id`) AS 'Total_parents',
                                COUNT(CASE
                                    WHEN d.`amount` <= 1350 AND d.`amount` > 0 THEN d.`id`
                                END) AS 'Beneficiary',
                                COUNT(CASE
                                    WHEN d.`is_valid` = 1 THEN 1
                                END) AS 'Disbursement_through_mobile_banking',
                                SUM(d.`no_of_student`) AS NoOfStd,
                                SUM(CASE
                                    WHEN d.`amount` <= 1350 AND d.`amount` > 0 THEN d.`no_of_student`
                                END) AS Beneficiary_NoOnStd,
                                SUM(CASE
                                    WHEN d.`is_valid` = 1 THEN d.`no_of_student`
                                END) AS 'Mobile_Beneficiary_Std'
                            FROM
                                `disbursement_sheet_raw_data_experiment3` d
                                    INNER JOIN
                                `school` s ON d.`school_code` = s.`school_code`
                                    INNER JOIN
                                `thana` t ON t.id = s.`thana_id`
                                    INNER JOIN
                                `district` dis ON t.`district_id` = dis.`id`
                                where t.id >= """ + str(lowerLimit) + """ and t.id <=""" + str(upperLimit) + """
                            GROUP BY t.`name`"""

    try:
        cursor.execute(batchInsertQuery)
        db.commit()
        print 'Successfully inserted data into the table thanaId range [' + str(lowerLimit) + ' , ' + str(
            upperLimit) + ']'

    except Exception as inst:
        print type(inst)  # the exception instance
        print inst.args  # arguments stored in .args
        print inst  # __str__ allows args to be printed directly
        x, y = inst.args
        print 'x =', x
        print 'y =', y


def insertDataInSummaryTable(db):
    getMaxIdQuery = 'Select id FROM thana ORDER BY id DESC limit 1 offset 0'
    cursor = db.cursor()
    cursor.execute(getMaxIdQuery);
    maxId = cursor.fetchall()
    maxId = maxId[0][0]
    print "Max thana Id: " + str(maxId)
    upperLimit = 99
    lowerLimit = 0
    while 1:
        insertByBatchQuery(db, upperLimit, lowerLimit)
        upperLimit = upperLimit + 100
        lowerLimit = lowerLimit + 100
        if lowerLimit > maxId:
            break


hostIp = "192.168.1.56"
userName = "naimul"
password = "naimul#56"
dbName = "pesp_test_db_naimul"
db = MySQLdb.connect(hostIp, userName, password , dbName)
cursor = db.cursor()

dropThanaSummaryTable(db)
insertDataInSummaryTable(db)