pandas真是数据分析的好东西，使得代码简洁了不少
利用python做了爬虫，服务端，自动化测试，也都做了相应的编码以及架构处理，近期就想来玩玩数据分析，这个才是好前景呀！
我们来看看这个网站，url = "http://www.compassedu.hk/qs"，爬取里面的table数据， 今天就来爬虫这个地址，并保存csv以及自动创建数据库
在还没学习数据分析前，像这种静态网站我们一般使用requests+BeautifulSoup进行请求和解析，以下是我的代码，不使用pandas的
# -*- coding: utf-8 -*- import requests import pandas as pd from bs4 import BeautifulSoup from sqlalchemy import create_engine import pymysql import numpy as np import traceback from openpyxl import Workbook url = "http://www.compassedu.hk/qs" res = requests.get(url) #提取表格数据 soup = BeautifulSoup(res.content,"lxml") table = soup.select("table#rk")[0] table_datas = [] #提取列名 one_tr = table.select("tr")[0] tr_datas = [] for th in one_tr.select("th"): tr_datas.append(th.get_text()) table_datas.append(tr_datas) for tr in table.select("tr"): tr_datas = [] for th in tr.select("td"): tr_datas.append(th.get_text()) if tr_datas: table_datas.append(tr_datas) #保存excel wb = Workbook() ws = wb.active for table in table_datas: ws.append(table) wb.save("result3.xlsx") mysql_conf = { "host": '127.0.0.1', "port": 3306, "user": 'root', "password": '你自己的数据库密码', "db": 'analysis', "charset": "utf8", "cursorclass": pymysql.cursors.DictCursor } def get_connection(): try: conn = pymysql.connect(**mysql_conf) return conn except: return None #创建数据库连接 conn = get_connection() ok_table_datas = [] for i in range(1,len(table_datas)): ok_table_datas.append(tuple(table_datas[i])) try: with conn.cursor() as cursor: #以元组形式插入 一次插入完 这个快 sql = '''insert into university2020three values (%s,%s,%s,%s,%s,%s,%s,%s,%s,%s,%s)''' cursor.executemany(sql,ok_table_datas) conn.commit() #分条插入 分条提交 效率比较慢 # for i in range(1,len(table_datas)): # print(table_datas[i]) # sql = '''insert into university2020three values (%s,%s,%s,%s,%s,%s,%s,%s,%s,%s,%s)''' # cursor.execute(sql,(table_datas[i])) # conn.commit() except Exception as e: print(traceback.format_exc()) print(e) finally: conn.close()
在我学习完pandas之后，我就觉得这个写法太笨了，我就解析还是用BeautifulSoup，保存excel和sql用pandas
# -*- coding: utf-8 -*- import requests import pandas as pd from bs4 import BeautifulSoup from sqlalchemy import create_engine import pymysql import numpy as np import traceback from openpyxl import Workbook url = "http://www.compassedu.hk/qs" res = requests.get(url) soup = BeautifulSoup(res.content,"lxml") table = soup.select("table#rk")[0] table_datas = [] #获取列标题 one_tr = table.select("tr")[0] tr_datas = [] for th in one_tr.select("th"): tr_datas.append(th.get_text()) table_datas.append(tr_datas) #获取table的数据 for tr in table.select("tr"): tr_datas = [] for th in tr.select("td"): tr_datas.append(th.get_text()) if tr_datas: table_datas.append(tr_datas) # data = pd.DataFrame(table_datas) #指定标题 上面是不指定标题 # data = pd.DataFrame(table_datas,columns=['Ranking', 'University Name','Country/Region','Academic Reputation','Employer Reputation','Faculty Student','International Faculty','International Students','Citations per Faculty','Overall Score','Free']) #转numpy.ndarray data = np.array(table_datas) #获取列名称 并转数据类型 和上面注释的data一样 data = pd.DataFrame(data[1:],columns=data[0]) data.to_csv("results_2.csv",index=None,encoding='utf_8_sig') conn = create_engine("mysql+pymysql://root:数据库密码@127.0.0.1:3306/analysis") data.to_sql("university2020-2",con=conn,if_exists='append',index=False)
最后我发现pandas不止可以用来csv，和sql简化很多，还可以直接读取就完事了，请看接下来的表演
# -*- coding: utf-8 -*- import requests import pandas as pd from bs4 import BeautifulSoup from sqlalchemy import create_engine import pymysql import numpy as np import traceback from openpyxl import Workbook url = "http://www.compassedu.hk/qs" res = requests.get(url) # 比较简便 还会根据数据类型创建数据库 html=res.content.decode(encoding=res.apparent_encoding) soup = BeautifulSoup(html,"lxml") table = soup.find("table",id="rk") datas = pd.read_html(table.prettify()) data = datas[0] data.to_csv("results.csv",encoding='utf_8_sig',index=False) conn = create_engine("mysql+pymysql://root:数据库地址@127.0.0.1:3306/analysis") data.to_sql("university2020",con=conn,if_exists='append',index=False)
第三种简简单单就搞定了，利用python有时候已经很方便了，但是会数据分析之后，有些东西不管爬虫还是什么的都会更加便捷，加油，未来可期。
