创意电子

标题: 「Python从零到壹」八.数据库之MySQL和Sqlite底子知识及操作详解 [打印本页]

作者: 林夕编程 时间: 2021-8-28 18:00
标题: 「Python从零到壹」八.数据库之MySQL和Sqlite底子知识及操作详解

数据库（Database）是按照数据结构来构造、存储和管理数据的仓库，在数据库管理体系中，用户可以对数据举行新增、删除、更新、查询等操作，从而转变为用户所必要的各种数据，并举行灵魂的管理。前面介绍的Python网络数据爬取，得到的语料通常采用TXT文本、Excel或CSV格式举行存储的，而本章节报告了如何将爬取的数据存储至数据库中，从而更方便数据分析和数据统计。本章将重点介绍MySQL数据库相关知识及Python操作MySQL的方法。
一.MySQL数据库

数据库技术是信息管理体系、自动化办公体系、销售统计体系等各种信息体系的焦点部分，是举行科学研究和决策管理的重要技术手段，常用的数据库包括：Oracle、DB2、MySQL、SQL Server、Sybase、VF等。由于MySQL数据库具有性能优良、稳定性好、配置简单、支持各种操作体系等优势，本章主要报告Windows体系下Python操作MySQL数据库的内容。
1.MySQL的安装与配置

起首，打开欣赏器输入MySQL官方网址“https://www.mysql.com/” ，点击“DOWNLOADS”菜单，显示如图1所示的下载页面。

接着找到MySQL软件的下载页面，网址如下，点击“Download”按钮下载相关软件，如下图所示的 mysql-installer-community-5.7.21.0.msi。同时，读者也可以直接在百度搜索“MySQL”软件下载安装。

https://dev.mysql.com/downloads/installer/

最新已更新至MySQL 8.0版本。

图形化安装过程如下，作者联合数据库开辟及现实安装履历举行叙述，读者如果想体系地学习MySQL数据库，推荐《深入浅出MySQL》这本书给大家。
(1) 双击MySQL安装文件setup.exe，进入MySQL安装欢迎界面，如图3所示，点击“Next”举行安装。

(2) 紧接着进入“Setup Type”界面，选择MySQL安装范例，这里选择“Typical”范例，如图4所示。
其中Typical表示安装常用的组件，默认安装到C盘“Program Files\MySQL”文件夹下，推荐读者选择此安装套件；Complete表示安装全部的组件；Custom表示根据用户自定义举行安装组件，可以更改默认的安装路径，此范例更为机动。

(3) 然后点击“Next”按钮进入下一个安装步骤，选择安装目录和“Developer Components”组件如图5所示。

(4) 在安装过程中，通常选择默认选项，点击“Next”按钮进入下一步。同时，读者也可以根据自己的电脑环境及喜欢举行配置，图6选择手动准确配置（Detailed Configuration）。

(5) 继续点击“Next”按钮进入下一步，在MySQL应用范例选择界面中，提供了三种方式：

Developer Machine（开辟机），使用最小数量的内存。
Server Machine（服务器），使用中等大小的内存。
Dedicated MySQL Server Machine（专用服务器），当前可用的最大内存。

这里，作者选择服务器范例（Server Machine），如图7所示。

(6) 点击“Next”下一步按钮，进入数据库用途选择界面，这里作者选择“Multifunctional Database”，它表示多功能数据库，此选项对变乱性和非变乱性存储引擎的存取速度都很快。

(7) 然后点击“Next”按钮，进入并发毗连设置页面，选择“Decision Support(DSS)/ OLAP”，它表示决策支持体系，设置数据库访问量毗连数为15（默认），如图9所示。

(8) 设置MySQL端口号为3306（默认），再点击下一步。其中MySQL调用的端口号为3306，如图10所示。

(9) 设置编码方式为utf-8（中文编码），如图11选择字符集“Character Set”为utf8。

读者必要注意，软件开辟过程中的编码乱码问题是一个常见的典型问题，尤其是处理中文字符，而其解决方法的焦点思想是将全部开辟环境的编码方式设置为一致，通常将数据库、Python、HTML源码、前端欣赏器等编码方式都配置成UTF-8中文编码方式。其中MySQL数据库设置编码方式为utf-8的过程如下图所示，否则数据库存储或查询中文汉字时，可能出现乱码错误。

