SQLite3—数据库的学习—python
SQLite3
一、简介。
SQLite是一种嵌入式数据库，它的数据库就是一个文件。由于SQLite本身是C写的，而且体积很小，所以，经常被集成到各种应用程序中，甚至在iOS和Android的App中都可以集成。
Python就内置了SQLite3，所以，在Python中使用SQLite，不需要安装任何东西，直接使用。
二、SQL语言。
学习SQLite之前，必须了解SQL语言，此处只简述本文遇到的几条 SQL语句。（SQL教程更详细请前往）
1、CREATE TABLE 语句用于创建数据库中的表。
CREATE TABLE 表名称(列名称1 数据类型,列名称2 数据类型,列名称3 数据类型,....)
2、INSERT INTO 语句用于向表格中插入新的行。
INSERT INTO 表名称 VALUES (值1, 值2,....)
3、查找。
（1）基本查找——SELECT * FROM XX表——查询XX表的所有数据。
（2）条件查找——SELECT * FROM XX表 WHERE 条件——按条件查询XX表。
4、排列。
SELECT * FROM XX表 ORDER BY 排序依据 。——按排列依据从低到高。
三、python—SQLite语句编写。
step1:访问和操作SQLite数据时，需要首先导入sqlite3模块，然后创建一个与数据库关联的Connection对象：
import sqlite3 #导入模块 conn = sqlite3.connect('example.db') #连接数据库 # 连接到SQLite数据库 # 数据库文件是example.db # 如果文件不存在，会自动在当前目录创建:
——————————— >
| sqlite3.Connection.execute() | 执行SQL语句 |
| sqlite3.Connection.cursor() | 返回游标对象 |
| sqlite3.Connection.commit() | 提交事务 |
| sqlite3.Connection.rollback() | 回滚事务 |
| sqlite3.Connection.close() | 关闭连接 |
——————————— >
step2:成功创建Connection对象以后，再创建一个Cursor对象，并且调用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()
step1和step2代码形成的example.db数据库如下：
>>>有关execute(sql[, parameters])：该方法用于执行一条SQL语句。（即上文的SQL语句）
SQL语句传递参数有两种方法，分别使用 问号 和 命名变量 作为占位符。
# 使用问号作为占位符 cur.execute("INSERT INTO people VALUES (?, ?)", (who, age)) # 使用命名变量作为占位符 cur.execute("SELECT * FROM people WHERE name_last=:who AND age=:age", {"who": who, "age": age})
~~~~~~
Cursor对象常用方法：
| close(...) | 关闭游标 |
| execute(...) | 执行SQL语句 |
| executemany(...) | 重复执行多次SQL语句 |
| executescript(...) |
一次执行多条SQL语句 |
| fetchall(...) | 从结果集中返回所有行记录 |
| fetchmany(...) |
从结果集中返回多行记录 |
| fetchone(...) | 从结果集中返回一行记录 |
~~~~~~
在将数据写入数据库时，可先用 sqlite3.connect(":memory:") 连接":memory:"在内存中创建数据库，查询数据无误后，再正式写入数据库。
例：
import sqlite3 conn = sqlite3.connect(":memory:") cur = conn.cursor() cur.execute("CREATE TABLE people (name_last, age)") who = "Dong" age = 38 # 使用问号作为占位符 cur.execute("INSERT INTO people VALUES (?, ?)", (who, age)) # 使用命名变量作为占位符 cur.execute("SELECT * FROM people WHERE name_last=:who AND age=:age", {"who": who, "age": age}) print(cur.fetchone())
运行结果如下：
在正式写入时，记得要关闭连接，即.close()。
四、SQLite可视化工具——sqlitestudio（简介）。
sqlitestudio是一款可以帮助用户管理sqlite数据库的工具，该sqlite数据库管理工具具有功能完善的sqlite2和sqlite3工具，视图编码支持utf8，还可以同时打开多个数据库文件，软件支持查看和编辑二进制字段。sqlitestudio软件支持的导出数据格式有csv、html、plain、sql、xml。
—————————————————————分割线——————————————————————
利用SQLite完成任务
任务一：将上次爬到的2019中国大学排名的csv文件存入以自己学号命名的数据库中。
任务二：查找广东技术师范大学排名。
任务三：查询并显示，广东省的学校的排名和得分（按照 "科技服务（企业科研经费·千元）" 排名规则，）
任务一代码实现：
首先要对读取到的csv文件数据进行处理，使其可以存入数据库。
f = open("D:\\桌面\\pytest\\2019中国大学排名.csv", 'r', encoding='utf-8') # 按行读取文件 data = [] for line in f.readlines(): line = line.replace('\n', '') line = line.split(',') # 将字符串按照 ',' 分割为列表 for i in range(len(line)): try: # 使用 异常处理 避开 出现中文无法转换 的错误 if line[i] == '': # 数据中存在空值，处理为0 line[i] = '0' line[i] = eval(line[i]) # 将数字 字符串形式 转换为数值，为了能在数据库储存 except: continue data.append(tuple(line))
处理后就可以写入数据库：
（完整代码）
1 f = open("D:\\桌面\\pytest\\2019中国大学排名.csv", 'r', encoding='utf-8') # 按行读取文件 2 data = [] 3 for line in f.readlines(): 4 line = line.replace('\n', '') 5 line = line.split(',') # 将字符串按照 ',' 分割为列表 6 for i in range(len(line)): 7 try: # 使用 异常处理 避开 出现中文无法转换 的错误 8 if line[i] == '': # 数据中存在空值，处理为0 9 line[i] = '0' 10 line[i] = eval(line[i]) # 将数字 字符串形式 转换为数值，为了能在数据库储存 11 except: 12 continue 13 data.append(tuple(line)) 14 15 16 17 18 import sqlite3 #导入模块 19 conn = sqlite3.connect('D:\\桌面\\pytest\\FZY2019310143110.db') #连接数据库 20 c = conn.cursor() 21 headline = ("排名", "学校名称", "省市", "总分", "生涯质量", "培养结果(%)",\ 22 "社会声誉", "科研规模","科研质量", "顶尖成果", "顶尖人才", "科技服务", "成果转化","学生国际化（留学生比例）") 23 c.execute('''CREATE TABLE college {}'''.format(headline)) 24 for i in range(1,len(data)): 25 c.execute("INSERT INTO college VALUES (?,?,?,?,?,?,?,?,?,?,?,?,?,?)",data[i]) 26 conn.commit() # 提交当前事务，保存数据 27 conn.close() # 关闭数据库连接
用sqlitestudio呈现结果如下：
（可以看到表中的数据没有单位，这是小编在第21行设置headline时，偷懒的结果，只要将单位补上即可）
任务二代码实现：（使用SQL查找语句即可）
1 import sqlite3 #导入模块 2 conn = sqlite3.connect('D:\\桌面\\pytest\\FZY2019310143110.db') #连接数据库 3 c = conn.cursor() 4 for row in c.execute("SELECT * FROM college WHERE 学校名称 = '广东技术师范大学'"): 5 print(row) 6 7 conn.close() # 关闭数据库连接
结果：
任务三代码实现：（使用SQL排序语句即可）
1 import sqlite3 #导入模块 2 conn = sqlite3.connect('D:\\桌面\\pytest\\FZY2019310143110.db') #连接数据库 3 c = conn.cursor() 4 5 for row in c.execute("SELECT 学校名称，科技服务 FROM college WHERE 省市 = '广东' ORDER BY 科技服务"): 6 print(row) 7 8 conn.close() # 关闭数据库连接
结果如下（从低到高）：
右边数据为科技服务（企业科研经费·千元）
