python连接MySQL数据库并访问数据

       这是用python在本地做的数据库连接操作，源代码如下：

# -*- coding: utf-8 -*-
"""
Created on Fri Jul 19 09:23:19 2019

@author: sunwf1114
"""

from tensorflow import keras
import numpy as np  # 用于转换数据
import pandas as pd  # 用于分析数据集
import time
import pymysql

global conn, cur
conn = pymysql.Connect(host="127.0.0.1", port=3306, user="***", passwd="****", db="energy",
                       charset='utf8',
                       cursorclass=pymysql.cursors.DictCursor)  # ,cursorclass = MySQLdb.cursors.DictCursor
cur = conn.cursor()

powerResult = {}
index = ['lengji1', 'lengji2', 'lengji3', 'lengji4']

for i in index:
    powerResult[i] = 0


class model_pre():
    def load_model1(self):  # 加载模型
        with open(r'./lengjiPower/1/model/model.json', 'r') as file:
            model_json1 = file.read()
        model = keras.models.model_from_json(model_json1)  # 返回模型结构
        model.load_weights("./lengjiPower/1/model/model.hdf5", by_name=False)  # 加载模型的权重
        return model

    # 加载基本数据进行归一化
    def read_min_max(self):
        filename = "./lengjiPower/1/model/data.txt"
        with open(filename, 'r+', encoding='utf-8') as f:
            for lines in f.readlines():
                ss = lines.strip('[]').split(', ')
        ds = np.asarray(ss)
        li_max = []
        li_min = []
        for i in range(0, len(ds), 2):  # 分成最大最小分别存储
            li_max.append(ds[i])
            li_min.append(ds[i + 1])
        nu_max = np.asarray(li_max)  # 转换为数组
        nu_min = np.asarray(li_min)
        return nu_max, nu_min

    def check1(self):
        while True:
            self.data_pre()
            # print("depty")
            time.sleep(2)

    def load_model2(self):  # 加载模型
        with open(r'./lengjiPower/2/model/model.json', 'r') as file:
            model_json1 = file.read()
        model = keras.models.model_from_json(model_json1)  # 返回模型结构
        model.load_weights("./lengjiPower/2/model/model.hdf5", by_name=False)  # 加载模型的权重
        return model

    def check2(self):
        while True:
            self.data_pre()
            # print("depty")
            time.sleep(2)

    def load_model3(self):  # 加载模型
        with open(r'./lengjiPower/3/model/model.json', 'r') as file:
            model_json1 = file.read()
        model = keras.models.model_from_json(model_json1)  # 返回模型结构
        model.load_weights("./lengjiPower/3/model/model.hdf5", by_name=False)  # 加载模型的权重
        return model

    def check3(self):
        while True:
            self.data_pre()
            # print("depty")
            time.sleep(2)

    def load_model4(self):  # 加载模型
        with open(r'./lengjiPower/4/model/model.json', 'r') as file:
            model_json1 = file.read()
        model = keras.models.model_from_json(model_json1)  # 返回模型结构
        model.load_weights("./lengjiPower/4/model/model.hdf5", by_name=False)  # 加载模型的权重
        return model

    def check4(self):
        while True:
            self.data_pre()
            # print("depty")
            time.sleep(2)

    # 参数是一个列表，每一个元素代表一组数据：[冷冻水回水温度,冷冻水出水温度,冷却水回水温度,冷水机组蒸发侧压力（kg）,冷水机组冷凝侧压力（kg）,冷冻水流量,输入功率]
    def data_pre_lengji1gonglv(self):
        global cur, conn

        # y_max = 439.0 #根据原始数据最大值做为基准
        # y_min = 345.0
        # read = pd.read_excel('data_input.xlsx')
        # ts = read.iloc[:,0:read.columns.size].values #读取前4列，分别是流量、进水温度、出水温度、COP

        ###############lengji1Gonglv######################
        try:
            conn.ping()
        except pymysql.OperationalError:
            conn = pymysql.Connect(host="127.0.0.1", port=3306, user="**", passwd="***", db="energy",
                                   charset='utf8',
                                   cursorclass=MySQLdb.cursors.DictCursor)  # ,cursorclass = MySQLdb.cursors.DictCursor
            cur = conn.cursor()
        sql_select = 'Select * from lengji1_power order by id desc limit 1'
        cur.execute(sql_select)
        conn.commit()
        results = cur.fetchall()
        listSequence = []
        for i in results[0]:
            if i != 'power' and i != 'id':
                listSequence.append(results[0][i])
        ts = []
        ts.append(listSequence)
        ds = np.asarray(ts)  # ts转换为数组矩阵
        # wnd_sz = read.columns.size  #定义矩阵宽度

        wnd_sz = len(results[0]) - 2
        print(ts, wnd_sz)
        x_1 = ds[:, 0:wnd_sz]  # 输出前n - 1维的特征

        max, min = self.read_min_max()  # 加载数据中的最大最小值用于还原归一化
        x_nomal = np.full((len(x_1), wnd_sz - 1), -1000., dtype=float)  # 创建一个空数组,用来存储float类型的归一值
        for i in range(0, len(x_1)):
            for j in range(0, len(x_1[0])):
                x = x_1[i]
                new = x_nomal[i]
                up = float(x[j]) - float(min[j])
                down = float(max[j]) - float(min[j])
                new[j] = up / down  # 归一化

        #x_nomal = x_1  # MinMaxScaler().fit_transform(x_1) #归一化
        model = self.load_model1()  # 加载模型
        y = model.predict(x_nomal)  # 预测加载值
        # y = y #y*(y_max - y_min) + y_min
        # y = np.asarray(y)
        # if os.path.exists('data_out.txt') != True:

        # path_txt = "data_out.txt"
        # file = open(path_txt, 'w')
        # file.write(str(y))

        powerResult['lengji1'] = int(y)

        ###############lengji2Gonglv######################

        try:
            conn.ping()
        except pymysql.OperationalError:
            conn = pymysql.Connect(host="127.0.0.1", port=3306, user="***", passwd="****", db="energy",
                                   charset='utf8',
                                   cursorclass=MySQLdb.cursors.DictCursor)  # ,cursorclass = MySQLdb.cursors.DictCursor
            cur = conn.cursor()
        sql_select = 'Select * from lengji2_power order by id desc limit 1'
        cur.execute(sql_select)
        conn.commit()
        results = cur.fetchall()
        listSequence = []
        for i in results[0]:
            if i != 'power' and i != 'id':
                listSequence.append(results[0][i])
        ts = []
        ts.append(listSequence)
        ds = np.asarray(ts)  # ts转换为数组矩阵
        # wnd_sz = read.columns.size  #定义矩阵宽度

        wnd_sz = len(results[0]) - 2
        print(ts, wnd_sz)
        x_1 = ds[:, 0:wnd_sz]  # 输出前n - 1维的特征
        x_nomal = x_1  # MinMaxScaler().fit_transform(x_1) #归一化
        model = self.load_model2()  # 加载模型
        y = model.predict(x_nomal)  # 预测加载值
        # y = y #y*(y_max - y_min) + y_min
        # y = np.asarray(y)
        # if os.path.exists('data_out.txt') != True:

        # path_txt = "data_out.txt"
        # file = open(path_txt, 'w')
        # file.write(str(y))

        powerResult['lengji2'] = int(y)

    def dataWrite_lengji1gonglv(self):
        global conn, cur
        tablename = 'powerdata'

        try:
            conn.ping()
        except pymysql.OperationalError:
            conn = pymysql.Connect(host="127.0.0.1", port=3306, user="****", passwd="****", db="energy",
                                   charset='utf8')  # ,cursorclass = MySQLdb.cursors.DictCursor
            cur = conn.cursor()

        sql_delete = 'DELETE FROM powerdata WHERE id = 0'
        cur.execute(sql_delete)
        conn.commit()

        sql = "INSERT INTO %s" % tablename + "(id,lengji1)\
            VALUES (%s,%s)"

        param = (0, powerResult['lengji1'])

        cur.execute(sql, param)
        conn.commit()


if __name__ == '__main__':
    # print("hello world")
    back = model_pre()
    print('System Started!')
    while True:
        time.sleep(2)
        back.data_pre_lengji1gonglv()
        back.dataWrite_lengji1gonglv()