Python全栈之路-MySQL（七）

1 面向对象回顾

函数编程：数据和逻辑分离
面向对象：数据和逻辑（属性和行为）组合在一起

类的特殊方法： __call__ __getitem__ __setitem__ __delitem__

2 SQLAlchemy

ORM 框架：SQLAlchemy
ORM框架原理：
目的是让用户不在写SQL语句，而是通过ORM框架内的类和对象的方式以及ORM内部提供的方法来进行数据库操作，ORM框架进行转换SQL语句，并执行结果返回给用户

http://www.cnblogs.com/wupeiqi/articles/5713330.html

类 --> 数据表 对象 --> 数据行

2.1 作用

• 提供简单的规则
• 自动转换成SQL语句

2.2 DB first / Code first

DB first：手动创建数据库以及表 --> ORM框架 --> 自动生成类
Code first：手动创建类和数据库 --> ORM框架 --> 自动生成表

SQLAlchemy默认支持Code first

SQLAlchemy功能：

• 创建数据库表
  • 连接数据库（非SQLAlchemy做的，SQLAlchemy借助DBAPI（pymysql）做的）
  • 类转换SQL语句

2.3 SQLAlchemy实战

#!/usr/bin/env python
# __Author__: "wanyongzhen"
# Date: 2017/6/14

from sqlalchemy.ext.declarative import declarative_base
from sqlalchemy import Column,Integer,String,ForeignKey,UniqueConstraint,Index,CHAR,VARCHAR
from sqlalchemy.orm import sessionmaker, relationship
from sqlalchemy import create_engine

# 封装了SQLAlchemy的功能的类
Base = declarative_base()

# 创建单表
class UserType(Base):
    __tablename__ = 'usertype'
    id = Column(Integer, primary_key=True, autoincrement=True)
    user_type_name = Column(String(32), nullable=True, index=True)

class Users(Base):
    __tablename__ = 'users'
    id = Column(Integer, primary_key=True, autoincrement=True)
    name = Column(String(32), nullable=True, default='df', index=True)
    email = Column(String(16), unique=True)
    user_type_id = Column(Integer, ForeignKey('usertype.id'))
    __table_args__ = (
        UniqueConstraint('id', 'name', name='unique_id_name'), # 联合唯一索引
        Index('index_name_email', 'name', 'email'),            # 联合索引
    )
    user_type = relationship("UserType",backref="xxoo")  # 建立关系

def init_db():
    Base.metadata.create_all(engine)  # 找到当前页面所有的表（继承了Base的类）进行创建

def drop_db():
    Base.metadata.drop_all(engine)    # 找到当前页面所有的表（继承了Base的类）进行删除



# 调用pymysql创建连接 最大连接数为5
engine = create_engine("mysql+pymysql://root:123456@127.0.0.1:3306/db02?charset=utf8", max_overflow=5)
# drop_db()
# init_db()

Session = sessionmaker(bind=engine)
session = Session()

# 添加单条数据
# obj1 = UserType(user_type_name='黑金用户')
# session.add(obj1)

# 添加多条数据
# objs = [
#     UserType(user_type_name='白金用户'),
#     UserType(user_type_name='绿金用户'),
#     UserType(user_type_name='黄金用户'),
# ]
# session.add_all(objs)

# 查询
# user_type_list = session.query(UserType).all() # session.query(UserType)返回SQL语句，后面加.all()返回SQL语句执行得到的数据
# print(type(user_type_list))   # list类型
# print(user_type_list)    # list里面存放了多个UserType对象
# for row in user_type_list:
#     print(row.id,row.user_type_name)

# filter 想当于where条件
user_type_list = session.query(UserType).filter(UserType.id > 2)
print(type(user_type_list))
print(user_type_list)
for row in user_type_list:
    print(row.id, row.user_type_name)

# 删除
# delete()
# session.query(UserType).filter(UserType.id > 2).delete()

# 修改
# update()
session.query(UserType).filter(UserType.id > 2).update({UserType.user_type_name: '粉金用户'})
# session.query(UserType).filter(UserType.id > 2).update({UserType.user_type_name: UserType.user_type_name + "099"}, synchronize_session=False) # 处理字符串
# session.query(UserType).filter(UserType.id > 2).update({"num": Users.num + 1}, synchronize_session="evaluate") # 处理数字

# 连表(通过外键)
session.query(Users).join(UserType).all()  # inner join
session.query(Users).join(UserType, isouter=True).all()  # left join

# 子查询
# select * from (select * from tb) as B
# q1 = session.query(UserType).filter(UserType.id > 0).subquery()
# result = session.query(q1).all()
# print(result)
# select id,(select id from where id=x) from xxx;
# result = session.query(UserType.id, session.query(Users.name).filter(Users.user_type_id == UserType.id).as_scalar()).all()
# print(result)

# 问题1：获取用户欣欣以及与其关联的用户类型名称
user_list = session.query(Users.name, UserType.user_type_name).join(UserType, isouter=True).all()
# user_list = session.query(Users, UserType).join(UserType, isouter=True)
# 加all()获取到所有数据，不加all()是迭代器

print(type(user_list))
for row in user_list:
    # print(row[0].id, row[0].name, row[0].user_type_id, row[1].user_type_name)
    print(row[0], row[1], row.name, row.user_type_name) # 通过索引和属性都可以获取
# 建立relationship后  正向操作
user_list = session.query(Users)
for row in user_list:
    print(row.name, row.id, row.user_type, row.user_type.user_type_name)  # row.user_type是一个对象

# 问题2:获取用户类型,并获取有哪些用户
# 传统方式
# type_list = session.query(UserType)
# for row in type_list:
#     print(row.id, row.user_type_name, session.query(Users).filter(Users.user_type_id == row.id).all())
# backref方式  反向操作
type_list = session.query(UserType)
for row in type_list:
    print(row.id, row.user_type_name, row.xxoo)


session.commit()
session.close()

2.4 其他

#　条件 ...
ret = session.query(Users).filter_by(name='alex').all()
ret = session.query(Users).filter(Users.id > 1, Users.name == 'eric').all()
ret = session.query(Users).filter(Users.id.between(1, 3), Users.name == 'eric').all()
ret = session.query(Users).filter(Users.id.in_([1,3,4])).all()
ret = session.query(Users).filter(~Users.id.in_([1,3,4])).all()
ret = session.query(Users).filter(Users.id.in_(session.query(Users.id).filter_by(name='eric'))).all()
from sqlalchemy import and_, or_
ret = session.query(Users).filter(and_(Users.id > 3, Users.name == 'eric')).all()
ret = session.query(Users).filter(or_(Users.id < 2, Users.name == 'eric')).all()
ret = session.query(Users).filter(
    or_(
        Users.id < 2,
        and_(Users.name == 'eric', Users.id > 3),
        Users.extra != ""
    )).all()


# 通配符 % _
ret = session.query(Users).filter(Users.name.like('e%')).all()
ret = session.query(Users).filter(~Users.name.like('e%')).all()

# 限制 limit 
ret = session.query(Users)[1:2]

# 排序 order by 
ret = session.query(Users).order_by(Users.name.desc()).all()
ret = session.query(Users).order_by(Users.name.desc(), Users.id.asc()).all()

# 分组group by 
from sqlalchemy.sql import func

ret = session.query(Users).group_by(Users.extra).all()
ret = session.query(
    func.max(Users.id),
    func.sum(Users.id),
    func.min(Users.id)).group_by(Users.name).all()

ret = session.query(
    func.max(Users.id),
    func.sum(Users.id),
    func.min(Users.id)).group_by(Users.name).having(func.min(Users.id) >2).all()


# 组合union
q1 = session.query(Users.name).filter(Users.id > 2)
q2 = session.query(Favor.caption).filter(Favor.nid < 2)
ret = q1.union(q2).all()

q1 = session.query(Users.name).filter(Users.id > 2)
q2 = session.query(Favor.caption).filter(Favor.nid < 2)
ret = q1.union_all(q2).all()