data-verification/Monitor/yesterday_top_order.py

# -*- encoding=utf8 -*-

import time
import datetime
import pandas as pd
import pymysql
from DBUtils.PooledDB import PooledDB



#创建数据库连接池
def createPool(db_config):
   spool = PooledDB(pymysql, 5, **db_config) 
   return spool


#查询数据
def getData(sql,db):
  df = pd.read_sql(sql,con=db)
  db.close
  #print(df)
  return df

def saveData():

  timestamp = time.time()
  ins_sql = """ INSERT INTO balance_monitoring VALUES(0,%s,%s,1,%s)  """  
  msg = '昨日还在充值中的订单总数量为：{},手机号为：\n {}'
  try:
    #print(111)  
    ord_cursor = ord_conn.cursor()
    if(df.empty is False):
        #print(22)
        #print(df.values.tolist())
        total_num = df.shape[0]
        phone_list = []
        if total_num >10:
           phone_list =  df['used_mobile'].head(10).tolist()
        else:
           phone_list =  df['used_mobile'].values.tolist()
        #print(total_num)
        #print(phone_list)
        ord_msg = msg.format(total_num,phone_list)

        #print(ord_msg)
        ord_cursor.execute(ins_sql, (ord_msg,'超时订单监控群',int(timestamp))) 
  except:
      ord_conn.rollback()
      print('数据回滚')
  finally:
      ord_cursor.close
      ord_conn.close 

if __name__ == '__main__':

  #数据库连接信息
  chanel_db_config = {
    'host' : '47.95.217.180',
    'port' : 3306,
    'user' : 'root',
    'password' : '93DkChZMgZRyCbWh',
    'db' : 'fmp',
    'charset' : 'utf8',
    'autocommit' : 1
  }
  #监控消息数据库连接信息
  mon_db_config = {
          'host' : '127.0.0.1',
          'port' : 9001,
          'user' : 'root',
          'password' : 'nibuzhidaowozhidao',
          'db' : 'monitoring',
          'charset' : 'utf8',
          'autocommit' : 1
  }

  #数据库连接 
  conn = createPool(chanel_db_config).connection()
  ord_conn = createPool(mon_db_config).connection()


  #获取时间
  today = datetime.date.today()
  yesterday = today - datetime.timedelta(days=1)
  yesterdayStart = yesterday.strftime("%Y-%m-%d") + ' 00:00:00'
  yesterdayEnd = yesterday.strftime("%Y-%m-%d") + ' 23:59:59'  

  sql = """ SELECT  used_mobile,apply_date
            FROM 
                flow_order_info
            WHERE
                apply_date BETWEEN ' """ + yesterdayStart +""" '    AND  ' """+ yesterdayEnd + """ '
                AND   status NOT IN  (4,6) 
        """ 

  
  df = getData(sql,conn)
  #df = pd.DataFrame([['159','8.1']])
  saveData()