'''
遇到問題沒人解答？小編創建了一個Python學習交流QQ群：857662006 
尋找有志同道合的小伙伴，互幫互助,群里還有不錯的視頻學習教程和PDF電子書！
'''
#!/usr/bin/env python3
# -*- coding: UTF-8 -*-

import pymysql
import random
import time

# 初始化mysql 連接池
def MySQLInit(mysql_host,mysql_port,mysql_user,mysql_password,mysql_charset,mysql_dbname,mysql_maxconnect):

  # 定義返回mysql連接池的類型，這里定義為 list   
  return_db_list = []

  for Connect in range(0,mysql_maxconnect):
    try:
      db = pymysql.connect(
        host = mysql_host , 
        port = mysql_port , 
        user = mysql_user , 
        passwd = mysql_password ,
        db = mysql_dbname ,
        charset = mysql_charset 
      )

      print ("連接MySQL數據庫成功，MySQL數據庫ID為:%s" %(db))
      return_db_list.append(db)
    except Exception as e:
      print ("連接MySQL數據庫出錯")
      print (e)

  # 如果mysql連接池為空，則返回-1
  if 0 == len(return_db_list):
    print ("錯誤 , 連接MySQL數據庫失敗,連接池為0")
    return -1

  else:
    # 返回mysql連接池
    return return_db_list

def Pull_DB_Connect(mysql_host,mysql_port,mysql_user,mysql_password,mysql_charset,mysql_dbname , MySQL_Connect_Pool , mysql_maxconnect) :
  
  # 打印補充前的mysql連接數
  print ("補充前MySQL連接數為:",len(MySQL_Connect_Pool))
  
  # 循環相差的連接數
  for Connect in range((len(MySQL_Connect_Pool)) , mysql_maxconnect):
    try:
      db = pymysql.connect(
        host = mysql_host , 
        port = mysql_port , 
        user = mysql_user , 
        passwd = mysql_password ,
        db = mysql_dbname ,
        charset = mysql_charset 
      )

      print ("重新補充，連接MySQL數據庫成功，MySQL數據庫ID為:%s" %(db))
      # 將剛剛連接成功的DB連接追加到MySQL_Connect_Pool中
      MySQL_Connect_Pool.append(db)
    except Exception as e:
      print ("重新補充，連接MySQL數據庫出錯")
      print (e)

  if 0 == len(MySQL_Connect_Pool):
    print ("錯誤 , 連接MySQL數據庫失敗,連接池為0")
    return -1

  else:
    # 打印連接池總數并且返回
    print("Mysql 連接池總數為:" , len(MySQL_Connect_Pool))
    return MySQL_Connect_Pool 

def main() :

  # 定義測試mysql信息
  mysql_host = "192.168.1.100"
  mysql_port = 3306
  mysql_user = "liwang" 
  mysql_password = "liwang"
  mysql_charset = "utf8"
  mysql_dbname = "itchat"
  mysql_maxconnect = 3

  # 初始化mysql連接池
  MySQL_Connect_Pool = MySQLInit(mysql_host,mysql_port,mysql_user,mysql_password,mysql_charset,mysql_dbname,mysql_maxconnect)

  # 定義初始化sql 
  sql = "show databases;"

  
  while True:
    # 隨機獲連接池的下標
    db_link = random.randint(0,len(MySQL_Connect_Pool)-1)
    try:
      # 執行sql 
      cursor = MySQL_Connect_Pool[db_link].cursor()
      cursor.execute(sql)
      print (cursor.fetchall())
    except Exception as e:
      # 如果執行失敗，模擬為DB連接失效，則打印錯誤信息，刪除錯誤的MySQL_Connect_Pool信息
      print (e)
      del MySQL_Connect_Pool[db_link]
      print ("數據庫無法連接，刪除Mysql連接池下標:" , db_link)
      # 重新補充mysql連接池
      Pull_DB_Connect(mysql_host,mysql_port,mysql_user,mysql_password,mysql_charset,mysql_dbname,MySQL_Connect_Pool,mysql_maxconnect)
    
    # 休眠10s 
    time.sleep(10)

if __name__ == "__main__" :
  main()