Python自学笔记之数据库管理

数据库概述

    前面学习的内容中，我们通常把代码写在了.py结尾的文件中，也可以把程序中的一些内容存放到目录文件中，但是当我们要保存大量数据的时候，如果使用多个文档来保存，就不方便我们进行管理，因此我们要引入数据库来管理这些内容，本章我们就来了解一下数据库的相关内容。

    1. 什么是数据库

        数据库是以一定方式储存在一起、能与多个用户共享、具有尽可能小的冗余度、与应用程序彼此独立的数据集合，可视为电子化的文件柜——存储电子文件的处所，用户可以对文件中的数据进行新增、查询、更新、删除等操作。

        我们在没有创建数据库之前可以对应Excel表来表示一下，如下图：

        在保存数据的时候，数据库可以保存多种的数据类型，相对Excel表会有更多功能，也有更多的操作能够帮我们迅速的变更大量的数据。

    2. 数据持久化

        我们使用数据库或者是文件保存都是为了数据的持久化，当我们把数据保存下来，当我们再次使用的时候，就可以直接通过相关指令去调取，数据持久化的方式包括前面我们提到过的目录文件，通过Python的文件操作和管理技术把数据存放在文本文件中，然后再去进行相关的读写，这种文件大多是结构化的文档，如XML、JSON、CSV文件。

        另外我们就是通过数据库将数据持久化，数据先是存放在数据库中，然后我们通过数据库管理系统来管理数据库中的数据，它能帮助我们进行事务处理，并发控制、高级查询和以及更多的数据库语句的操作。

    3. 数据库技术

        在Python中关于数据库的技术分为两类，一类是遵循Python DB-API规范技术，一类是ORM（对象关系映射）技术，前者是使用中Python中编写SQL语句去访问数据接口，而后者是面向对象的，不使用SQL语句也可以完成，而我们将通过程序来管理数据库，所以我们选择前者。

    4. MySQL数据库管理系统

        现在主流的管理系统还有Oracle、SQL Server、SQLite、Sysbase、DB 2等，我们将结合MySQL数据库和Navicat软件进行下一步的学习。

        MySQL是一个关系型数据库管理系统，由瑞典MySQL AB公司开发，属于Oracle 旗下产品。MySQL 是最流行的关系型数据库管理系统之一，在WEB 应用方面，MySQL是最好的 RDBMS 应用软件之一，当然大家也可以选择一种自己喜欢的数据库去使用，数据库管理同样是计算机专业的一门重要的必修课程，在本教程里大家只能学会简单的下载安装和使用数据库，我们在项目开发的时候是离不开数据库的管理，更深层次的学习大家可以去参考一下数据库相关的书籍，下面我们就来学习一下如何进行MySQL的安装和使用。

        

本文固定URL：https://www.dotcpp.com/course/304