(10) 点击“Next”按钮，进入Windows选项设置界面，再单击“Next”按钮进入安全选项配置界面，超级用户root的密码通常设置为“123456”，如下图12所示。

(11) 点击“Next”按钮，进入准备执行界面，等待MySQL安装配置，当全部的选项都打上勾显示乐成的时候表示MySQL安装乐成，如图13所示，末了点击“Finish”完玉成部安装。

(12) 至此，MySQL数据库安装完成，Windows的“全部步伐”菜单中已经多了“MySQL 5.0”等选项。
2.SQL语句详解

数据库中最重要的就是SQL语句，它是结构化查询语言，英文Structure Query Language的缩写，SQL是使用关系模子的数据库应用语言。在MySQL安装乐成后，我们将详细介绍SQL语句，并通过MySQL软件介绍SQL语句的基础用法及对应代码。
SQL语句主要划分为三种种别。

DDL（Data Definition Language）语句

数据定义语言。该语句定义了不同的数据字段、数据库、数据表、列、索引等数据库对象。常用语句关键字包括create、drop、alter等。

DML（Data Manipulation Language）语句

数据库利用语句。该语句用于插入、删除、更新和查询数据库的记录，是数据库操作最常用的语句，并检查数据完整性。常用的语句关键字包括insert、delete、update和select。

DCL（Data Control Language）语句

数据控制语句。该语句用于控制不同数据字段的允许和访问级别，定义了数据库、表、字段、用户的访问权限和安全级别。常用的语句关键字包括grant、revoke等。
下面联合我们安装的MySQL软件具体的讲解SQL语句的用法。
起首，运行MySQL并输入默认的用户密码“123456”，如图14所示。

(1) 显示数据库
输入“show databases”语句，查看当前MySQL数据库中存在的全部数据库，如果某个数据库已经存在，则可以使用use语句直接使用；如果数据库不存在，则第一次必要使用create语句创建数据库。
mysql> show databases;+---------------------------------+| Database |+---------------------------------+| information_schema || mysql || performance_schema || test || test01 || yxz |+---------------------------------+6 rows in set (0.03 sec)

(2) 使用数据库
如果想直接使用已经存在的数据库test01，则直接使用如下语句。
mysql> use test01;Database changed

(3) 创建数据库
创建数据库语句是：

create database 数据库名字

如果想创建新的数据库，使用create关键字创建。创建乐成后，再调用use关键词选择该数据库举利用用，代码如下：
mysql> create database bookmanage;Query OK, 1 row affected (0.00 sec)mysql> use bookmanage;Database changed

图15显示创建数据库“bookmanage”图书管理体系及选择数据库。

这里同样可以使用“show tables”语句显示该数据库中全部存在的表，但是目前还没有一张表，故返回“Empty set”。
(4) 创建表
创建表使用：

create table 表名 (字段名字段范例约束条件…)

比方创建books图书表，包括图书编号bookid、图书名称bookname、价格price、图书日期bookdate等字段。代码如下所示：
create table books(bookid int primary key,bookname varchar(20),price float,bookdate date);

其中创建的表名称为books，图书编号为int范例，同时为主键（primary key），用于唯一标识表的字段；图书名称位varchar范例，长度为20；价格为浮点型float；图书日期为date范例。详细介绍推荐大家课后去学习，这里只是入门讲解。

(5) 查看表信息
如果想查看当前数据库存在多少张表，则使用show关键字。
mysql> show tables;+---------------------------------+| Tables_in_bookmanage |+---------------------------------+| books |+---------------------------------+1 row in set (0.00 sec)

当前仅存在一张表books。如果想查看某张表的定义，则使用desc关键字。
desc books;

运行结果如下图17所示，显示了图书表的详细信息。

desc下令可以查看表的定义，但是如果想查看表更全面的信息，则通过更深入的SQL语句，好比通过查看创建表的SQL语句。

(6) 删除表
如果想要删除表books，使用“drop”关键词。
drop table books;

