Python数据库应用

标签：execute cur Python 数据库 t2 应用 sqlite3 con

    通过文件操作可以实现简单的数据操作功能，如果要处理的数据量巨大，则需要将数据存储在数据库中。Python支持多种数据库。

    本章主要介绍数据库概念以及结构化数据库查询语言SQL，分析并理解Python自带的轻量级关系数据库SQLlite的使用方法（同样用于MySQL数据库）

    文章以附上软件压缩包，下载即可

一、数据库的概念

     1.数据库（Database，简称DB)，是存储数据的仓库，即存储在计算机系统中结构化的、可共享的相关数据的集合。DB中的数据按一定的数据模型组织、描述和存储，可以最大限度减少数据的冗余度。

    2.数据库管理系统（Database Management System，简称DBMS），是用于管理数据的计算机软件。其主要功能为：

            （1）数据定义功能：使用数据定义语言（DDL）可以生成和维护各种数据对象的定义。

            （2）数据操作功能：使用数据定义语言（DDL）可以对数据库进行查询、插入、删除和修改等基本操作。

            （3）数据库的管理和维护：数据库的安全性、完整性、并发性、备份和恢复等功能。

    目前主要流行的数据库管理系统分为两类：
            （1）适合于企业用户的网络版DBMS：例如 Oracle、Microsoft SQL Server、IBM DB2 等。

            （2）适合于个人用户的桌面版DBMS：例如 Microsoft Access 等。

    3.数据库系统（Database System，简称DBS），是指在计算机系统中引入数据库后组成的系统。数据库系统一般包括计算机硬件、操作系统、DBMS、开发工具、应用系统（DBAS）、数据库管理员（DBA）和用户等。

二、关系型数据库

    常用关系型数据库包括：层次模型、网络模型、关系模型、面向对象的数据模型。

    关系模型以及成为数据库的标准。目前流行的数据库管理系统（DBMS）都是基于关系模型的关系数据库管理系统。

    1.关系数据库中常用术语

          关系：是一种规范化了的二维表

          属性：指二维表中的一列，在数据库中称为字段

          元组：指二维表中的一行，在数据库中称为记录

          域：属性的取值范围，即数据库中的某一列的取值范围

          关键字：一组可以唯一标识元组的属性

    2.关系模型认为世界由实体和联系构成

          实体是相互可以区别、具有一定属性的对象。联系是指实体之间的关系，一般分为三种类型：

          1、一对一的联系（1:1）

          例如：班级和班长的关系

          2、一对多的联系（1:n）

          例如：学生和班级的关系

          3、多对多的联系（n:n）

          例如：学生和课程之间的关系

三、sqlite3模块和SQLite数据库简介

    1.sqlite3模块

          Python标准模块sqlite3是Python中唯一一个数据库接口类模块，使用C语言实现，提供了访问和操作数据库SQLite的各种功能，sqlite3模块主要包括下载常量、函数和对象。

         （1）sqlite3.version：常量，版本号

         （2）sqlite3.connect(database)：函数，链接到数据库，返回 Connect 对象

         （3）sqlite3.connect：数据库链接对象

         （4）sqlite3.cursor：建立游标对象

         （5）sqlite3.row：行对象

    2.SQLite数据库

          SQLite数据库是Python自带的、轻量级的嵌入式关系模型数据库。SQLite是用C语言编写的，提供了简单和易于使用的数据库接口（API）模块sqlite3。

四、访问数据库的步骤

   使用sqlite3模块操作数据库的步骤：

        1.导入sqlite3模块

>>> import sqlite3    #导入数据库模块

       2.建立数据库链接，返回 Connection 对象

              作用数据库模块的connect()函数建立数据库链接，返回链接对象 con

>>> con = sqlite3.connect(Connectstring)   #链接到数据库，返回sqlite3.connection 对象
>>> #此处 con 为变量名，可以根据实际情况修改
>>> #connectstring是链接字符串，是数据库的文件名。如果指定的链接字符串为':memory:',则可以创建一个内存数据库

             例如：

>>> import sqlite3
>>> con = sqlite3.connect('D:\db1.db')
>>> #如果“D:\db1.db”存在，则打开数据库，否则创建并打开数据库

       3.创建游标对象

             使用游标对象能够领灵活地从表中检索出数据进行操作，就本质而言，游标实际上就是一种能从包括多条记录的结果集中每次提取一条记录的机制。

             调用 con.cursor() 函数创建游标对象 cur

