python -- sqlalchemy

1、sqlAlchemy简介

#!/usr/bin/env python
# -*- coding:utf8 -*-

'''
sqlalchemy是orm(对象关系映射)，是第三方库，需要安装。
orm优点：
    隐藏了数据访问细节，“封闭”的通用数据库交互，ORM的核心。他使得我们的通用数据库交互变得简单易行，并且完全不用考虑该死的SQL语句。快速开发，由此而来。
    ORM使我们构造固化数据结构变得简单易行。

orm缺点：
    无可避免的，自动化意味着映射和关联管理，代价是牺牲性能（早期，这是所有不喜欢ORM人的共同点）。现在的各种ORM框架都在尝试使用各种方法来减轻这块（LazyLoad，Cache），
效果还是很显著的。
    
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...]
    
'''

2、sqlAlchemy连接mysql基础

#!/usr/bin/env python
# -*- coding:utf8 -*-

import sqlalchemy
from sqlalchemy import create_engine
from sqlalchemy.ext.declarative import declarative_base
from sqlalchemy import Column, Integer, String
from sqlalchemy.orm import sessionmaker

engine = create_engine("mysql+pymysql://root:root@127.0.0.1:3306/pystudy?charset=utf8",echo=True)
        #端口默认可不写，echo=True返回创建过程执行细节
# engine = create_engine("mysql+pymysql://root:root@127.0.0.1:3306/pystudy",encoding='utf8')

Base = declarative_base()  #生成orm基类

class Host(Base):  #映射，把表映射成一个类
    __tablename__ = 'hosts'   #表名
    id = Column(Integer, primary_key=True, autoincrement=True)
                    #autoincrement=True  默认就是True，可以不写，自增
    hostname = Column(String(64), unique=True, nullable=False)
    ip_addr = Column(String(128), unique=True, nullable=False)
    port = Column(Integer, default=22)

Base.metadata.create_all(engine) #创建所有表结构(如果该表已经存在，do nothing)

if __name__ == '__main__':
    #上面的代码只是创建一个表，若要对指定表进行增删查改等操作，要先建立一个会话
    Session_class = sessionmaker(bind=engine) #创建与数据库的会话session_class类
        #注意：这里返回给session的是个class,不是实例
    Session = Session_class()  #生成session实例，接下来就可以进行操作了

#添加数据
    # h1 = Host(hostname='localhost', ip_addr='127.0.0.1')
    # h2 = Host(hostname='txowner',ip_addr='127.0.0.1', port=9999)
    # # session.add(h1)  #一次把一条数据对象添加到会话中，还没插入
    # # session.add(h2)
    # Session.add_all([h1,h2])  #同时把多条数据对象添加到会话中，等待插入(参数以列表形式传递)
    # Session.commit()  #此时才将数据插入到表中(这样更好的支持回滚操作)

#修改数据（先查询出来再修改，不能直接修改）
    # res = Session.query(Host).filter(Host.hostname == 'txowner').all()
    # # print("=====>",res)  # first表示第一个  all 表示全部
    # res.hostname = 'test server'
    # Session.commit()  #这里不强求，通常加上

#删除数据(先查询出来再删除，不能直接删除)
    res = Session.query(Host).filter(Host.hostname == 'test server').first()
    # print("--->",res)
    Session.delete(res)  #一次只删除一条
    Session.commit()

#查询数据
'''
在sqlalchemy中，很多sql关键字为了与python的关键字区分，做了如下改变：
    函数 in_ ： 接收列表参数，作用与sql内置的in一样 
             eg：user.username.in_(['root','admin']) 
       not in:  eg:  ~user.username.in_(['root','admin'])
    is None :  eg: user.username.is_(None)、user.username == None
    is not None eg: user.username.isnot(None)
    
    like :  user.userneme.like('ad%')
    
    and_、or_ 在sqlalchemy中，需要单独导入, 跟的几个参数就是几个条件
    
    filter、filter_by 区别：
        filter在判断条件时，等号要用 '=='
        filter_by在判断条件时，等号用 '='
    order_by 按什么来排序 eg: order_by(user.id) 
'''

3、连接查询

#!/usr/bin/env python
# -*- coding:utf8 -*-