(7) 插入语句
当数据库和表创建成果后，必要向表中插入数据，使用的insert关键字。
insert into 表名(字段1,字段2,...) values(值1,值2,...)好比向books图书表中插入信息，代码如下：
insert into books(bookid, bookname, price, bookdate)values('1', '平常的天下', '29.8', '2017-06-10');

使用select查询语句显示结果如图19所示，背面详细介绍select语句。

在执行insert插入过程中，如果省略全部字段，只必要values值逐一对应即可。
mysql> insert into books-> values('2', '活着', '25.0', '2017-06-11');

如图20所示，插入第二本书《活着》，select显示的结果为两本书的详细信息。

同理，如果只想插入某几个字段的数据，只必要对应一致即可，好比查询书序号和书名《钢铁是怎样炼成的》。
mysql> insert into books(bookid, bookname)-> values('3', '钢铁是怎样炼成的');

运行结果如图21所示，《钢铁是怎样炼成的》册本中，省略的字段价格（price）和日期（bookdate）字段显示为NULL空值。

(8) 查询语句
查询语句根本语法是：
select 字段 from 表名 [where 条件]该语句用于查询指定字段的数据，当字段为星号时，它用于查询表中的全部字段；where紧跟着查询条件，该参数可以省略。最简单的查询语句如下所示，将显示books表中的全部字段和数据，包括三本册本的详细信息。
mysql> select * from books;+--------+---------------------------------------------+--------+-------------------+| bookid | bookname | price | bookdate |+--------+---------------------------------------------+--------+-------------------+| 1 | 平常的天下 | 29.8 | 2017-06-10 || 2 | 活着 | 25 | 2017-06-11 || 3 | 钢铁是怎样炼成的 | NULL | NULL |+--------+--------------------------------------------+---------+--------------------+3 rows in set (0.00 sec)

如果想显示必要的字段，则可以用逗号分隔，如下所示：
select bookid,bookname,price from books;

显示结果如图22所示，其中bookdate字段没有显示出来。

如果必要增加查询条件，则使用where语句即可，好比查询编号大于1的册本、查询价格非空的册本，代码如下：
select bookid,bookname,price,bookdate from books where bookid>1;select bookid,bookname,price,bookdate from books where price is not null;

显示结果如图23所示，第一条语句显示编号为2和3的结果，第二条语句显示图书价格不为空的结果。

更多的查询语句希望读者自己去研究，包括排序、group by分组、子查询等，同时背面数据分析部分，会联合现实应用介绍数据库查询语句及可视化分析处理。
(9) 更新语句
更新语句使用update关键字，根本格式是：

update 表名 set 字段=新值 [where 条件]

比方将《活着》书名更新为《朝花夕拾》，代码如下：
update books set bookname=’朝花夕拾’ where bookname=’活着’;

运行结果如图24所示，其中图书序号为2的信息举行了更新。

(10) 删除语句
删除语句使用delete关键字，其根本格式是：

delete from 表名 where 条件;

比方将价格为空的数据删除，使用的条件是“where price is null”，代码如下：
delete from books where price is null;

运行结果如图25所示，可以看到第三本书《钢铁是怎样炼成的》已经被删除。

此时，MySQL数据库的基础知识就介绍完了，更多知识推荐读者下来自行补充学习，好比联合 Navicat for MySQL 工具可视化联合。下面将介绍Python调用MySQL及操作。

二.Python操作MySQL数据库

Python访问数据库必要对应的接口步伐，我们可以把接口步伐明白为Python的一个模块，它提供了数据库客户端的接供词您访问。本小节主要介绍了Python操作MySQL数据库，通过调用MySQLdb扩展包操作数据库。
1.安装MySQL扩展包

起首必要在Python环境下安装MySQL扩展包，有两种方法。
第一种方法：通过“pip install mysql”安装Python的MySQL库。

但是使用该方法可能会碰到一些错误，如“Microsoft Visual C++ 9.0 is required (Unable to find vcvarsall.bat)”或“_mysql.c(42) : fatal error C1083: Cannot open include file: ‘config-win.h’: No such file or directory”等，这些错误可能来自于驱动等问题。可以安装一个Micorsoft Visual C++ Compiler for Python 2.7包解决。请读者百度自行解决，通常为路径错误或版本必要升级。

