数据处理：利用pandas实现自动化数据表关联合并导出

之前提到可以使用excel的内置函数vlookup实现数据表之间的关联合并，但这毕竟需要人力介入太多，数据量大的话整理起来还是很费力的，所以就开始研究如何自动化进行数据关联合并。

解决思路

Step1

因为pyspider默认是把爬取到的数据结果存入sqlite里的，而存入的信息是一股脑全放在一个字段里的，这样直接取库信息就还要格式化数据，而pyspider是可以直接通过webgui下载数据的，支持json和csv格式。

那么首先就是通过python-request来下载csv文件保存本地

#!/usr/bin/env python3
# -*- coding:utf-8 -*-

#pip install openpyxl
#pip install pandas

import pandas

import csv, sqlite3
import requests

###原始数据下载模块###
print ('wait for downloading csv')

url1 = 'http://127.0.0.1:5000/results/dump/nvd.csv' 
r1 = requests.get(url1) 
with open("nvd.csv", "wb") as code:
     code.write(r1.content)

url2 = 'http://127.0.0.1:5000/results/dump/cnnvd.csv' 
r2 = requests.get(url2) 
with open("cnnvd.csv", "wb") as code:
     code.write(r2.content)

print ('download csv ok')

Step2

接下来就是需要把csv文件录入到数据库中，为了方便我还是使用sqlite作为数据库引擎，通过python-pandas一键入库

###原始数据入库合并模块###
conn= sqlite3.connect("vul.db")

df1 = pandas.read_csv('./nvd.csv')
df2 = pandas.read_csv('./cnnvd.csv')
df3 = pandas.read_csv('./cnvd.csv')


# csv文件直接写入数据库
df1.to_sql('nvd', conn, if_exists='replace', index=False)
df2.to_sql('cnnvd', conn, if_exists='replace', index=False)
df3.to_sql('cnvd', conn, if_exists='replace', index=False)

print ('toSQLite ok')

Step3

入库之后，现在就是根据需要对信息进行筛选替换了，可以直接使用sql语句

c = conn.cursor()

c.execute("update nvd set url=replace(url,'https://nvd.nist.gov/vuln/detail/','')")
c.execute("update cnnvd set cnnvd_id=replace(cnnvd_id,'CNNVD编号：','')")
c.execute("update cnnvd set level='1' where level = '超危'")
c.execute("update cnnvd set level='1' where level = '高危'")
c.execute("update cnnvd set level='2' where level = '中危'")
c.execute("update cnnvd set level='3' where level = '低危'")
c.execute("update cnnvd set cpe=(select cpe from nvd where cnnvd.cve_id = nvd.url)")
c.execute("update cnnvd set cwe=(select cwe_id from nvd where cnnvd.cve_id = nvd.url)")
c.execute("update cnnvd set cvss_base_score=(select cvss2_nvd_base_score from nvd where cnnvd.cve_id = nvd.url)")
c.execute("update cnnvd set cnvd_url=(select cn_url from cnvd where cnnvd.cve_id = cnvd.cve_id)")
c.execute("update cnnvd set cnvd_id=(select cnvd_id from cnvd where cnnvd.cve_id = cnvd.cve_id)")
c.execute("update cnnvd set cnvd_impact=(select cn_impact from cnvd where cnnvd.cve_id = cnvd.cve_id)")

conn.commit()

print ('data handling ok')

Step4

通过sql语句重新整理好数据后，就需要再导出Excel文件，这里使用强大的python-pandas即可瞬间解决

###数据处理之后导出excel模块###
sql_cmd = "SELECT cnnvd_id,cnnvd_threat_type,cnnvd_type,cnnvd_upload_time,cpe,cve_id,cvss_base_score,cwe,detail,glsve,killchain_type,level,ms_id,patch,pub_date,reference_url,solution,title,cnnvd_url,cnvd_id,cnvd_impact,cnvd_url FROM cnnvd ORDER BY cnnvd_id,cnnvd_upload_time"
data=pandas.read_sql(sql=sql_cmd, con=conn)

data.to_excel('./toVul.xlsx',sheet_name="vul",index_label=["vid"],engine='openpyxl')

到此，脚本就已经能实现我所需要的功能了，但实际运行发现处理数据极慢，因为录入的信息共有三十多万条，查阅相关资料发现，是sqlite自身引擎的问题，对update操作就是非常的慢，后续可改用mysql数据库引擎。
这里找到缓解方法

conn.execute("PRAGMA synchronous=OFF") #关闭同步
conn.execute("BEGIN TRANSACTION") #显式开启事务

完整代码

遇到的问题和解决方法都记录在注释里了

#!/usr/bin/env python3
# -*- coding:utf-8 -*-

# From CSV to SQLite3 by python 导入csv到sqlite: https://www.cnblogs.com/oikoumene/p/6550076.html
# pandas csv读取——用实际样例介绍read_csv方法参数: https://blog.csdn.net/hubin1995/article/details/89398689
# 如何用一个表的字段填充另一个表: https://blog.csdn.net/qq_39781497/article/details/77075111
# sqlite与csv相关：https://blog.csdn.net/sinat_24568351/article/details/81157787
# pd.read_sql()知道这些就够用了:https://blog.csdn.net/The_Time_Runner/article/details/86601988
# python下载文件几种方法：https://www.cnblogs.com/jiu0821/p/6275685.html

