山东大学创新实训2020/6/29

数据库给的晚，这里创建4个索引

同时logstash实例开启创建管道，同步更新mysql的数据到ES服务中去。

这是mysql的代码，实现了重mysql获得数据访问

import time
import pymysql
import csv

def get_time():
    time_str =  time.strftime("%Y{}%m{}%d{} %X")
    return time_str.format("年","月","日")

def get_conn():
    conn = pymysql.connect(host="47.98.****",
                           # port=3306,
                           user="test",
                           password="******",
                           db="innotrain",
                           charset="utf8mb4")
    # 创建游标
    cursor = conn.cursor()# 执行完毕返回的结果集默认以元组显示
    return conn, cursor

def close_conn(conn, cursor):
    cursor.close()
    conn.close()

def query(sql,*args):

    conn, cursor = get_conn()
    cursor.execute(sql)
    # cursor.execute(sql,args)
    res = cursor.fetchall()
    close_conn(conn, cursor)
    return res



# def get_toutiao(topic):
#     sql = "select title,ds,content,topic from toutiao " \
#           "where topic =%s order by ds"%topic
#     res = query(sql)
#     return res

def get_zhihu_a(topic):
    sql = "select question_name,answer_content,voteup_count," \
          "comment_count,created_time,updated_time,topic" \
          " from zhihu_a where topic =%s order by updated_time"%topic
    res = query(sql)
    return res

def get_zhihu_q(topic):
    sql = "select question,description," \
          "follower_count,comment_count,answer_count," \
          "visits_count,updated_time,topic from zhihu_ques " \
          "where topic =%s order by topic"%topic
    res = query(sql)
    return res

def get_emotion_val(topic):
    sql = "select ds,emotion_val,topic,predict from emotion_val where topic =%s order by predict"%topic
    res = query(sql)
    return res

def get_rawdata(topic):
    sql = "select context,ds,topic from raw_data where topic =%s "%topic
    res = query(sql)
    return res

def get_predict(plat):
    sql = "select ds,emotion_val,topic, predict from emotion_val " \
          "where predict=1 and topic =%s order by ds"%plat
    res = query(sql)
    return res

def get_midrawdata(topic):
    sql = "select ds,emotion_val,topic,predict from emotion_val " \
          "where predict=0 and topic =%s order by ds"%topic
    res = query(sql)
    return res


# def get_l1_data():
#
# 	sql = "select ds,confirm,suspect,heal,dead from history"
# 	res = query(sql)
# 	return res
#
# def get_l2_data():
#
# 	sql = "select ds,confirm_add,suspect_add from history"
# 	res = query(sql)
# 	return res
#将CSV文件处理为dict在转换为json格式数据
# def dataprocess(file):

这是Elasticsearch提供的接口实现了远程访问elasticsearch数据

import time
from elasticsearch import Elasticsearch, RequestsHttpConnection
import time



def get_time():
    time_str =  time.strftime("%Y{}%m{}%d{} %X")
    return time_str.format("年","月","日")

def get_conn():
    """
    :return: 连接，游标
    """
    # 创建连接
    es = Elasticsearch(
        ['es-cn-st21p5hmc0****.public.elasticsearch.aliyuncs.com'],
        http_auth=('elastic', '*****'),
        port=9200,
        use_ssl=False
    )
    return es

def close_conn(conn, cursor):
    pass


def get_zhihu_a(topic):
    es = get_conn()
    action = {"query": {
        "term": {
            "topic": topic,
        }
    },
        "sort": [
            {
                "updated_time": {
                    "order": "asc"
                }
            }
        ],
        "size": 10000
    }

    res = es.search(index="zhihu_a1", body=action)
    print(res)
    return res

def get_zhihu_q(topic):
    es = get_conn()
    action = {"query": {
        "match": {
            "topic": topic
        }
    },
        "sort": [
            {
                "topic": {
                    "order": "asc"
                }
            }
        ],
        "size": 10000
    }

    res = es.search(index="zhihu_ques1", body=action)
    print(res)
    return res

def get_emotion_val(topic):
    es = get_conn()
    action = {"query": {
        "match": {
            "topic": topic
        }
    },
        "sort": [
            {
                "predict": {
                    "order": "asc"
                }
            }
        ],
        "size": 10000
    }

    res = es.search(index="emotion_val1", body=action)
    print(res)
    return res

def get_rawdata(topic):
    es = get_conn()
    action = {"query": {
        "match": {
            "topic": topic
        }
    },
        "size": 10000
    }

    res = es.search(index="raw_data1", body=action)
    print(res)
    return res

def get_midrawdata(topic):
    es = get_conn()
    action = {"query": {
        "bool": {
            "must": [
                {"match": {"predict": "0"}},
                {"match": {"topic": topic}}
            ],

        }
    },
        "sort": [
            {
                "ds": {
                    "order": "asc"
                }
            }
        ],
        "size": 10000
    }

    res = es.search(index="emotion_val1", body=action)
    print(res)
    return res


def get_predict(topic):
    es = get_conn()

    action = {"query": {
        "bool": {
            "must": [
                {"match": {"predict": "1"}},
                {"match": {"topic":topic}}
            ],

    }
    },
        "sort": [
            {
                "ds": {
                    "order": "asc"
                }
            }
        ],
        "size": 10000
    }

    res = es.search(index="emotion_val1", body=action)
    print(res)
    return res
if __name__ == "__main__":
    get_midrawdata("trump")

下面是通过es接口访问获取的数据

实现了ES的数据存储和查询服务。