MySQL&python交互
一、Python操作MySQL步骤（原始的执行SQL语句）
- 引入pymysql模块
from pymysql import *
-
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语句并获取结果
-
Cursor对象
- 用于执行SQL语句，使用频度最高的语句为select、insert、update、delete
- 获取Cursor对象：调用Connection对象的cursor()方法
cs1 = conn.cursor() # 执行sql语句 rows = cs1.execute("""select * from goods;""") print(rows) # rows:影响的数据库行数 -
对象的方法
cs1.close() # 关闭数据库的查询连接 cs1.execute() # 执行sql语句，返回受影响的行数 cs1.fetchone() # 执行查询语句时，获取查询结果集的第一个行数据，返回一个元祖 cs1.fetchall() # 执行查询时，获取查询结果集的所有行，一行构成一个元祖，在将这些元祖祖入一个元祖返回 -
对象的属性
cs1.rowcount # 只读属性，表示最近一次execute()执行后受影响的行数 cs1.connection # 获得当前连接对象
二、Mysql与Flask的交互
- Flask提供了扩展Flask-SQLAlchemy，对其交互，操作数据库，是一个简化SQLALchemy操作的扩展
- SQLALchemy 实际是对数据库的抽象，不使用SQL语句，通过Python对象来操作数据库。
- SQLALchemy 是一个关系型数据库框架，提供了高层的ORM和底层的原生数据库的操作。
-
安装
pip install flask-sqlalchemy # 如果连接的是mysql数据库，需要安装mysqldb pip insstall flask-mysqldb -
数据库连接设置
- 在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中 -
常用的SQLALchemy字段类型
Integer int 普通整数 String str 变长字符串 Text str 变长字符串，对较长或不限长的字符串做了优化 Boolean bool 布尔值 Date datetime.date 时间 Time datetime.datetime 日期和时间 -
常用的SQLAlchemy列选项
- 是对列的约束，字段约束
选项名 说明 primary_key 如果为True，代表表的主键 unique 如果为True，代表这列不允许出现重复的值 index 如果为True，为这列创建索引，提高查询效率 nullable 如果为True，允许有空值，如果为False，不允许有空值 default 为这列定义默认值 -
常用的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中,修改数据库
- 会话管理用db.session表示。在数据写入数据库前，要将数据添加到会话中，在调用commit()方法提交会话
- 查询操作是通过query 对象操作数据
- 增-删-改都需要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.nameuser:模型实例对象 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或者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]: 2In [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; # 给表起别名
-
消除重复行
- 在 select 后面，列的前面使用 distinct
select distinct 列1，.... from 表名； select distinct gender from students; -
条件查询
- 使用 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; -
排序
- 为了方便查看数据，可以对数据进行排序
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; -
聚合函数
- 为了快速统计
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; -
分组
- 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; -
获取部分行
- 当数据量较大时，在一页查看数据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个页面，按照年龄排序 -
连接查询
- 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 -
自关联
- 类似省市区-三级联动的表格设计
# 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=".." -
子查询
- 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语句执行的结果；作用：就是便于查询
- 视图是对若干张基本表的引用，一张虚表，查询语句执行的结果（基本表结构发生改变，视图也会改变）
- 定义视图
# 创建视图
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数据表上的索引，是表空间的一个组成部分），包含对数据表里面所有记录的引用指针。好比一本书的目录，快速的查询。
- 在表格上创建唯一的索引。意味着两个行不能拥有相同的索引值。
-
索引原理
索引问题就是一个查找问题。
数据库索引，是数据库管理系统中一个排序的数据结构，以协助快速查询、更新数据库表中数据。索引的实现通常使用 B 树及其变种 B+树。 在数据之外，数据库系统还维护着满足特定查找算法的数据结构，这些数据 结构以某种方式引用(指向)数据，这样就可以在这些数据结构上实现高级 查找算法。这种数据结构，就是索引。
相当于把数据进行分块整理，从小块中查询数据。
-
索引使用
- 注意：
- 建立太多索引将会影响更新和插入速度，索引会占用磁盘空间
# 1.创建索引 create index 索引名称 on 表名（字段名称（长度））； # 如果字段是字符串，需要指定长度。与定义时一样 # 2.查看索引 show index from 表名； # 3.删除索引 drop index 索引名称 on 表名； # 查询开启运行时间监测 set profiling = 1； # 查看执行的时间 show profiles； - 注意：
