Recently, the company has used Python to connect database runs and automatically send e-mail reports on a regular basis, so we recently spent some time on this piece.
For automatic mail sending, please refer to the previous articles [dry goods] send monitoring emails regularly every day in Python , this article will share with you how to use Python to automatically connect database runs.
Contents of this article
- Effect display
- Code explanation 2.1 import and stock in 2.2 connecting to database 2.3 executing sql 2.4 data processing
1, Effect display
Before officially entering the code explanation, let's take a look at the implementation effect of automatically sending e-mail regularly.
Since some data involve company information, we have done numerical processing and coding out of professional ethics. Please forgive me.
2, Code explanation
This section will explain in detail how to use python to realize the number of runs connected to the database. The main ideas are:
Step 1: connect to the database and create a cursor.
Step 2: determine the running number script and execute the running number code.
Step 3: data processing for the obtained execution results.
1. Import and stock in
First, import the libraries to be loaded in this article. If some libraries have not been installed, resulting in errors in running code, you can install them in Anaconda Prompt with pip method.
import datetime import pymysql import numpy as py import pandas as pd
datetime is the time processing package, pymysql is the package connecting to the database, and numpy and pandas are the data processing packages.
2 connect to the database
After importing the library, execute the following code to connect to the database.
db = pymysql.connect(host='10.101.2.36', #Host address to connect user='selxxx', #Database user for login password='selxxx@123', #User password database='xxx_loan', #Database to connect to port=3306, #Connected port charset='', #Character encoding use_unicode=None, #Use unicode encoding connect_timeout=10000 #Connection timeout ) cursor = db.cursor() #Create a cursor
The corresponding contents of user, password and database need to be replaced with the database information you want to connect to.
3 execute sql
Then, execute the sql statement to get the data results.
#Version 1: write sql directly in the program sql_recent_5d_sx = ''' select appl_date, count( distinct case when u.appl_type=1 then loan_appl_no else null end ) as `Application volume`, count( distinct case when u.appl_type=1 and OUT_TEMPDECISION = 'PASS' then loan_appl_no else null end ) as `System automatic throughput` from table ''' #sql script cursor.execute(sql_recent_5d_sx) #Execute sql statement sql_result = cursor.fetchall() #Fetch all rows returned by the operation #Version 2: save sql to txt document sql_path_recent_5d_sx = r'C:\Users\xzy\recent_5day_sx.txt' #Mark sql storage path sql_recent_5d_sx = open(sql_path_recent_5d_sx, encoding='utf-8').read() #Open the sql script and read the sql cursor.execute(sql_recent_5d_sx) #Execute sql statement sql_result = cursor.fetchall() #Fetch all rows returned by the operation
This article introduces two versions of executing sql. When your sql statement is not very long, you can use version 1 to write sql directly in the program.
When the sql statement is long, it is recommended to use version 2, which will make the python code clearer and cleaner.
Where sql_result stores the full amount of data obtained after executing the sql statement.
4 processing data
Finally, the full amount of data obtained is personalized according to the needs.
data_recent_5d_yx = pd.DataFrame(sql_result) #Convert sql results into data frames data_recent_5d_yx.columns = [ 'date', 'Number of letter applications', 'Number of letters passed', 'Letter passing rate', 'Credit amount', 'Proportion of white note payment', 'Proportion of cash withdrawal with white slip', 'Proportion of external use of white paper', 'Proportion of interest before capital', 'Proportion of equal principal and interest' ] #Add a column name to the data frame data_recent_5d_yx = data_recent_5d_yx.T #Turn the data frame around data_recent_5d_yx.columns = data_recent_5d_yx.iloc #Add row name to data frame data_recent_5d_yx = data_recent_5d_yx.iloc[1:] #Delete first row #data_recent_5d_yx for i in data_recent_5d_yx.columns: data_list_2.remove(i) for i in data_list_2: data_recent_5d_yx[i] = 0 data_recent_5d_yx_f = data_recent_5d_yx[data_list_0] #final result
The handling method of this step is changed as needed, and you can handle it by yourself.
So far, the data retrieval requirements for connecting to the database with Python have been shared, and small partners in need can implement them by themselves according to the code in this article.