数据库实验

SQLite数据库是一款轻量级的数据库，无服务器、零配置、事务性的SQL数据库引擎。SQLite是世界上最广泛部署的SQL数据库引擎，而且SQLite的源代码不受版权限制，是小型项目和简单web应用的理想选择。SQLite数据库是一个单一的，不依赖于其他模块与组件的数据库文件，它允许我们直接访问存储文件。
1、创建一个访问数据库的连接

import sqlite3

conn = sqlite3.connect('test.db')

print ("Opened database successfully")

如果数据库文件不存在，该方法就会自动在当前目录下创建一个test.db的文件。

2、创建游标

   c = conn.cursor()

获取到游标之后，就可以使用SQL语句来对数据进行操作，创建表、添加数据、遍历数据等。

3、创建表

import sqlite3

conn = sqlite3.connect('test.db')
print("Opened database successfully")
c = conn.cursor()
c.execute('''CREATE TABLE COMPANY
       (ID INT PRIMARY KEY     NOT NULL,
       NAME           TEXT    NOT NULL,
       AGE            INT     NOT NULL,
       ADDRESS        CHAR(50),
       SALARY         REAL);''')
print("Table created successfully")
conn.commit()
conn.close()

``4、将上次爬虫的csv文件写入到数据库中

import requests
from bs4 import BeautifulSoup
import pandas
 # 1. 获取网页内容
def getHTMLText(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 fillTabelList(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 PrintTableList(tabel_list, num):
     # 输出前num行数据
    print("{1:^2}{2:{0}^10}{3:{0}^6}{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 saveAsCsv(filename, tabel_list):
    FormData = pandas.DataFrame(tabel_list)
    FormData.columns = ["排名", "学校名称", "省市", "总分", "生涯质量", "培养结果", "科研规模", "科研质量", "顶尖成果", "顶尖人才", "科技服务", "产学研合作", "成果转化"]
    FormData.to_csv(filename, encoding='utf-8', index=False)
 
if __name__ == "__main__":
    url = "http://www.zuihaodaxue.cn/zuihaodaxuepaiming2016.html"
    html = getHTMLText(url)
    soup = BeautifulSoup(html, features="html.parser")
    data = fillTabelList(soup)
     #print(data)
    PrintTableList(data, 10)   # 输出前10行数据
    saveAsCsv("E:\\University_Rank.csv", data)

结果为：

import requests
from bs4 import BeautifulSoup
import pandas
 # 1. 获取网页内容
def getHTMLText(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 fillTabelList(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 PrintTableList(tabel_list, num):
     # 输出前num行数据
    print("{1:^2}{2:{0}^10}{3:{0}^6}{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 saveAsCsv(filename, tabel_list):
    FormData = pandas.DataFrame(tabel_list)
    FormData.columns = ["排名", "学校名称", "省市", "总分", "生涯质量", "培养结果", "科研规模", "科研质量", "顶尖成果", "顶尖人才", "科技服务", "产学研合作", "成果转化"]
    FormData.to_csv(filename, encoding='utf-8', index=False)
 
if __name__ == "__main__":
    url = "http://www.zuihaodaxue.cn/zuihaodaxuepaiming2016.html"
    html = getHTMLText(url)
    soup = BeautifulSoup(html, features="html.parser")
    data = fillTabelList(soup)
     #print(data)
    PrintTableList(data, 10)   # 输出前10行数据
    saveAsCsv("E:\\University_Rank.csv", data)

结果：