Shulou(Shulou.com)06/02 Report--

This article is about how to use Python to slack off at work, the editor thinks it is very practical, so I share it with you to learn. I hope you can get something after reading this article.

Some friends will have this confusion at work: why don't I get a high rating when I am busy from morning till night?

You know, the evaluation of an employee is based on "output" rather than "giving". Therefore, if you spend a lot of time on mechanically repetitive work, it is not only inefficient, but also not helpful to personal development.

For people who know how to program, these tasks can often be done quickly with a few lines of code.

So, I went to understand the repetitive work that different positions around me (HR, product, operation, market, data analyst, etc.) need to face every day (there will be imperfections, welcome to add), summed up some very common examples in my work, and sorted out the source code for reference. I hope these programs can make your work more efficient! Don't forget to come back and give out red envelopes when you get a promotion and a raise.

So how do you make all this happen?

I will divide these into the following categories. You can make your own assessment and take what you need:

System input automation

Because you often need to constantly enter some information into the system, you may need to click some buttons each time. In this case, you can write an automatic script to perform these clicks on your behalf each time.

We need to use splinter here:

Pip install splinter

Here is a script that automatically logs in to the mailbox, which can achieve text input and web page clicks:

# coding=utf-8 import time from splinter import Browser def splinter (url): browser = Browser () # login 126 email websize browser.visit (url) # wait web element loading time.sleep (5) # fill in account and password browser.find_by_id ('idInput'). Fill (' xxxxxx') browser.find_by_id ('pwdInput'). Fill (' xxxxx') # click the button of login browser.find_by_id ('loginBtn'). Click () time.sleep ( 8) # close the window of brower browser.quit () if _ _ name__ ='_ _ main__': websize = 'https://mail.163.com/' splinter (websize)

In the same way, you can write a simple game on-hook script, which is nothing more than automatically move the mouse, click automatically, and repeat the operation, so the first step is how to control the mouse.

Import win32api

Import time

Def move_click (x, y, twee0): # move the mouse and click the left button

Win32api.SetCursorPos ((x, y)) # sets the mouse position (x, y)

Win32api.mouse_event (win32con.MOUSEEVENTF_LEFTDOWN |

Win32con.MOUSEEVENTF_LEFTUP, x, y, 0,0) # click the left mouse button

If t = = 0:

Time.sleep (random.random () * 2o1) # sleep

Else:

Time.sleep (t)

Return 0

# Test

Move_click (30,30)

Def resolution (): # get screen resolution

Return win32api.GetSystemMetrics (0), win32api.GetSystemMetrics (1)

It is worth noting that it must be run in the cmd under administrator privileges, otherwise the click is invalid.

At this time, you can write a loop, constantly click on a few different points on the screen, the most basic hang-up script will be implemented.

Excel automatic processing

Excel merger

In practical application, there may be different months of data or different weeks of reports, etc., Excel data, are a single independent file, if you want to use it as a whole, you need to merge it, so how to use python to merge all the Excel data under the specified directory into one file?

Idea: use the python xlrd package to read the excle file, then store the file contents in a list, and then use xlsxwriter to write the contents to a new excel file.

#-*-coding: utf-8-*-# merge multiple Excel files into one import xlrd import xlsxwriter # get all the sheet tables in excel def getsheet (fh): return fh.sheets () # get the number of rows of the sheet table def getnrows (fh,sheet): table=fh.sheets () [sheet] return table.nrows # read the file contents and return the line contents def getFilect (file Shnum): fh=open_xls (file) table=fh.sheets () [shnum] num=table.nrows for row in range (num): rdata=table.row_values (row) datavalue.append (rdata) return datavalue

Or it can be implemented directly with a concat+ loop:

