In [59]:

    

import MySQLdb
import simplejson as json
import requests
from bs4 import BeautifulSoup
import re

db = MySQLdb.connect(host="localhost", port=3306,
                     user="root", 
                      passwd="mysql", 
                      db="civic")

url = "http://myneta.info/delhi2015/index.php?action=show_winners&sort=default"
politician = [None] * 7
headers = {'Accept': 'text/html'}
r = requests.get(url, headers=headers)
soup = BeautifulSoup(r.content)
tables = soup.findAll("table")
for idx, table in enumerate(tables):
    if(idx == 2):
        allrows = table.find_all('tr')
        del allrows[0]
        del allrows[0]
        for row in allrows:
            cells = row.find_all("td")
            del cells[0]
            for colIndex, cell in enumerate(cells):
                if(colIndex == 0):
                    for anchorIndex, a in enumerate(cell.findAll('a')):
                        if(anchorIndex == 1):
                            politician[0] = a.contents[0]
                            break
                if(colIndex == 1):
                    cur = db.cursor() 
                    cur.execute("select id from c_constituency where name='"+cell.contents[0]+"'");
                    for row in cur.fetchall() :
                        politician[1] = row[0]
                if(colIndex == 2):
                    cur = db.cursor() 
                    cur.execute("select id from c_party where abbreviation='"+cell.contents[0]+"'");
                    for row in cur.fetchall() :
                        politician[2] = row[0]
                if(colIndex == 3):
                    for spanIndex, b in enumerate(cell.findAll('span')):
                        politician[3] = b.contents[0]
                        break
                    if (not cell.findAll('span')):
                        politician[3] = cell.contents[0]
                if(colIndex == 4):
                    politician[4] = cell.contents[0]
                if(colIndex == 5):
                    politician[5] = re.sub("[^0-9]", "", cell.contents[0])
                if(colIndex == 6):
                    politician[6] = re.sub("[^0-9]", "", cell.contents[0])
            cur = db.cursor() 
            query = 'insert into c_politician(name,constituency_id,party_id,education,totalAssets,liabilities,criminal_cases_pending) values ("'
            query = query + politician[0]+'",'
            query = query + str(politician[1])+','
            query = query + str(politician[2])+',"'
            query = query + politician[4]+'",'
            query = query + str(politician[5])+','
            query = query + str(politician[6])+','
            query = query + str(politician[3])+')'
            cur.execute(query)
            print query, cur.rowcount
            db.commit()
            cur.close ()
        db.close ()


    

    




