SQList3—数据库的学习—python

loveandroid0

2020-05-31

关注关注

SQList3

一、简介。

　　SQLite是一种嵌入式数据库，它的数据库就是一个文件。由于SQLite本身是C写的，而且体积很小，所以，经常被集成到各种应用程序中，甚至在iOS和Android的App中都可以集成。

　　Python就内置了SQLite3，所以，在Python中使用SQLite，不需要安装任何东西，直接使用。

二、SQL语言。

　　学习SQList之前，必须了解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—SQList语句编写。

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数据库如下：

SQList3—数据库的学习—python

>>>有关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())

运行结果如下：

SQList3—数据库的学习—python

　　在正式写入时，记得要关闭连接，即.close()。

四、sqlist可视化工具——sqlitestudio（简介）。

sqlitestudio是一款可以帮助用户管理sqlite数据库的工具，该sqlite数据库管理工具具有功能完善的sqlite2和sqlite3工具，视图编码支持utf8，还可以同时打开多个数据库文件，软件支持查看和编辑二进制字段。sqlitestudio软件支持的导出数据格式有csv、html、plain、sql、xml。

—————————————————————分割线——————————————————————

利用SQList完成任务

任务一：将上次爬到的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))

处理后就可以写入数据库：

（完整代码）

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))




import sqlite3                           #导入模块
conn = sqlite3.connect(‘D:\\桌面\\pytest\\FZY2019310143110.db‘)     #连接数据库
c = conn.cursor()
headline = ("排名", "学校名称", "省市", "总分", "生涯质量", "培养结果(%)",22      "社会声誉", "科研规模","科研质量", "顶尖成果", "顶尖人才", "科技服务", "成果转化","学生国际化（留学生比例）")
c.execute(‘‘‘CREATE TABLE college {}‘‘‘.format(headline))
for i in range(1,len(data)):
    c.execute("INSERT INTO college VALUES (?,?,?,?,?,?,?,?,?,?,?,?,?,?)",data[i])
conn.commit()                 # 提交当前事务，保存数据
conn.close()             # 关闭数据库连接

用sqlitestudio呈现结果如下：

SQList3—数据库的学习—python

（可以看到表中的数据没有单位，这是小编在第21行设置headline时，偷懒的结果，只要将单位补上即可）

任务二代码实现：（使用SQL查找语句即可）

import sqlite3                           #导入模块
conn = sqlite3.connect(‘D:\\桌面\\pytest\\FZY2019310143110.db‘)     #连接数据库
c = conn.cursor()
for row in c.execute("SELECT * FROM college WHERE 学校名称 = ‘广东技术师范大学‘"):
    print(row)

conn.close()             # 关闭数据库连接

结果：

SQList3—数据库的学习—python

任务三代码实现：（使用SQL排序语句即可）

import sqlite3                           #导入模块
conn = sqlite3.connect(‘D:\\桌面\\pytest\\FZY2019310143110.db‘)     #连接数据库
c = conn.cursor()

for row in c.execute("SELECT 学校名称，科技服务 FROM college WHERE 省市 = ‘广东‘ ORDER BY 科技服务"):
    print(row)

conn.close()             # 关闭数据库连接

结果如下（从低到高）：

右边数据为科技服务（企业科研经费·千元）

SQList3—数据库的学习—python