ORM与MYSQL指令语句

查询

• all表示查询所有,查询执行器

SQL:
select user_id,mobile,user_name from user_basic;
ORM:query是对象
User.query.all()

• first表示查询结果第一个,查询执行器

SQL:
select user_id,mobile,user_name from user_basic limit 1;
ORM:
User.query.first()  

• get查询，参数为主键id

SQL:
select user_id,mobile,user_name from user_basic where user_id=1;
# django中如果主键不存在，报错DoesNotExists
# Flask中如果主键不存在，返回空
User.query.get(1)

• SQLAlchemy查询方式

  • session表示数据库会话，封装了数据库的基本操作，add/add_all/commit/delete等操作

db.session.query(User).all()
db.session.query(User).first()
db.session.query(User).get(2)

• 过滤查询：结果为对象，需要使用查询执行器

  • 1.filter：条件不写查询所有，条件必须是类名.字段名，使用更多的运算符(比较、逻辑)
  • 2.filter_by：条件不写查询所有，条件是字段名，只能使用赋值操作

SQL:根据手机号查
select user_id,mobile,user_name from user_basic where mobile='18516952650';  
ORM:
# <flask_sqlalchemy.BaseQuery object at 0x7f4db6a42da0> 
User.query.filter(User.mobile=='18516952650').first()/all()
# <flask_sqlalchemy.BaseQuery object at 0x7f4dc74b93c8>
User.query.filter_by(mobile='18516952650').first()

# 查询13开头的手机号
select user_id,mobile,user_name from user_basic where mobile like '13%';
# startswith表示开头，endswith表示结尾
User.query.filter(User.mobile.startswith("13")).all()

• 运算符，需要导入使用or_,not_,and_

# 多条件默认是and关系
User.query.filter(or_(User.mobile.startswith("13"),User.id>=10)).all()

• offset和limit

SQL:
select user_id,mobile,user_name from user_basic limit 1,1;
ORM:
# offset表示偏移、起始
User.query.offset(1)# 对象
User.query.offset(1).first() # <User2>
User.query.offset(1).all() # 多个
####################################
# limit表示限制结果
User.query.limit(1)# 对象
User.query.limit(1).first() # <User1>
User.query.limit(1).all() # [<User1>]
# 在一起使用,值相同，limit1,1,不区分先后顺序
User.query.limit(1).offset(1) # 对象
User.query.limit(1).offset(1).all() # [<User2>]
User.query.offset(1).limit(1).all() # [<User2>]

• order_by表示排序，asc表示升序，desc表示降序

SQL:
select user_id,mobile,user_name from user_basic order by user_id desc;  
ORM:
User.query.order_by(User.id.desc()).all()
User.query.order_by(User.id.asc()).all()

• group_by表示分组

# 查询所有用户的关注信息
SQL:
select user_id,count(target_user_id) from user_relation group by user_id;
+---------+-----------------------+
| user_id | count(target_user_id) |
+---------+-----------------------+
|       1 |                     3 |
|       2 |                     1 |
|       5 |                     3 |
|      33 |                     1 |
|      52 |                     1 |
|      61 |                     1 |
+---------+-----------------------+  
ORM:  
# 分组查询，因为Relation模型中没有字段叫做count(target_user_id),不能直接使用Relation查询
--------Relation.query.group_by(Relation.user_id)
# 使用sqlalchemy的查询方式
from sqlalchemy import func
db.session.query(Relation.user_id,func.count(Relation.target_user_id)).filter().group_by(Relation.user_id).all()
[(1, 3), (2, 1), (5, 3), (33, 1), (52, 1), (61, 1)]  

• ORM查询语句优化：

# ORM
User.query.filter(User.id==1).first()
# 转化后的SQL，相当于select *
SELECT user_basic.user_id AS user_basic_user_id, user_basic.user_name AS user_basic_user_name, user_basic.mobile AS user_basic_mobile, user_basic.password AS user_basic_password, user_basic.profile_photo AS user_basic_profile_photo, user_basic.last_login AS user_basic_last_login, user_basic.is_media AS user_basic_is_media, user_basic.is_verified AS user_basic_is_verified, user_basic.introduction AS user_basic_introduction, user_basic.certificate AS user_basic_certificate, user_basic.article_count AS user_basic_article_count, user_basic.following_count AS user_basic_following_count, user_basic.fans_count AS user_basic_fans_count, user_basic.like_count AS user_basic_like_count, user_basic.read_count AS user_basic_read_count, user_basic.account AS user_basic_account, user_basic.email AS user_basic_email, user_basic.status AS user_basic_status 
FROM user_basic 
WHERE user_basic.user_id = %s 
 LIMIT %S
# 如果自己手写sql,效率会更高
select user_id,mobile,name from user_basic wherer user_id=1;

# ORM优化需要使用load_only
from sqlalchemy.orm import load_only
User.query.options(load_only(User.id,User.mobile)).filter(User.id==1).first()
# 优化后
SELECT user_basic.user_id AS user_basic_user_id, user_basic.mobile AS user_basic_mobile 
FROM user_basic 
WHERE user_basic.user_id = %s 
 LIMIT %s

• 关联查询：连表查询

# 查询手机号18516952650关注了谁？
# 分析：User表有mobile，Relation有target_user_id,需要连表查询；
SQL:
select user_basic.user_id,user_relation.target_user_id 
from user_basic join user_relation on user_basic.user_id=user_relation.user_id where user_basic.mobile='18516952650';
+---------+----------------+
| user_id | target_user_id |
+---------+----------------+
|       1 |              2 |
|       1 |              3 |
|       1 |              5 |
+---------+----------------+
ORM:需要在模型类中指定表之间的连接条件

• 关联查询的实现方式：

  • 1.ForeignKey

# User和Relation之间产生关联，从User查询Relation，从Relation查询User
# 一对多
# 一方定义关系
class User(db.Model):
  # 参数为另外一方的类名,backref表示反向引用，
  # u是从Relation查询User使用
  # follows是从User查询Relation使用
  follows = db.relationship('Relation',backref='u')

# 多方定义外键
class Relation(db.Model):
  # 定义外键,不是指创建数据库中表的外键，是表示连接查询的条件
  user_id = db.Column(db.Integer,db.ForeignKey('user_basic.user_id') ,doc='用户ID')
# 查询数据
user = User.query.filter_by(mobile='18516952650').first()
user.follows # [<Relation 11>, <Relation 19>, <Relation 10>]
r = Relation.query.get(11)
r.u # <User 1>

# 实体字段：
1.如果需要创建外键，把模型类迁移创建表，外键即会生成实体字段。 
2.relationship定义的字段不会生成实体字段。
3.如果不迁移，relationship和foreignkey在数据库中都没有实体，仅仅是连表查询会被触发使用；

• 2.primaryjoin

class User(db.Model):
  # 仅仅在User中定义primaryjoin即可实现连表查询
    follows = db.relationship('Relation',primaryjoin='User.id==foreign(Relation.user_id)',backref='u')
# 查询数据
user = User.query.filter_by(mobile='18516952650').first()
user.follows # [<Relation 11>, <Relation 19>, <Relation 10>]
r = Relation.query.get(11)
r.u # <User 1>

• 连表查询优化：

# 查询手机号18516952650关注了谁？
user.follows # 可以查询数据，但是，效率不高
# 指定字段load_only，基于一个模型类
User.query.options(load_only(User.mobile,xxx)).filter().all()
# 从User到Relation，基于二个模型类
from sqlalchemy.orm import contains_eager,load_only
# user.follows
User.query.join(User.follows).options(load_only(User.id,User.mobile),contains_eager(User.follows).load_only(Relation.target_user_id)).filter(User.mobile=='18516952650').all()

# SQL和ORM对比理解:
select user_basic.user_id,user_relation.target_user_id <==>options(load_only(User.id,User.mobile),contains_eager(User.follows).load_only(Relation.target_user_id))

from user_basic join user_relation on user_basic.user_id=user_relation.user_id <==>User.query.join(User.follows)

where user_basic.mobile='18516952650'<==>filter(User.mobile=='18516952650')

添加、修改、删除

• 添加数据：通过模型类对象

  • 1.需要使用db对象和session对象，User

user = User(name='itcast_py29',mobile='13366667777')
# session表示数据库会话对象，封装了数据库的操作
db.session.add(user) # add提交一个对象，给数据库会话对象
db.session.add_all([user1,user2]) # add_all提交多个对象，列表方式
db.session.commit() # 提交数据到数据库中

• 更新修改

# 第一种：
user = User.query.filter_by(mobile='13366667777').first()
user.name = 'python29'
db.session.add(user)
db.session.commit()
# 第二种：
User.query.filter(User.mobile='13366667777').update({'name':'itcast_python29'}) # 1
db.session.commit()

• 删除数据：

# 第一种：
user = User.query.order_by(User.id.desc()).first()
db.session.delete(user)
db.session.commit()
# 第二种：
User.query.filter(User.mobile=='xxx').delete()
db.session.commit()