MySQL的下载安装

        我们已经在上一节进行了相关介绍，这一节我们直接学习安装过程，大家可以手动搜索MySQL进入官网进行下载，也可以输入网址：https://dev.mysql.com/downloads/mysql/进行下载。

    1. MySQL下载安装

        1)  进入下载界面点击下载

        2)  我们可以先跳过账户的注册和登录直接进行下载

        下载完毕。

         

        3)  我们打开下载好的文件开始安装，然后回进入下图中的界面，该界面提供五种安装类型供我们选择：

        A. Developer Default是开发者安装，该类型会安装M有SQL服务器以及开发MySQL应用所需的工具，包括GUI工具、访问数据口的Excel插件等多种内容，如果要进行大型项目开发的可以选择这种类型

        B. Server only是只安装服务器，适用于部署MySQL服务器

        C. Client only是只安装客户端

        D. Full 是安装所有可用组件

        E. Custom为自定义安装

        由于我们只是简单的通过Python来了解一下数据库，所以选择第二种Server only即可。

        4) 点击next之后会需要我们安装一些配置文件，下载即可，然后进入如图界面，选择一种类型后点击next

 

        5)  设置一个密码，一定要记住这个密码，在连接数据库的时候需要用到。

        6) 点击next。

        7)  继续next。

        8) 完成安装。

    2. 连接MySQL

        安装完成后我们在菜单选项的最近添加中可以看到这些图标。

        打开第一个会让我们输入密码，输入刚刚安装时设置的密码即可。

        然后我们进进入到了MySQL命令行服务器，可以通过help指令查看。

    3. 创建数据库

        既然进入了数据库，那么我们创建一个数据库来试一试。

        创建使用create database 数据库名字;一定要注意分号的使用，然后我们再通过show databases;来查看是否已经创建好，具体如下图。

        也可以进行数据库的删除，使用drop database 数据库名;即可。

 Navicat for MySQL管理软件

    我们在上一节学习下载并安装了MySQL，然而命令提示符在操作数据库不能够帮助初学者形象的了解数据库，因此我们要选用一种MySQL图形化管理工具，一般我们会选择Navicat for MySQL软件去帮助完成相关操作。

    1. Navicat下载安装

        我们直接搜索Navicat或者打开网址：https://www.navicat.com.cn/,然后找到下载项，网址为:https://www.navicat.com.cn/download/navicat-for-mysql，

        在此页面选择一个符合自己电脑位数的版本进行下载。

        下载完成之后运行它，然后点击下一步选择安装路径。

         

        然后一直点击next完成安装即可。

    2. Navicat的连接

        完成安装之后我们打开桌面的图标进入软件，会提示购买或者使用，我们可以暂且选择试用，然后进入软件之后如下界面，我们点击左上角的连接。

        点击后选择MySQL之后如下图：

 

        测试连接显示通过则连接成功，在这里一定要记得这个密码，改密码是件麻烦的事情，否则就需要重新安装MySQL。

    3. Navicat的使用

        连接成功之后我们进入了如下主页：

        我们可以看到当前的数据库和上一节我们在MySQL命令行中看到的是一样的，我们可以在这个界面直接进行操作。

        我们通过右键test新建数据库，也可以直接使用当前存在的数据库。

        双击打开Python的数据库，能看到里面没有任何信息。

        可以右键表来创建一个表，输入相应信息并保存。

        然后打开名为student的表我们就可以手动输入信息，关于数据库的操作还有很多，后续我们大多是通过程序来操作，如果想要进一步使用这个软件，大家可以去了解数据库的更多内容，下一节我们就进入数据库的学习。

        

本文固定URL：https://www.dotcpp.com/course/306

对象和游标

        前面几节我们初步认识了数据库，我们想要把数据库连接到程序中，还是需要一个模块，我们使用PyMySQL模块来进行连接，安装它的命令为：pip install PyMySQL，如果安装失败可以使用下面的命令：pip install pymysql -i http://pypi.douban.com/simple/ --trusted-host pypi.douban.com安装完成如图所示。

        安装完成后我们再来讲解一下为什么数据库都下载好了还需要再安装一个模块，因为我们的数据库是存放在我们的电脑上的，这就等于你有一个U盘，U盘里有很多数据，但是U盘放到电脑旁边是没有办法获取其中的数据的，因此需要一个接口，通过这个接口电脑可以获取到U盘中的数据，我们下载的这个模块就充当这个接口的作用，把数据库和我们的程序连接到一起。在我们学习数据库操作之前，我们先了解一下连接对象的方法和游标对象。

    1. 连接对象的方法

        了解方法之前我们首先来了解一下连接对象，连接对象的功能就是负责对数据源的连接，连接成功之后我们才可以执行数据库的相关指令。

        Connection对象中有一些方法供我们使用，如下表：

函数	功能
close()	关闭数据库连接
commit()	提交事务
rollback()	回滚数据库事务
cursor()	获得cursor游标对象

        在数据库操作中，事务往往包含了读写的操作，如果把一个事务交给数据库管理系统，那么在当前事务的所有操作未执行完毕之前，该事务的所有数据都处在数据库中，如果有操作未完成就需要进行回滚操作，回到执行前的操作，上面的几种方法在我们是用数据库的过程中会经常遇到。

    2. 游标对象

        游标（Cursor）是处理数据的一种方法，为了查看或者处理结果集中的数据，游标提供了在结果集中一次一行或者多行前进或向后浏览数据的能力。游标在数据库管理系统中十分重要，创建游标对象的方法有多种，下面我们来介绍游标属性及方法。

        游标属性有：

        Description:数据库类型和值的相关信息。

        Rowcount:返回结果的行数统计信息。

        游标方法如下图：

