MySQL&python交互

MySQL&python交互

一、Python操作MySQL步骤（原始的执行SQL语句）

1. 引入pymysql模块

image.png

from pymysql import *

2. Connection对象

   • 用于建立与数据库的连接
   • 创建对象：调用connect()方法

   conn = connect(
   host='localhost', port=3306,user='root',password='mysql',database='jing_dong', charset='utf8')
   # host：      连接mysql主机IP地址
   # port：      连接的mysql主机的端口，默认是3306
   # database： 数据库的名称
   # user：  连接的用户名
   # password： 连接密码
   # charset：   通信的编码方式
   

   对象的方法

   conn.close()     # 关闭连接
   conn.commit()    # 提交
   conn.cursor()    # 返回cursor对象，用于执行sql语句并获取结果
   

3. Cursor对象

   • 用于执行SQL语句，使用频度最高的语句为select、insert、update、delete
   • 获取Cursor对象：调用Connection对象的cursor()方法

   cs1 = conn.cursor()
   # 执行sql语句
   rows = cs1.execute("""select * from goods;""")
   print(rows)  # rows:影响的数据库行数
   

4. 对象的方法

   cs1.close()  # 关闭数据库的查询连接
   cs1.execute()    # 执行sql语句，返回受影响的行数
   cs1.fetchone()   # 执行查询语句时，获取查询结果集的第一个行数据，返回一个元祖
   cs1.fetchall()   # 执行查询时，获取查询结果集的所有行，一行构成一个元祖，在将这些元祖祖入一个元祖返回
   

5. 对象的属性

   cs1.rowcount     # 只读属性，表示最近一次execute()执行后受影响的行数
   cs1.connection       # 获得当前连接对象
   

二、Mysql与Flask的交互

• Flask提供了扩展Flask-SQLAlchemy，对其交互，操作数据库，是一个简化SQLALchemy操作的扩展
• SQLALchemy 实际是对数据库的抽象，不使用SQL语句，通过Python对象来操作数据库。
• SQLALchemy 是一个关系型数据库框架，提供了高层的ORM和底层的原生数据库的操作。

1. 安装

   pip install flask-sqlalchemy
   # 如果连接的是mysql数据库，需要安装mysqldb
   pip insstall flask-mysqldb
   

2. 数据库连接设置

   • 在Flask-SQLAlchemy中，数据库使用URL指定，而且程序使用的数据库必须保存到flask配置对象的SQLALCHEMY_DATABASE_URI键中。

   app.config['SQLALCHEMY_DATABASE_URI'] = 'mysql://root:mysql@127.0.0.1:3306/test'
   # 动态追踪修改设置，如未设置只会提示警告,默认是True/设置为False
   app.config['SQLALCHEMY_TRACK_MODIFICATIONS'] = False
   #查询时会显示原始SQL语句
   app.config['SQLALCHEMY_ECHO'] = True
   
   # 文档：http://docs.sqlalchemy.org/en/latest/core/engines.html
   
   db = SQLAlchemy(app) # 要把 app 对象注册到SQLAlchemy中
   

3. 常用的SQLALchemy字段类型

   Integer      int             普通整数
   String       str             变长字符串
   Text     str             变长字符串，对较长或不限长的字符串做了优化
   Boolean      bool            布尔值
   Date     datetime.date   时间
   Time     datetime.datetime 日期和时间
   

4. 常用的SQLAlchemy列选项

   • 是对列的约束，字段约束

   选项名	说明
   primary_key	如果为True，代表表的主键
   unique	如果为True，代表这列不允许出现重复的值
   index	如果为True，为这列创建索引，提高查询效率
   nullable	如果为True，允许有空值，如果为False，不允许有空值
   default	为这列定义默认值

5. 常用的SQLALchemy关系选项

   backref	在关系的另一模型中添加反向引用
   primary join	明确指定两个模型之间使用的联结条件
   uselist	如果为False，不使用列表，而使用标量值
   order_by	指定关系中记录的排序方式
   secondary	指定多对多关系中关系表的名字
   secondary join	在SQLAlchemy中无法自行决定时，指定多对多关系中的二级联结条件

1. 基本演练代码

