SQLAlchemy ORM

ORM

　　对象-关系映射（Object Relational Mapping，简称ORM），是随着面向对象的软件开发方法发展而产生的。用来把对象模型表示的对象映射到基于S Q L 的关系模型数据库结构中去。这样，我们在具体的操作实体对象的时候，就不需要再去和复杂的 SQ L 语句打交道，只需简单的操作实体对象的属性和方法  。O R M 技术是在对象和关系之间提供了一条桥梁，前台的对象型数据和数据库中的关系型的数据通过这个桥梁来相互转化。

　　简单来说我们类似python这种面向对象的程序来说一切皆对象，但是我们使用的数据库却都是关系型的，为了保证一致的使用习惯，通过orm将编程语言的对象模型和数据库的关系模型建立映射关系，这样我们在使用编程语言对数据库进行操作的时候可以直接使用编程语言的对象模型进行操作就可以了，而不用直接使用sql语言。

SQLAlchemy

　　SQLAlchemy是Python编程语言下的一款ORM框架，该框架建立在数据库API之上，使用关系对象映射进行数据库操作，简言之便是：将对象转换成SQL，然后使用数据API执行SQL并获取执行结果。SQLAlchemy的理念是，SQL数据库的量级和性能重要于对象集合；而对象集合的抽象又重要于表和行。

　　SQLAlchemy与数据库关系图：

　　 部分操作过程说明；SQLAchemy调用DBAPI，Dialect 是选择哪个DBAPI,enging负责连接，连接需要用到连接池(ConnectionPooling)，通过Dlialect，具体去选择哪个第三方插件。

 　  SQLAlchemy本身无法操作数据库，其必须以来pymsql等第三方插件，Dialect用于和数据API进行交流，根据配置文件的不同调用不同的数据库API，从而实现对数据库的操作，如：

MySQL-Python
    mysql+mysqldb://<user>:<password>@<host>[:<port>]/<dbname>
   
pymysql
    mysql+pymysql://<username>:<password>@<host>/<dbname>[?<options>]
   
MySQL-Connector
    mysql+mysqlconnector://<user>:<password>@<host>[:<port>]/<dbname>
   
cx_Oracle
    oracle+cx_oracle://user:pass@host:port/dbname[?key=value&key=value...]
   
更多详见：http://docs.sqlalchemy.org/en/latest/dialects/index.html

一、底层处理

　　使用 Engine/ConnectionPooling/Dialect 进行数据库操作，Engine使用ConnectionPooling连接数据库，然后再通过Dialect执行SQL语句。

#!/usr/bin/env python
# -*- coding:utf-8 -*-
from sqlalchemy import create_engine
  
  
engine = create_engine("mysql+pymysql://root:123@127.0.0.1:3306/t1", max_overflow=5)
  
# 执行SQL
# cur = engine.execute(
#     "INSERT INTO hosts (host, color_id) VALUES ('1.1.1.22', 3)"
# )
  
# 新插入行自增ID
# cur.lastrowid
  
# 执行SQL
# cur = engine.execute(
#     "INSERT INTO hosts (host, color_id) VALUES(%s, %s)",[('1.1.1.22', 3),('1.1.1.221', 3),]
# )
  
  
# 执行SQL
# cur = engine.execute(
#     "INSERT INTO hosts (host, color_id) VALUES (%(host)s, %(color_id)s)",
#     host='1.1.1.99', color_id=3
# )
  
# 执行SQL
# cur = engine.execute('select * from hosts')
# 获取第一行数据
# cur.fetchone()
# 获取第n行数据
# cur.fetchmany(3)
# 获取所有数据
# cur.fetchall()

二、ORM功能使用

　　使用 ORM/Schema Type/SQL Expression Language/Engine/ConnectionPooling/Dialect 所有组件对数据进行操作。根据类创建对象，对象转换成SQL，执行SQL。

1、建表

1、创建一个单表的基本

#!/usr/bin/env/ python
# -*-coding:utf-8 -*-
import sqlalchemy
from sqlalchemy import create_engine
from sqlalchemy.ext.declarative import declarative_base
from sqlalchemy import Column,Integer ,String,UniqueConstraint,Index
from sqlalchemy.orm import sessionmaker

print(sqlalchemy.__version__)#查看版本信息

