Python-day(十二)-ORM之sqlalchemy

一、ORM介绍

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

　　ORM的优点：

        1、隐藏了数据访问细节，“封闭”的通用数据库交互，ORM的核心，他使得我们的通用数据库交互变的简单易行，并且完全不用考虑该死的sql语句。快速开发，由此而来。
        2、ORM使我们构造固化数据结构变的简单易行。   

　　ORM的缺点：

        1、无可避免的，自动化意味这映射和关联管理，代价是牺牲性能(早期，这是所有人不喜欢ORM人的共同点)。现在的各种ORM框架都在尝试使用各种方法来减轻这块(lazyload，cache)，效果还是很显著的。

二、sqlalchemy安装

    在python中，最有名的ORM框架是SQLAlchemy。用户包括OpenStackdropbox等知名公司或应用，主要用户列表http://www.sqlalchemy.org/organizations.html#openstack
    
   

 

    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
    
    安装sqlalchemy
        pip3 install SQLAlchemy
        pip3 install pymysql #由于mysqldb依然不支持py3，所以这里我们用pymysql与sqlalchemy交互。

三、内部处理

    使用Engine/ConnectionPilling/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。

4.1、创建表

        #!/usr/bin/env python
        from sqlalchemy.ext.declarative import declarative_base
        from sqlalchemy import Column,Integer,String,ForeignKey,UniqueConstraint,Index
        from sqlalchemy.orm import sessionmaker,relationship
        from sqlalchemy import create_engine
        
        engine= create_engine("mysql+pymysql://chenxin:cx123456@172.16.17.100:3306/chenxin",max_overflow=5)
        
        Base=declarative_base()
        
        #创建单表
        
        class Users(Base):
            __tablename__ = 'users'
            id = Column(Integer,primary_key=True)
            name=Column(String(32))
            extra =Column(String(16))
        
            __table_args__ = (
                UniqueConstraint('id','name',name='uix_id_name'),
                    Index('ix_id_name','name','extra'),
            )
        # #一对多
        class Favor(Base):
            __tablename__='favor'
            nid=Column(Integer,primary_key=True)
            caption = Column(String(50),default='red',unique=True)
        
        class Person(Base):
            __tablename__='preson'
            nid=Column(Integer,primary_key=True)
            name=Column(String(32),index=True,nullable=True)
            favor_id=Column(Integer,ForeignKey("favor.nid"))
        
        #多对多
        class Group(Base):
            __tablename__='group'
            id=Column(Integer,primary_key=True)
            name=Column(String(64),unique=True,nullable=False)
            port=Column(Integer,default=22)
        class Server(Base):
            __tablename__='server'
            id=Column(Integer,primary_key=True,autoincrement=True)
            hostname=Column(String(64),unique=True,nullable=False)
        
        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'))
        
        def init_db():
            Base.metadata.create_all(engine)
        
        def drop_db():
            Base.metadata.drop_all(engine)
        
        
        if __name__ == '__main__':
            # drop_db()
            init_db()    

4.2、操作表

4.2.1、表结构+数据库连接       

内容为：创建表+创建会话内容
　　创建会话           

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

4.2.2、增

obj=Users(name="chenxin",extra='nb')
session.add(obj) #增加一条
session.add_all([
Users(name='chenxin1',extra='nb1'),
Users(name='chenxin3',extra='nb3'),])#增加多条
session.commit()#提交

4.2.3、删

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

4.2.4、改

session.query(Users).filter(Users.id >1).update({"name":"Cx"})#将id大于1的所有数据的name都改为Cx session.query(Users).filter(Users.id>1).update({Users.name:Users.name+"099"},synchronize_session=False)#将id大于1的所有数据的name都追加099字符
session.query(Users).filter(Users.id >1).update({"num":Users.num+1},synchronize_session='evaluate')
session.commit()   

4.2.5、查

ret=session.query(Users).all()#查询所有Users表的数据，以列表的形式返回，返回格式[id-name]
ret=session.query(Users.name,Users.extra).all()#查询指定字段的值，以元组的形式返回，每条数据是一个元组
ret=session.query(Users).filter_by(name='chenxin').all()#查询name是chenxin的数据，以列表的实现返回，返回格式[id-name]
ret=session.query(Users).filter_by(name='chenxin').first()#查询name=chenxin的数据
print(ret)

4.2.6、其他        

　　　　条件

ret=session.query(Users).filter_by(name='chenxin').all() #查询条件是name=chenixn的数据
ret=session.query(Users).filter(Users.id>1,Users.extra=='nb2').all()#查询条件是id>1，extra=nb2的数据
ret = session.query(Users).filter(Users.id.between(1,3),Users.name =='Cx099').all()#查询条件是id在1到3的范围，name=Cx099的数据
ret=session.query(Users).filter(Users.id.in_([1,3,4])).all()#查询条件的id在1，3，4中的所有字段
ret=session.query(Users).filter(~Users.id.in_([1,3,4])).all()#查询条件的id不在1，3，4中的所有字段         ret=session.query(Users).filter(Users.id.in_(session.query(Users.id).filter_by(name='chenxin'))).all()#
print(ret)                    

　　　　通配符

ret=session.query(Users).filter(Users.name.like('Cx%')).all() #查找Cx开头的字段
ret=session.query(Users).filter(~Users.name.like('Cx%')).all()#查找排除Cx开头的字段                

　　　　限制

ret=session.query(Users)[1:3] #排除1和3的id数据          

　　　　排序

ret=session.query(Users).order_by(Users.id.desc()).all()#按照id排序，倒序排序，默认是正序排列             
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) >1).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()