python's selenium automated click-through simple application

I originally intended to use Python's requests library to simulate clicks to collect the returned information; however, after several attempts, I found that I was still unable to post access pop-ups and was not familiar with some aspects of ajax.There is no problem with posting forms for login, as the web address of posting submissions does not change, and multiple pop-ups use the same web address of submitting forms.However, when posting the form to the web page, I found that the results returned were unchanged and did not return to the results I wanted. Because there were too many parameters for the submitted form, I checked some parameters repeatedly or found no problems.So I was forced to use selenium's library.

1. Application of selenium;

To my knowledge, selenium libraries are mostly used for testing.And when I saw the automated web testing provided by selenium, I thought I could do that.For little white, I think this is a button wizard like an enhanced version.

Local environment:

Win7+python 3.7+webdriver.exe+Chrome (version 75.0.3770.100 (official version) (32 bit)+selenium

download chromedriver address

The precondition is to install the Chrome driver application of webdriver.exe. The web driver should be placed in the program running directory, or the address of the webdriver should be specified.

First needs analysis:

1. Want to automate the transfer of daily tasks by clicking on them.

2. To whom do you want to record tables, directions of task transfers, etc.

3. Want to be able to take time off without turning around.

4. Want to be able to handle exceptions to record transfers.

...Needs to increase in the future

Demand fulfillment:

First, the first requirement is that selenium's webdriver can be used to automate clicks on Web pages.By locating the element's location or executing a js script.The js script can perform validation through the console background of F12, the easiest way to copy a js path is to check a web page element.

Second, my idea is to do this through xlwings, because you need to be able to read and write continuously.Also to determine who to transfer to and who to transfer to, I will create a nested dict store that stores the number of times to transfer as a dimension with the date of the day, {group team:{username:(times)}, using with open(r') as f:to open the file for reading and writing.

Third, because the table of personal information relation that needs to be stored is not big, I use Access data to store the basic table of personal information relation, add a field to the table to ask for leave or not.(Each time you read the Access database, check the field once to pick out the true.)

The fourth point is that there is no auto-processing solution for the moment, so you have to restart the operation manually after encountering an error.

Disadvantages:

Because I find that most of the errors that occur are due to the slow loading of the web page, which makes the elements impossible to find and therefore makes an error.

This can be accomplished by adding a wait time to the program or by waiting for the required elements to load.

Also, due to the risk of server downtime, the failure of the web page to log on and load is not handled.

Related conding, mainly browser startup, database links, initial data reading and preparation.

from selenium import webdriver
from time import sleep 
#Sign in
driver=webdriver.Chrome()
driver.get('http://xxxxxxxxxx')
sleep(2)
driver.execute_script("document.querySelector('#ContentPlaceHolder1_txtUserName').value='happykkk'")
driver.execute_script("document.querySelector('#ContentPlaceHolder1_txtPassword').value='123456'")
driver.execute_script("document.querySelector('#ContentPlaceHolder1_btnOK').click()")



import pyodbc  

DBfile = r"\\login_information.mdb"  # Database Files  
conn = pyodbc.connect(r"Driver={Driver do Microsoft Access (*.mdb)};DBQ=" + DBfile + ";Pwd=p123456")  

cursor = conn.cursor()  
SQL = "SELECT * from user_relation_autotransfer;"  
cur=cursor.execute(SQL)
data=cur.fetchall()

#Initialize dict
dic={'Team1':{},'Team2':{},'Team3':{},'Team4':{},'Team5':{},'team6':{},'Team7':{}}
for i in data:
    if i[3]==True:
        dic[i[1]][i[0]]=0

import json
from datetime import datetime
t=str(datetime.now())[:10]
with open(r'./transfer record{}.txt'.format(t),'r') as f1:
    rest=json.load(f1)
#Read excel file contents
import xlwings as xw #Use xlwings to read data
try:
    filepath=r'C:\11.xlsx'
    app = xw.App(visible=False, add_book=False)
    wb=app.books.open(filepath)
    sht=wb.sheets[0]
    taskdata=sht.range('A1').expand('table').value
    print(taskdata)
    wb.close()
    app.quit()
    app.kill()

except:
    wb.close()
    app.quit()
    app.kill()

There are also some controls to manipulate simulated clicks based on Web page elements.

#data refers to a table of people relationships.Team is the team to be transferred, maker is the person who confirms the position, default is None
def chooseperson(data,team,maker=None):
    
    m=list(rest[team].keys())
    v=list(rest[team].values())
    
    if maker in m:  #First, determine if maker is in the team and to whom: who has the least number of tasks in the group
        nn=m.index(maker)
        m.pop(nn)
        v.pop(nn)
        num=v.index(min(v))#Determine the minimum number of tasks
        
        result=m[num]#Person to transfer
        rest[team][result]+=1  #Post-transfer statistics
        return result#Return results
    elif maker ==None:
        num=v.index(min(v))
        result=m[num]
        rest[team][result]+=1
        return result
    else:#Task error team type occurred, maker does not match team
        for i in data:
            if i[0]==maker:#Reconfirm team to transfer
                chooseperson(data,i[1],maker) #Recursive call
def searchtask(driver,tasknum):
    
#Click on Search Task
    #Using xpath
    
#     driver.find_element_by_xpath('//*[@id="ContentPlaceHolder1_lbtnSearch"]').click()
    driver.execute_script("document.querySelector('#ContentPlaceHolder1_lbtnSearch').click()")
    sleep(1.5)
    #sleep because it opens too fast
    driver.execute_script("document.querySelector('#ContentPlaceHolder1_TextBox1').value=''")

    # driver.execute_script("document.querySelector('#ContentPlaceHolder1_cmbForm17').click()")
    sleep(1)
    #Select payroll Team
    driver.execute_script("document.getElementById('ContentPlaceHolder1_cmbForm17').options[28].selected=true")
    sleep(1)
    #Fill in the task number
    driver.execute_script("document.querySelector('#ContentPlaceHolder1_TextBox2').value='%s'"%(tasknum))
    #Click Search
    driver.execute_script("document.querySelector('#ContentPlaceHolder1_Button4').click()")
    



global rest#global variable
#judge
def transfertask(driver,data,team,maker):
    #Click Check
    sleep(1)
    driver.execute_script("document.querySelector('#ContentPlaceHolder1_GridView1_chk1_0').click()")
    #Click to transfer task
    sleep(1)
    driver.execute_script("document.querySelector('#ContentPlaceHolder1_ImageButton5').click()")
    person=chooseperson(data,team,maker)
    print(person)
    sleep(1.5)
    driver.execute_script("document.querySelector('#ContentPlaceHolder1_TextBox8').value='%s'"%(person))
    sleep(2)
#     driver.find_element_by_xpath('//*[@id="ContentPlaceHolder1_Button8"]').click()#click go
    
    driver.execute_script("document.querySelector('#ContentPlaceHolder1_Button9').click()")#Click Cancel
    sleep(1.5)
driver.implicitly_wait(20)

global rest
import datetime

def writesheet(data1,i,person):#Plan mark table
    
#     try:
        team=''
        for d in data1:
            if d[0].split(' ')[0].title()==i[4].split(' ')[0].title():
                team=d[1]
        print(team)
        datares=[i[0],team,i[2],'incorrect task allocation','Y',i[5],i[6],'Y',datetime.datetime.strftime(datetime.datetime.now(),'%Y%m%d')[:6],i[4],person]
        filepath=r'\\Transfer Record.xlsx'
#         filepath=r''
#         app = xw.App(visible=False, add_book=False)
#         wb=app.books.open(filepath)
        wb=xw.Book(filepath)
        sht=wb.sheets[0]
#         sht2=wb.sheets[1]
        nrow = sht.range('A65536').end('up').row+1
        print(nrow)
        sht.range('A{}'.format(nrow)).value=datares
        wb.save(filepath)
    
for i in taskdata:
    tasknum=str(i[0]).split(".")[0]
    searchtask(driver,tasknum)
    sleep(1.5)
    jud=driver.find_element_by_xpath('//*[@id="ContentPlaceHolder1_GridView1"]/tbody/tr[2]/td').text
#     print(jud)
    if jud!="No data record":


        postype=driver.find_element_by_xpath('//*[@id="ContentPlaceHolder1_GridView1"]/tbody/tr[2]/td[4]').text
        if postype=='maker post':
            maker=None
            transfertask(driver,data,i[2].title(),maker)
        elif postype=='check post':
            #Click to view task details
            driver.execute_script("document.querySelector('#ContentPlaceHolder1_GridView1_linkButton1_0').click()")
            sleep(1.5)
            #Get a person's name
            maker=driver.find_element_by_xpath('//*[@id="ContentPlaceHolder1_GridView5"]/tbody/tr[3]/td[3]').text
            sleep(1)
            print(maker)
            driver.execute_script("document.querySelector('#ContentPlaceHolder1_Button9').click()")#Sign out
            sleep(1.5)
            searchtask(driver,tasknum)
            sleep(1.5)
            transfertask(driver,data,i[2].title(),maker)


with open(r'./transfer record{}.txt'.format(t),'w') as f:#Record rest, which is the number of times that day was transferred to everyone
    f.write(json.dumps(rest))

Adding some conditional judgments allows simulated clicks to perform more tedious operations.

However, there are still some problems with the running of this code, such as the web page element card, the network speed can not keep up with, or the server abnormal results in errors in the operation; this problem needs to be solved.Idea: You can run tasknum again with the appeal logic if an error occurs, or you can let the exception catch the taskid and return an error message prompt if it is still an error.If the server hangs up, I won't be able to, but the speed card can also be run again.

Disadvantages and problems:

1. Since I use more than one sleep, yes, the program runs slower

Optimize removes some unimportant sleep s, but it is set mainly for network speed.

2. Because the processed dataset is used, some people in the data are deleted, causing a dead loop in the chooseperson function.But records are lost because the program automatically ends.

Solution: Re-introduce a more complete dataset as a parameter to import

3. Re-transfer of failed records not resolved

 

selenium reference link:

https://www.cnblogs.com/101718qiong/p/8250104.html

Tags: REST Selenium Database JSON

Posted on Mon, 26 Aug 2019 18:45:58 -0700 by ToolJob