At the beginning of April, in a weekly meeting of the head teachers, Hua Hua asked them with concern: Do the head teachers have any problems recently? That’s what you spend most of your time doing! Our Python team can help you solve your problems.
Head teacher big director star say: have. There is a big problem. We every day into the group of students to register his real name, contact information, address, and the current salary and expected salary and why choose lemon class ~ and then for the payment of all the students go to tencent for inspection, to ensure that the student have a class, and each will remind installment classmates on time pay in installment fees in advance, to prevent late class without permission.
Huahua: Isn’t that so easy?
Star: not easy, first of all the students are not a day to come, but every day different classes will have different students, so it is necessary to sort out. Second: each student’s payment information is different, some are installment, some are full payment, some are wechat account to buy courses, so it is really difficult to check the qq in the group with these tuition information!
Hua hua: That’s easy. I’ll help you out (of course it’s impossible). After the meeting, I found the head of the Python Team: Big Jane. Xiao Jane, the head teacher team has recently encountered a tedious problem, that is, it takes time to collect students’ information. Can you help to automate it?
Jane: (short for Big Jane) Sure, what do you need? Keyou and Yuze in our team can help do this. You can finish the requirements and we will deliver them within one week at the latest. After all, we still have tasks of automated testing, test development and open classes. With strong support from the Python Skyteam, you’re ready to go!
PART 1: Organizing requirements
Thus: Hua Hua united with Xing Xing to guide the work flow and data sources of the head teacher and sort out a demand. (Dear VIP students, your service comes from the headteacher’s hard work!)
Student information statistics:
Contains some of the information shown below
Among them, QQ comes from QQ group, wechat orders come from the CRM system of Lemon Class, and the rest information comes from the information management background of lemon class students (students fill in the form provided by Lemon class by themselves). After several rounds of analysis, it is confirmed that the data comes from these three different places.
About student payment information statistics :(this relates to whether students have the right to attend class, whether they will miss the class time, very important!
However, there are two problems with this information: 1) If students sign up through QQ, they can directly collect information according to QQ.
2) If the student is registered on wechat, then it is necessary to find the corresponding wechat in CRM system according to the QQ of joining the class group, and then make a match on QQ and wechat before going to Tencent class to query, for example, the QQ of Python17 group students is: 1248***816(to protect students’ privacy, including the middle three digits) is registered through wechat, so the wechat account in the CRM system is ningMengpyn. We can only query the payment information through this wechat account in the background of Tencent class
3) If students are installments, they should also click on the details page of installments to check the payment status of installments and register the payment time of subsequent installments, so as to remind students regularly to avoid overdue. For example, Python17 a student’s QQ is: 792***306, then query the instalments order
We need to click “Instalment Details” to enter the instalment details page to see the specific payment information of students and the final payment time of each instalment
PART 2: Writing scripts
After finishing the requirements, the picture and text to Jane guy, of course, part1 is a simple description, try to let everyone can see the point, more complex will not write out! Jane guy began to distribute tasks: can excellent big guy is responsible for student payment information statistical script, rain ze big guy is responsible for student information statistical script, as for the specific implementation process, of course, to share with you! Hua Hua will do the writing, because hua Hua will act as the teacher in charge and the bridge and requirements coordinator for Python Team in this process.
Implementation of the script for student information statistics:
1. Firstly, export the Q information of students from the QQ group of the corresponding class
2. Then export the order information of students from the CRM system of lemon Class
3: The two parts of information are saved in the XLSX file, and then the Python OpenPyXL third-party library is used to compare the two pieces of data, and the student’s wechat information is matched with QQ. Finally, the first step is as follows:
4. Then, according to the QQ information of the students, use Python’s python-mysql-connector third-party library to conduct database query, and obtain the detailed information of the students.
Script words ~~ screenshots also small look, part of the code, not complete oh! After learning The Lemon Class in Python Automation, you can too!
The script implementation method of student payment information statistics:
1: Use the results of student information statistics to query, if QQ can not be queried, use QQ corresponding micro signal to query
2: If it is full payment, the information will be directly registered; if it is installment, the installment link will be registered, and the detailed information of installment will be counted and registered separately in the later stage.
3: The above two steps are very clear, the implementation method is nothing more than the location of web page elements, there are many ways, xpath CSS or ID, but this script, there are two highlights to share with you, I hope you can use similar methods to deal with this problem in the future.
Highlight 1: fixed query conditions before the query
There are two explicit conditions that need to be fixed, one is the time, we only query the order from January 1, 2019, so here is the design to the calendar fixed page HTML source
We can do it in a better way
Another condition that needs to be fixed is: course package name, this is a non-selected control, after observation is to support fuzzy query, and need to select the corresponding drop-down elements from the results of fuzzy query can be effective! Page HTML source code
The steps we can achieve are as follows: first input the course conditions of the query (try to determine the only element, and then select the first element of the drop-down box, and the 0 element shows “” all”), for example, we input: Python Full Stack Automation Test Engineer Issue 7-19, then the drop down box will show you the name of the course package you can select
We select the first element of the drop-down list in code, and the magic happens! There’s a hidden element behind it, class!
The next step is to select the class, but our query class drop-down box is also unselected, and does not support input and can only be selected from the dropdown element. The HTML source of the page is as follows:
The code we implemented looks like this:
So far the query problem has been all implemented, in addition to here a little waste of time, the rest are very simple!
Highlight two: the use of regular matching staging data
To learn automation, you need to learn not only Python programming skills but also other knowledge, such as re! And it’s all in the lemon class automated instruction! Here intercept part of the code, only for reference!
This process, from conception to implementation, is realized on weekdays, May 1st, the project officially launched! And already deployed to Jenkins on the homeroom teacher’s computer! This can be timed to run the task! Homeroom teachers can also start arming themselves with Python! In these days, if you don’t learn some automation, you are embarrassed to say that you are the head teacher of your Python class.
If you’ve noticed that your homeroom teachers have become more beautiful and in a better mood recently, and are full of energy when you talk to them every day, it must be because Python has given them less work to do and more time to put on makeup and rest!
Interested students can pay more attention to the Lemon Class Python automation course, you can find professional Python automation instructor to receive professional learning materials! The Lemon class has been waiting for you for a long time and has prepared a wave of automated testing secrets to help you break through your salary skill bottleneck!
Including Jmeter, Python programming, Selenium, UNITTest +Http technology + DDT; Appium Automation; There are also automated interview questions and project case sharing. Interested can + I, free to send!