engine=create_engine('mysql+pymysql://root:a741258963@localhost:3306/tb1?charset=utf8',echo=True,max_overflow=5)
#如果有中文需加上charset=utf8
#echo=True 执行时输出由类创表时翻译成的sql语句,
#max_overflow 表示最大连接数

Base=declarative_base() #生成一个SQLORM基类

class Staff(Base):
    __tablename__="staffs"
    id=Column(Integer,primary_key=True,autoincrement=True)
    name=Column(String(16),index=True)#index=True,建立普通索引
    age=Column(Integer)
    sex=Column(String(32))
    department=Column(String(16))

#创建索引的另一种方式
    __table_args__=(
        #联合索引
        UniqueConstraint('id','name',name='uix_id_name'),
        Index('id','name')
    )

    def __repr__(self):  #在打印对象时，默认输出该方法的返回值。
        return "<Staff(name='%s',age='%s',sex='%s',department='%s')?>"%(self.name,self.age,self.sex,self
                                                                 .department)

Base.metadata.drop_all(engine)#删除所有表
Base.metadata.create_all(engine) #创建所有表结构


#!/usr/bin/env/ python
# -*-coding:utf-8 -*-
import sqlalchemy
from sqlalchemy import create_engine
from sqlalchemy.ext.declarative import declarative_base
from sqlalchemy import Column,Integer ,String,UniqueConstraint,Index
from sqlalchemy.orm import sessionmaker

print(sqlalchemy.__version__)#查看版本信息

engine=create_engine('mysql+pymysql://root:a741258963@localhost:3306/tb1?charset=utf8',echo=True,max_overflow=5)
#如果有中文需加上charset=utf8
#echo=True 执行时输出由类创表时翻译成的sql语句,
#max_overflow 表示最大连接数

Base=declarative_base() #生成一个SQLORM基类

class Staff(Base):
    __tablename__="staffs"
    id=Column(Integer,primary_key=True,autoincrement=True)
    name=Column(String(16),index=True)#index=True,建立普通索引
    age=Column(Integer)
    sex=Column(String(32))
    department=Column(String(16))

#创建索引的另一种方式
    __table_args__=(
        #联合索引
        UniqueConstraint('id','name',name='uix_id_name'),
        Index('id','name')
    )

    def __repr__(self):  #在打印对象时，默认输出该方法的返回值。
        return "<Staff(name='%s',age='%s',sex='%s',department='%s')?>"%(self.name,self.age,self.sex,self
                                                                 .department)

Base.metadata.drop_all(engine)#删除所有表
Base.metadata.create_all(engine) #创建所有表结构

"""
 """
session的主要目的是建立与数据库的会话，它维护你加载和关联的所有数据库对象。它是数据库查询（Query）的一个入口
"""
"""

# 创建session对象
Mysession=sessionmaker(bind=engine)#创建绑定到数据库的Session对象
session=Mysession() #实例例化一个Session和数据库建立连接

#这两行触发sessionmaker类下的__call__方法,return得到Session的实例，赋给变量session，所以session可以调用Session类下的add，add_all等方法

#添加对象
staff=Staff(name='xiaoli',age='25',sex='female',department='IT')
print(staff)
session.add(staff)
#添加多条
session.add_all([
    Staff(name='xiaoming', age=22,sex='male',department='HR'),
    Staff(name='xiaohong', age=30, sex='female',department="manage"),
    Staff(name='xiaojun', age=23, sex='male',department='IT')
])
#回滚
# session.rollback()#假如在commit前添加了回滚的话,上面对表的修改便不起作用

#提交数据
session.commit() #查询不需要commit,其他都要
# 关闭session:
session.close()

# 创建session对象
Mysession=sessionmaker(bind=engine)#创建绑定到数据库的Session对象
session=Mysession() #实例例化一个Session和数据库建立连接

#这两行触发sessionmaker类下的__call__方法,return得到Session的实例，赋给变量session，所以session可以调用Session类下的add，add_all等方法

#添加对象
staff=Staff(name='xiaoli',age='25',sex='female',department='IT')
print(staff)
session.add(staff)
#添加多条
session.add_all([
    Staff(name='xiaoming', age=22,sex='male',department='HR'),
    Staff(name='xiaohong', age=30, sex='female',department="manage"),
    Staff(name='xiaojun', age=23, sex='male',department='IT')
])
#回滚
# session.rollback()#假如在commit前添加了回滚的话,上面对表的修改便不起作用

