flask安装，连接influxdb

1. 启动influxdb

因为项目里有用到influxdb,可以先把influxdb数据库安装好，这样也方便flask连接测试:   InfluxDB根据配置文件启动服务

2. flask安装

flask安装文档参考官网：
安装文档

依次输入以下5个命令：

3. 创建并启动flask项目

3.1在 myproject 目录下面创建一个项目analysis以及项目初始文件__init__.py文件

import os
import json
from flask import Flask
from flask import request

def create_app(test_config=None):
    # create and configure the app
    app = Flask(__name__, instance_relative_config=True)
    app.config.from_mapping(
        SECRET_KEY='dev',
        #DATABASE=os.path.join(app.instance_path, 'flaskr.sqlite'),
    )

    if test_config is None:
        # load the instance config, if it exists, when not testing
        app.config.from_pyfile('config.py', silent=True)
    else:
        # load the test config if passed in
        app.config.from_mapping(test_config)

    # ensure the instance folder exists
    try:
        os.makedirs(app.instance_path)
    except OSError:
        pass

    # a simple page that says hello
    @app.route('/hello')
    def hello():
        return 'Hello, World!'

    return app

3.2 flask run

依次执行以下三条命令：

> set FLASK_APP=analysis
> set FLASK_ENV=development
> flask run --host=0.0.0.0

其中如果自己项目里有一些依赖包没下，可以通过 pip install安装，
运行成功：

4. 数据读取和保存

4.1 读取excel数据源

insertalarm.py 文件：

import xlrd
import os
import requests, json
import sys

def getIDstr(message):
    if message.find("_") <= 0:
        return ""
    infos = message.split('_')
    if infos[1].find("("):
        datas = infos[1].split('(')
        return datas[0]
    return infos[1]


# Press the green button in the gutter to run the script.
if __name__ == '__main__':
    print(len(sys.argv))
    if len(sys.argv) != 2:
        print("usage: python insertalarm.py <filepath>")
        exit(0)
    excel_path = os.path.join(os.getcwd(), sys.argv[1])  # Excel文件路径
        
    #excel_path = os.path.join(os.getcwd(), 'C:/work/source/python/test/excel9_14.xls')  # Excel文件路径
    print('Excel文件的路径：' + excel_path)
    excel_file = xlrd.open_workbook(excel_path)  # 打开Excel文件
    table = excel_file.sheets()[0]  # 通过索引打开
    print('已经打开的工作簿的名字：' + table.name)
    print('**********开始读取Excel单元格的内容**********')

    post_url = "http://127.0.0.1:5000/event"   # 请求地址
    for i in range(table.nrows):
        row_content = []
        if i==0:
            continue
        for j in range(table.ncols):
            inner_cell_value = table.cell(i, j).value  # 获取单元格内容
            row_content.append(inner_cell_value)  # 追加当前行的列数据
        
        data = json.dumps(row_content)   # json.dumps将一个Python数据结构转换为JSON
        #print(data)
        r = requests.post(post_url, data)  # 发起请求

        #print(r.text)
        message = json.loads(r.text).get("message")
        #print(message)
        #message = json.load(r.text).get('message')
        if message == "The data has been inserted into the influxdb":
            print("The event has been inserted into database before, interrupt the operation " + row_content[0])
            break
      

4.2 数据处理

4.2.1 路由

下面处理/event请求，由于处理方法放在了不同文件里面，需要引入一下：

from . import alarms

4.2.2 业务层处理

alarms.py文件：

from flask import Flask, Response
from . import db
app = Flask(__name__)


def post_event(alarm):
    data = {} 
    duration = 0.0
    if len(alarm[4]) > 0:
        duration = float(alarm[4])
    #app.logger.info(alarm)   
    data.update(key1 = alarm[3])  #  update() 函数把字典 dict 的键/值对更新到 dict 里
	# .......
    data.update(key2 = alarm[16])  # 
    data.update(key3 = alarm[0])  # 
    data.update(key4 = alarm[1])  # 
    data.update(key5 = duration)  # 
    #data['operate_duration'] = duration,  # 
    
    #app.logger.info(data)
    return db.insert_alarm_todb(data)  # 对数据库操作的方法调用

4.2.3 influxdb对数据库操作

from flask import Flask, Response
import random
import re
import json
import xlrd
import os
from datetime import datetime
from influxdb import InfluxDBClient

