# -*- coding: utf-8 -*
import os
import pymysql
import pandas as pd
# csv文件每两分钟更新一次,要获取最新的csv文件。
filename_list = os.listdir("/var/www/data/aa")
date_filename = []
for name in filename_list:
date_filename.append(name[0:14])
date_filename.sort()
sorted_file = []
for sort_num in date_filename:
for file in filename_list:
if str(sort_num) == file[0:14]:
sorted_file.append(file)
last_file = sorted_file[-1]
# 连接mysql数据库
def getcon(db_name):
conn = pymysql.connect(host="localhost", port=3306, user='root', passwd='root', db=db_name, charset='utf8')
cursor1 = conn.cursor()
return conn, cursor1
# 插入数据
# 定义读取文件并且导入数据库数据sql语句
def insertData(db_name, table_name):
conn, cursor1 = getcon(db_name)
df = pd.read_csv("/var/www/data/aa/" + last_file, index_col=False)
counts = 0
for each in df.values:
sql = 'insert into ' + table_name + ' values('
for i, n in enumerate(each):
if i < (len(each) - 1):
sql = sql + '"' + str(n) + '"' + ','
else:
sql = sql + '"' + str(n) + '"'
sql = sql + ');'
cursor1.execute(sql)
conn.commit()
counts += 1
# print('成功添加了' + str(counts) + '条数据 ')
return conn, cursor1
# 清空表数据
def delete_data(db_name):
conn, cursor1 = getcon(db_name)
sql1 = 'use workers;'
sql2 = ' delete from mtab;'
cursor1.execute(sql1)
cursor1.execute(sql2)
conn.commit()
cursor1.close()
conn.close()
# 插入数据
def main(db_name,table_name):
conn, cursor1 =insertData(db_name,table_name)
cursor1.close()
conn.close()
# 查询数据,count_name就是我们所需要的数值
count_nanshan = 0
count_zhenxing = 0
count_xingan = 0
count_xinlu = 0
count_yixin = 0
count_junde = 0
count_fuli = 0
count_niaoshan = 0
def query_data(db_name, table_name):
conn, cursor1 = insertData(db_name, table_name)
sql = "select COUNT(DISTINCT `workercode`) as 人数, minecode from `mtab` where `exttime` like '%xxx-xx-xx xx:xx%' or `exttime` like '%1900-01-01 00:00:00%' group by `minecode`;"
cursor1.execute(sql)
res = cursor1.fetchall()
print(res)
for data in res:
count, minecode = data
print(count, minecode)
if minecode == '230404021026':
count_nanshan = count
elif minecode == '230402021069':
count_zhenxing = count
elif minecode == '230401007579':
count_xingan =count
elif minecode == '230404021173':
count_xinlu = count
elif minecode == '230406002607':
count_yixin =count
elif minecode == '230405020622':
count_junde = count
elif minecode == '230404004483':
global count_fuli
count_fuli = count
elif minecode == '230406021037':
count_niaoshan = count
cursor1.close()
conn.close()
# 下面是用write方式写一个新的html文件
with open("/var/www/data/zhuzhuangtu_aas.html", 'w') as f:
f.write("""
echarts
""")
# 每次执行前将之前的数据删除,在插入最新的文件数据
delete_data('workers')
# 插入最新数据
main('workers','mtab')
query_data('workers','mtab')