#pip install openpyxl
#pip install pandas

import pandas

import csv, sqlite3
import requests


###原始数据下载模块###

print ('wait for downloading csv')

url1 = 'http://127.0.0.1:5000/results/dump/nvd.csv' 
r1 = requests.get(url1) 
with open("nvd.csv", "wb") as code:
     code.write(r1.content)

url2 = 'http://127.0.0.1:5000/results/dump/cnnvd.csv' 
r2 = requests.get(url2) 
with open("cnnvd.csv", "wb") as code:
     code.write(r2.content)

print ('download csv ok')
print ('wait for toSQLite...')


###原始数据入库合并模块###
conn= sqlite3.connect("vul.db")

df1 = pandas.read_csv('./nvd.csv')
df2 = pandas.read_csv('./cnnvd.csv')
df3 = pandas.read_csv('./cnvd.csv')


# csv文件直接写入数据库
df1.to_sql('nvd', conn, if_exists='replace', index=False)
df2.to_sql('cnnvd', conn, if_exists='replace', index=False)
df3.to_sql('cnvd', conn, if_exists='replace', index=False)

print ('toSQLite ok')
print ('wait for handling data...')
####################################
# # pandas函数-to_sql
# #             name是表名
# #             con是连接
# #             if_exists：表如果存在怎么处理
# #                 append：追加
# #                 replace：删除原表，建立新表再添加
# #                 fail：什么都不做
# #             index=False：不插入索引index
###################################




#为了缓解sqlite执行update操作太慢问题
#参见：https://blog.csdn.net/qq_21063873/article/details/104493195
#
conn.execute("PRAGMA synchronous=OFF") #关闭同步
conn.execute("BEGIN TRANSACTION") #显式开启事务

c = conn.cursor()

c.execute("update nvd set url=replace(url,'https://nvd.nist.gov/vuln/detail/','')")
c.execute("update cnnvd set cnnvd_id=replace(cnnvd_id,'CNNVD编号：','')")
c.execute("update cnnvd set level='1' where level = '超危'")
c.execute("update cnnvd set level='1' where level = '高危'")
c.execute("update cnnvd set level='2' where level = '中危'")
c.execute("update cnnvd set level='3' where level = '低危'")
c.execute("update cnnvd set cpe=(select cpe from nvd where cnnvd.cve_id = nvd.url)")
c.execute("update cnnvd set cwe=(select cwe_id from nvd where cnnvd.cve_id = nvd.url)")
c.execute("update cnnvd set cvss_base_score=(select cvss2_nvd_base_score from nvd where cnnvd.cve_id = nvd.url)")
c.execute("update cnnvd set cnvd_url=(select cn_url from cnvd where cnnvd.cve_id = cnvd.cve_id)")
c.execute("update cnnvd set cnvd_id=(select cnvd_id from cnvd where cnnvd.cve_id = cnvd.cve_id)")
c.execute("update cnnvd set cnvd_impact=(select cn_impact from cnvd where cnnvd.cve_id = cnvd.cve_id)")

conn.commit()

print ('data handling ok')
print ('wait for toExcel...')

###数据处理之后导出excel模块###
sql_cmd = "SELECT cnnvd_id,cnnvd_threat_type,cnnvd_type,cnnvd_upload_time,cpe,cve_id,cvss_base_score,cwe,detail,glsve,killchain_type,level,ms_id,patch,pub_date,reference_url,solution,title,cnnvd_url,cnvd_id,cnvd_impact,cnvd_url FROM cnnvd ORDER BY cnnvd_id,cnnvd_upload_time"
data=pandas.read_sql(sql=sql_cmd, con=conn)

data.to_excel('./toVul.xlsx',sheet_name="vul",index_label=["vid"],engine='openpyxl')

# to_excel函数：
#         index : boolean, default True Write row names (index)
#         默认为True，显示index，当index=False 则不显示行索引(名字)
#         index_label : string or sequence, default None
#         设置索引列的列名。
# pandans中read_excel、to_excel相关https://www.cnblogs.com/hankleo/p/11426784.html
# 

#
# 遇到问题：Pandas中read_excel和to_excel函数的默认引擎是 xlrd ， xlrd 虽然同时支持 .xlsx 和 .xls 两种文件格式，但是在源码文件 xlrd/sheet.py 中限制了读取的 Excel 文件行数必须小于 65536，列数必须小于 256。

# 解决方法：openpyxl 是一个专门用来操作 .xlsx 格式文件的 Python 库，和 xlrd 相比它对于最大行列数的支持和 .xlsx 文件所定义的最大行列数一致。
# Pandas 的 read_excel 和to_excel 函数，有 engine 字段，可以指定所使用的处理 Excel 文件的引擎，填入 openpyxl ，再读取文件就可以了。
# 参考见：https://www.cnblogs.com/hankleo/p/11426784.html
#

conn.close()
print('toExcel ok')
print('all done')