'''
连接查询：
    把几张表根据连接方式连接到一起，返回一个多个表组成的射影
    
原生sql语句：  eg： 表a -> 列 ca    表b -> 列 cb 
内连接查询(求两表的交集组成的表)：select * from a inner join b on a.ca = b.cb
左连接查询(表a的全列出来，表b没有这么多就填null，以a为准)：select * from a left join b on a.ca = b.cb
右连接查询(表b的全列出来，表a没有这么多就填null，以b为准)：select * from a right join b on a.ca = b.cb

涉及到多张表的查询就要用join连接查询
'''
import sqlalchemy
from sqlalchemy import Column, Integer, String, ForeignKey,func
from sqlalchemy import create_engine
from sqlalchemy.ext.declarative import declarative_base
from sqlalchemy.orm import sessionmaker, relationship

#连接数据库
engine = create_engine("mysql+pymysql://root:root@localhost:3306/pystudy?charset=utf8",echo=True)

#生成orm基类
Base = declarative_base()

#这里的应用情景是一个主机只属于一个组，一个组可以有多个主机
class Host(Base):
    __tablename__ = 'hosts'
    id = Column(Integer, primary_key=True)
    hostname = Column(String(64),unique=True,nullable=False)
    ip_addr = Column(String(128),unique=True,nullable=False)
    port = Column(Integer, default=22)
    group_id = Column(Integer,ForeignKey('group.id'))
    group_map = relationship('Group', backref='host_map')   #这里类似于反射，把Group表对象反射到当前属性

class Group(Base):
    __tablename__ = 'group'
    id = Column(Integer,primary_key=True)
    name = Column(String(64),unique=True,nullable=False)


#创建所有表结构(如果该表已经存在，do nothing)
Base.metadata.create_all(engine)

if __name__ == '__main__':
    #生成session实例
    Session_class = sessionmaker(bind=engine)  #生成session_class类
    session = Session_class()  #实现session_class类的实例

    #在这里表中之前就有插入的数据

    #通过host表查询group的信息(relationship的作用)
    h = session.query(Host).filter(Host.hostname=='localhost').first()
    print('--->',h.group_map.name)

    # 通过group表查询hosts的信息(relationship的作用)
    g = session.query(Group).filter(Group.id == 2).first()
    print('--->', g.host_map)

    #连接查询(内连接查询)
    join_select_all1 = session.query(Host).join(Host.group_map)
    print("join_select_all:",join_select_all1)  #这里结果是返回的sql原生语句
    '''
    join_select_all: SELECT hosts.id AS hosts_id, hosts.hostname AS hosts_hostname, hosts.ip_addr AS hosts_ip_addr, hosts.port AS hosts_port,
    hosts.group_id AS hosts_group_id FROM hosts INNER JOIN `group` ON `group`.id = hosts.group_id
    '''
    join_select_all2 = session.query(Host).join(Host.group_map).all()
    print("join_select_all:", join_select_all2)  #这里返回的查询结果
    '''
    join_select_all: [<__main__.Host object at 0x000001500637D080>, <__main__.Host object at 0x000001500637D358>]

    '''

    #在sqlalchemy中，聚合函数(count、sum、max...)在func中，要导入
    #group_by 分组聚合(group_by加在条件判断(filter)后面)
    join_select_all3 = session.query(Host,func.count(Group.name)).join(Host.group_map).group_by(Group.id).all()
    print("分组聚合后的结果：",join_select_all3)
    session.commit()

4、一对多外键关联

#!/usr/bin/env python
# -*- coding:utf8 -*-

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

#连接数据库
engine = create_engine("mysql+pymysql://root:root@localhost:3306/pystudy?charset=utf8",echo=True)

#生成orm基类
Base = declarative_base()

#这里的应用情景是一个主机只属于一个组，一个组可以有多个主机
class Host(Base):
    __tablename__ = 'host'
    id = Column(Integer, primary_key=True)
    hostname = Column(String(64),unique=True,nullable=False)
    ip_addr = Column(String(128),unique=True,nullable=False)
    port = Column(Integer, default=22)
    group_id = Column(Integer,ForeignKey('group.id'))
    group_map = relationship('Group')   #这里类似于反射，把Group表对象反射到当前属性

class Group(Base):
    __tablename__ = 'group'
    id = Column(Integer,primary_key=True)
    name = Column(String(64),unique=True,nullable=False)


#创建所有表结构(如果该表已经存在，do nothing)
Base.metadata.create_all(engine)