方法	功能
execute(sql[, parameters])	执行一条SQL语句
executemany(sql, seq_of_parameters)	用于批量执行SQL语句
fetchone()	捕获查询结果集的下一行，返回单个序列，在没有更多数据可用时不返回
fetchmany(size=cursor.arraysize)	搜索查询结果的下一组行，返回列表。当不可用行时，将返回一个空列表。
fetchall()	捕获查询结果的所有（剩余）行，返回列表。光标的数组大小属性可能会影响此操作的性能。如果没有行，则返回空列表。
close()	关闭当前游标
Callproc(procname,[,parameters])	执行存储过程，procname为存储过程名，parameters为存储过程提供的参数
connection.rollback()	回滚从上一次调用 commit() 之后对数据库所做的所有变动
cursor.executescript(sql_script)	接收到脚本会执行多个 SQL 语句，它首先执行 COMMIT 语句，然后执行作为参数传入的 SQL 脚本
connection.executescript(sql_script)	这是一个非标准的快捷方式，通过调用游标（）方法创建游标对象，使用给定的sql_script调用游标的executescript（）方法，并返回游标。
connection.total_changes()	返回自数据库连接打开以来被修改、插入或删除的数据库总行数

    3. 总结

        本节我提到的这些是数据库操作中比较常用的方法，在遇到相关问题的时候可以回头翻看本节内容，下面两节我们先来学一种嵌入式数据库SQLite，我们通过接口连接这种数据库也可以进行相关操作。

        

本文固定URL：https://www.dotcpp.com/course/307

 SQlite数据库(1)

  在学习MySQL模块的操作之前，我们先来学习一个名为SQLite的Python内置的数据库模块，SQLite是一种嵌入式数据库，而不是一个客户端的数据库引擎，所以它是一款轻型的遵守ACID的关系型数据库管理系统，它存在于一个相对小的C库中，经常被集成到各种应用程序中，这两节我们就来学习使用SQLite。

    1. 创建数据库文件流程

        Python中内置了SQLite3模块，我们可以直接使用import导入。

        我们在操作数据库的时候流程一般如下：

        

    2. 创建数据库文件和表

        连接SQLite数据库的方式为sqlite.connect(‘数据库名’)，我们通过一个例子来看一下,代码如下：

1 2 3 4 5 6 7 8 9 10 11 12

import sqlite3 con = sqlite3.connect('test.db') print('创建数据库文件成功') cursor =  con.cursor()#创建一个游标 cursor.execute('''#执行SQL语句                 create table user( #创建一个表                 id     int(8) primary key,                 name   varchar(18),                 age    int(8))                 ''')#执行SQL语句 cursor.close() con.close()

        执行之后：

        输出结果为：

1

创建数据库文件成功

        我们可以看到创建好的数据库文件，如果创建了已存在的user表将会出现错误信息。我们在创建的时候使用通常是使用单引号，这里为了方便大家理解使用三引号方式，我们在创建列表项的时候需要给出姓名和数据类型，主键能帮助我们保证数据的唯一性，当数据不易区分的时候我们需要引入主键，像学生信息这样的表单，我们要设置学号为主键来区分信息，即使出现姓名相同的学生，但也可以通过唯一的主键来区分他们。

    3. 插入操作

        在创建了表之后我们肯定要往表中添加数据，添加数据的语法格式为：

        con.execute("insert into user (id,name,age) values (1001, '李华', 21,)")

        注意插入的数据类型一定要和我们在创建表的时候一一对应，也就是id对应1001，name对应李华，age对应21。

        代码如下：

1 2 3 4 5 6 7 8 9 10

import sqlite3 con = sqlite3.connect('test.db') cursor =  con.cursor() con.execute("insert into user (id,name,age) values (1001, '李华', 21)") con.execute("insert into user (id,name,age) values (1002, '小明', 20)") con.execute("insert into user (id,name,age) values (1003, '小张', 21)") print('插入数据成功') cursor.close() con.commit() con.close()

        输出结果为：

1

插入数据成功

        如果我们重复运行当前代码，会出现如下错误，错误提示为存在了相同的信息，我们可以看出已经完成插入。

1 2 3 4

Traceback (most recent call last):   File "C:/Users/轻烟/PycharmProjects/untitled1/Akgirutgnm/test.py", line 4, in <module>     con.execute("insert into user (id,name,age) values (1001, '李华', 21)") sqlite3.IntegrityError: UNIQUE constraint failed: user.id

本文固定URL：https://www.dotcpp.com/course/308

