Python 之 Sqlite3数据库，,　　SQLite数据

Python 之 Sqlite3数据库，,　　SQLite数据

　　SQLite数据库是一款非常小巧的嵌入式开源数据库软件，它使用一个文件存储整个数据库，优点是使用方便，但是功能相比于其它大型数据库来说，确实有点差距。由于此次数据库实践所需要求不多，所以就使用SQLite来完成。值得一提的是，Python内置SQLite3，所以在Python中使用SQLite，不需要安装任何东西，直接使用。

一、数据库（database）简介

　　表是数据库中存放关系数据的集合，一个数据库里面通常都包含多个表，表和表之间通过外键关联。

　　因为Python的数据库模块有统一的接口标准，所以数据库操作都有统一的模式。要操作关系数据库，大致是以下几步（假设数据库模块名称为db）：

二、SQLite使用

（1）SQlite数据库的导入

1 import sqlite3

（2）创建数据库/打开数据库

1 conn = sqlite3.connect("test.db")

　　在调用connect函数的时候，指定数据库名称，如果指定的数据库存在就直接打开这个数据库，如果不存在就新创建一个再打开。

（3）数据库连接对象

　　打开数据库时返回的对象conn就是一个数据库连接对象，有以下几种基本操作：

 1 #创建一个Cursor游标 2 cur = conn.cursor() 3  4 #执行一条SQL语句，创建user表 5 cur.execute(‘create table user (id varchar(20) primary key, name varchar(20))‘) 6  7 #执行一条SQL语句，插入一条记录 8 cur.execute(‘insert into user (id, name) values (\‘1\‘, \‘Michael\‘)‘) 9 10 #获得插入的行数11 cur.rowcount12 13 #执行查询语句14 cursor.execute(‘select * from user where id=?‘, (‘1‘,))15 #获得查询结果16 values = cur.fetchall()               #如果使用cur.fetchone()则首先返回列表中的第一项,再次使用,则返回第二项,依次下去17 18 #关闭cur19 cur.close()20 21 #提交事务22 conn.commit()23 24 #关闭connection连接25 conn.close()

　　使用Cursor游标对象执行insert，update，delete语句时，执行结果由rowcount返回影响的行数，就可以拿到执行结果。

　　使用Cursor游标对象执行select语句时，通过fetchall()可以拿到结果集。结果集是一个list，每个元素都是一个tuple，对应一行记录。

　　如果SQL语句带有参数，那么需要把参数按照位置传递给execute()方法，有几个?占位符就必须对应几个参数。另外一种方法是使用字符串格式化format()方法。

以上为SQLite数据库的常用的基本操作，具体用法请参阅SQLite的官方网站。（SQLite数据库菜鸟教程）

三、SQLite数据库实践

（1）将上次爬取得到的2017中国最好大学排名.csv文件以db格式文件写入数据库

 1 #!D:/Python/Python.exe 2 # -*- coding: utf-8 -*- 3 import sqlite3 as sql 4  5 #获取csv文件的数据 6 def GetData(Csv_Path): 7     with open(Csv_Path, "r", encoding="utf-8") as f: 8         lines = f.readlines() 9         Data = lines[1:]10         Headers = lines[0].replace(‘\n‘, ‘‘)11         Headers = Headers.split(‘,‘)12     return Data, tuple(Headers)13 14 #创建数据库15 def CreateDB(DB_Path, Headers, Tab_name):16     try:17         conn = sql.connect(DB_Path)18         cur = conn.cursor()19         cur.execute(‘‘‘CREATE TABLE {0} {1}‘‘‘.format(Tab_name, Headers))20         print("Table created successfully")21         return conn, cur22     except:23         print("Created failed")24 25 #将数据导入数据库26 def Import(Csv_Path, DB_Path, Tab_name):27     Data, Headers = GetData(Csv_Path)28     Conn, Cur = CreateDB(DB_Path, Headers, Tab_name)29 #为了之后的排序工作, 以下进行了数据处理30     for row in Data:31         row = row.replace(‘无‘, ‘0‘)32         row = row.replace(‘\n‘, ‘‘)33         row = tuple(row.split(‘,‘))34         Cur.execute("INSERT INTO {0} VALUES {1}".format(Tab_name, row))35     print("Data import successful")36     Conn.commit()                  #提交事务37     Cur.close()                    #关闭游标 38     Conn.close()                   #关闭数据库连接39 40 if __name__ == "__main__":41     Csv_Path = input("请输入需要导入的csv文件的路径或名称:")42     DB_Name = input("请输入数据库的名称:")43     Tab_name = input("请输入数据库的表名称:")44     Import(Csv_Path, DB_Name, Tab_name)46

将结果用SQLite Studio呈现：

