data_analyze_platform/USER/SPF/alibaba/Common/DBDownload.py

import pymysql
import time
import pandas as pd

class DBDownload:

    def __init__(self, host='', port='', db='', user='', password='', mode=''):
        self.host = host
        self.port = port
        self.db = db
        self.user = user
        self.password = password
        self.mode=mode
        pass

    def __enter__(self):
        self.connect()
        return self

    def __exit__(self, exc_type, exc_val, exc_tb):
        self.close()

    def connect(self):
        conn_success_flag = 0
        while not conn_success_flag:
            try:
                self.conn = pymysql.connect(host=self.host, port=self.port, user=self.user, password=self.password, database=self.db)
            except Exception as e:
                conn_success_flag = 0
                print("数据库连接失败 :{}".format(e))
                time.sleep(5)
            else:
                conn_success_flag = 1
                print('数据库连接成功！')
                self.cursor = self.conn.cursor()

    def getdata(self,param,tablename,sn,timename,st,sp):
        print('数据获取中......')
        if self.mode==1:
            self.cursor.execute("select %s from %s where sn='%s' order by id desc limit 1" %(param,tablename,sn))
        else:
            self.cursor.execute("select %s from %s where sn='%s' and %s between '%s' and '%s'" %(param,tablename,sn,timename,st,sp))
        res = self.cursor.fetchall()
        df_res = pd.DataFrame(res, columns=param.split(','))
        return(df_res)

    def close(self):
        try:
            self.cursor.close()
            self.conn.close()
        except Exception as e:
            print(e)
        else:
            print('数据库已断开连接！')