SQlite数据库(2)

    1. 查看数据

        查看表中的数据使select语句，它的语法格式为：

1	cursor = con.execute("select 字段1,字段2...(id,name...). from 表名(user) where 查询条件")

        我们在查询数据的时候可以通过多种方式：

        1) 使用fetchone()方法来查询结果中的下一条信息

        2) 使用fetchmany(size)方法来获取size数量的记录

        3) 使用fetchall()方法来查看所有记录

        代码如下：

1 2 3 4 5 6 7 8 9 10 11 12 13 14 15

import sqlite3 con = sqlite3.connect('test.db') cursor =  con.cursor() cursor.execute('select * from user') print('第一种方式：') print(cursor.fetchone()) print(cursor.fetchone()) print(cursor.fetchone()) print('第二种方式：') print(cursor.fetchmany(2)) print('第三种方式：') print(cursor.fetchall()) cursor.close() con.commit() con.close()

        注意使用每一种方式的时候把其它两种方式删除掉，输出结果为：

1 2 3 4 5 6 7

第一种方式：  (1001, '李华', 21)  (1002, '小明', 20)  (1003, '小张', 21)  第二种方式：  [(1001, '李华', 21), (1002, '小明', 20)]  第三种方式：[(1001, '李华', 21), (1002, '小明', 20), (1003, '小张', 21)]

        第一种方式是依次查询，第二种方式指定了查询数量为2，第三种方式为全部查询。

        下面我们再介绍一种方式：

1 2 3 4 5 6 7 8 9

import sqlite3 con = sqlite3.connect('test.db') cursor =  con.cursor() cursor.execute('select * from user') for row in cursor:    print ("id = ", row[0],"name = ", row[1],"age = ", row[2]) cursor.close() con.commit() con.close()

        输出结果为：

1 2 3

id =  1001 name =  李华 age =  21 id =  1002 name =  小明 age =  20 id =  1003 name =  小张 age =  21

        这种方式我们可以通过索引获得每个元素对应的值。

    2. 修改数据

        修改数据使用update方法，语法格式为：

1	con.execute("update 表名(user) set 字段名(age) = 字段值(21) where 查询条件(id=1002)")

        我们通过下面代码来看一下：

1 2 3 4 5 6 7 8 9

import sqlite3 con = sqlite3.connect('test.db') cursor =  con.cursor() cursor.execute('select * from user') for row in cursor:    print ("id = ", row[0],"name = ", row[1],"age = ", row[2]) cursor.close() con.commit() con.close()

        输出结果为：

1 2 3

id =  1001 name =  李华 age =  21 id =  1002 name =  小明 age =  20 id =  1003 name =  小张 age =  21

        我们可以通过输出结果看出数据已经被修改。

    3. 删除数据

        删除数据的方式为：

1	con.execute("delete from 表名(user) where 条件(id = 1002)")

        我们通过代码看一下：

1 2 3 4 5 6 7 8 9 10

import sqlite3 con = sqlite3.connect('test.db') cursor =  con.cursor() con.execute("delete from user where id = 1002;") cursor.execute('select * from user') for row in cursor:    print ("id = ", row[0],"name = ", row[1],"age = ", row[2]) cursor.close() con.commit() con.close()

        输出结果为：

1 2	id =  1001 name =  李华 age =  21 id =  1003 name =  小张 age =  21

        可以看出id为1002的数据已经被删除。

    4. 总结

        通过这两节的学习，我们来回顾一下操作流程：

        1) 首先要连接到数据库文件

        2) 然后创建一个游标来进行相关操作

        3) 如果是增加数据一定要注意主键的唯一性，不能插入相同的主键信息

        4) 如果是删除数据时，在选择条件的时候，会删除满足条件的所有项

        5) 查看数据的时候有多种方式可选择

        6) 关闭游标

        7) 提交事务

        8) 关闭连接

 

PyMySQL数据库的使用

     前面我们学习了SQLite数据库的相关内容，我们也通过前面的内容学习到了数据库的相关操作，PyMySQL数据库的操作和前面的内容类似，但我们可以通过Navicat直接来查看数据库中的内容，实现数据可视化，前面我们已经完成了下载安装，本节我们直接进入学习。

    1. 建立数据连接

        我们要使用数据库就首先要建立数据连接，连接的时候我们要使用到connect()函数，这个函数中有很多参数，我们先通过例子来看一下它的用法：

