Loop Dynamic Ajax Based Dropdown values using python selenium and grab data to mysql database

Question

I am able to grab data if i apply static value of each dropdown but now i am trying to fetch dynamic values loop through each value of dropdown and store result to mysqldb.

Issue : I am unable to loop each elements of drop-down and store result to database, if i pass static value for each three dropdown then i am able to do.

Working Code for Static values

import time
from selenium import webdriver
import requests
from bs4 import BeautifulSoup
import MySQLdb

url = "http://xlnindia.gov.in/frm_G_Cold_S_Query.aspx"
browser = webdriver.Chrome()
browser.get(url)

#=========State====================
state = browser.find_element_by_id("ddlState")
state.send_keys("GJ")

#=========District====================
district = browser.find_element_by_id("ddldistrict")
district.send_keys("AD2")

#=========Taluka====================
category = browser.find_element_by_id("ddltaluka")
category.send_keys("AMB")

button = browser.find_element_by_id("btnSearch")
button.click()


# Open database connection
db = MySQLdb.connect(host="localhost", # your host, usually localhost
                     user="root", # your username
                      passwd="", # your password
                      db="test") # name of the data base

cursor=db.cursor()


time.sleep(10)
browser.save_screenshot(browser.title + ".JPEG")
html = browser.page_source

soup = BeautifulSoup(html, "html.parser")
table = soup.findChildren('table')[3]

for row in table.findAll("tr")[1:]:
    cells = row.findAll("td")
    name = cells[0].find(text=True)
    city = cells[1].find(text=True)
    licence = cells[2].find(text=True)
    owner = cells[3].find(text=True)
    cursor.execute ("INSERT INTO distributors (name, city, licence, owner) VALUES (%s, %s, %s, %s);", (name, city, licence, owner))


#print(table.prettify())
#print(html)

db.commit()
db.close()

browser.close()
browser.quit()

Trying to pass dynamic values by looping each elements of dropdown

import time
from selenium import webdriver
import requests
from bs4 import BeautifulSoup
import MySQLdb

url = "http://xlnindia.gov.in/frm_G_Cold_S_Query.aspx"
browser = webdriver.Chrome()
browser.get(url)

#=======================================================
#Array of Stats Dropdown
states_array = []
el = browser.find_element_by_id('ddlState')
for option in el.find_elements_by_tag_name('option'):
    states_array.append(option.get_attribute("value"))


#print(states_array)
#=======================================================

#=======================================================
#Array of Districts Dropdown
district_array = []
el = browser.find_element_by_id('ddldistrict')
for option in el.find_elements_by_tag_name('option')[1:]:
    district_array.append(option.get_attribute("value"))

#print(district_array)
#=======================================================


state = browser.find_element_by_id("ddlState")
district = browser.find_element_by_id("ddldistrict")
category = browser.find_element_by_id("ddltaluka")


# Open database connection
db = MySQLdb.connect(host="localhost", # your host, usually localhost
                     user="root", # your username
                      passwd="", # your password
                      db="test") # name of the data base
cursor=db.cursor()
taluka_array = []

for i in range(len(states_array)):
    state.send_keys(states_array[i])
for j in range(len(district_array)):
    district.send_keys(district_array[j])
    district.click()
    el = browser.find_element_by_id('ddltaluka')
        for option in el.find_elements_by_tag_name('option')[1:]:
            taluka_array.append(option.get_attribute("value"))
        print(taluka_array)
for k in range(len(taluka_array)):  
    category.send_keys(taluka_array[k])
    button = browser.find_element_by_id("btnSearch")
    button.click()
    html = browser.page_source
    soup = BeautifulSoup(html, "html.parser")
    table = soup.findChildren('table')[3]
    for row in table.findAll("tr")[1:]:
        cells = row.findAll("td")
        name = cells[0].find(text=True)
        city = cells[1].find(text=True)
        licence = cells[2].find(text=True)
        owner = cells[3].find(text=True)
        cursor.execute ("INSERT INTO distributors (name, city, licence, owner) VALUES (%s, %s, %s, %s);", (name, city, licence, owner))
    db.commit()
    db.close()

Can you be much more explicit - what you expect to happen, what actually does happen, what specific issues you are facing - add this to the question pls, not in another comment. — Todor Minakov
– Todor Minakov, Commented May 31, 2017 at 6:40

MD. Khairul Basar · Accepted Answer · 2017-05-31 13:49:16Z

First find the td tag to filter out states then get the values of option tags. Here you get all the states. For each state go to the url of that state and find districts. Districts are in the 2nd td tag so use find_next_sibling() and again get the values of the option tags. So you find all the districts too for each state. And rest of the things you already did.

import time
from bs4 import BeautifulSoup
from selenium import webdriver

url = "http://xlnindia.gov.in/frm_G_Cold_S_Query.aspx"
browser = webdriver.Chrome()
browser.get(url)

time.sleep(5)
html = browser.page_source
soup = BeautifulSoup(html, "lxml")

states = [ x["value"] for x in soup.find("td", bgcolor="#ffe0c0").find_all("option") ]

for state in states:

    browser.get(url + "?ST=" + state)
    time.sleep(5)

    html = browser.page_source
    soup = BeautifulSoup(html, "lxml")

    districts = [ x["value"] for x in soup.find("td", bgcolor="#ffe0c0").find_next_sibling().find_all("option") ]
    districts = districts[1:]

    for dist in districts:

        browser.get(url + "?ST=" + state)
        district = browser.find_element_by_id("ddldistrict")
        district.send_keys(dist)

        button = browser.find_element_by_id("btnSearch")
        button.click()

browser.close()
browser.quit()

Collectives™ on Stack Overflow

Loop Dynamic Ajax Based Dropdown values using python selenium and grab data to mysql database

1 Answer 1

Comments

Your Answer

Linked

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

Comments

Your Answer

Sign up or log in

Post as a guest

Linked

Related