#提交数据
session.commit() #查询不需要commit,其他都要
# 关闭session:
session.close()

创建表

print(session.query(Staff).all())
for row in session.query(Staff).order_by(Staff.id):
     print(row)
for row in session.query(Staff).filter(Staff.department.in_(['IT', 'HR'])):#这里的部门是完全匹配
    print(row)
for row in session.query(Staff).filter(~Staff.department.in_(['IT', 'HR'])): # ~Staff.department.in_表示取反
    print(row)
print(session.query(Staff).filter(Staff.department == 'IT').count())#查询个数

#引入and_, or_方法
from sqlalchemy import and_, or_

for row in session.query(Staff).filter(and_(Staff.department == 'IT', Staff.sex =='male')):
    print(row)
for row in session.query(Staff).filter(or_(Staff.department == 'IT', Staff. department== 'HR')):
    print(row)

filter和filter_by的区别:filter_by接收的参数形式是关键字参数，而filter接收的参数是更加灵活的SQL表达式结构

　　filter_by（关键字参数）用于对列名称的简单查询，指定列名时，不需要额外指定类名,如：

session.query(Staff).filter(department='IT')

　　filter（条件判断），可以像写sql 的 where 条件那样写 > < 等条件，但引用列名时，需要通过类名.属性名 的方式。 

session.query(Staff).filter(Staff.department == 'IT')

注意：filter使用的是赋值=，而filter使用的是判断==

2、一对多

#!/usr/bin/env/ python
# -*-coding:utf-8 -*-
import sqlalchemy
from sqlalchemy import create_engine
from sqlalchemy.ext.declarative import declarative_base
from sqlalchemy import Column,Integer ,String,ForeignKey
from sqlalchemy.orm import sessionmaker,relationship

engine=create_engine('mysql+pymysql://root:a741258963@localhost:3306/tb1',echo=True,max_overflow=5)

Base=declarative_base() #生成一个SQLORM基类

# 一对多
class Favor(Base):
    __tablename__ = 'favor'
    nid = Column(Integer, primary_key=True)
    caption = Column(String(50), default='red', unique=True)

    def __repr__(self):
        return "%s-%s" %(self.nid, self.caption)

class Person(Base):
    __tablename__ = 'person'
    nid = Column(Integer, primary_key=True)
    name = Column(String(32), index=True, nullable=True)
    favor_id = Column(Integer, ForeignKey("favor.nid"))#外键
    # 关联查询 基于join功能实现的
    favor = relationship("Favor", backref='person')#与Favor类进行关联，与生成表结构无关，仅用于查询方便
#backref:在一对多或多对一之间建立双向关系  ，Person对象获取favor,Person.favor,反过来Favor对象可以获取Person，favor.person.

def init_db():
    Base.metadata.create_all(engine)

def drop_db():
    Base.metadata.drop_all(engine)

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

f=Favor(caption='yellow')
P1=Person(name='p1',favor_id=1)
P2=Person(name='p2',favor_id=1)

# 传统方法
# session.add(f)
# session.commit()
# session.add_all([P1,P2])

# relationship方法
f.person=[P1,P2] #添加关联对象 只有建立relationship后才能用这个方法

session.add(f)
session.commit()

#继续添加
P3=Person(name='p3',favor_id=1)
f.person.append(P3)

session.add(f)
session.commit()

3、多对多

#!/usr/bin/env/ python
# -*-coding:utf-8 -*-
import sqlalchemy
from sqlalchemy import create_engine
from sqlalchemy.ext.declarative import declarative_base
from sqlalchemy import Column,Integer ,String,ForeignKey
from sqlalchemy.orm import sessionmaker,relationship

engine=create_engine('mysql+pymysql://root:a741258963@localhost:3306/tb1?charset=utf8',max_overflow=5)

Base=declarative_base() #生成一个SQLORM基类

# 多对多
class ServerToGroup(Base):
    # 关系表要放对应表上面，否则找不到
    __tablename__ = 'servertogroup'
    nid = Column(Integer, primary_key=True, autoincrement=True)
    server_id = Column(Integer, ForeignKey('server.id'))
    group_id = Column(Integer, ForeignKey('group.id'))
    # group = relationship("Group", backref='s_g')
    # server = relationship("Server", backref='g_s')

