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。用户包括OpenStack\dropbox等知名公司或应用，主要用户列表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()