SQLite Studio是一款 Sqlite数据库可视化工具，使用非常简单，想要了解的话这里给一个我参考的教程。（SQLite Studio使用教程）

（2）查询学校的数据

1 import sqlite3 as sql2 Conn = sql.connect("db2019310143115.db")3 Cur = Conn.execute("SELECT * from Rank where 学校名称 = ?", (‘广东技术师范学院‘,))          #这句代码是产生一个指定位置的cursor游标4 row = Cur.fetchone()5 print(row)6 Cur.close()7 Conn.close()

　　这里只是单纯执行一句sql语句实现查询，并未整合在上面的代码。值得一提的是connection.execute()与cursor.execute()不相同，前者是产生一个游标，后者是直接操作游标。

结果为：

（3）按照要求查询并显示广东省的学校的排名和成果转化

1 1 import sqlite3 as sql2 2 Conn = sql.connect("db2019310143115.db")3 3 Cur = Conn.execute("SELECT 学校名称, 成果转化（技术转让收入·千元） FROM Rank order by 成果转化（技术转让收入·千元） desc")          #执行sql的order by语句进行排序, desc为倒序, 默认为顺序4 4 for i in Cur.fetchall():5 5     print(i)6 6 Cur.close()7 7 Conn.close()

一部分结果为（左边是学校名称，右边是成果转化的数据）：

（4）将广东省（或全国）的学校的排名和数据存为一个新表，并且使用一个算法，综合每个因素进行总排名

　　由于这一要求并未完成，我仅把上面的功能整合在一起，给出代码：

 1 #!D:/Python/Python.exe 2 # -*- coding: utf-8 -*- 3 import sqlite3 as sql 4  5 #获取csv文件的数据 6 def GetData(): 7     Csv_Path = input("请输入csv文件的路径或名称：") 8     with open(Csv_Path, "r", encoding="utf-8") as f: 9         lines = f.readlines()10         Data = lines[1:]11         Headers = lines[0].replace(‘\n‘, ‘‘)12         Headers = Headers.split(‘,‘)13     return Data, tuple(Headers)14 15 #创建新表16 def CreateDB(Headers, Cur):17     Tab_name = input("请输入表名称:")18     try:19         Cur.execute(‘‘‘CREATE TABLE {0} {1}‘‘‘.format(Tab_name, Headers))20         print("Table created successfully.")21     except:22         print("Creation failed.")23 24 #将数据导入数据库25 def Import(Data, Cur):26     Tab_name = input("请输入表的名称: ")27 #为了之后的排序工作, 以下进行了数据处理28     for row in Data:29         row = row.replace(‘无‘, ‘0‘)30         row = row.replace(‘\n‘, ‘‘)31         row = row.split(‘,‘)32         for i in range(len(row)):33             try:34                 row[i] = eval(row[i])35             except:36                 continue37         Cur.execute("INSERT INTO {} VALUES {}".format(Tab_name, tuple(row)))                #执行sql语句, 写入数据38     print("Data imported successfully.")39 40 #查询数据41 def Search(Headers, Cur):42     Tab_name = input("请输入表的名称: ")43     School = input("请输入想要查找的学校: ")44     Cur.execute("SELECT * FROM {} WHERE 学校名称 = ?".format(Tab_name), (School,))45     Info = Cur.fetchone()46     for i in range(len(Headers)):47         print("{}: {}".format(Headers[i], Info[i]))48 49 #排序50 def Sort(Cur):51     Tab_name = input("请输入表的名称: ")52     Col = input("请输入想要按照哪列进行排序（默认倒序）: ")53     Cur.execute("SELECT 学校名称, 成果转化（技术转让收入·千元） FROM {} order by {} desc".format(Tab_name, Col))54     for i in Cur.fetchall():55         print(i)56 57 #数据库功能58 def Function(Data, Headers):59     DB_Path = input("请输入数据库的路径或名称：")60     try:61         Conn = sql.connect(DB_Path)62         Cur = Conn.cursor()63         print("Connect completed.")64     except:65         print("Connection fail.")66     else:67         while True:68             Choi = input("请选择你想要使用的功能(1、创建新表 2、插入数据 3、查询 4、排序 0、退出)：")69             if Choi == "0":70                 Cur.close()71                 Conn.close()72                 print("Database closed successfully.")73                 break74             elif Choi == "1":75                 CreateDB(Headers, Cur)76             elif Choi == "2":77                 Import(Data, Cur)78                 Conn.commit()79             elif Choi == "3":80                 Search(Headers, Cur)81             elif Choi == "4":82                 Sort(Cur)83             else:84                 print("Input Error!")85 86 if __name__ == "__main__":87     Data, Headers = GetData()88     Function(Data, Headers)

未完待续...

Python 之 Sqlite3数据库