from flask import Flask
from flask_sqlalchemy import SQLAlchemy

app = Flask(__name__)

# 链接数据库（数据库地址）
app.config['SQLALCHEMY_DATABASE_URI'] = 'mysql://root:mysql@127.0.0.1:3306/flask01'
# 自动追踪数据库的修改
app.config['SQLALCHEMY_TRACK_MODIFICATIONS'] = False
# 查询时会显示原始SQL语句
# app.config['SQLALCHEMY_ECHO'] = True
# 先给flask对象设置配置，在传对象数据库--创建SQLAlchemy传入app：目的：获取数据库相关参数
db = SQLAlchemy(app)


class Role(db.Model):
    # 定义表名
    __tablename__ = 'roles'
    # 定义列对象
    id = db.Column(db.Integer, primary_key=True)
    name = db.Column(db.String(64), unique=True)
    us = db.relationship('User', backref='role')

    # repr()方法显示一个可读字符串
    # 直接对象回车就可以打印返回的数据 __str__的区别print打印实例对象
    def __repr__(self):
        return 'Role:name-%s,id-%s' % (self.name, self.id)


class User(db.Model):
    __tablename__ = 'users'
    id = db.Column(db.Integer, primary_key=True)
    name = db.Column(db.String(64), unique=True, index=True)
    email = db.Column(db.String(64), unique=True)
    password = db.Column(db.String(64))
    role_id = db.Column(db.Integer, db.ForeignKey('roles.id'))

    def __repr__(self):
        return 'User:name-%s,id-%s' % (self.name, self.id)


@app.route('/')
def hello_world():
    return 'Hello World!'


if __name__ == '__main__':
    # 这两个仅供测试使用，开发上线不能使用
    # 删除所有表的数据
    db.drop_all()
    # 创建新表
    db.create_all()

    # 添加角色数据
    ro1 = Role(name='admin')
    db.session.add(ro1)
    db.session.commit()
    # 再次插入一条数据
    ro2 = Role(name='user')
    db.session.add(ro2)
    db.session.commit()

    # 一次性插入十条数据
    us1 = User(name='wang', email='wang@163.com', password='123456', role_id=ro1.id)
    us2 = User(name='zhang', email='zhang@189.com', password='201512', role_id=ro2.id)
    us3 = User(name='chen', email='chen@126.com', password='987654', role_id=ro2.id)
    us4 = User(name='zhou', email='zhou@163.com', password='456789', role_id=ro1.id)
    us5 = User(name='tang', email='tang@itheima.com', password='158104', role_id=ro2.id)
    us6 = User(name='wu', email='wu@gmail.com', password='5623514', role_id=ro2.id)
    us7 = User(name='qian', email='qian@gmail.com', password='1543567', role_id=ro1.id)
    us8 = User(name='liu', email='liu@itheima.com', password='867322', role_id=ro1.id)
    us9 = User(name='li', email='li@163.com', password='4526342', role_id=ro2.id)
    us10 = User(name='sun', email='sun@163.com', password='235523', role_id=ro2.id)
    db.session.add_all([us1, us2, us3, us4, us5, us6, us7, us8, us9, us10])
    db.session.commit()
    app.run(debug=True)

2.在ipython环境下测试

flask_sqlalchemy中,修改数据库

1. 会话管理用db.session表示。在数据写入数据库前，要将数据添加到会话中，在调用commit()方法提交会话
2. 查询操作是通过query 对象操作数据
3. 增-删-改都需要commit

一、增
In [1]: from day04 import *

In [2]: role = Role(name='admin01')

In [3]: db.session.add(role)

In [4]: db.session.commit()
二、改

In [5]: role.name = 'admin001'

In [6]: db.session.commit()

三、删
In [7]: db.session.delete(role)

In [8]: db.session.commit()

————————————查询------------------------------------------------------------

查看用户对应的角色

from database import *

user查询出来是模型的对象,查询具体的值的时候,使用对象的属性查询属性值

创建出来的每一个模型对象,就是一条数据,数据模型类定义的属性就是模型的字段属性数据

user = User.query.get(1)
user.role.name

user:模型实例对象 role:反向引用的字段属性 name:查询的字段属性

查看管理角色有那些用户

