python3将csv数据写入mysql_python3将csv文件存入mysql数据库,读取筛选数据库，将数据传入代码，用write方式写成html文件...

# -*- 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')