>>> cur = con.cursor()   #cur为变量名，可根据实际情况改变

       4.使用 cursor 对象的 execute 执行 SQL 命令返回结果

             调用 cur.execute()、cur.executemany()、cur.executescript() 方法查询数据库

             （1） cur.execute(sql)：执行 SQL 语句

             （2）cur.execute(sql,parameters)：执行带参数的 SQL 语句

             （3）cur.execute(sql,seq_of_parameters) ：根据参数执行多次 SQL 语句

             （4） cur.execute(sql_script)：执行 SQL 脚本

             例如：创建一个名为t1的数据表

>>> con.execute('create table t1(id primary key,name)')

             执行语句后，将创建一个名为【t1】的数据表，表中包含id和name两个字段，并且通过primary key将id设置为主键。

             打开SQLiteStudio，在软件内打开数据表，就会看见我们刚才创建的t1数据表以及添加的字段

 小声：不知道你们看见这个表是不是歪的，我总感觉有点歪，盗版软件也不怪它，能用就行

             接下来，尝试在表中插入记录

>>> con.execute('insert into t1 values("001","北京")')
>>> con.commit()   #使用con.commit()方法，将数据提交上去

             然后再软件端，先单击数据，再单击刷新按钮，就可以看见添加进去的记录啦~

             另外，SQL语句在字符串中可以使用占位符"?"表示参数，传递的参数为元组，或者使用命名参数，传递的参数使用字典，就像这样

>>> con.execute('insert into t1 values(?,?)',("002","上海"))
>>> con.execute('insert into t1 values(:id,:name)',{"id":"003","name":"广州"})
>>> con.execute('insert into t1(id,name) values(?,?)',("004","杭州"))   #此处也可指定字段名

           注意：不指定具体字段名的话将按照数据表中字段的顺序依次添加，就像这样

>>> con.execute('insert into t1 values(?,?)',("深圳","005"))

           在这段代码中，我们的字段名用"?“来代替，未指定具体字段名，在运行 con.commit() 函数后，添加到数据表中的数据就会变成这样

         所以，在添加的时候一定要注意是否指定了字段名，如果未指定，一定要及时修改数据哦~

       5.获取游标查询结果集

             调用 cur.fetchone()、cur.fetchall()、cur.fetchmany() 返回查询结果

             （1）cur.fetchone()：返回结果的集的下一行（ROW对象）；无数据时返回None

             （2）cur.fetchall()：返回结果集的剩余行（ROW对象列表）；无数据时返回空 list

             （3）cur.fetchmany(size)：返回结果集的多行（ROW对象列表）；无数据时返回空 list

             （4）cur.rowcount：返回影响的行数、结果集的行数

             例如：

cur = con.cursor()  #创建游标对象
cur.execute('selete * from t1')  #执行SQL语句
r = cur.fetchone()    #获取一行ROW对象的结果
print(r)    #('001','北京'), 返回结果为元组 
r = cur.fetchall()
print(r)    #[('002', '上海'), ('003', '广州'), ('004', '杭州')], 返回结果为列表

           也可以直接使用循环输出结果

for i in cur.execute('select * from t1'):
    print(i)

          运行结果就像这样

          再一个就像这样，直接输出cur.fetchall()的内容

cur = con.cursor()
cur.execute('select * from t1 ')
print(cur.fetchall())     #返回结果为：[('001', '北京'), ('002', '上海'), ('003', '广州'), ('004', '杭州')]

       6.数据的提交和回滚

             根据数据库事务隔离级别的不同，可以提交或者回滚

             （1）con.commit()：提交函数

             （2）con.rallback()：回滚函数

       7.关闭Cursor对象和Commection对象 

             最后，需要关闭打开的Cursor对象和Commection对象

             （1）cur.close()： 关闭Cursor对象

             （2）con.close()：关闭Connection对象

五、创建数据库和表

    使用sqlite3.connect("数据库文件名")可以创建或打开SQLite数据库，并返回连接对象con，使用con.execute("create table ...")可以创建表。

    例：创建数据库db1.db和表t1，表中的字段名包括学号、姓名、性别和班级，其中学号为主键。

>>> con = sqlite3.connect('D:\\db1.db')
>>> con.execute('create table t1(学号 primary key,姓名,性别,班级)')

六、数据库表的插入、更新和删除操作

    在数据表中插入、更新和删除记录的一般步骤为：

     （1）建立数据库链接

     （2）创建游标对象cur，使用cur.execute(sql)执行SQL 的insert、update、delete 等语句，完成数据库表记录的插入、更新、删除操作，并根据返回的值判断操作结果。

     （3）提交操作。

     （4）关闭数据库。

       例：在数据库db1新建数据表t2并中插入、更新、删除若干条记录。

