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

1. Effect display
2. 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[0] #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.