python编程-ORM框架

一、什么是ORM

摘自百度百科：ORM一般指对象关系映射。对象关系映射（英语：Object Relational Mapping，简称ORM，或O/RM，或O/R mapping）

这段话可能抽象，不好理解，其实就是把面向对象编程和关系型数据库的一种映射关系的模型。

这样我们通过操作编程对象来直接进行对关系型数据库的操作，

• 数据库的表（table） --> 类（class）
• 记录（record，行数据）–> 实例对象（object）
• 字段（field）–> 对象的属性（attribute）

优点：

• 数据模型都在一个地方定义，更容易更新和维护，也利于重用代码。
• ORM 有现成的工具，很多功能都可以自动完成，比如数据消毒、预处理、事务等等。
• 它迫使你使用 MVC 架构，ORM 就是天然的 Model，最终使代码更清晰。
• 基于 ORM 的业务代码比较简单，代码量少，语义性好，容易理解。
  你不必编写性能不佳的 SQL

不足：

• ORM 库不是轻量级工具，需要花很多精力学习和设置。
• 对于复杂的查询，ORM 要么是无法表达，要么是性能不如原生的 SQL。
• ORM 抽象掉了数据库层，开发者无法了解底层的数据库操作，也无法定制一些特殊的 SQL

二、sql

2.1 概念和数据类型

概念：

Engine：连接,驱动引擎
Session：连接池，事务 由此开始查询
Model：表，类定义
Column：列
Query：若干行，可以链式添加多个条件

数据类型：

数据类型	数据库数据类型	python数据类型		说明
Integer	int				int					整形，32位
String	varchar			string				字符串
Text	text			string				长字符串
Float	float			float				浮点型
Boolean	tinyint			bool				True / False
Date	date			datetime.date		存储时间年月日
DateTime	datetime	datetime.datetime	存储年月日时分秒毫秒等
Time	time	datetime.datetime	存储时分秒

2.2 创建连接

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

engine = create_engine("mysql+pymysql://user:password@IP:PORT/dbname?charset=utf8",
                       echo=True,
                       pool_size=8,
                       pool_recycle=60*30
                       )

这里我们采用的是pymysql的连接模式，因此ORM框架其实并不包含和数据库的连接

• echo: 当设置为True时会将orm语句转化为sql语句打印，一般debug的时候可用
• pool_size: 连接池的大小，默认为5个，设置为0时表示连接无限制
• pool_recycle: 设置时间以限制数据库多久没连接自动断开

2.3 操作表

Base = declarative_base()# 所有的类都要继承自这个基础类

class Users(Base):
    __tablename__ = "users"
    id = Column(Integer,primary_key=True,autoincrement=True)
    name = Column(String(32),nullable=True,default='luren',index=True)
    email = Column(String(32),unique=True)
    user_type_id = Column(Integer,ForeignKey("usertype.id")#外键

class Usertype(Base):
    __tablename__ = "usertype"
    id = Column(Integer,primary_key=True,autoincrement=True)
    title = Column(String(32),nullable=True,index=True)


Base.metadata.create_all(engine) #这是将所有类都进行转换为表的语句

• Base.metadata.drop_all(engine)：删除所有类对应的表

2.4 操作行数据

2.4.1 增加数据

插入单行数据

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

obj1 = Usertype(title="青铜用户")
session.add(obj1)

session.commit()
session.close()

插入多行数据

Session = sessionmaker(bind=engine)
session = Session()
objs = [
    Usertype(title="白银用户"),
    Usertype(title="黄金用户"),
    Usertype(title="钻石用户")
]
session.add_all(objs)
session.commit()
session.close()

2.4.2 查询数据

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

user_type_list = session.query(Usertype).all()
for row in user_type_list:
    print(row.id,row.title)

session.commit()
session.close()

根据ORM定义，行数据是类的对象，因此例子中user_type_list的变量是一个列表，列表中每一个元素都是usertype表中的行对象，因此我们可以采用遍历的方法来取到相关的值

条件查询

# 单个条件过滤
user_type_list = session.query(Usertype).filter(Usertype.title="白金用户").all()

# and
user_type_list = session.query(Usertype).filter(and_(title="白金用户",id=2)).all()
# or
user_type_list = session.query(Usertype).filter(or_(title="白金用户",id=2)).all()

# 通配符
ret = session.query(Users).fileter(Users.name.like('e%'))
ret = session.query(Users).fileter(-Users.name.like('e%'))

# 分页
ret = session.query(Users)[1:2]

# 排序
ret = session.query(Users).order_by(Users.name.desc())

# 分组
from sqlalchemy import func
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()
)

连表查询

# 内连接
ret = session.query(Users,Usertype).filter(Users.user_type_id == Usertype.id)

# 根据表之间的关系，你如外键，来进行表连接
ret = session.query(Users).join(Usertype)

# 外连接
ret = session.query(Users).join(Usertype,isouter=True)

子查询

# slect * from b in ()

# select * from (select * from usertype where id > 1) as B
q1 = session.query(Usertype).filter(Usertype.id > 1).subquery()
ret = session.query(q1).all()

# select id,(select * from usertyep where users.user_type_id = usertype.id) from users
# 方法一
ret = session.query(Usertype.id,session.query(Users).as_scalar())
# 方法二
ret = session.query(Usertype.id,session.query(Users).filter(Users.id == Usertype.id).as_scalar())

2.4.3 删除数据

删除数据，我们是先查询后删除，非常简单

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

user_type_list = session.query(Usertype).filter(Usertype.id == 1).delete()# 删除usertype表中id数据为1的数据

session.commit()
session.close()

2.4.4 修改数据

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

user_type_list = session.query(Usertype).filter(Usertype.id == 2).update({"title":"黑金用户"})

session.commit()
session.close()

当我们要在数据的原有基础上修改的话，我们需要加入不同的参数

# 字符模式
user_type_list = session.query(Usertype).filter(Usertype.id == 2).update({Usertype.title:Usertype.title + "001"},synchronize_session=False)

# 整数模式
user_type_list = session.query(Usertype).filter(Usertype.id == 2).update({Usertype.title:Usertype.title + "001"},synchronize_session="evaluate")

三、表关系

Base = declarative_base()# 所有的类都要继承自这个基础类

class Users(Base):
    __tablename__ = "users"
    id = Column(Integer,primary_key=True,autoincrement=True)
    name = Column(String(32),nullable=True,default='luren',index=True)
    email = Column(String(32),unique=True)
    user_type_id = Column(Integer,ForeignKey("usertype.id")#外键
	user_type = relationship("Usertype"，backref='usersinfo') #通过外键和usertype表建立联系，而生成一个不在数据库中的虚拟行 

class Usertype(Base):
    __tablename__ = "usertype"
    id = Column(Integer,primary_key=True,autoincrement=True)
    title = Column(String(32),nullable=True,index=True)



ret = session.query(Users)
for row in ret:
    print(row.id,row.name,row.user_type.title)
    #row.user_type是一个实例对象，这个对象就保存着和相应的usertype表中的一行数据

通过relationship字段，我们在查询的时候可以轻松的将两个表进行一个类似外连接的效果，从而达到简便查询的目的。

其中relationship是字段是用于User表对应Usertype表的时候使用，backref顾名思义就是反向关系，是Usertype表对应User表的时候使用。