第二种方法：从Python官网下载安装文件（推荐该方法）。
假设已经下载了一个whl文件，然后安装该文件。

https://www.lfd.uci.edu/~gohlke/pythonlibs/#mysqlclient
https://pypi.python.org/pypi/MySQL-python/

安装过程如下图所示。

同样，可以下载MySQL-python-1.2.3.win-amd64-py2.7.exe文件安装，如下图所示。

2.步伐接口DB-API
接着给大家介绍Python操作MySQL数据库的API接口。Python接口步伐肯定要遵守Python DB-API规范。DB-API定义了一系列必须的操作对象和数据库存取方式，以便为各种各样的底层数据库体系和不同的数据库接口步伐提供一致的访问接口。由于DB-API为不同的数据库提供了一致的访问接口，这让它在不同的数据库之间移植代码成为一件轻松的事变。
下面简单介绍DB-API的使用方法。
模块属性
一个DB-API模块的定义如表1所示。

毗连数据库函数
毗连数据库的函数是connect()函数，其生成一个connect对象，用于访问数据库。Connect()函数的参数如表2所列。

下面是Python导入MySQLdb扩展包，调用connect()函数毗连数据库的代码。
import MySQLdbconn = MySQLdb.connect(host='localhost', db='bookmanage', user='root',passwd='123456', port=3306, charset='utf8')

MySQLdb扩展包的connect对象常用方法如表3所示。

commit()、rollback()、cursor()方法对于支持变乱（Transaction）的数据库更有意义，变乱是指作为单个逻辑工作单元执行的一系列操作，要么完整地执行，要么完全地不执行，从而保证数据的完整性和安全性。
游标对象
上面说了connect()方法用于提供毗连数据库的接口，如果要对数据库操作那么还必要使用游标对象，游标对象的属性和方法如表4所示。

下面通过简单的示例举行讲解。
3.Python调用MySQLdb扩展包

前面我们创建了数据库“bookmanage”和表“books”，它们用来记录册本管理体系中的册本信息，接下来讲解怎样通过Python来显示。
(1) 查询数据库名称
起首，我们查看本地数据库中所包含的数据库名称，使用“show databases”语句。代码如下：
import MySQLdbtry:conn=MySQLdb.connect(host='localhost',user='root',passwd='123456',port=3306)cur=conn.cursor()res = cur.execute('show databases')print(res)for data in cur.fetchall():print('%s' % data)cur.close()conn.close()except MySQLdb.Error as e:print("Mysql Error %d: %s" % (e.args[0], e.args[1]))

调用 MySQLdb.connect() 访问用户root本地MySQL数据库，其默认密码为“123456”。然后调用cur.execute(‘show databases’)执行显示全部数据库的语句，返回结果通过循环获取，如图29所示。

如果本地数据库已经存在，并且读者忘记其数据库的名称，可以通过该方法查询数据库中所包含全部数据库，再毗连该数据库举行相关的操作。
(2) 查询表
这里我们必要查询“bookmanage”数据库中的册本表（books）内容，代码如下：
# coding:utf-8# By：Eastmount CSDNimport MySQLdbtry:conn = MySQLdb.connect(host='localhost',user='root',passwd='123456',port=3306, db='bookmanage', charset='utf8')cur = conn.cursor()res = cur.execute('select * from books')print('表中包含', res, u'条数据\n')for data in cur.fetchall():print('%s %s %s %s' % data)cur.close()conn.close()except MySQLdb.Error as e:print("Mysql Error %d: %s" % (e.args[0], e.args[1]))

代码通过connect()函数毗连数据库，通过cursor()函数定义游标，然后调用游标的 excute(‘select * from books’) 执行数据库操作，此处为查询操作，再通过fetchall()函数获取全部数据。其中查询语句为“select * from books”，查找books表中全部数据，输出结果如下：
>>>表中包含 2 条数据1 平常的天下 29.8 2017-06-102 朝花夕拾 25 2017-06-11>>>对应的MySQL中的结果是一致的，图30是对应的结果。