if __name__ == '__main__':
    #生成session实例
    Session_class = sessionmaker(bind=engine)  #生成session_class类
    session = Session_class()  #实现session_class类的实例
    '''
         #插入数据
        g1 = Group(name='g1')   #在group表中插入三条数据，创建三个组
        g2 = Group(name='g2')
        g3 = Group(name='g3')
        session.add_all([g1,g2,g3])  #这里只是把对象添加到会话，还没有插入

        # session.commit()

        h1 = Host(hostname='localhost', ip_addr='127.0.0.1', port=9999, group_id=g2.id)
        h2 = Host(hostname='ubuntu', ip_addr='192.168.22.132', port=6666, group_id=g1.id)
        session.add_all([h1,h2])

        session.commit()

    '''
    '''
        #两表链接修改数据
        g1 = session.query(Group).filter(Group.name == 'g1').first()
        g2 = session.query(Group).filter(Group.name == 'g2').first()
        h1 = session.query(Host).filter(Host.hostname == 'localhost').update({'group_id':g2.id})
        h2 = session.query(Host).filter(Host.hostname == 'ubuntu').update({'group_id': g1.id})
        session.commit()
    '''
    #通过host表查询group的信息(relationship的作用)
    h = session.query(Host).filter(Host.hostname=='localhost').first()
    print('--->',h.group_map.name)
    session.commit()

5、一对多外键关联改进

#!/usr/bin/env python
# -*- coding:utf8 -*-

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

#连接数据库
engine = create_engine("mysql+pymysql://root:root@localhost:3306/pystudy?charset=utf8",echo=True)

#生成orm基类
Base = declarative_base()

#这里的应用情景是一个主机属于一个组，一个组可以有多个主机
class Host(Base):
    __tablename__ = 'host'
    id = Column(Integer, primary_key=True)
    hostname = Column(String(64),unique=True,nullable=False)
    ip_addr = Column(String(128),unique=True,nullable=False)
    port = Column(Integer, default=22)
    group_id = Column(Integer,ForeignKey('group.id'))
    group_map = relationship('Group',backref='host_list')
        #这里类似于反射，把Group表对象反射到当前属性
    # group_map = relationship('Group',back_populates='host_list')
    # back_populates作用与backref一样，但是backref只需要一个表指明，
    # 而back_populates要两个表同时有，
    # 两个表直接不写back_populates一样可以实现，所以一般用backref

class Group(Base):
    __tablename__ = 'group'
    id = Column(Integer,primary_key=True)
    name = Column(String(64),unique=True,nullable=False)
    # host_list = relationship('Host',back_populates='group_map')


#创建所有表结构(如果该表已经存在，do nothing)
Base.metadata.create_all(engine)

if __name__ == '__main__':
    #生成session实例
    Session_class = sessionmaker(bind=engine)  #生成session_class类
    session = Session_class()  #实现session_class类的实例
    '''
         #插入数据
        g1 = Group(name='g1')   #在group表中插入三条数据，创建三个组
        g2 = Group(name='g2')
        g3 = Group(name='g3')
        session.add_all([g1,g2,g3])  #这里只是把对象添加到会话，还没有插入

        # session.commit()

        h1 = Host(hostname='localhost', ip_addr='127.0.0.1', port=9999, group_id=g2.id)
        h2 = Host(hostname='ubuntu', ip_addr='192.168.22.132', port=6666, group_id=g1.id)
        session.add_all([h1,h2])

        session.commit()

    '''
    '''
        #两表链接修改数据
        g1 = session.query(Group).filter(Group.name == 'g1').first()
        g2 = session.query(Group).filter(Group.name == 'g2').first()
        h1 = session.query(Host).filter(Host.hostname == 'localhost').update({'group_id':g2.id})
        h2 = session.query(Host).filter(Host.hostname == 'ubuntu').update({'group_id': g1.id})
        session.commit()
    '''
    #通过host表查询group的信息(relationship的作用)
    h = session.query(Host).filter(Host.hostname=='localhost').first()
    g = session.query(Group).filter(Group.id == 2).first()
    print('g:',g.name,g.id)
    print('--->',h.group_map.name)
    print('===>',g.host_list)
    # g.host_list.group_id 报错，这里到底查询出来的是什么？
    session.commit()

6、多对多外键关联

#!/usr/bin/env python
# -*- coding:utf8 -*-

'''
多对多外键关联时跟不同程序进程间通信一样，需要第三方中介
在这里两个表多对多关联查询时，常常需要第三个表只作为中间桥梁

多对多关联：
    1、创建中间表，关联其他两个表，不用class方式，直接是类似这种方式：
        Host2Group = Table('host_2_group',Base.metadata,
            Column('host_id',ForeignKey('host.id'),primary_key=True),
            Column('group.id',ForeignKey('group.id'),primary_key=True),
        )
        Host2Group是一个实例，不再是类
    
    2、创建两个表映射类，并在两个类中分别指定中间表实例
        class Host(Base):
            __tablename__ = 'host'
            id = Column(Integer, primary_key=True)
            hostname = Column(String(64),unique=True,nullable=False)
            ip_addr = Column(String(128),unique=True,nullable=False)
            port = Column(Integer, default=22)
            groups = relationship('Group',
                                secondary=Host2Group, #指定中间表实例
                                backref=hosts   #设置反向的反射
                                )
        
    3、先创建组数据，在创建主机数据，(没有区分先后一样可行)然后将他们关联
       
'''

