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>]