(3) 新建表
下面创建一张学生表，主要是调用commit()函数提交数据，执行create table语句操作。
# coding:utf-8# By：Eastmount CSDNimport MySQLdbtry:conn = MySQLdb.connect(host='localhost',user='root',passwd='123456',port=3306, db='bookmanage', charset='utf8')cur = conn.cursor()sql = '''create table student(id int not null primary key auto_increment,name char(30) not null,sex char(20) not null)'''cur.execute(sql)#查看表print('插入后包含表:')cur.execute('show tables')for data in cur.fetchall():print('%s' % data)cur.close()conn.commit()conn.close()except MySQLdb.Error as e:print("Mysql Error %d: %s" % (e.args[0], e.args[1]))

输出结果如下所示，包括册本表和学生表，其中学生表包括序号、姓名和性别。
>>>插入后包含表:booksstudent>>>

(4) 插入数据
插入数据也是定义好SQL语句，调用execute(sql)方法实现。焦点代码是：
cur.execute("insert into student values( '3', 'xiaoyang', '男')")

通常插入的新数据必要通过变量举行赋值，其值不是固定的，参考文件。
# coding:utf-8# By：Eastmount CSDNimport MySQLdbtry:conn=MySQLdb.connect(host='localhost',user='root',passwd='123456',port=3306, db='bookmanage', charset='utf8')cur=conn.cursor()#插入数据sql = '''insert into student values(%s, %s, %s)'''cur.execute(sql, ('3', 'xiaoyang', '男'))#查看数据print('\n插入数据:')cur.execute('select * from student')for data in cur.fetchall():print('%s %s %s' % data)cur.close()conn.commit()conn.close()except MySQLdb.Error as e:print("Mysql Error %d: %s" % (e.args[0], e.args[1]))

输出结果如下：
>>>插入数据:3 xiaoyang 男>>>

这里作者只报告了这几种常见的数据库操作，其他SQL语句类似，请读者下来自行阅读实践。接下来讲解Python操作轻量型数据集Sqlite3。
三.Python操作Sqlite3数据库

SQLite是一款轻型数据库，是一种遵守变乱ACID性子的关系型数据库管理体系，它占用的资源非常低，能够支持Windows/Linux/Unix等主流操作体系，同时能够跟许多步伐语言如C#、PHP、Java、Python等相联合使用。
SQLite可以使用Sqlite3模块与Python举行集成，Sqlite3模块是由Gerhard Haring编写的，提供了一个与DB-API 2.0规范兼容的SQL接口。读者可以直接使用Sqlite3模块，因为Python 2.5.x 以上版本默认自带了该模块。
Sqlite3使用方法和前面介绍的MySQLdb库类似，起首必须创建一个数据库的毗连对象，然后有选择地创建光标对象，再定义SQL语句举行执行，末了必要关闭对象和毗连。Sqlite3常用方法如下所示：

sqlite3.connect()：打开一个到SQLite数据库文件database的毗连。
connection.cursor()：创建一个cursor，将在Python数据库编程中用到。
cursor.execute(sql)：执行一个sql语句，注意sql 语句可以被参数化使用。
cursor.executescript(sql)：该例程一旦接收到脚本，会执行多个sql语句。sql语句应该用分号分隔。
connection.commit()：提交当前的变乱。
connection.rollback()：回滚至上一次调用commit()对数据库所做的更改。
connection.close()：关闭数据库毗连。
cursor.fetchone()：获取查询结果会合的下一行，返回一个单一的序列，当没有更多可用的数据时，则返回 None。
cursor.fetchmany()：获取查询结果会合的下一行组数据，返回一个列表。
cursor.fetchall()：获取查询结果会合全部的数据行，返回一个列表。