insert into c_politician(name,constituency_id,party_id,education,totalAssets,liabilities,criminal_cases_pending) values ("Pawan Kumar Sharma",1,1,"12th Pass",10996895,256710, 1 ) 1
insert into c_politician(name,constituency_id,party_id,education,totalAssets,liabilities,criminal_cases_pending) values ("Ajay Dutt",2,1,"Post Graduate",23427501,2972000,0) 1
insert into c_politician(name,constituency_id,party_id,education,totalAssets,liabilities,criminal_cases_pending) values ("Gopal Rai",3,1,"Post Graduate",5239843,0, 2 ) 1
insert into c_politician(name,constituency_id,party_id,education,totalAssets,liabilities,criminal_cases_pending) values ("Narayan Dutt Sharma",4,1,"12th Pass",6178144,50000,0) 1
insert into c_politician(name,constituency_id,party_id,education,totalAssets,liabilities,criminal_cases_pending) values ("Ajesh Yadav",5,1,"Graduate",145047653,15404944,0) 1
insert into c_politician(name,constituency_id,party_id,education,totalAssets,liabilities,criminal_cases_pending) values ("Imran Hussain",6,1,"Graduate",7397824,750000, 1 ) 1
insert into c_politician(name,constituency_id,party_id,education,totalAssets,liabilities,criminal_cases_pending) values ("Ved Parkash",7,1,"Graduate",11782955,554074, 1 ) 1
insert into c_politician(name,constituency_id,party_id,education,totalAssets,liabilities,criminal_cases_pending) values ("Col Devinder Sehrawat",8,1,"Post Graduate",22936566,480000, 2 ) 1
insert into c_politician(name,constituency_id,party_id,education,totalAssets,liabilities,criminal_cases_pending) values ("Sanjeev Jha",9,1,"Graduate",827915,0,0) 1
insert into c_politician(name,constituency_id,party_id,education,totalAssets,liabilities,criminal_cases_pending) values ("Alka Lamba",10,1,"Post Graduate",14012477,0,0) 1
insert into c_politician(name,constituency_id,party_id,education,totalAssets,liabilities,criminal_cases_pending) values ("Kartar Singh Tanwar",11,1,"Others",176512596,92433052, 1 ) 1
insert into c_politician(name,constituency_id,party_id,education,totalAssets,liabilities,criminal_cases_pending) values ("Surender Singh",12,1,"Graduate",7629527,0,0) 1
insert into c_politician(name,constituency_id,party_id,education,totalAssets,liabilities,criminal_cases_pending) values ("Prakash",13,1,"Post Graduate",46740,16436, 1 ) 1
insert into c_politician(name,constituency_id,party_id,education,totalAssets,liabilities,criminal_cases_pending) values ("Adarsh Shastri",14,1,"Post Graduate",22138623,5107118,0) 1
insert into c_politician(name,constituency_id,party_id,education,totalAssets,liabilities,criminal_cases_pending) values ("Anil Kumar Bajpai",15,1,"Graduate",447001,0,0) 1
insert into c_politician(name,constituency_id,party_id,education,totalAssets,liabilities,criminal_cases_pending) values ("Shri Dutt Sharma",16,1,"12th Pass",140108017,10440607,0) 1
insert into c_politician(name,constituency_id,party_id,education,totalAssets,liabilities,criminal_cases_pending) values ("Fateh Singh",17,1,"Graduate",72646007,200000,0) 1
insert into c_politician(name,constituency_id,party_id,education,totalAssets,liabilities,criminal_cases_pending) values ("Saurabh Bharadwaj",18,1,"Graduate Professional",10229151,0, 1 ) 1
insert into c_politician(name,constituency_id,party_id,education,totalAssets,liabilities,criminal_cases_pending) values ("Jagdeep Singh",19,1,"12th Pass",18589244,531170, 3 ) 1
insert into c_politician(name,constituency_id,party_id,education,totalAssets,liabilities,criminal_cases_pending) values ("Rajesh Rishi",20,1,"Graduate",1423939,0, 4 ) 1
insert into c_politician(name,constituency_id,party_id,education,totalAssets,liabilities,criminal_cases_pending) values ("Praveen Kumar",21,1,"Post Graduate",807000,0,0) 1
insert into c_politician(name,constituency_id,party_id,education,totalAssets,liabilities,criminal_cases_pending) values ("Avtar Singh",22,1,"8th Pass",577958,0,0) 1
insert into c_politician(name,constituency_id,party_id,education,totalAssets,liabilities,criminal_cases_pending) values ("Kapil Mishra",23,1,"Post Graduate",4600637,1999864,0) 1
insert into c_politician(name,constituency_id,party_id,education,totalAssets,liabilities,criminal_cases_pending) values ("Vishesh Ravi",24,1,"12th Pass",717000,35000,0) 1
insert into c_politician(name,constituency_id,party_id,education,totalAssets,liabilities,criminal_cases_pending) values ("Madan Lal",25,1,"Post Graduate",26829444,1924841,0) 1
insert into c_politician(name,constituency_id,party_id,education,totalAssets,liabilities,criminal_cases_pending) values ("Rituraj Govind",26,1,"Others",37933,0,0) 1
insert into c_politician(name,constituency_id,party_id,education,totalAssets,liabilities,criminal_cases_pending) values ("Manoj Kumar",27,1,"10th Pass",946186,79082,0) 1
insert into c_politician(name,constituency_id,party_id,education,totalAssets,liabilities,criminal_cases_pending) values ("S K Bagga",28,1,"Post Graduate",74141683,5303529,0) 1
insert into c_politician(name,constituency_id,party_id,education,totalAssets,liabilities,criminal_cases_pending) values ("Nitin Tyagi",29,1,"Post Graduate",14339460,384000,0) 1
insert into c_politician(name,constituency_id,party_id,education,totalAssets,liabilities,criminal_cases_pending) values ("Girish Soni",30,1,"10th Pass",12688397,1269467,0) 1
insert into c_politician(name,constituency_id,party_id,education,totalAssets,liabilities,criminal_cases_pending) values ("Somnath Bharti",31,1,"Post Graduate",13981720,2096655, 1 ) 1
insert into c_politician(name,constituency_id,party_id,education,totalAssets,liabilities,criminal_cases_pending) values ("Rakhi Birla",32,1,"Post Graduate",18000,290000, 2 ) 1
insert into c_politician(name,constituency_id,party_id,education,totalAssets,liabilities,criminal_cases_pending) values ("Asim Ahmed Khan",33,1,"Graduate",65349988,1633760,0) 1
insert into c_politician(name,constituency_id,party_id,education,totalAssets,liabilities,criminal_cases_pending) values ("Gulab Singh",34,1,"12th Pass",57030400,2053382, 10 ) 1
insert into c_politician(name,constituency_id,party_id,education,totalAssets,liabilities,criminal_cases_pending) values ("Naresh Yadav",35,1,"Graduate Professional",268080564,27350000,0) 1
insert into c_politician(name,constituency_id,party_id,education,totalAssets,liabilities,criminal_cases_pending) values ("Akhilesh Pati Tripathi",36,1,"Post Graduate",188118,0, 1 ) 1
insert into c_politician(name,constituency_id,party_id,education,totalAssets,liabilities,criminal_cases_pending) values ("Shiv Charan Goel",37,1,"12th Pass",181462602,41300000,0) 1
insert into c_politician(name,constituency_id,party_id,education,totalAssets,liabilities,criminal_cases_pending) values ("Sukhvir Singh",38,1,"Post Graduate",181096152,827809,0) 1
insert into c_politician(name,constituency_id,party_id,education,totalAssets,liabilities,criminal_cases_pending) values ("Jagdish Pradhan",39,2,"10th Pass",139974291,1760325,0) 1
insert into c_politician(name,constituency_id,party_id,education,totalAssets,liabilities,criminal_cases_pending) values ("Kailash Gahlot",40,1,"Post Graduate",374541806,85525139,0) 1
insert into c_politician(name,constituency_id,party_id,education,totalAssets,liabilities,criminal_cases_pending) values ("Raghuvinder Shokeen",41,1,"Graduate",62297490,2282122,0) 1
insert into c_politician(name,constituency_id,party_id,education,totalAssets,liabilities,criminal_cases_pending) values ("Sharad Kumar",42,1,"10th Pass",25953409,0,0) 1
insert into c_politician(name,constituency_id,party_id,education,totalAssets,liabilities,criminal_cases_pending) values ("Arvind Kejriwal",43,1,"Graduate Professional",20985366,4100000, 10 ) 1
insert into c_politician(name,constituency_id,party_id,education,totalAssets,liabilities,criminal_cases_pending) values ("Amanatullah Khan",44,1,"12th Pass",20073316,0, 1 ) 1
insert into c_politician(name,constituency_id,party_id,education,totalAssets,liabilities,criminal_cases_pending) values ("Bhavna Gaur",45,1,"Graduate Professional",269570,0,0) 1
insert into c_politician(name,constituency_id,party_id,education,totalAssets,liabilities,criminal_cases_pending) values ("Hazari Lal Chauhan",46,1,"8th Pass",26112537,33000,0) 1
insert into c_politician(name,constituency_id,party_id,education,totalAssets,liabilities,criminal_cases_pending) values ("Manish Sisodia",47,1,"Others",4166133,0, 6 ) 1
insert into c_politician(name,constituency_id,party_id,education,totalAssets,liabilities,criminal_cases_pending) values ("Parmila Tokas",48,1,"12th Pass",879098874,121596944,0) 1
insert into c_politician(name,constituency_id,party_id,education,totalAssets,liabilities,criminal_cases_pending) values ("Vijender Garg Vijay",49,1,"Graduate",23604194,0,0) 1
insert into c_politician(name,constituency_id,party_id,education,totalAssets,liabilities,criminal_cases_pending) values ("Jarnail Singh",50,1,"Post Graduate",11974780,155000, 3 ) 1
insert into c_politician(name,constituency_id,party_id,education,totalAssets,liabilities,criminal_cases_pending) values ("Mohinder Goyal",51,1,"10th Pass",29958454,563044,0) 1
insert into c_politician(name,constituency_id,party_id,education,totalAssets,liabilities,criminal_cases_pending) values ("Vijender Gupta",52,2,"Post Graduate",85886067,11560502, 2 ) 1
insert into c_politician(name,constituency_id,party_id,education,totalAssets,liabilities,criminal_cases_pending) values ("Sarita Singh",53,1,"Post Graduate",118280,0,0) 1
insert into c_politician(name,constituency_id,party_id,education,totalAssets,liabilities,criminal_cases_pending) values ("Som Dutt",54,1,"Graduate",1079433,0,0) 1
insert into c_politician(name,constituency_id,party_id,education,totalAssets,liabilities,criminal_cases_pending) values ("Dinesh Mohaniya",55,1,"12th Pass",2260631,0, 2 ) 1
insert into c_politician(name,constituency_id,party_id,education,totalAssets,liabilities,criminal_cases_pending) values ("Mohd. Ishraque",56,1,"5th Pass",27300738,0,0) 1
insert into c_politician(name,constituency_id,party_id,education,totalAssets,liabilities,criminal_cases_pending) values ("Rajendra Pal Gautam",57,1,"Graduate Professional",6349317,0,0) 1
insert into c_politician(name,constituency_id,party_id,education,totalAssets,liabilities,criminal_cases_pending) values ("Ram Niwas Goel",58,1,"Graduate",43968000,3481000, 2 ) 1
insert into c_politician(name,constituency_id,party_id,education,totalAssets,liabilities,criminal_cases_pending) values ("Satyendar Kumar Jain",59,1,"Graduate",80841007,11705000,0) 1
insert into c_politician(name,constituency_id,party_id,education,totalAssets,liabilities,criminal_cases_pending) values ("Bandana Kumari",60,1,"Graduate",88771227,445709, 1 ) 1
insert into c_politician(name,constituency_id,party_id,education,totalAssets,liabilities,criminal_cases_pending) values ("Sandeep Kumar",61,1,"Graduate Professional",1316629,0,0) 1
insert into c_politician(name,constituency_id,party_id,education,totalAssets,liabilities,criminal_cases_pending) values ("Jarnail Singh",62,1,"12th Pass",18889333,500452, 2 ) 1
insert into c_politician(name,constituency_id,party_id,education,totalAssets,liabilities,criminal_cases_pending) values ("Pankaj Pushkar",63,1,"Post Graduate",14880949,571442,0) 1
insert into c_politician(name,constituency_id,party_id,education,totalAssets,liabilities,criminal_cases_pending) values ("Jitender Singh Tomar",64,1,"Graduate Professional",10724369,746950,0) 1
insert into c_politician(name,constituency_id,party_id,education,totalAssets,liabilities,criminal_cases_pending) values ("Raju Dhingan",65,1,"8th Pass",1010862,23605,0) 1
insert into c_politician(name,constituency_id,party_id,education,totalAssets,liabilities,criminal_cases_pending) values ("Sahi Ram",66,1,"10th Pass",9406703,0, 3 ) 1
insert into c_politician(name,constituency_id,party_id,education,totalAssets,liabilities,criminal_cases_pending) values ("Naresh Balyan",67,1,"8th Pass",581182557,1806000,0) 1
insert into c_politician(name,constituency_id,party_id,education,totalAssets,liabilities,criminal_cases_pending) values ("Mahinder Yadav",68,1,"10th Pass",21532814,0,0) 1
insert into c_politician(name,constituency_id,party_id,education,totalAssets,liabilities,criminal_cases_pending) values ("Om Prakash Sharma",69,2,"Graduate",192576370,41341948,0) 1
insert into c_politician(name,constituency_id,party_id,education,totalAssets,liabilities,criminal_cases_pending) values ("Rajesh Gupta",70,1,"12th Pass",1652676,109795,0) 1

In [ ]:

    




    

In [ ]:

    




    

In [ ]: