【20天搞定Python爬虫】第六天：让Python帮你搞定MySQL数据库

千锋python

2021年04月30日 18:50

收录于文集

共23篇

【千锋教育干货暴击】

如果你想更好的学习python乃至转行，弯道超车，快人一步！本课程零基础即可加入学习，抓住大数据、机器学习、人工智能时代的红利，开启你的第一行代码吧！

↓ ↓ ↓

千锋Python全套视频教程（700集）

千锋教育Python教程全套_python零基础入门到精通（学完可达到Python工程师水平）

宋宋讲Python第六天：让Python帮你搞定MySQL数据库

音乐

千锋python

本篇文章我们介绍MySQL的使用，我们介绍的思路是这样的：

介绍MySQL数据库
MySQL数据库的基本操作
pymysql操作mysql数据库

MySQL数据库

数据库就是存储数据的仓库，其本质是一个文件系统，数据按照特定的格式将数据存储起来，用户可以对数据库中的数据进行增加，修改，删除及查询操作。但是如果访问这样的存储数据的仓库呢？使用数据库管理系统(DataBase Management System，DBMS)，它是一种操作和管理数据库的大型软件，用于建立、使用和维护数据库，对数据库进行统一管理和控制，以保证数据库的安全性和完整性。MySQL就是一个数据库管理系统。

用户通过数据库管理系统访问和操作库中表内的数据，下图展示Python程序、DBMS、DB和表的关系

MySQL数据库数据库的安装本文不做具体的阐述，如果大家想安装MySQL可以自行找“度娘”。

SQL语句

结构化查询语言(Structured Query Language)简称SQL，是一种数据库查询和程序设计语言，用于存取数据以及查询、更新和管理关系数据库系统。

SQL分类：

数据定义语言：简称DDL(Data Definition Language)，可以对数据库，表，列等结构进行改变的语句。关键字：create，alter，drop

数据查询语言：简称DQL(Data Query Language)，可以查询数据库中表记录的语言。关键字：select，from，where，order by等

数据操作语言：简称DML(Data Manipulation Language)，可以对表中的记录进行添加、删除、修改操作的语句。关键字：insert，delete，update

数据控制语言：简称DCL(Data Control Language)，用来定义数据库的访问权限和安全级别，及创建用户，关键字：grant，revoke

常规语法：

 代码块
JavaScript
自动换行
复制代码
MySQL数据库的SQL语句不区分大小写，建议使用大写
SQL语句可以单行或多行书写，以分号结尾
可使用空格和缩进来增强语句的可读性
使用 -- 两个横杠表示单行注释，使用/**/ 进行多行注释复制成功

MySQL数据库的基本操作

数据库操作

查看所有数据库：

show databases;

创建数据库： create database dbname;如创建一个数据库shop语句为：create database shop;

使用数据库:

use dbname；

如：use shop；

查看正在使用的数据库：

select database();

删除数据库：

drop database dbname;

如删除shop数据库语句为：drop database shop;

表的操作

查看所有表

show tables;

创建表

