Download all info about prayer time at www.e-solat.gov.my and store in database

waktu_solat.py

#!/usr/bin/env python

"""
Original Script : https://gist.github.com/efaisal/4583986

Muat turun data waktu solat Jakim

Create database nama 'waktusolat' dalam MySQL

Lepas tu create table 'waktu_solat'

CREATE TABLE IF NOT EXISTS `waktu_solat` (
  `id` int(11) NOT NULL AUTO_INCREMENT,
  `date` varchar(20) NOT NULL,
  `imsak` varchar(5) NOT NULL,
  `subuh` varchar(5) NOT NULL,
  `syuruk` varchar(5) NOT NULL,
  `zohor` varchar(5) NOT NULL,
  `asar` varchar(5) NOT NULL,
  `maghrib` varchar(5) NOT NULL,
  `isyak` varchar(5) NOT NULL,
  `zone_code` varchar(5) NOT NULL,
  `date_retrieved` datetime NOT NULL,
  PRIMARY KEY (`id`)
) ENGINE=InnoDB  DEFAULT CHARSET=latin1 AUTO_INCREMENT=731 ;

run script bawah ni
"""

import re
import requests
from lxml import etree
import pymysql.cursors

# connection credential ubah ikut info database sendiri

connection = pymysql.connect(host='localhost',
    user='root',
    password='',
    db='waktusolat',
    port=3306)

try:
    with connection.cursor() as cursor:
        tahun = '2017'
        
        zone = ['JHR01','JHR02','JHR03','JHR04','KDH01','KDH02','KDH03','KDH04','KDH05',
        'KDH06','KDH07','KTN01','KTN03','MLK01','NGS01','NGS02','PHG01','PHG02','PHG03',
        'PHG04','PHG05','PHG06','PLS01','PNG01','PRK01','PRK02','PRK03','PRK04','PRK05',
        'PRK06','PRK07','SBH01','SBH02','SBH03','SBH04','SBH05','SBH06','SBH07','SBH08',
        'SBH09','SGR01','SGR02','SGR03','SGR04','SWK01','SWK02','SWK03','SWK04','SWK05',
        'SWK06','SWK07','SWK08','SWK09','TRG01','TRG02','TRG03','TRG04','WLY02']

        url = 'http://www.e-solat.gov.my/web/waktusolat.php?zone=%s&state=&year=%s&jenis=year&bulan=%s&LG=BM'
        
        peta_bulan = {'Januari': '01', 'Februari': '02', 'Mac': '03', 'April': '04',
                     'Mei': '05', 'Jun': '06', 'Julai': '07', 'Ogos': '08',
                     'September': '09', 'Oktober': '10', 'November': '11',
                     'Disember': '12'}
        
        regex = re.compile("([\d]{1,2}) ([\w]+)")

        for zon in zone: 
            data = []
            print("\nData %s" % zon)
            for i in range(1, 13):
                bulan = "%02d" % i
                print("Download data for month %s" % i)
                r = requests.get(url % (zon, tahun, bulan))
                c = r.text
                root = etree.HTML(c)

                print("Process data..")
                for c in root.getchildren()[0].findall('table')[1][1][0].findall('table')[1][0][0][1].getchildren()[1:-1]:
                    tmp = []
                    for ctd in c.getchildren():
                        tmp.append(''.join([t.strip() for t in ctd.itertext()]))
                    data.append(tmp)

            print("Insert data to database....")
            
            for d in data:
                hari, bulan = regex.match(d[0]).groups()
                bulan = peta_bulan[bulan]
                tarikh = "%s-%s-%02d" % (tahun, bulan, int(hari))
                imsak = d[2] if len(d[2]) == 5 else '0' + d[2]
                subuh = d[3] if len(d[3]) == 5 else '0' + d[3]
                syuruk = d[4] if len(d[4]) == 5 else '0' + d[4]
                zohor = d[5] if len(d[5]) == 5 else '0' + d[5]
                asar = d[6] if len(d[6]) == 5 else '0' + d[6]
                maghrib = d[7] if len(d[7]) == 5 else '0' + d[7]
                isya = d[8] if len(d[8]) == 5 else '0' + d[8]

                sql = "INSERT INTO `waktu_solat` (`date`,`imsak`,`subuh`,`syuruk`,`zohor`,`asar`,`maghrib`,`isyak`,`zone_code`,`date_retrieved`) VALUES (%s, %s, %s, %s, %s, %s, %s, %s, %s, NOW())"
                cursor.execute(sql, (tarikh, imsak, subuh, syuruk, zohor, asar, maghrib, isya, zon))
            
            print("Success insert data for zone %s!" % zon)
        
        print("Success get all data. :)")
    
    connection.commit()

finally:
    
    connection.close()