设计大学排名+数据库实践
一丶数据库SQLite的基本知识
- SQLite背景
SQLite，是一款轻型的数据库，是遵守ACID的关系型数据库管理系统，它包含在一个相对小的C库中。它是D.RichardHipp建立的公有领域项目。它的设计目标是嵌入式的，而且目前已经在很多嵌入式产品中使用了它，它占用资源非常的低，在嵌入式设备中，可能只需要几百K的内存就够了。它能够支持Windows/Linux/Unix等等主流的操作系统，同时能够跟很多程序语言相结合，比如 Tcl、C#、PHP、Java等，还有ODBC接口，同样比起Mysql、PostgreSQL这两款开源的世界著名数据库管理系统来讲，它的处理速度比他们都快。SQLite第一个Alpha版本诞生于2000年5月。 至2015年已经有15个年头，SQLite也迎来了一个版本 SQLite 3已经发布。
- SQLite3的基本语句(与python集成)
上面是一部分的Python SQLite3的命令，可以在https://www.runoob.com/sqlite/sqlite-python.html中找到更多，并且，
如果您需要了解更多细节，请查看 Python sqlite3 模块的官方文档。
二丶数据库SQLite3的基本使用
- 连接，生成数据库
代码如下
import sqlite3 conn = sqlite3.connect('base.db') print ("Opened database successfully")
如果没有连接到此数据库，数据库不存在，便会重新创建一个
此时，在桌面上生成base.db，代表数据库已经被创建
初步了解后，我们进入下一个部分
三丶设计大学排名并将其导入数据库分析
利用爬虫爬取最好大学网上对于2018年全国各大高校的各项指标的排名及综合状况
代码如下
import requests from bs4 import BeautifulSoup import pandas # 1. 获取网页内容 def gettext(url): try: r = requests.get(url, timeout = 30) r.raise_for_status() r.encoding = 'utf-8' return r.text except Exception as e: print("Error:", e) return "" # 2. 分析网页内容并提取有用数据 def getTabelList(soup): # 获取表格的数据 tabel_list = [] # 存储整个表格数据 Tr = soup.find_all('tr') for tr in Tr: Td = tr.find_all('td') if len(Td) == 0: continue tr_list = [] # 存储一行的数据 for td in Td: tr_list.append(td.string) tabel_list.append(tr_list) return tabel_list # 3. 可视化展示数据 def Print(tabel_list, num): # 输出前num行数据 print("{1:^2}{2:{0}^10}{3:{0}^5}{4:{0}^5}{5:{0}^8}".format(chr(12288), "排名", "学校名称", "省市", "总分", "生涯质量")) for i in range(num): text = tabel_list[i] print("{1:{0}^2}{2:{0}^10}{3:{0}^5}{4:{0}^8}{5:{0}^10}".format(chr(12288), *text)) # 4. 将数据存储为csv文件 def save(filename, tabel_list): FormData = pandas.DataFrame(tabel_list) FormData.columns = ["排名", "学校名称", "省市", "总分", "生涯质量", "培养结果", "科研规模", "科研质量", "顶尖成果", "顶尖人才", "科技服务", "产学研合作", "成果转化"] FormData.to_csv(filename, encoding='utf_8_sig', index=False) if __name__ == "__main__": url = "http://www.zuihaodaxue.cn/zuihaodaxuepaiming2016.html" html = gettext(url) soup = BeautifulSoup(html, features="html.parser") data = getTabelList(soup) #print(data) Print(data, 5) # 输出前5行数据 save("end.csv", data)
运行结果如下
如上对象我们可以理解为用来connection的对象，接下来我们就再创建一个SQL中的Cursor对象，并调用其中的execute()方法来执行SQL语句创建数据表以及对数据的增删查改：
c = conn.cursor() # 创建表， c.execute('''CREATE TABLE stocks (date text, trans text, symbol text, qty real, price real)''') # 插入一条记录 c.execute("INSERT INTO stocks VALUES ('2006-01-05','BUY', 'RHAT', 100, 35.14)") # 提交当前事务，保存数据 conn.commit() # 关闭数据库连接 conn.close()
而connect(database[, timeout, isolation_level, detect_types, factory]) ：连接数据库文件，也可以连接":memory:"在内存中创建数据库。
sqlite3.Connection.execute()：执行SQL语句
sqlite3.Connection.cursor()：返回游标对象
sqlite3.Connection.commit()：提交事务
sqlite3.Connection.rollback()：回滚事务
sqlite3.Connection.close()：关闭连接
在充分理解上面的操作后，我们就可以应用SQList3了
直接上代码# -*- coding: utf-8 -*-import sqlitefrom pandas import DataFrameimport re
class SQL_method: ''' function: 可以实现对数据库的基本操作 ''' def __init__(self, dbName, tableName, data, columns, COLUMNS, Read_All=True): ''' function: 初始化参数 dbName: 数据库文件名 tableName: 数据库中表的名称 data: 从csv文件中读取且经过处理的数据 columns: 用于创建数据库，为表的第一行 COLUMNS: 用于数据的格式化输出，为输出的表头 Read_All: 创建表之后是否读取出所有数据 ''' self.dbName = dbName self.tableName = tableName self.data = data self.columns = columns self.COLUMNS = COLUMNS self.Read_All = Read_All def creatTable(self): ''' function: 创建数据库文件及相关的表 ''' # 连接数据库 connect = sqlite3.connect(self.dbName) # 创建表 connect.execute("CREATE TABLE {}({})".format(self.tableName, self.columns)) # 提交事务 connect.commit() # 断开连接 connect.close() def destroyTable(self): ''' function: 删除数据库文件中的表 ''' # 连接数据库 connect = sqlite3.connect(self.dbName) # 删除表 connect.execute("DROP TABLE {}".format(self.tableName)) # 提交事务 connect.commit() # 断开连接 connect.close() def insertDataS(self): ''' function: 向数据库文件中的表插入多条数据 ''' # 连接数据库 connect = sqlite3.connect(self.dbName) # 插入多条数据 connect.executemany("INSERT INTO {} VALUES(?,?,?,?,?,?,?,?,?,?,?,?,?)".format(self.tableName), self.data) #for i in range(len(self.data)): # connect.execute("INSERT INTO university VALUES(?,?,?,?,?,?,?,?,?,?,?,?,?)", data[i]) # 提交事务 connect.commit() # 断开连接 connect.close() def getAllData(self): ''' function: 得到数据库文件中的所有数据 ''' # 连接数据库 connect = sqlite3.connect(self.dbName) # 创建游标对象 cursor = connect.cursor() # 读取数据 cursor.execute("SELECT * FROM {}".format(self.tableName)) dataList = cursor.fetchall() # 断开连接 connect.close() return dataList def searchData(self, conditions, IfPrint=True): ''' function: 查找特定的数据 ''' # 连接数据库 connect = sqlite3.connect(self.dbName) # 创建游标 cursor = connect.cursor() # 查找数据 cursor.execute("SELECT * FROM {} WHERE {}".format(self.tableName, conditions)) data = cursor.fetchall() # 关闭游标 cursor.close() # 断开数据库连接 connect.close() if IfPrint: self.printData(data) return data def deleteData(self, conditions): ''' function: 删除数据库中的数据 ''' # 连接数据库 connect = sqlite3.connect(self.dbName) # 插入多条数据 connect.execute("DELETE FROM {} WHERE {}".format(self.tableName, conditions)) # 提交事务 connect.commit() # 断开连接 connect.close() def printData(self, data): print("{1:{0}^3}{2:{0}<11}{3:{0}<4}{4:{0}<4}{5:{0}<5}{6:{0}<5}{7:{0}^5}{8:{0}^5}{9:{0}^5}{10:{0}^5}{11:{0}^5}{12:{0}^6}{13:{0}^5}".format(chr(12288), *self.COLUMNS)) for i in range(len(data)): print("{1:{0}<4.0f}{2:{0}<10}{3:{0}<5}{4:{0}<6}{5:{0}<7}{6:{0}<8}{7:{0}<7.0f}{8:{0}<8}{9:{0}<7.0f}{10:{0}<6.0f}{11:{0}<9.0f}{12:{0}<6.0f}{13:{0}<6.0f}".format(chr(12288), *data[i])) def run(self): try: # 创建数据库文件 self.creatTable() print(">>> 数据库创建成功！") # 保存数据到数据库 self.insertDataS() print(">>> 表创建、数据插入成功！") except: print(">>> 数据库已创建！") # 读取所有数据 if self.Read_All: self.printData(self.getAllData()) def get_data(fileName): ''' function: 读取获得大学排名的数据 并 将结果返回 ''' data = [] # 打开文件 f = open(fileName, 'r', encoding='utf-8') # 按行读取文件 for line in f.readlines(): # 替换掉其中的换行符和百分号 替换百分号是为了方便之后的排序和运算 line = line.replace('\n', '') line = line.replace('%','') # 将字符串按照 ',' 分割为列表 line = line.split(',') for i in range(len(line)): # 使用 异常处理 避开 出现中文无法转换 的错误 try: # 将空值填充为 0 if line[i] == '': line[i] = '0' # 将数字转换为数值 line[i] = eval(line[i]) except: continue data.append(tuple(line)) # EN_columns、CH_columns 分别为 用于数据库创建、数据的格式化输出 EN_columns = "Rank real, University text, Province text, Grade real, SourseQuality real, TrainingResult real, ResearchScale real, \ ReserchQuality real, TopResult real, TopTalent real, TechnologyService real, Cooperation real, TransformationResults real" CH_columns = ["排名", "学校名称", "省市", "总分", "生涯质量", "培养结果(%)", "科研规模", "科研质量", "顶尖成果", "顶尖人才", "科技服务", "产学研合作", "成果转化"] return data[1:], EN_columns, CH_columns if __name__ == "__main__": fileName = "D:\\end.csv" data, EN_columns, CH_columns = get_data(fileName) dbName = "db2018310143103.db" tableName = "db2018310143103"
SQL = SQL_method(dbName, tableName, data, EN_columns, CH_columns, False) # 创建数据库并保存数据 SQL.run() # 在数据库中查找数据项 # print(">>> 查找数据项（University = '中山大学'） :") SQL.searchData("University = '中山大学'", True) # 将选取广东省的数据 并 对培养结果（毕业生就业率）大小排序 print("\n>>> 筛选数据项并按照培养结果排序（Province = '广东省'） :") SQL.searchData("Province = '广东省' ORDER BY TrainingResult", True) # 定义权值 Weight = [0.3, 0.1, 0.2, 0.05, 0.05, 0.1, 0.05, 0.05, 0.1] value, sum = [], 0 # 获取 Province = '广东省' 的所有数据 sample = SQL.searchData("Province = '广东省'", False) # 按照权值求出各个大学的总得分 for i in range(len(sample)): for j in range(len(Weight)): sum += sample[i][4+j] * Weight[j] value.append(sum) sum = 0 # 将结果通过 pandas 的 DataFrame 方法组成一个二维序列 university = [university[1] for university in sample] uv, tmp = [], [] for i in range(len(university)): tmp.append(university[i]) tmp.append(value[i]) uv.append(tmp) tmp = [] df = DataFrame(uv, columns=list(("大学", "总分"))) df = df.sort_values('总分') df.index = [i for i in range(1, len(uv)+1)] # 输出结果 print("\n>>> 筛选【广东省】的大学并通过权值运算后重排名的结果：\n", df) #在数据库中删除数据项 SQL.deleteData("Province = '河南省'") print("\n>>> 数据删除成功！") SQL.printData(SQL.getAllData())
# 在数据库中删除表 SQL.destroyTable() print(">>> 表删除成功！")
运行结果为
因篇幅原因，截图部分，大家可以亲自去尝试一下噢~
本期分享到此结束~