create table table_name( 字段名数据类型可选的约束条件）；

其中字段名要求做到见名知意，常用的数据类型如下：

SQL常用数据类型：

 代码块
JavaScript
自动换行
复制代码
int 普通大小的整数，4字节，当然整型的类型还有tinyint，smallint，bigint等等，保存的长度不同
float 单精度浮点数，4字节
double 双精度浮点数，8字节
decimal（m,d）也是浮点数 m表示数字总位数,d表示保留到小数点后d位，不足部分就添0，如果不设置m、d默认保存精度是整型
varchar(n) 可变字符类型
char(n) 固定长度字符类型. n为0~255之间的整数，固定长度为n，不足后面补全空格
date 日期 YYYY-MM-DD 1000-01-01~9999-12-3，3字节
time 时间 HH:MM:SS -838:59:59~838:59:59，3字节
datetime 日期时间 YYYY-MM-DD HH:MM:SS 1000-01-01 00:00:00~ 9999-12-31 23:59:59，8字节复制成功

约束条件即对表中数据的限制条件。表在设计的时候加入约束的目的就是为了保证表中的记录完整和有效。

MySQL中的约束有：

 代码块
JavaScript
自动换行
复制代码
非空约束(not null) 即用not null约束的字段不能为null值，如果出现null值则添加失败，因此必须给定具体的数据
唯一性约束(unique) 即在定义的时候使用unique约束的字段，此字段具有唯一性，不可重复，但可以为null。如果添加数据的时候出现重复的数据，则添加失败。
默认值约束(default) 即给表的字段添加一个默认值，如果该字段的值不添加，则默认使用该值。
主键约束(primary key) PK 表设计时一定要有主键，有主键的意思就是unique+not null 的综合，但是又是有所区别的。主键约束除了可以做到&#34;not null unique&#34;之外，还会默认添加&#34;索引——index&#34;
外键约束(foreign key) FK 外键主要是在两个表之间有关系的时候添加，一张表中添加数据的时候会参照另一张表是否存在。复制成功

在Oracle中还支持检查约束，但是在MySQL中不支持这个约束。

比如创建一个商品表

 代码块
JavaScript
自动换行
复制代码
CREATE TABLE GOODS (
  id INT PRIMARY KEY, #商品ID
  name VARCHAR(100) NOT NULL, #商品名称
  price FLOAT, # 商品价格
  number INT, # 商品数量
)复制成功

定义完毕之后我们还可以查看表结构，使用: desc 表名;

修改表

修改表分了四种，分别是：添加列、删除列、修改列的类型或者约束、修改列名

修改表添加列的格式：

alter table 表名 add 列名类型(长度) 约束;

为商品表添加一个新的字段为：weight varchar(20)

也可以使用修改表中某列的类型长度及约束。

如果想要修改列名，则使用来完成。

最后，如果某列我们不需要了也可以通过语句删除此列。格式是：【alter table 表名 drop 列名;】

但是数据库中表的结构建议不要轻易的改变，尤其是里面如果有了数据之后。而应该在添加数据之前就把结构给确定好了。

删除表

删除表的格式比较简单：

drop table 表名;

比如可以删除我们上面创建的goods表。使用drop table语句即：drop table goods;

增加表中数据

基本的语法结构是：

 代码块
JavaScript
自动换行
复制代码
基本语法
insert into 表名 [字段1,字段2,......] values (值1,值2,......); -- 插入单条记录
insert into 表名 [字段1,字段2,......] values (值1,值2,......), (值1,值2,......); -- 插入多条记录复制成功

比如上面的goods表我们向里面添加数据：

 代码块
JavaScript
自动换行
复制代码
insert into goods(id,name,price,num) values(1,&#39;乐事薯片&#39;,5.9,100);
insert into goods(id,name,price,num) values(2,&#39;喜之郎果冻&#39;,3.5,120),(3,&#39; 盼盼小面包&#39;,11.9,99);复制成功

其中select * from goods表示查询表中所有数据。

更新表中数据

基本语法如下：

 代码块
JavaScript
自动换行
复制代码
UPDATE 表名 SET 字段1=值1, 字段2=值2, ...... [WHERE 条件1 ......]复制成功

其中set后面的字段表示表中的任意想要更改的字段，而字段后面的值就是要改之后的新值。比如我们要将上面goods表中【乐事薯片】的库存变成150件，则语句如下：

 代码块
JavaScript
自动换行
复制代码
update goods set num = 150 where id=1;复制成功

为什么此处还要加一个where条件呢？因为如果没有where条件则会将表中num列下所有的库存数量变成150.所以我们要加上where条件进行限制id=1的num=150.

如果要将【盼盼小面包】的价格变成促销价：9.9，并且库存增加100件如何实现呢？

 代码块
JavaScript
自动换行
复制代码
update goods set num=num+100,price=9.9 where name=&#39;盼盼小面包&#39;;复制成功

删除表中数据

删除表中数据的基本语法：

 代码块
JavaScript
自动换行
复制代码
DELETE FROM 表名 [WHERE 条件1, 条件2, ...]
此处务必注意是删除表中的数据，而不是删除表结构，删除表结构是drop table复制成功

比如我们要将【喜之郎果冻】这条数据删除

 代码块
JavaScript
自动换行
复制代码
delete from goods where name = &#39;喜之郎果冻&#39;;复制成功

查询表中数据

查询表中数据其实是数据库操作很重要的内容，因为表中数据的查询方式很多，比如有where条件查询、排序查询、分组查询、连接查询等。

为了能更好的练习查询的使用，可以向goods表中添加多条商品记录。

 代码块
JavaScript
自动换行
复制代码
-- 表示查询表中所有字段的值，*代表的是所有字段
select * from goods复制成功

下面我们通过user表来给大家介绍查询：

结构：

基础查询

 代码块
JavaScript
自动换行
复制代码
select username,password  from user;
select usernname as 用户名, password as 密码  from user;  #可以给字段起别名
select *  from user; # 查询所有字段，慎用，一般不建议使用，会导致无法优化sql语句
select 2018,username,password  from user; #可以有常量，表达式
select username,2018-year(birthday) from user; #year是mysql的内置函数
select distinct username  from  user; #去除重复记录 distinct 针对查询结果去除重复记录，不针对字段复制成功

条件查询（where）

关系运算

关系运算符：> 、 >=、 <、 <=、 =、!=、<>、 between and

 代码块
JavaScript
自动换行
复制代码
select username,password from user where id &lt;10
select username,password from user where id != 10
select username,password from user where id between 10 and 20复制成功

逻辑运算

逻辑运算符：and 、or、not

 代码块
JavaScript
自动换行
复制代码
select username,password from user where id &lt; 100 and id &gt; 20;
select username,password from user where id &gt; 100 or id &lt; 20;复制成功

集合运算

集合运算符：in、not in

 代码块
JavaScript
自动换行
复制代码
select username,password form user where id in (2,3,4)
select username,password form user where id not in (2,3,4)复制成功

判空

判空运算：is null、is not null

 代码块
JavaScript
自动换行
复制代码
select username,password from user where username is null复制成功

字符串的模糊查询(like)

通配符 _代表一个字符，%代表任意长度字符串

select * from user where username like '王_'; select * from user where username like '王%';

排序（order by）

asc 升序(默认)、desc 降序、

限制结果集(limit)

limit n #取前n条记录

limit offset,n #从第offset条开始取，取n条

聚合函数

count统计结果集中记录数
max 最大值
min 最小值
avg 平均值，只针对数值类型统计
sum 求和，只针对数值类型统计
注意，集合函数不能直接使用在where后面的条件里，但可以在子查询中

 代码块
JavaScript
自动换行
复制代码
select count(*) num from user;
select count(distinct age) num from user; //去除重复记录
select * from user where age = max(age);//错误复制成功

分组（group by)

将结果集分组统计，规则：

出现了groub by的查询语句，select后面的字段只能是集合函数和group by后面有的字段，不要跟其它字段
对分组进行过滤，可以使用having

 代码块
JavaScript
自动换行
复制代码
select id, count(*) num from user group by sex;
select id,count(*) num from forum group by sex having count(*) &gt;=2;
having和where的区别：
    where针对原始表进行过滤
    having 是针对分组进行过滤复制成功

pymysql操作mysql数据库

安装pymysql

 代码块
JavaScript
自动换行
复制代码
pip install pymysql复制成功

pymysql操作数据库

连接数据库使用Connect方法连接数据库

 代码块
JavaScript
自动换行
复制代码
pymysql.Connections.Connection(host=None, user=None, password=&#39;&#39;, database=None, port=0,  charset=&#39;&#39;)
参数说明：
    host – 数据库服务器所在的主机。
    user – 登录用户名。
    password – 登录用户密码。
    database – 连接的数据库。
    port – 数据库开放的端口。（默认: 3306）
    charset – 连接字符集。
返回值：
   返回连接对象
   
例子：
link = pymysql.Connect(host=&#39;localhost&#39;, port=3306, user=&#39;root&#39;, password=&#39;123456&#39;, db=&#39;shop&#39;, charset=&#39;utf8&#39;)
复制成功

连接对象方法

2.创建游标

 代码块
JavaScript
自动换行
复制代码
cursor = link.cursor()
print(cursor.rowcount) #打印受影响行数复制成功

3.执行sql语句

 代码块
JavaScript
自动换行
复制代码
# 执行sql语句
sql = &#39;select * from user1&#39;
# 执行完sql语句，返回受影响的行数
num = cursor.execute(sql)复制成功

4.获取结果集

 代码块
JavaScript
自动换行
复制代码
result1 = cursor.fetchone()
print(result1)复制成功

5.关闭连接

 代码块
JavaScript
自动换行
复制代码
cursor.close()
link.close()复制成功

pymysql中事务处理

pymysql默认是没有开启自动提交事务，所以我们如果进行增、删、改，就必须手动提交或回滚事务。

 代码块
JavaScript
自动换行
复制代码
sql = &#39;delete from user where id=%s&#39; % user_id

# 如果要执行增删改语句的时候，下面的就是固定格式
try:
 cursor.execute(sql)
 # 如果全部执行成功，提交事务
 link.commit()
 print(cursor.lastrowid) #获取最后插入记录的自增id号
except Exception as e:
 print(e)
 link.rollback()
finally:
 cursor.close()
 link.close()复制成功

案例

使用pymysql向goods表中添加一条数据：

 代码块
JavaScript
自动换行
复制代码
from pymysql import *
 

def main():
  # 创建connection连接
  conn = connect(host=&#39;localhost&#39;, port=3306, database=&#39;shop&#39;, user=&#39;root&#39;,
          password=&#39;root&#39;, charset=&#39;utf8&#39;)
  # 获取cursor对象
  cs1 = conn.cursor()
  # 执行sql语句
  query = &#34;insert into goods(id,name,price,num) values(%s,%s,%s,%s)&#34;
  cs1.execute(query,(4,&#39;蒙牛酸奶&#39;,13.9,88))
 
  # 提交之前的操作，如果之前已经执行多次的execute，那么就都进行提交
  conn.commit()
 
  # 关闭cursor对象
  cs1.close()
  # 关闭connection对象
  conn.close()
 
 
if __name__ == &#39;__main__&#39;:
  main()复制成功

当然也可以删除、查询、修改表中的数据，但是无论是怎么操作，都需要创建连接并在结束的时候关闭连接对象。

需要资料也可以关注微信公众号：Python专栏，事不宜迟，一起进步吧！

python基础 python爬虫 python自学千锋python

cv11097778

分享至

投诉或建议