# coding:utf-8

# 1.導(dǎo)入模塊
# datatime模塊用于定義時(shí)間及時(shí)間的加減操作
# MySQLdb模塊用于Python2.0連接數(shù)據(jù)庫，Python3.0連接數(shù)據(jù)庫使用pymysql
# xlwt模塊是excel操作模塊，用于將數(shù)據(jù)寫入excel中

import datetime
import MySQLdb
import xlwt

# 2.連接數(shù)據(jù)庫，獲取數(shù)據(jù)
# MySQLdb.connect用于定義連接數(shù)據(jù)庫的屬性
# myconn.cursor()定義游標(biāo)對(duì)象
# query_sql定義查詢的語句
# mycursor.execute()執(zhí)行查詢語句，僅僅是執(zhí)行語句，不輸出結(jié)果。
# mycursor.fetchall()提取查詢數(shù)據(jù)。all全部數(shù)據(jù)，one單條數(shù)據(jù)，many取多少條數(shù)據(jù)。fetchmany(10)取10條數(shù)據(jù)。
# mycursor.close()關(guān)閉游標(biāo)
# myconn.close()關(guān)閉連接

myconn = MySQLdb.connect(host='1',user='wn',passwd='9eu',db='bs',charset='utf8')
mycursor = myconn.cursor()
query_sql = '''
select JOIN_TIME,LEAVE_TIME from commfee where JOIN_TIME between '2019-12-24 15:00:00' and '2019-12-24 15:30:00' 
'''

mycursor.execute(query_sql)
sql_result = mycursor.fetchall()
mycursor.close()
myconn.close()

# 3.定義全局參數(shù)
# sum1 = []定義列表sum1,sum1用于生成比較的時(shí)間列表
# sum2 = []定義列表sum2,sum2用于生成并發(fā)數(shù)的列表

sum1 = []
sum2 = []

# 4.定義數(shù)據(jù)篩選函數(shù)
# compare_time 比較時(shí)間，最開始值取開始時(shí)間的第一個(gè)值。
# start_time = [sql_result[i][0] for i in range(0,len(sql_result))]將查詢到的結(jié)果拆分為兩個(gè)列表start_time和end_time。
# compare_time < start_time[len(sql_result)-1]，compare_time時(shí)間和start_time列表中的時(shí)間比較
# compare_time += datetime.timedelta(seconds=1)，每次比較后，compare_time時(shí)間+1
# datetime.timedelta(seconds=1)，timedelta(seconds=1)時(shí)間變化1s
# sum1.append(compare_time),將得到的compare_time寫入sum1列表中。

def query_data():
    compare_time = sql_result[0][0]
    start_time = [sql_result[i][0] for i in range(0,len(sql_result))]      
    end_time = [sql_result[i][1] for i in range(0,len(sql_result))]
    while compare_time < start_time[len(sql_result)-1]:
          compare_time += datetime.timedelta(seconds=1)          
          count1 = 0         
          count2 = 0
          for time1 in start_time:            
              if time1 <= compare_time:                  
                 count1 = count1 + 1                 
          for time2 in end_time:                            
              if time2 <= compare_time:                    
                 count2 = count2 - 1                 
          sum1.append(compare_time)          
          sum2.append(count1+count2)

# 5.定義excel操作函數(shù)
# xlwt.Workbook(encoding='utf-8')定義編碼格式
# wbk.add_sheet('My worksheet')定義操作的sheet表
# xlwt.XFStyle()定義單元格格式
# datastyle.num_format_str = 'yyyy-mm-dd hh:mm:ss'定義單元格中數(shù)據(jù)格式
# worksheet.write(row,0,sum1[row],datastyle) 按定義的格式寫入數(shù)據(jù)
# wbk.save()保存操作的excel表格。

def re_sheet():            
    wbk = xlwt.Workbook(encoding='utf-8')
    worksheet = wbk.add_sheet('My worksheet')
    datastyle = xlwt.XFStyle()
    datastyle.num_format_str = 'yyyy-mm-dd hh:mm:ss'
    for row in range(0,len(sum1)):       
          worksheet.write(row,0,sum1[row],datastyle)          
          worksheet.write(row,1,sum2[row])          
    wbk.save('Concurrency.xls')    
query_data()     
re_sheet()