import sqlite3
a = [('10000', '张三', '男', '2班', 280),
     ('10001', '李四', '女', '1班', 210),
     ('10002', '王五', '女', '2班', 200),
     ('10003', '赵六', '男', '3班', 230),
     ('10004', '李七', '男', '3班', 260)]
# 打开数据表
con = sqlite3.connect('D:\\db1.db')
# 创建游标对象
cur = con.cursor()
# 创建数据表文件t2
con.execute('create table t2(学号 primary key,姓名,性别,班级,入学成绩)')
# 使用不同的方法插入一行数据
cur.execute('insert into t2 values("10005","孙八","男","3班",220)')
cur.execute('insert into t2 values(?,?,?,?,?)', ('10006', '周九', '女', '1班', 265))
# 或者通过字段名插入，此方法比较复杂
sql = "INSERT INTO t2 (学号, 姓名, 性别, 班级, 入学成绩) VALUES (:学号, :姓名, :性别, :班级, :入学成绩)"
data = {"学号": "10007", "姓名": "吴十", "性别": "男", "班级": "2班", "入学成绩": 290}
cur.execute(sql, data)
# 插入多行数据
cur.executemany("insert into t2 values(?,?,?,?,?)", a)
# 修改一行数据
cur.execute('update t2 set 姓名=? where 学号=?', ('张琦','11000'))
# 删除一行数据
n = cur.execute('update from t2 where 学号=?', ('11000', ))
print('删除了', n.rowcount, "行记录")
# 将修改提交
con.commit()
# 关闭游标
cur.close()
# 关闭数据库
con.close()

然后，打开SQLiteStudio软件，链接数据库之后，就能看见啦~

 ps：测试的时候刷新半天都没有刷新出来，最后还是在IDLE里一条一条来才成功.....

七、数据库表查询操作

    查询数据库表的一般步骤为：

    （1）建立数据库链接

    （2）根据SQL select 语句，使用con.execute(sql) 执行数据库查询操作，返回游标对象cur。

    例：查询数据表信息，查询并输出数据库db1的数据表t2并返回所有记录内容。

import sqlite3
# 打开数据表
con = sqlite3.connect('D:\\db1.db')
# 查询数据表中的记录内容
cur = con.execute('select * from t2')
for i in cur:
    print(i)
con.commit()
con.rollback()
cur.close()
con.close()

运行结果为

八、结构化查询语言SQL 

    SQL是结构化查询语言 Structured Query Language 的缩写，是通用的关系数据库操作语言。SQL包括数据定义、数据查询、数据操纵和数据控制4个部分。

    数据定义：如create table 、drop table

    数据查询：如select

    数据操纵：如insert into 、update 、delete from

    数据控制：如commit

    1.数据表的建立和删除

        （1）create table ：用于创建数据表

          格式：create table 表名(字段名1，字段名2，.......)

          例：在db1.db数据库中创建数据表t1

import sqlite3
con = sqlite3.connect('D:\\db1.db')
con.execute('create table t1(id primary key,name)')
con.commit()
con.close()

        （2）drop table ：用于删除表

          格式：drop table 表名

          例：在db1.db数据库中删除数据表t1

import sqlite3
con = sqlite3.connect('D:\\db1.db')
con.execute('drop table t2')
con.commit()
con.close()

     2.查询语句 select

          select 语句用于从表中查询数据，结果被存储在一个结果集中（称为结果集）

          格式：select 字段列表 from 表名 where 查询条件 group by 分组条件 order by 排序字段

         （1）字段列表

                     字段列表指查询列，它可以是一组列名、星号、表达式、变量等。

                     例1：查询 t2 表中的所有列的数据

import sqlite3
con = sqlite3.connect('D:\\db1.db')
cur = con.execute('selete * from t2')
for i in cur:
    print(i)

                      例2：查询 t2 表中的所有学生的学号和姓名字段

import sqlite3
con = sqlite3.connect('D:\\db1.db')
cur = con.execute('select 学号,姓名 from t2')
for i in cur:
    print(i)

      2.where 子句

         where 子句用于设置查询条件

         （1）比较运算符 

                     有 >、>=、=、<、<=、<> 

                     例1：查询 t2 表中姓名为 “张三” 的学生学号