import sqlalchemy
from sqlalchemy import Column, Integer, String, ForeignKey,func,Table
from sqlalchemy import create_engine
from sqlalchemy.ext.declarative import declarative_base
from sqlalchemy.orm import sessionmaker, relationship

#连接数据库
engine = create_engine("mysql+pymysql://root:root@localhost:3306/pystudy?charset=utf8",
                       echo=True)

#生成orm基类
Base = declarative_base()

#创建一个中间表的实例
Host2Group = Table('host_2_group',Base.metadata,
                   Column('host.id',ForeignKey('host.id'), primary_key=True),
                   Column('group.id',ForeignKey('group.id'), primary_key=True),
                   #这里把两个列都设置为主键，使得每一条数据中这两个列都为唯一确定
                   )

#这里的应用情景是一个主机属于多个组，一个组可以有多个主机
class Host(Base):
    __tablename__ = 'host'
    id = Column(Integer, primary_key=True)
    hostname = Column(String(64), unique=True, nullable=False)
    ip_addr = Column(String(128), unique=True, nullable=False)
    port = Column(Integer, default=22)
    # group_id = Column(Integer,ForeignKey('group.id'))
    # #这里就不用再设置外键，secondary替代工作
    groups = relationship('Group',
                             secondary=Host2Group,
                             backref='hosts')
        #这里类似于反射，把Group表对象反射到当前属性
    # group_map = relationship('Group',back_populates='host_list')
    # back_populates作用与backref一样，但是backref只需要一个表指明，
    # 而back_populates要两个表同时有，
    # 两个表直接不写back_populates一样可以实现，所以一般用backref

    #打印字符串格式
    def __repr__(self):
        return "<id=%s, hostname=%s, ip_addr=%s, port=%s>" %(self.id,
                                                             self.hostname,
                                                             self.ip_addr,
                                                             self.port)

class Group(Base):
    __tablename__ = 'group'
    id = Column(Integer, primary_key=True)
    name = Column(String(64), unique=True, nullable=False)
    # host_list = relationship('Host',back_populates='group_map')

    def __repr__(self):
        return "<id=%s, name=%s>" %(self.id, self.name)

#创建所有表结构(如果该表已经存在，do nothing)
Base.metadata.create_all(engine)

if __name__ == '__main__':
    #生成session实例
    Session_class = sessionmaker(bind=engine)  #生成session_class类
    session = Session_class()  #实现session_class类的实例

    '''
    #插入数据(插入数据只能插入一次，若数据存在后再进行插入就会报错)
    #实例化Host类的对象(host表中的一条条数据)
    h1 = Host(hostname='localhost', ip_addr='127.0.0.1', port=6666)
    h2 = Host(hostname='ubuntu', ip_addr='192.168.22.132', port=9999)
    h3 = Host(hostname='centos', ip_addr='192.168.22.129')
    #把对象添加到会话中
    session.add_all([h1, h2, h3])

    #实例化Group类的对象(group表中的一条条数据)
    g1 = Group(name='g1')
    g2 = Group(name='g2')
    g3 = Group(name='g3')
    g4 = Group(name='g4')
    session.add_all([g1, g2, g3, g4])

    #把实例化的对象数据分别插入到对应表中
    session.commit()
    '''

    #关联数据
    #分别查找出主机和要设置的所属组，再对他们进行关联
    g = session.query(Group).all()
    g4 = session.query(Group).filter(Group.name=='g4').first()
    h1 = session.query(Host).filter(Host.hostname=='localhost').first()

    h1.groups = g  #对一个主机设置所有分组 h1.groups就是一个列表
    h1.groups.pop()   #删除某个主机的指定分组
    h1.groups = [g4]  #这里必须是列表
    session.commit()

    print('g:',g)
    #[<id=1, name=g1>, <id=2, name=g2>, <id=3, name=g3>, <id=4, name=g4>]
    #type g: <class 'list'>
    print('g4:',g4)
    print('h1:',h1)
    #<id=1, hostname=localhost, ip_addr=127.0.0.1, port=6666>
    print('反向测试：',g4.hosts)
    #反向测试： [<id=1, hostname=localhost, ip_addr=127.0.0.1, port=6666>]