#!/usr/bin/python3
# -*- coding: utf-8 -*-
from bs4 import BeautifulSoup
import requests
import pymysql

db = pymysql.connect(host='127.0.0.1', user='spaceapp2017',
        password='5PGfPUMlNqt7HyFe', db='spaceapp2017', charset='utf8')

cursor = db.cursor()

# Prepare SQL query to INSERT a record into the database.
sql = "INSERT INTO rain_10min(batch, area, subarea, 10min, 1hr, 3hr, 6hr, 12hr, 24hr, today, day1, day2) VALUES (%s, %s, %s, %s, %s, %s, %s, %s, %s, %s, %s, %s)"

r = requests.get('http://www.cwb.gov.tw/V7/observe/rainfall/A136.htm')
r.encoding = 'utf-8'
soup = BeautifulSoup(r.text, 'html.parser')

data = []
table = soup.find('table', attrs={'class':'tablesorter'})
table_body = table.find('tbody')

rows = table_body.find_all('tr')
for row in rows:
    cols = row.find_all('td')
    cols = [ele.get_text() for ele in cols]
    data.append([ele for ele in cols if ele]) # Get rid of empty values
#print(data)

batch = 0
cols = soup.select('table.description tr td') # , attrs={'class':'description'}
batch = cols[3]
batch = batch.get_text()
tag = batch.index(':')
batch = batch[tag+2:len(batch)-3]
batch = batch.replace("/", "")
batch = batch.replace(" ", "")
batch = batch.replace(":", "")

for i in range(len(data)):
    val = []
    for j in range(len(data[0])):
        if(data[i][j] == '-' or data[i][j] == 'X'):
            val.append(None)
        elif(j != 0 and j != 1):
            val.append(float(data[i][j]))
    tag = data[i][1].index(' (')
    cursor.execute(sql, (batch, data[i][0], data[i][1][:tag], val[0], val[1],
                    val[2], val[3], val[4], val[5], val[6], val[7], val[8]))

'''path = './10minrain.txt'
rain = open(path,'w')
for i in data:
    for j in i:
        rain.write(str(j) + ' ')
    rain.write('\n')'''

db.commit()