role = Role.query.get(1)
In [7]: role
Out[7]: <Role 1>

In [8]: role.users # 生成对象,在点属性查询结果值
Out[8]: [<User 1>, <User 3>]

In [10]: role.users[1].name
Out[10]: '王五'

增加数据

role = Role(name='admin01')

In [3]: db.session.add(role)

In [4]: db.session.commit()

修改数据

In [3]: role.name='admin01'

In [4]: db.session.commit()

删除数据

In [7]: db.session.delete(role)

In [8]: db.session.commit()

----------------------------------------查询需求----------------------------------------------

1.查询所有用户数据
因为repr可以直接使用对象(User)来查询便于查询
User.query.all()

2.查询有多少个用户
User.query.count()

3.查询第1个用户
User.query.first()

4.查询id为4的用户[3种方式]
User.query.get(4)

过滤器是做数据检索判断，后面要加上执行器

filter的功能比较强大，filter_by的查询功能简单
User.query.filter(User.id==4).all()
User.query.filter_by(id=4).first()

filter的语法格式：
filter_by（属性名=X）
filter（模型名.属性名==X）

5.查询名字结尾字符为g的所有数据[开始/包含]
User.query.filter(User.name.endswith('g')).all() -- 以什么结尾
User.query.filter(User.name.startswith('g')).all() --以什么开始
User.query.filter(User.name.contains('g')).all() --包含

6.查询名字不等于wang的所有数据[2种方式]
6.1 User.query.filter(User.name!='wang').all()
6.2 （不用）
from sqlalchemy import not_
User.query.filter(not_(User.name=='wang')).all()

7.查询名字和邮箱都以 li 开头的所有数据[2种方式]
7.1 User.query.filter(User.name.startswith('li'),User.email.startswith('li')).all()
7.2
8.查询password是 123456 或者 email 以 itheima.com 结尾的所有数据
from sqlalchemy import or_

User.query.filter(or_(User.password=='123456',User.email.endswith('itheima.com'))).all()

9.查询id为 [1, 3, 5, 7, 9] 的用户列表
User.query.filter(User.id.in_([1, 3, 5, 7, 9])).all()

10.查询name为liu的角色数据
In [16]: user = User.query.filter(User.name=='liu').first()
In [17]: user
Out[17]: User:name-liu,id-8
In [18]: user.role.name
Out[18]: 'admin'

11.查询所有用户数据，并以邮箱排序
User.query.order_by('email').all()

12.每页3个，查询第2页的数据

返回一个Paginate对象，它包含指定范围内的结果

paginate = User.query.paginate(2, 3, False)

In [33]: paginate
Out[33]: <flask_sqlalchemy.Pagination at 0x10e736208>

In [34]: paginate.items # 获取分页的数据
Out[34]: [User: name-zhou id-4, User: name-tang id-5, User: name-wu id-6]

In [35]: paginate.page # 当前页码
Out[35]: 2

In [36]: paginate.pages # 总页码
Out[36]: 4

3. 数据库迁移

• 在Flask中可以使用Flask-Migrate扩展，来实现数据迁移。并且集成到Flask-Script中，所有操作通过命令就能完成
• 为了导出数据库迁移命令，Flask-Migrate提供了一个MigrateCommand类，可以附加到flask-script的manager对象上。

pip install flask-migrate

from flask import Flask

from flask_sqlalchemy import SQLAlchemy
from flask_migrate import Migrate,MigrateCommand
from flask_script import Shell,Manager

....
db = SQLAlchemy(app)

#第一个参数是Flask的实例，第二个参数是Sqlalchemy数据库实例
migrate = Migrate(app,db) 

#manager是Flask-Script的实例，这条语句在flask-Script中添加一个db命令
manager.add_command('db',MigrateCommand)

.....

• 创建迁移脚本

  pyhton database.py db init
  python database.py db migrate -m 'initial migration'
  python database.py db upgrade
  

三、Mysql与Django的交互

• Django默认初始配置使用sqlite数据库。使用MySQL数据库首先需要安装驱动程序。

• 在Django的工程同名子目录的init.py文件中添加执行MySQL的执行驱动。

