Python连接MySQL及查询实战

@TOC

1Python DB-API

1.1概述

• Python 标准数据库接口为 Python DB-API，Python DB-API为开发人员提供了数据库应用编程接口。
• PyMySQL 是在 Python3.x 版本中用于连接 MySQL 服务器的一个实现库，Python2中则使用mysqldb。
• PyMySQL 遵循 Python 数据库 API v2.0 规范，并包含了 pure-Python MySQL 客户端库。

1.2安装PyMySQL

• 在使用 PyMySQL 之前，我们需要确保 PyMySQL 已安装。
• PyMySQL下载地址：https://github.com/PyMySQL/PyMySQL。
• 如果还未安装，我们可以使用以下命令安装最新版的 PyMySQL： pip install PyMySQL

1.3连接数据库

• 数据库准备，连接数据库之前，请确保已经创建了python数据库，以及students表
• 创建Connection对象：用于建立与数据库的连接

from pymysql import * 
# 导入pymysql模块 
# 创建连接对象 Connection对象 
# host:数据库主机地址 
# user:数据库账号 
# password：数据库密码 
# database : 需要连接的数据库的名称 
# port: mysql的端口号 
# charset: 通信采用编码格式 
conn = connect(host='127.0.0.1', user='root', password='mysql', database='python', port=3306, charset='utf8')

Connection 连接对象拥有的方法

• close 关闭连接，连接数据库跟打开文件一样，操作完成之后需要关闭，否则会占用连接。
• commit()提交，pymysql 默认开启事物，所以每次更新数据库都要提交
• rollback()回滚，事物回滚
• cursor()返回Cursor对象，用于执行sql语句并获得结果

获取cursor对象

cur = conn.cursor() # cursor对象用于执行sql语句

cursor对象拥有的方法

• close()关闭cursor对象
• execute(operation [, parameters ])执行语句，返回受影响的行数，可以执行所有语句
• fetchone()获取查询结果集的第一个行数据，返回一个元组
• fetchall()执行查询时，获取结果集的所有行，一行构成一个元组，再将这些元组装入一个元组返回

插入数据

res = cur.execute('insert into students VALUES (0,"白里守约",0,"广州",2,18);’) 
print(res) # 查看受影响的行数 
conn.commit() # 提交事物

查询数据：

cur.execute('select * from students;') # 执行sql语句，select * from students; 
res = cur.fetchone() # 获取结果集的第一条数据 
res1 = cur.fetchall() # 获取结果及的所有数据 
print(res) # 将获取的查询结果打印 
print(res1)

• 执行sql语句参数化，参数化sql语句中使用%s占位。
• execute(operation [parameters])
• 执行语句，返回受影响的行数，可以执行所有语句 [parameters] 参数列表

sql = 'select * from students where id=%s and gender= %s;' # sql语句中使用%s占位	
#执行sql语句					
cur.execute(sql,[15,0])

实例：

from pymysql import *
#创建数据库的连接
conn=connect(host='192.168.117.128',user='root',password='111111',
             database='stuDB',charset='utf8')
#创建一个游标对象 可以利用这个对象进行数据库的操作
try:
    cur=conn.cursor()
    insertsql='''
    insert into student(id,name,hometown) values (66,'钱之坑','北京市')
    '''
    cur.execute('select * from student where id=%s',[66])
    # conn.commit()
    res=cur.fetchall()
    for item in res:
        print('姓名;{0} 地址{1}'.format(item[1],item[3]))
    print(res)
    #print('sucess')
except Exception as ex:
    print(ex)
finally:
    cur.close()
    conn.close()

2查询实战

2.1准备数据

创建表

create table goods( 
id int unsigned primary key auto_increment not null, 
name varchar(150) not null, 
cate varchar(40) not null, 
brand_name varchar(40) not null, 
price decimal(10,3) not null default 0 
);

使用insert语句，插入多条数据

insert into goods values(0,' Apple MacBook Air 13.3英寸笔记本电脑','笔记本','苹果','6588’); 
insert into goods values(0,'联想(Lenovo)拯救者R720 15.6英寸大屏','笔记本','联想','6099’); 
insert into goods values(0,'法国酒庄直采原瓶原装进口AOC级艾落干红葡萄酒','红酒','法国','499’); 
insert into goods values(0,'x550cc 15.6英寸笔记本','笔记本','华硕','2799’); 
insert into goods values(0,'清扬(CLEAR)洗发水','洗发水','清扬','35’); 
......

2.2查询

查询goods表中所有的商品

select * from goods;

查询所有产品的平均价格,并且保留两位小数

select round(avg(price),2) as avg_price from goods;

通过子查询来实现，查询所有价格大于平均价格的商品，并且按价格降序排序

select id,name,price from goods 
where price > (select round(avg(price),2) as avg_price from goods) 
order by price desc;

查询所有 “联想” 的产品

select * from goods where brand_name='联想';

查询价格大于或等于”联想”价格的商品，并且按价格降序排列

select id,name,price from goods  where price >= any(select price from goods where brand_name = '联想’)  order by price desc;

查询每个产品类型的最低价格的，通过cate字段进行分组。

select cate,min(price) from goods group by cate;

查询价格区间在4500-6500之间的笔记本

select * from goods where price between 4500 and 6500 and cate='笔记本';

2.3查询数据分表

创建一个商品表

create table if not exists goods_cates(

cate_id int unsigned primary key auto_increment, 
cate_name varchar(40) 
);

1、查询goods表中所有的商品，并且按”类别”分组

select cate from goods group by cate;

2、将分组后的结果写入到刚才创建的表中

insert into goods_cates (cate_name) select cate from goods group by cate;

3、通过goodscates数据表来更新goods表，将goods表中的cate字段，修改成goodscates的id字段

update goods as g inner join goods_cates as c on g.cate = c.cate_name 
set cate = cate_id;

4、字段 brand_name 进行分表。

create table if not exists goods_brands( 
brand_id int unsigned primary key auto_increment, 
brand_name varchar(40) 
); 

insert into goods_brands(brand_name) select brand_name from goods group by brand_name;

5、通过goodsbrands数据表来更新goods表，将goods表中的barndname字段，修改成goods_brands的id字段

update goods as g inner JOIN goods_brands as j on g.brand_name=j.brand_name 
set g.brand_name=j.brand_id;

6、查看goods表结构，发现 cate， 、brand_name 两个字段都是varchar字段，需要修改成int类型字段。

desc goods; 
alter table goods change cate cate_id int unsigned not null, 
change brand_name brand_id int unsigned not null;

7、通过左连接查询所有商品的信息

select id,name,cate_name,brand_name,price from goods as g 
left join goods_cates as c on g.cate_id = c.cate_id 
left join goods_brands as b on g.brand_id = b.brand_id;

8、通过右连接查询所有商品的信息

select id,name,cate_name,brand_name,price from goods as g 
right join goods_cates as c on g.cate_id = c.cate_id 
right join goods_brands as b on g.brand_id = b.brand_id;