SQLITE3 database

import sqlite3
from pandas import DataFrame < br>import re

class SQL_method:
”’
function: Basic operations on the database can be realized
”’
def __init__(self, dbName, tableName, data, columns, COLUMNS, Read_All=True):
”’
Function: Initialization parameters
dbName: Database file name
TableName: Name of table in database
Data: From csv file Data read and processed in the
columns: used to create the database, which is the first row of the table
COLUMNS: used for formatted output of the data, is the header of the output
Read_All: After the table is created Have all data been read?
”’
Self.dbName = dbName
self.tableName = tableName
self.data = data
self.columns = COL
self.columns = COL
. self.columns = COL
. = COLUMNS
self.Read_All = Read_All

def creat Table(self):
”’
Function: Create database files and related tables
”’
# Connect to the database
connect = sqlite3.connect(self.dbName)
# Create table
connect.execute(“CREATE TABLE {}({})”.format(self.tableName, self.columns))
# Submit transaction
connect.commit()
# Disconnect
connect.close()

def destroyTable(self):
”’
Function: Delete the table in the database file
”’
# Connect to the database
connect = sqlite3.connect(self.dbName)
# Delete table
connect.execute(“DROP TABLE {}”.format(self.tableName))
# Submit transaction
connect.commit()
# Disconnect
connect.close()

def insertDataS(self):
”’
Function: File to database Insert multiple data in the table in
”’
# Connect to the database
connect = sqlite3.connect(self.dbName)
# Insert multiple data
connect.executemany(“INSERT INTO {} VALUES(?,?,?,?,?,?,?,?,?,?,?,?,?)”.format(self.tableNa me), self.data)
#for i in range(len(self.data)):
# connect.execute(“INSERT INTO university VALUES(?,?,?,?,?,?, ?,?,?,?,?,?,?)”, data[i])
# Submit transaction
connect.commit()
# Disconnect
connect.close()

def getAllData(self):
”’
Function: Get all the data in the database file
”’
# Connect to the database
connect = connect = sq3. Connect (Self.dbName)
# Create a cursor object
Cursor = connect.cursor ()
# Read data
Cursor.execute (“SELECT * from {}”. Format (Self.Tablename ))
dataList = cursor.fetchall()
# Disconnect
connect.close()
return dataList

def searchData(self, conditions, IfPrint=True) :
”’
Function: Find specific data
”’
# Connect to the database
Connect = sqlite3.connect(self.dbName)
# Create cursor
cursor = connect.cursor()
# Find data
cursor.execute(“SELECT * FRO M {} WHERE {}”.format(self.tableName, conditions))
data = cursor.fetchall()
# Close cursor
cursor.close()
# Disconnect database connection< br> connect.close()
if IfPrint:
self.printData(data)
return data

def deleteData(self, conditions):
”’
Function: Delete data in the database
”’
# Connect to the database
connect = sqlite3.connect(self.dbName) # Insert multiple data
TE Connect.execute(“DEL FROM {} WHERE {}”.format(self.tableName, conditions))
# Submit transaction
connect.commit()
# Disconnect
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)) < br> 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])
            e xcept:
                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:\\123.csv”
    data, EN_columns, CH_columns = get_data(fileName)
    dbName = “university.db”
    tableName = “university”

    # ================= 创建一个SQL_method对象 ==================
    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 ResearchScale”, True)

    # =============== 对数据库中的数据进行重新排序操作 ================
    # 定义权值
    Weight = [0.3, 0.15, 0.1, 0.1, 0.1, 0.1, 0.05, 0.05, 0.05]
    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 方法组成一个二维序列
    universit y = [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 = ‘北京市‘”)
    SQL.deleteData(“Province = ‘广东省‘”)
    SQL.deleteData(“Province = ‘山东省‘”)
    SQL.deleteData(“Province = ‘山西省‘”)
    SQL.deleteData(“Province = ‘江西省‘”)
    SQL.deleteData(“Province = ‘河南省‘”)
    print(“\n>>> 数据删除成功！ “)
    SQL.printData(SQL.getAllData())

    # ====================== 在数据库中删除表 ========================
    SQL.destroyTable()
    print(“>>> 表删除成功！”)
    PrintTableList(data, 10)   # 输出前10行数据
    saveAsCsv(“D:\\123.csv”, data)