class Group(Base):
    __tablename__ = 'group'
    id = Column(Integer, primary_key=True)
    name = Column(String(64), unique=True, nullable=False)
    port = Column(Integer, default=22)
    server = relationship('Server', secondary=ServerToGroup.__table__, backref='group')  # secondary 这里指定关联表,ServerToGroup.__table__==>servertogroup
    def __repr__(self):
        return self.name

class Server(Base):
    __tablename__ = 'server'

    id = Column(Integer, primary_key=True, autoincrement=True)
    hostname = Column(String(64), unique=True, nullable=False)

    def __repr__(self):
        return self.hostname

def init_db():
    Base.metadata.create_all(engine)

def drop_db():
    Base.metadata.drop_all(engine)

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

S1=Server(hostname="127.0.0.1")
S2=Server(hostname="127.0.0.2")
G1=Group(name="A")
G2=Group(name="B")
G3=Group(name="C")

#法一 传统
# session.add_all([S1,S2,G1,G2,G3])
# session.commit()
# t1=ServerToGroup(server_id=1,group_id=2)
# session.add(t1)
# session.commit()

#方法二 relationship方法
S1.group=[G1,G2] #添加关联对象
S2.group=[G2,G3]

session.add_all([S1,S2,G1,G2,G3])
session.commit()

#查
print('--------通过group表查与其关联的主机---------')
group_obj = session.query(Group).filter_by(name='B').first()
print(group_obj, group_obj.server)

print('--------通过server表查与其关联的组---------')
server_obj = session.query(Server).filter_by(hostname="127.0.0.1").first()
print(server_obj, server_obj.group)

#删
# 通过group删除server
server_obj = session.query(Server).filter_by(hostname="127.0.0.1").first()
group_obj = session.query(Group).filter_by(name="B").first()

group_obj.server.remove(server_obj)  # 从group删除server
session.commit()

#直接删除
# 删除group时，会把这个group跟其关联server也自动删除
group_obj = session.query(Group).filter_by(name="B").first()
session.delete(group_obj)
session.commit()

# 也就是说，127.0.0.1本来关联的group为[A,B]，当B删除后，与其关联的组就剩下[A]了
server_obj = session.query(Server).filter_by(hostname="127.0.0.1").first()
print(server_obj, server_obj.group)


#关闭session
session.close()

 2、操作表

1、增：

#添加对象
staff=Staff(name='xiaoli',age='25',sex='female',department='IT')
session.add(staff)
#添加多条
session.add_all([
    Staff(name='xiaoming', age=22,sex='male',department='HR'),
    Staff(name='xiaohong', age=30, sex='female',department="manage"),
    Staff(name='xiaojun', age=23, sex='male',department='IT')
])

 2、删：

session.query(Staff).filter(Staff.id > 2).delete()
session.commit()

 3、改：

# 改
session.query(Staff).filter(Staff.id > 2).update({"name" : "099"})
session.query(Staff).filter(Staff.id > 2).update({Staff.name: Staff.name + "099"}, synchronize_session=False)
session.query(Staff).filter(Staff.id > 2).update({"age": Staff.age + 1}, synchronize_session="evaluate")
session.commit()

4、查

# 查
ret = session.query(Staff.name).all()
ret = session.query(Staff).filter_by(department = 'IT').all()
ret = session.query(Staff).filter_by(department = 'IT').first()

5、其他

#　条件
ret = session.query(Users).filter_by(name='sb').all()
ret = session.query(Users).filter(Users.id > 1, Users.name == 'sb').all()
ret = session.query(Users).filter(Users.id.between(1, 3), Users.name == 'sb').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='sb'))).all()
from sqlalchemy import and_, or_
ret = session.query(Users).filter(and_(Users.id > 3, Users.name == 'sb')).all()
ret = session.query(Users).filter(or_(Users.id < 2, Users.name == 'sb')).all()
ret = session.query(Users).filter(
    or_(
        Users.id < 2,
        and_(Users.name == 'sb', 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()

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

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

# 分组
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()

# 连表

ret = session.query(Users, Favor).filter(Users.id == Favor.nid).all()

ret = session.query(Person).join(Favor).all()

ret = session.query(Person).join(Favor, isouter=True).all()


# 组合
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()

更多：https://github.com/lzjun567/note/blob/master/note/python/sqlalchemy.md