import sqlite3
con = sqlite3.connect('D:\\db1.db')
cur = con.execute('select 学号 from t2 where 姓名="张三"')
r = cur.fetchone()
print(r)    # 运行结果为：('10000',)

         （2）范围运算符 

                     有 between···and···、not between···and··· 

                     例1：查询 t2 表中入学成绩在200~500分之间的学生姓名和入学成绩信息

import sqlite3
con = sqlite3.connect('D:\\db1.db')
cur = con.execute('select 姓名,入学成绩 from t2 where 入学成绩 between 200 and 250')
r = cur.fetchall()
print(r)     # 运行结果：[('李四', 210), ('王五', 200), ('赵六', 230)]

         （3）列表运算符 

                     判断表达式是否为为列表中的指定项

                     有 in(项1,项2,···)、 not in(项1,项2,···)

                     例1：查询 t2 表中1班或者2班的学生姓名和班级信息

import sqlite3
con = sqlite3.connect('D:\\db1.db')
cur = con.execute('select 姓名,班级 from t2 where 班级 in ("1班","2班")')
r = cur.fetchall()
print(r)    
# 代码运行结果：[('周九', '1班'), ('吴十', '2班'), ('张三', '2班'), ('李四', '1班'), ('王五', '2班')]

         （4）逻辑运算符 

                     用于多条件的逻辑链接

                     有 not 、and 、or 

                     例1：查询 t2 表中1班或2班的学生姓名和班级信息

import sqlite3
con = sqlite3.connect('D:\\db1.db')
cur = con.execute('select 姓名,班级 from t2 where 班级 = "1班" or 班级 = "2班"')
r = cur.fetchall()
print(r)
# 代码运行结果为：[('周九', '1班'), ('吴十', '2班'), ('张三', '2班'), ('李四', '1班'), ('王五', '2班')]

                     例2：查询 t2 表中入学成绩大于220分的男生姓名和入学成绩信息

import sqlite3
con = sqlite3.connect('D:\\db1.db')
cur = con.execute('select 姓名,班级 from t2 where 入学成绩>220 and 性别 = "男"')
r = cur.fetchall()
print(r)
# 代码运行结果为：[('吴十', '2班'), ('张三', '2班'), ('赵六', '3班'), ('李七', '3班')]

         （5）模式匹配符 

                     判断值是否与指定的的字符通配格式相符

                     有 like 、not like

                         ①通配符 “_”：表示匹配一个字符

                         ②通配符 “%”：表示匹配任意长度字符

                     通常用于模糊查找，判断值是否与指定的字符串格式相匹配

                     例1：查询 t2 表中姓为 “李” 的所有学生信息

import sqlite3
con = sqlite3.connect('D:\\db1.db')
cur = con.execute('select * from t2 where 姓名 like "李%"')
r = cur.fetchall()
print(r)
# 代码运行结果为：[('10001', '李四', '女', '1班', 210), ('10004', '李七', '男', '3班', 260)]

      3.数据分组：group up

         group up 子句通常用于结合聚合函数，根据一个或多个列对结果集进行分组

       （1）常用的聚合函数

                ①sum(字段名)：求一列数据的和

                ②avg(字段名)：求一列数据的平均值

                ③max(字段名)：求一列数据的最大值

                ④min(字段名)：求一列数据的最小值

                ⑤count(*)：统计查询的行数

       （2）组内条件子句：having

                   例1：统计t2表中各个班级的学生人数

import sqlite3
con = sqlite3.connect('D:\\dbq.db')
cur = con.execute('select 班级,count(*) as 人数 from t2 gruop up by 班级')
r = cur.fetchall()
print(r)
# 代码运行结果：[('1班', 2), ('2班', 3), ('3班', 2)]

                   例2：统计t2表中各个班级入学成绩的平均分

import sqlite3
con = sqlite3.connect('D:\\db1.db')
cur = con.execute('select 班级,avg(入学成绩) as 平均分 from t2 group by 班级')
r = cur.fetchall()
print(r)
# 代码运行结果：[('1班', 237.5), ('2班', 256.6666666666667), ('3班', 245.0)]

                   例3：统计t2表中学生人数至少有3人的班级和人数

import sqlite3
con = sqlite3.connect('D:\\db1.db')
cur = con.execute('select 班级,count() as 人数 from t2 group by 班级 having 人数 >= 3')
r = cur.fetchall()
print(r)
# 代码运行结果：[('2班', 3)]

                   例4：统计t2表中所有男生的入学成绩平均分