下面这段代码是Python操作Sqlite3的基础用法，其语法根本和前面报告的MySQLdb库类似。
#-*- coding:utf-8 -*-# By：Eastmount CSDNimport sqlite3#毗连数据库：如果数据库不存在则创建conn = sqlite3.connect('test6.db')cur = conn.cursor()print('数据库创建乐成.\n')#创建表 PEOPLE(编号,姓名,年龄,公司,薪水)cur.execute('''CREATE TABLE PEOPLE(ID INT PRIMARY KEY NOT NULL,NAME TEXT NOT NULL,AGE INT NOT NULL,COMPANY CHAR(50),SALARY REAL);''')print("PEOPLE表创建乐成.\n")conn.commit()#插入数据cur.execute("INSERT INTO PEOPLE (ID,NAME,AGE,COMPANY,SALARY) \VALUES (1, '小杨', 26, '华为', 10000.00 )");cur.execute("INSERT INTO PEOPLE (ID,NAME,AGE,COMPANY,SALARY) \VALUES (2, '小颜', 26, '百度', 8800.00 )");cur.execute("INSERT INTO PEOPLE (ID,NAME,AGE,COMPANY,SALARY) \VALUES (3, '小红', 28, '腾讯', 9800.00 )");conn.commit()print("数据插入乐成.\n")#查询操作cursor = cur.execute("SELECT id, name, age, company, salary from PEOPLE")print("数据查询乐成.")print("序号", "姓名", "年龄", "公司", "薪水")for row in cursor:print(row[0], row[1], row[2], row[3], row[4])print('')#更新操作cur.execute("UPDATE PEOPLE set COMPANY = '华为' where ID=2")conn.commit()print("数据更新乐成.")cursor = cur.execute("SELECT id, name, company from PEOPLE")for row in cursor:print(row[0], row[1], row[2])print('')#删除操作cur.execute("DELETE from PEOPLE where COMPANY='华为';")conn.commit()print("数据删除乐成.")cursor = cur.execute("SELECT id, name, company from PEOPLE")for row in cursor:print(row[0], row[1], row[2])print('')#关闭毗连conn.close()

输出结果如图31所示：

其步骤如下：

1.起首在本地创建了一个test6.db的数据库文件。
2.执行游标中的execute()函数，创建了PEOPLE表，包括编号、姓名、年龄、公司、薪水等字段，字段涉及各种数据范例。
3.执行插入数据库操作，注意必要调用conn.commit()函数提交执行。
4.执行查询操作，SQL语句为“SELECT id, name, age, company, salary from PEOPLE”，然后通过for循环获取查询的结果，显示“小杨”、“小颜”、“小红”的信息。
5.执行更新操作并查询数据结果，将编号为“2”的公司信息更改为“华为”。
6.执行删除操作，删除公司名称为“华为”的数据，末了剩下“小红”的信息。

更多数据库现实操作将在背面的数据分析章节和爬虫部分举行详细介绍，同时推荐读者深入研究Python操作数据库的知识，包括变乱、存储过程、触发器等内容。
四.本章小结

数据库是按照数据结构来构造、存储和管理数据的仓库，用户可以通过数据库来存储和管理所需的数据，包括简单的数据表格、海量数据等。数据库被广泛应用于各行各业，好比信息管理体系、办公自动化体系、各种云信息平台等。
本章为什么要介绍Python操作数据库知识呢？

一方面，数据爬取、数据存储、数据分析、数据可视化是密不可分的四个部分，当我们爬取了相关数据之后，必要将其存储至数据库中，这能够更加标准化、智能化、自动化、便捷地管理数据，也为后续的数据分析提供强大的技术支撑，能够自定义提取所需数据块举行分析；
另一方面，数据库为数据共享，实现数据会合控制，保证数据的一致性和可维性提供保障。所以学习Python操作数据库是非常必要的，也希望读者认真学习本章内容，把握Python操作MySQL数据库、Sqlite3轻量型数据库知识，并应用到自己的科研项目中去。

该系列全部代码下载地址：

https://github.com/eastmountyxz/Python-zero2one

末了

更多参考精彩博文请看这里：@林夕编程
喜欢小编的小同伴可以加个关注、点个赞哦，一连更新嘿嘿！

————————————————

版权声明：本文为CSDN博主「Eastmount」的原创文章，遵循CC 4.0 BY-SA版权协议，转载请附上原文出处链接及本声明。
原文链接：https://blog.csdn.net/Eastmount/article/details/114331974

欢迎光临创意电子 (https://wxcydz.cc/)