#!/usr/bin/env python3
# _*_ coding:utf-8 _*_
import pymysql
from datetime import datetime
import logging
# 配置logging
logging.basicConfig(level=logging.WARNING,filename="./log.log", format='%(asctime)s - %(name)s - %(levelname)s - %(message)s')
logger = logging.getLogger(__name__)
# 利用pymysql連接數(shù)據(jù)庫
conn = pymysql.connect(host="127.0.0.1", port=3306, user="root", password="123456", db="test", charset="utf8")
cur = conn.cursor()
# 設(shè)置一個(gè)空列表保存數(shù)據(jù)
line_list = []
# 從csv文件中讀取整理數(shù)據(jù)
with open("new_bb.txt", "r")as f:? # 文件中的數(shù)據(jù)格式是??2019/05/16 17:36:02,113,207.46.13.30
for line in f:? # 以行為單位讀取csv文件的內(nèi)容
? ? line = line.split(",")
? ? a = datetime.strptime(line[0], "%Y/%m/%d %H:%M:%S")? # 把字符格式的時(shí)間轉(zhuǎn)換為mysql數(shù)據(jù)庫datatime類型認(rèn)識的時(shí)間類型
? ? b = line[1]
? ? c = line[-1].strip()
? ? line = (int(b), c, a)
? ? line_list.append(line)? # 出家數(shù)據(jù)到line_list列表
print(len(line_list))
for row in line_list:
? ? sql ="insert into history_blacklist_ip(access_num,access_ip,create_time) value(%s,%s,%s)"
? ? params = row
? ? line = sql % params
? ? try:
? ? ? ? cur.execute(sql, params)
? ? ? ? conn.commit()
? ? except Exception as e:
? ? ? ? conn.rollback()
? ? ? ? print(line)
? ? ? ? logger.warning(e)
? ? ? ? logger.warning(line)
conn.close()
