SQLite3 of python
一、SQLite3 数据库
SQLite3 可使用 sqlite3 模块与 Python 进行集成,一般 python 2.5 以上版本默认自带了sqlite3模块,因此不需要用户另外下载。
描述
Python的数据库模块有统一的接口标准,所以数据库操作都有统一的模式(假设数据库模块名为db):
1. 用db.connect创建数据库连接,假设连接对象为conn
2. 如果该数据库操作不需要返回结果,就直接使用conn.execute查询,根据数据库事物隔离级别的不同,可能修改数据库需要conn.commit
3. 如果需要返回查询结果则用conn.cursor创建游标对象cur,通过cur.execute查询数据库,cursor方法有fetchall、fetchone、fetchmany返回查询结果,根据数据库事物隔离级别不同,可能修改数据库需要coon.commit
4. 关闭cur.close
基本操作实例如下:
#coding=utf-8 import sqlite3 conn = sqlite3.connect("sqlite.db") #创建sqlite.db数据库 print ("open database success") conn.execute("drop table IF EXISTS student") query = """create table IF NOT EXISTS student( customer VARCHAR(20), produce VARCHAR(40), amount FLOAT, date DATE );""" conn.execute(query) print ("Table created successfully") #在表中插入数据 ''' 方法1 ''' #data = '''INSERT INTO student(customer,produce,amount,date) # VALUES("zhangsan","notepad",999,"2017-01-02")''' #conn.execute(data) #data = '''INSERT INTO student(customer,produce,amount,date) # VALUES("lishi","binder",3.45,"2017-04-05")''' #conn.execute(data) #conn.commit() ''' 方法2 ''' statement = "INSERT INTO student VALUES(?,?,?,?)" data = [("zhangsan","notepad",999,"2017-01-02"),("lishi","binder",3.45,"2017-04-05")] conn.executemany(statement, data) conn.commit() curson = conn.execute("select * from student") conn.commit() print (curson) rows = curson.fetchall() print (rows) conn.close()
Python爬取大学排名,直接上代码吧
# -*- coding: utf-8 -*- """ Created on Wed May 29 22:44:12 2019 @author: guo'yu'yi """ import sqlite3 from pandas import DataFrame import re class SQL_method: def __init__(self, dbName, tableName, data, columns, COLUMNS, Read_All=True): self.dbName = dbName self.tableName = tableName self.data = data self.columns = columns self.COLUMNS = COLUMNS self.Read_All = Read_All def creatTable(self): connect = sqlite3.connect(self.dbName) connect.execute("CREATE TABLE {}({})".format(self.tableName, self.columns)) connect.commit() connect.close() def insertDataS(self): connect = sqlite3.connect(self.dbName) connect.executemany("INSERT INTO {} VALUES(?,?,?,?,?,?,?,?,?,?,?,?,?)".format(self.tableName), self.data) connect.commit() connect.close() def searchData(self, conditions, IfPrint=True): 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 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 getAllData(self): connect = sqlite3.connect(self.dbName) cursor = connect.cursor() cursor.execute("SELECT * FROM {}".format(self.tableName)) dataList = cursor.fetchall() connect.close() return dataList def run(self): try: self.creatTable() print(">>> 数据库创建成功!") self.insertDataS() print(">>> 表创建、数据插入成功!") except: print(">>> 数据库已创建!") if self.Read_All: self.printData(self.getAllData()) def deleteData(self, conditions): connect = sqlite3.connect(self.dbName) connect.execute("DELETE FROM {} WHERE {}".format(self.tableName, conditions)) connect.commit() connect.close() def destroyTable(self): connect = sqlite3.connect(self.dbName) connect.execute("DROP TABLE {}".format(self.tableName)) connect.commit() connect.close() def get_data(fileName): 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: if line[i] == '': line[i] = '0' line[i] = eval(line[i]) except: continue data.append(tuple(line)) 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:\python文件\daxuepaimingRank.csv" data, EN_columns, CH_columns = get_data(fileName) dbName = "db24.db" tableName = "university" 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 ResearchScale", True) Weight = [0.1, 0.15, 0.2, 0.25, 0.3, 0.35, 0.40, 0.45, 0.5] value, sum = [], 0 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 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 = '北京市'") SQL.deleteData("Province = '山东省'") SQL.deleteData("Province = '河南省'") SQL.deleteData("Province = '山西省'") SQL.deleteData("Province = '广东省'") SQL.deleteData("Province = '江西省'") print(" 数据删除成功") SQL.printData(SQL.getAllData()) SQL.destroyTable() print(" 删除成功")
结果如下