1 2 3 4

import pymysql con = pymysql.connect(host = 'localhost', user = 'root',password = '123456',database ='python' ,charset = 'utf8',port = 3306) print('连接成功')

        输出结果为：

1

连接成功

        参数的介绍如下：

        host：数据库主机名或IP地址

        port：连接数据库的端口号

        user：访问数据账号

        passwprd或passwd：数据库密码

        database或db：数据库中的库名

        charset：编码方式
我们输入的信息一定要和连接的数据库信息保持一致，同时要使数据库处于连接状态。

    2. 创建数据表

        连接数据库的方式我们在前面已经介绍过，在这里我们直接来通过PyMySQL来创建一个表，它的语法结构和前面类似，我们直接进行使用，看下面代码：

1 2 3 4 5 6 7 8 9 10 11 12 13 14

import pymysql con = pymysql.connect(host = 'localhost', user = 'root',password = '123456', database ='python',charset = 'utf8',port = 3306) print('连接成功') cursor =  con.cursor()#创建一个游标 cursor.execute("""create table users                 ( id varchar(8) primary key,                   name varchar(18),                   age varchar(8),                   gender varchar(2),                    class varchar(2))                """) cursor.close() con.close()

        我们运行之后可以在我们的数据库中刷新一下来查看当前表。

        我们可以右键users然后选择设计表来查看表结构或者更改。

    3. 增加数据

        关于MySQL数据库的操作方式和SQLite基本一样，我们在再通过一个例子来学习一下它的使用方式：

        代码如下：

1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 25 26 27 28 29 30 31 32 33 34 35 36 37 38

import pymysql con = pymysql.connect(host = 'localhost', user = 'root',password = '123456',database ='python', charset = 'utf8',port = 3306) print('连接成功->') cursor =  con.cursor()#创建一个游标 print('开始创建表->') cursor.execute("""create table users                 ( id char(8) primary key,                   name char(18),                   age char(8),                   gender char(2),                    class char(2))                """) print('完成表的创建,开始插入数据->')#下面开始插入数据 cursor.execute("insert into users(id,name,age,gender,class) values('1001','李华','21','男','1')") cursor.execute("insert into users(id,name,age,gender,class) values('1002','小明','21','男','1')") cursor.execute("insert into users(id,name,age,gender,class) values('1003','小张','20','女','2')") print('插入数据完成,开始查看数据->') cursor.execute('select * from users')#查询数据 for row in cursor:    print ("id = ", row[0],"name = ", row[1],"age = ", row[2],"gender = ",row[3],    "class = ",row[4]) print('查看数据完成,开始修改数据->')#开始修改数据 cursor.execute("update users set age = 21 where id=1003") cursor.execute('select * from users')#查询数据 for row in cursor:    print ("id = ", row[0],"name = ", row[1],"age = ", row[2],"gender = ",row[3],    "class = ",row[4]) print('更改数据完成,开始删除数据->')#开始删除数据 cursor.execute("delete from users where id = 1002;") print('删除数据完成,最后数据为->') cursor.execute('select * from users')#查询数据 for row in cursor:    print ("id = ", row[0],"name = ", row[1],"age = ", row[2],"gender = ",row[3],    "class = ",row[4]) cursor.close() con.commit() con.close()

        输出结果为：

1 2 3 4 5 6 7 8 9 10 11 12 13 14 15

连接成功-> 开始创建表-> 完成表的创建,开始插入数据-> 插入数据完成,开始查看数据-> id =  1001 name =  李华 age =  21 gender =  男 class =  1 id =  1002 name =  小明 age =  21 gender =  男 class =  1 id =  1003 name =  小张 age =  20 gender =  女 class =  2 查看数据完成,开始修改数据-> id =  1001 name =  李华 age =  21 gender =  男 class =  1 id =  1002 name =  小明 age =  21 gender =  男 class =  1 id =  1003 name =  小张 age =  21 gender =  女 class =  2 更改数据完成,开始删除数据-> 删除数据完成,最后数据为-> id =  1001 name =  李华 age =  21 gender =  男 class =  1 id =  1003 name =  小张 age =  21 gender =  女 class =  2

        数据库中的信息也保持一致，通过这个例子我们使用了前两节所学习到的内容，依次进行了增加、更改及删除操作。