• 在setting文件中配置DATABASES配置信息

  pip install Pymysql
  # 在__init__文件下
  from pymysql import install_as_MySQLdb
  install_as_MySQLdb()
  

  DATABASES = {
      'default': {
          'ENGINE': 'django.db.backends.mysql',
          'HOST': '127.0.0.1',  # 数据库主机
          'PORT': 3306,  # 数据库端口
          'USER': 'root',  # 数据库用户名
          'PASSWORD': 'mysql',  # 数据库用户密码
          'NAME': 'django_demo'  # 数据库名字
      }
  }
  

四、MySQL的SQL语句

基本查询

select id as 序号， name as 名字，gender as 性别 from students；# 给查询字段起别名
select s.id,s.name,s.gender from students as s; # 给表起别名

1. 消除重复行

   • 在 select 后面，列的前面使用 distinct

   select distinct 列1，.... from 表名；
   select distinct gender from students;
   

2. 条件查询

   • 使用 where子句对表中的数据塞选，结果为true的行会出现在结果集

   seclect * from 表名 where 条件
   

   • where子句后面支持多种运算符，进行条件的处理

   1. 比较运算符 # =、>、>=、<、<=、!=、
   select * from students where age != 18;
   2. 逻辑运算符 # and、or、not
   select * from students where not( age > 18 and gender = "女");
   3. 模糊查询      # like、% 表示任意多个字符、__ 表示一个任意字符
   select * from students where name like '黄%' or name like '%靖';
   4. 范围查询      # in 表示在一个非连续的范围、between...and ...表示在一个连续的范围内
   select * from students where age not between 18 and 34;
   5. 空判断       # is null：判空、is not null：判非空
   select * from students where height is not null and gender=1;
   

3. 排序

   • 为了方便查看数据，可以对数据进行排序

   select * from 表名 order by  列1 asc|desc, [列2 asc|desc，...]
   # asc:升序；desc：降序
   select * from students where (age between 18 and 34) and gender = "女" order by height desc,age asc,id desc;
   

4. 聚合函数

   • 为了快速统计

   1. count(*):表示计算总行数，括号中写星与列名，结果是相同的 select count(*) from students;
   2. max(列):表示求此列的最大值  select max(id) from students where gender=2;
   3. min(列):表示求此列的最小值  select min(id) from students where is_delete=0;
   4. sum(列):表示求此列的和    select sum(age) from students where gender=1;
   5. avg(列):表示求此列的平均值 select avg(id) from students where is_delete=0 and gender=2;
   

5. 分组

   • group by：将查询结果按照1个或多个字段进行分组，字段值相同为一组
   • group by可用于单个字段、多个字段分组 。一般都是和其他函数一起使用

   select 分组字段 from 表名 group by 分组字段;
   select gender,count(*) from students group by gender;
   

   • group by + group_concat()

   # group_concat(字段名)：可以作为一个输出字段来使用
   # 表示分组之后，根据分组结果，使用group_concat()来放置每一组的某个字段的值集合
   select gender,group_concat(name) from students group by gender;
   

   • group by + 集合函数

   # 通过group_concat(),可以对统计出来每个分组的某个字段值的集合。通过聚合函数来对 值的集合 操作
   select gender,count(*) from students group by gender;
   

   • group by + having

     having：条件表达式：原来分组查询后指定一些条件输出查询的结果

     having 作用和where一样：但是having只能用在group by

     having 后通常也要跟 聚合函数

   select gender,avg(age),group_concat(name) from students group by gender having avg(age) > 30; # 查询性别分组、分组集合字段有哪些name、对于输出结果判断是否符合 平均年龄大于30
   

   • group by + with rollup
   • with rollup:在最后新增一行，来记录当前列里面所有记录的总和 (汇总的作用)

   select gender,group_concat(age) from students group by gender with rollup;
   

6. 获取部分行

   • 当数据量较大时，在一页查看数据limit要放到最后
   • 起始位置 = (页面数-1)每一页的个数*

   select * from students where is_delete=0 limit (n-1)*m,m
   

   # start:开始的地方，获取count条数据
   select * from 表名 limit start,count
   select * from students limit 6,2; # 每页显示2个，第4个页面
   select * from students order by age asc limit 10,2 ; # 每页显示2个，第6个页面，按照年龄排序
   

