##main modules
import requests
from bs4 import BeautifulSoup
import re
import csv
import sqlite3
from IPython.display import display,HTML

url='http://www.espncricinfo.com/icc-cricket-world-cup-2015/content/current/series/509587.html'
page=requests.get(url)
print(type(page))

print('{} \nWebpage current status code ---> {}'.format(url,page.status_code))
if page.status_code == requests.codes.ok:
    print('>>>The response status code of webpage indicates "ACTIVE" state')

page_html = page.text
soup=BeautifulSoup(page_html,"html.parser")

country_refs=soup.find_all('a',href=re.compile('/icc-cricket-world-cup-2015/content/squad/'))
for link in country_refs:
    print(link)

conn=sqlite3.connect('Cricket.sqlite')
cur=conn.cursor()

cur.execute('''CREATE TABLE IF NOT EXISTS Countries
            (country_id INTEGER PRIMARY KEY,country TEXT)''')

cur.execute('''CREATE TABLE IF NOT EXISTS Players
            (country_id INTEGER,player_id INTEGER UNIQUE,player TEXT)''')

cur.execute('''CREATE TABLE IF NOT EXISTS Batting_Stats
                        (player TEXT,runs INTEGER,sixes INTEGER,fours INTEGER,ducks INTEGER,fifties INTEGER,hundreds INTEGER,balls_faced INTEGER,innings INTEGER)''')
cur.execute('''CREATE TABLE IF NOT EXISTS Bowling_Stats
                        (player TEXT,bowlinnings INTEGER ,overs INTEGER ,runsgiven INTEGER,maidens INTEGER,wickets INTEGER,fourw INTEGER,fivew INTEGER)''')

for country_ref in country_refs:
    country=country_ref.text
    country_id=country_ref.get('href').split('/')[-1].split('.')[0]
    cur.execute('INSERT OR IGNORE INTO Countries (country_id,country) VALUES (?,?)',(country_id,country))

cur.execute('SELECT country_id FROM Countries')
countryid_list=list()
for row in cur:
    countryid_list.append(row[0])

for cid in countryid_list:
    squad_url='http://www.espncricinfo.com/icc-cricket-world-cup-2015/content/squad/'+str(cid)+'.html'
    squad_page=requests.get(squad_url)
    soup1=BeautifulSoup(squad_page.text,"html.parser")
    player_links=soup1.findAll('a', href=re.compile('/icc-cricket-world-cup-2015/content/player/'))
    for i in player_links:
        if i.text:
            player_id=player_id=i.get('href').split('/')[-1].split('.')[0]
            player=i.text.strip()
            cur.execute('INSERT OR IGNORE INTO Players (country_id,player_id,player) VALUES (?,?,?)',(cid,player_id,player))
conn.commit()

cur.execute('select a.country_id,a.country,b.player_id,b.player from Countries a,Players b where a.country_id=b.country_id')
play_list=list()
for row in cur:
    play_list.append(row)
play_action=['batting','bowling']

year=2015 ##can be changed according to requirement
for action in play_action:
    for play in play_list:
        pid=play[2]
        country_name=play[1]
        player_name=play[3]
        stats_url='http://stats.espncricinfo.com/ci/engine/player/'+str(pid)+'.html?class=2;template=results;type='+str(action)+';view=innings;year='+str(year)
        stats_page=requests.get(stats_url)
        soup2=BeautifulSoup(stats_page.text,"html.parser")
        stats=soup2.findAll(text='filtered')[0].findParents('tr')[0].findAll("td")
        if action=="bowling":
            check=[3,4,6,5,7,12,13]
        else:
            check=[4,13,12,11,10,9,7,2]
        results=[]
        for i in check:
            try:
                results.append(float(stats[i].get_text()))
            except:
                results.append(0)
        if action=="batting":
            cur.execute('INSERT OR IGNORE INTO Batting_Stats (player,runs,sixes,fours,ducks,fifties,hundreds,balls_faced,innings)VALUES(?,?,?,?,?,?,?,?,?)',(player_name,results[0],results[1],results[2],results[3],results[4],results[5],results[6],results[7]))
        elif action=="bowling":
            cur.execute('INSERT OR IGNORE INTO Bowling_Stats (player,bowlinnings,overs,runsgiven,maidens,wickets,fourw,fivew)VALUES(?,?,?,?,?,?,?,?)',(player_name,results[0],results[1],results[2],results[3],results[4],results[5],results[6]))
conn.commit()