import sqlite3
con = sqlite3.connect('D:\\db1.db')
cur = con.execute('select 性别,avg(入学成绩) as 平均分 from t2 group by 性别 having 性别 = "男"')
r = cur.fetchall()
print(r)
# 代码运行结果：[('男', 265.0)]

      4.查询结果排序：order by

             说明：asc表示升序，为默认值；desc表示降序

             例1：查找t2表中的姓名、入学成绩字段、查询结果按入学成绩降序排列

import sqlite3
con = sqlite3.connect('D:\\db1.db')
cur = con.execute('select 姓名,入学成绩 from t2 order by 入学成绩 desc')
r = cur.fetchall()
print(r)
# 代码运行结果为：[('吴十', 290), ('张三', 280), ('周九', 265), ('李七', 260), ('赵六', 230), ('李四', 210), ('王五', 200)]

      5.添加记录语句：insert into

             格式：inseet into 表名(字段1,字段2,....) value(值1,值2,....)

             例2：在t2表中添加一条记录

# 假设我们添加的记录为（‘12345’，‘林一’，‘女’，‘2班’，‘300’）
import sqlite3
con = sqlite3.connect('D:\\db1.db')
cur = con.execute('insert into t2 values("12345","林一","女","3班","300")')
cur = con.execute('select * from t2')
r = cur.fetchall()
print(r)

             代码运行成功后，打开SQLiteStudio链接数据库后即可看见新添加的记录

       6.更新语句：update

              update 用于修改表中的数据

              格式：update 表名 set 字段名 = 新值 where 字段名 = 要修改值的字段名

              例1：将 t2 表中 “林一” 的入学成绩修改为 295 分

import sqlite3
con = sqlite3.connect('D:\\db1.db')
cur = con.execute('update t2 set 入学成绩 = 295 where 姓名 = "林一"')
cur = con.execute('select * from t2')
r = cur.fetchall()
print(r)
con.commit()
con.rollback()

             代码运行成功后，刷新数据表，即可看见修改的信息

        7.删除记录语句：delete

              delete 语句用于删除表中的行

              格式：delete from 表名 where 字段名 = 值

              例1：在 t2 表中删除 “林一” 的记录

import sqlite3
con = sqlite3.connect('D:\\db1.db')
cur = con.execute('delete from t2 where 姓名 = "林一"')
cur = con.execute('select * from t2')
r = cur.fetchall()
print(r)
con.commit()
con.rollback()

             同样，刷新数据表，即可看见修改后的信息

九、综合练习

    练习1：GUI编程，效果如图，单击 ”开始“ 按钮，标签显示随机姓名，时间间隔为50ms，单击 ”暂停“ 按钮，屏幕停止滚动。

    要求：（1）创建数据库 db1.db 和数据表 t1 ，t1表中含有学号（设置为主码）和姓名两个字段，由用户输入5条数据（方法不限），使用列表，循环，函数均可

              （2）在数据表 t1 中查询姓名字段的值并随机显示在GUI 窗口中

 图1

 

图2

 

图3

 

 图4

代码如下

# 练习1：抽奖
import sqlite3
from tkinter import *
import random

# 创建数据库和表
a = [('100', '张三'), ('101', '李四'), ('102', '王五'), ('103', '赵六'), ('104', '孙七')]
con = sqlite3.connect('D:\\db1.db')
cur = con.cursor()
cur.execute('create table t1 (学号 primary key, 姓名)')
cur.executemany('insert into t1 values (?,?)', a)
cur.execute('select 姓名 from t1')
a = cur.fetchall()
b = [i[0] for i in a]  # 使用列表推导式简化代码
con.commit()

# 创建GUI
w = Tk()
w.title('练习1：抽奖')
w.geometry('300x200')
flag = True


def f1():
    global flag
    t = random.choice(b)
    lb['text'] = f'{t}'
    if flag:
        w.after(50, f1)
    else:
        flag = True


def f2():
    global flag
    flag = False


lb = Label(w, text='显示中奖姓名', font='size 16'); lb.pack(pady='30')
Button(w, text='开始', command=f1).pack(side='left', padx=80)
Button(w, text='停止', command=f2).pack(side='left')


w.mainloop()

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

好啦，终于写完啦！我是梦璃，一个准大一新生！

Tips：有Python代写需求可以找我哦，孩子挺需要的（qaq)

标签：execute,cur,Python,数据库,t2,应用,sqlite3,con
From： https://blog.csdn.net/weixin_59644464/article/details/139906621