app = Flask(__name__)
databasename='db_test'

def insert_alarm_todb(alarm):

    out_cell_value = alarm["系统类型"]
    create_order_value = alarm["字段2"]
    result = checkAlertItems(alarm)
    if result:
        resutl = {'result':'failed','message':'The data has been  inserted into influxdb'}
        return Response(json.dumps(resutl),mimetype="appliction/json") 
    level = 5

    if out_cell_value == "系统类型1":
        level = insert1(alarm)
        pass
    elif out_cell_value == '系统类型2':
        level = insert2(alarm)
        pass
    else:
        pass
    
    insertAlertItems(alarm, level)       
    resutl = {'result':'succeed','message':'The data is inserted to influxdb'}
    return Response(json.dumps(resutl),mimetype="appliction/json")
    pass # function end here     



# 
def insert1(alarm):
    client = InfluxDBClient(host='127.0.0.1', port=8086, database=databasename)

    sum = random.randint(0, 999999)
    create_time = tools.getUTCstr(alarm["happen_time"] + "." + str(sum).zfill(6))  
    
    value_dict = {0: '', 1: '', 2: '', 3: ''}
    # 其他处理

    # print(value_dict)

    json_body = [
        {
            "measurement": "table1",
            "tags": {
                "tag1": "value1",  
                "tag2": "value2",   
                "tag3": "value3",   
                "tag4": "value4", 
              
            },
            "time": create_time,  # 时间
            "fields": {
                "field1": "field1_value",  
                "field2": "field2_value",  
                "field3": "field3_value",  

            }
        }
    ]

    res = client.write_points(json_body)  # 写入数据，同时创建表
    if res != True:
        print(json_body)

        
    alarm.update(time = create_time)
    # alarm.update(event = value_dict[1])

    # return analysis.checkLevel(alarm,client)  可以在这里判断这个event的告警级别，然后返回
    return 1  # 这里就暂时先返回个固定的值
 


#检查数据库是否已经存在这条数据
def checkAlertItems(alarm):
    
    # 连接 influxDB数据库
    client = InfluxDBClient(host='127.0.0.1', port=8086, database=databasename)
    query = "select count(event_title) from t_alertevents where order_number='" + alarm["order_number"] + "'"
    #print(query)
    result = client.query(query)
    #print(result)
    for item in result:
        print(item[0])
        if item[0]['count'] > 0:
            return True
    return False
    pass

4.2.4工具方法抽取

tools.py文件共用方法

from flask import Flask
from datetime import datetime
import pytz

app = Flask(__name__)


def getIDstr(message):
    if message.find("_") <= 0:
        return ""
    infos = message.split('_')
    if infos[1].find("("):
        datas = infos[1].split('(')
        return datas[0]
    return infos[1]

def getUTCstr(create_time):
    local_tz = pytz.timezone('Asia/ShangHai')
    local_format = "%Y-%m-%d %H:%M:%S.%f"
    utc_format = "%Y-%m-%dT%H:%M:%S.%fZ"
    dt = datetime.strptime(create_time, local_format)
    local_dt = local_tz.localize(dt, is_dst=None)
    utc_dt = local_dt.astimezone(pytz.utc)
    return utc_dt.strftime(utc_format)

    

# 找字符串substr在str中第time次出现的位置
def findSubStrIndex(substr, str, time):
    times = str.count(substr)
    if (times == 0) or (times < time):
        pass
    else:
        i = 0
        index = -1
        while i < time:
            index = str.find(substr, index+1)
            i+=1
        return index

4.3 influxdb查询

4.3.1 定义路由

4.3.2 业务方法调用

4.3.3 数据库查询

analysis.py文件

def getAlertList(size, page):
    
    if size <10 or size >100:
        size = 30
    if page < 1 :
        page = 1
    sum = getAlertSum()
    client = InfluxDBClient(host='127.0.0.1', port=8086, database=databasename)
    offset  = (page-1) * size
    query = "select * from t_alertevents order by time desc limit " + str(size) + " offset " + str(offset)
  
    result = client.query(query)
    data = []
    for item in result:
        for j in range(len(item)):
            data.append(item[j])

    #print(data)
    retdata = {
        'total':sum,
        'page':page,
        'size':size,
        'data':data
    }

    return json.dumps(retdata)