7. 连接查询

   • mysql支持三种类型的连接查询：
   • 内连接查询：查询结果为两个表的匹配的结果（交集）
   • 右连接查询：查询结果为两个表的匹配的数据，右表特有的数据，对于左表不存在数据使用null填充
   • 左连接查询：查询的结果为两个表匹配到的数据，左表特有的数据，对于右表中不存在的数据使用null填充

   # inner join ... on ：on 后面是两个表之间的查询条件
   selcet * from 表1 inner或left或right jion 表2 on 表1.列 = 表2.列
   

   # 查询学生姓名和班级姓名
   select s.name,c.name from student as s inner join classes as c on s.cls_id = c.id
   

8. 自关联

   • 类似省市区-三级联动的表格设计

   # 1.查询所有的省份
   select * from areas where pid is null;
   # 2.查询某个省份下的所有城市
   select pid from areas where atitle = "河南省"
   select * from areas where pid=(select pid from areas where atitle = "河南省")
   或者：
   select * from areas as a1 inner join areas as a2 on a1.pid=a2.aid where a2.title=".."
   

9. 子查询

   • in 范围

   主查询 where 条件 in （列子查询）
   # 1.标量 子查询
   select * from students where age > (select avg(age) from students);
   # 2.列级 子查询
   select name from classes where id in (select cls_id from students);
   # 3.行级 子查询
   select * from students where (height,age) = (select max(height),max(age) from students);
   

增加数据

• 添加数据

格式：insert into 表名(字段...) values(值1...)
insert into students(name,age) values('zhang', '18') # 部分插入数据
insert into students(name) values('李四'),('王五'); # 多行插入数据

修改数据

格式：
update 表名  set 字段 = 值,字段2 = 值2 where 条件;
update user set name='小名';          # 全部修改
update user set name='小明' where id=2;   # 条件修改
update students set gender='男',name='小哈' where id=10;   # 条件修改多个值

五、MySQL的高级使用

1. 视图

• 视图就是一条select语句执行的结果；作用：就是便于查询
• 视图是对若干张基本表的引用，一张虚表，查询语句执行的结果（基本表结构发生改变，视图也会改变）

1. 定义视图

# 创建视图
create view 视图名称 as select语句；   # 视图名称建议使用 v_开头
# 查看视图
show tables；
# 删除视图
drop view 视图名称；

2. 事务命令

• 注意：修改数据的命令会自动的触发事务，包括insert、update、delete。
• 手动开启事务原因：可以多次数据修改，要么一起成功，要么一起回滚之前的数据。

# 1. 开启事务
begin；
# 2. 提交事务
commit；
# 3. 回滚
rollback;
2、直接用 SET 来改变 MySQL 的自动 交模式: 
SET AUTOCOMMIT=0 禁止自动 交
SET AUTOCOMMIT=1 开启自动 交

3. 索引

• 一种特殊的文件（InnoDB数据表上的索引，是表空间的一个组成部分），包含对数据表里面所有记录的引用指针。好比一本书的目录，快速的查询。
• 在表格上创建唯一的索引。意味着两个行不能拥有相同的索引值。

1. 索引原理

   1. 索引问题就是一个查找问题。

   2. 数据库索引，是数据库管理系统中一个排序的数据结构，以协助快速查询、更新数据库表中数据。索引的实现通常使用 B 树及其变种 B+树。 在数据之外，数据库系统还维护着满足特定查找算法的数据结构，这些数据 结构以某种方式引用(指向)数据，这样就可以在这些数据结构上实现高级 查找算法。这种数据结构，就是索引。

   3. 相当于把数据进行分块整理，从小块中查询数据。

2. 索引使用

   • 注意：
     1. 建立太多索引将会影响更新和插入速度，索引会占用磁盘空间

   # 1.创建索引
   create index 索引名称 on 表名（字段名称（长度））； # 如果字段是字符串，需要指定长度。与定义时一样
   # 2.查看索引
   show index from 表名；
   # 3.删除索引
   drop index 索引名称 on 表名；
   
   # 查询开启运行时间监测
   set profiling = 1；
   # 查看执行的时间 show profiles；