For i in var_list: df_0 = data [['var_1','var_2','var_3','var_4',i]] [data [I] =' Information'] df_0 ['month'] = date_replace (I) df_0 = df_0 [[' var_1','var_2','var_3','var_4'] 'var_5']] li.append (df_0) writer = pd.ExcelWriter (ritual C:\ Users\ mapping.xlsx') df = pd.concat (li) df.to_excel (writer,'Sheet1',index=False,header = None) df

Add data Chart in Excel after sorting out the excel file, the next step is to process the data in the file and generate some charts you need according to the data:

Import xlsxwriter # set up an example data = [20, 45, 26, 18, 45] # create table workbook = xlsxwriter.Workbook ("temp.xlsx") worksheet = workbook.add_worksheet ("data") # add data worksheet.write_column ('A1 records, data) # create chart chart = workbook.add_chart ({'type':' line'}) # Chart add data chart.add_series ({'values':' = dataplanes A1pura A6' 'name':' chart name', 'marker': {' type': 'circle',' size': 8, 'border': {' color': 'black'},' fill': {'color':' red'}}, 'data_labels': {' values': True}, 'trendline': {' type': 'polynomial',' order': 2, 'name':' trend line' 'forward': 0.5,' backward': 0.5, 'display_equation':True,' line': {'color':' red', 'width':1,' dash_type': 'long_dash'}) worksheet.insert_chart (' C1, chart) workbook.close ()

Achieve results:

Extraction of key information from word

Suppose you receive 10, 000 resumes and you want to do some screening according to the school. At this time, use python to summarize a large number of resumes, and it is more convenient to extract only the key information and use excel to view it.

The docx file itself is a compressed file. After opening the zip file, I found that there was a file specifically storing the text in the word. Then the steps become simple: 1. Open docx's compressed package 2. Get the text information in word 3. Use regular expressions to match the information we want. 4. Store the information in txt (txt can be opened with excel) 5. Batch call the above process to complete the extraction of 10,000 resumes and use regular matching to obtain key information:

Import re def get_field_value (text): value_list = [] m = re.findall (r "surname (. *?) Gender ", table) value_list.append (m) m = re.findall (r" gender (. *?) Learning calendar, table) value_list.append (m) m = re.findall (r) Minzu (. *?) Health status ", table) value_list.append (m) 'other field matches' return value_list''are omitted here

Automatic operation monitoring

In normal work, there must be operational monitoring. If you manage a store, then some key indicators must be checked every day, such as store visits, commodity views, orders, and so on. At this time, you do not have to count these data repeatedly every day, which requires you to write an automated program to save the data in a fixed folder every day to achieve real-time monitoring of the report.

If your data source is offline files:

Hongmeng official Strategic Cooperation to build HarmonyOS Technology Community

Use python to operate offline files and load them into the database

Process the data through the database

Using python to output the result

From impala.dbapi import connect from impala.util import as_pandas import datetime conn = connect (host='host',port=21050,auth_mechanism='PLAIN',user='user',password='password') # host: database domain name # user: database user name # password: database password df_data = pd.read_excel ('temp.xlsx') rows = [] for index Row in df_data.iterrows (): rows.append ('('+'"'+ str (row ['case_id']). Replace (' nan','null') +''+','+''+ str (row ['birth_date']) +''+'),') a =''INSERT into table (case_id) Birth_date) values''for i in rows: a + = I a = a [:-1] cursor1 = conn.cursor () cursor1.execute (a) cursor1.close () conn.close () print (' successfully imported data to the database.') Del a del rows

If your data source is an online file (stored in a database)

Hongmeng official Strategic Cooperation to build HarmonyOS Technology Community

Directly use python to link the database to do some column operations.

Export the results you need

Import sql # sql is an encapsulated sql file sql_end = sql.sql_end cursor1 = conn.cursor () for i in sql_end.split (';'): print (I) cursor1.execute (I) cursor1.close () conn.close () print ('program is finished, please proceed to the next step.')

Send mail automatically

Using Python to automate email delivery allows you to get rid of tedious and repetitive business and save a lot of time. Data analysts often encounter some data fetch requirements, some of which are needed on a daily basis and some on a weekly basis. For these periodic data requirements, it is tedious and time-consuming to manually export the data every time and send it back to the demand side. So you can set up automatic mail to solve the problem. "Talk is cheap, show you the code" common mail must have three parts: 1, body 2, picture 3, attachment OK import the package we need

From email.mime.text import MIMEText from email.mime.multipart import MIMEMultipart from email.mime.image import MIMEImage import smtplib msg = MIMEMultipart ()

Insert the body in the message:

# # inserting text message df_text=''' in email

Hi all

This is a test email. Please refer to the attachment for details.

The situation is as follows:

'' msgtext = MIMEText (df_text, 'html',' utf-8') msg.attach (msgtext)

If you need to insert a picture, use the same method to insert a picture in your email:

# # insert picture information image = open ('temp.jpg','rb') msgimage = MIMEImage (image.read ()) msg.attach (msgimage) in email

Insert an attachment in the message:

# # add attachment msgfile = MIMEText (open ('temp.xlsx',' rb'). Read (), 'base64',' utf-8') msgfile ["Content-Disposition"] = 'attachment; filename= "temp.xlsx"' msg.attach (msgfile)

All that's left is to set some mail parameters to send the email:

# set mail message constant email_host='# server address sender ='# sender password ='# password. If it is an authorization code, enter the authorization code receiver =''# recipient

To send an email:

Try: smtp = smtplib.SMTP (host=email_host) smtp.connect (email_host) smtp.starttls () smtp.login (sender, password) smtp.sendmail (sender, receiver.split (','), msg.as_string () smtp.quit () print ('sent successfully') except Exception: print ('send failed')

Then set your task to be executed regularly and it can be easily implemented.

Achieve results:

In my usual work, there are really too many tasks that can be automated. Due to the limitation of experience, I can't give examples one by one. I can only try to share some examples that I have encountered or heard of. I hope everyone will be more and more efficient and finish their work while stealing idleness.

The above is how to use Python to slack off at work. The editor believes that there are some knowledge points that we may see or use in our daily work. I hope you can learn more from this article. For more details, please follow the industry information channel.

Welcome to subscribe "Shulou Technology Information " to get latest news, interesting things and hot topics in the IT industry, and controls the hottest and latest Internet news, technology news and IT industry trends.