设计大学排名+数据库实践

一丶数据库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('
', '')
        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("
>>> 筛选数据项并按照培养结果排序（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("
>>> 筛选【广东省】的大学并通过权值运算后重排名的结果：
", df)
    
    #在数据库中删除数据项
    SQL.deleteData("Province = '河南省'")
    print("
>>> 数据删除成功！")
    SQL.printData(SQL.getAllData())

    # 在数据库中删除表
    SQL.destroyTable()
    print(">>> 表删除成功！")

运行结果为

因篇幅原因，截图部分，大家可以亲自去尝试一下噢~

本期分享到此结束~