Python学习笔记整理总结【ORM（SQLAlchemy）】，ormsqlalchemy,一、介绍SQLAlc

Python学习笔记整理总结【ORM（SQLAlchemy）】，ormsqlalchemy,一、介绍SQLAlc

一、介绍
SQLAlchemy是Python编程语言下的一款ORM框架，该框架建立在数据库API之上，使用关系对象映射进行数据库操作，简言之便是：将对象转换成SQL，然后使用数据API执行SQL并获取执行结果。

优点：
隐藏了数据访问细节，“封闭”的通用数据库交互，ORM的核心。他使得我们的通用数据库交互变得简单易行，并且完全不用考虑该死的SQL语句。快速开发，由此而来。
ORM使我们构造固化数据结构变得简单易行。
缺点：
无可避免的，自动化意味着映射和关联管理，代价是牺牲性能（早期，这是所有不喜欢ORM人的共同点）。现在的各种ORM框架都在尝试使用各种方法来减轻这块（LazyLoad，Cache），效果还是很显著的。

第一个阶段：将SQLAlchemy的对象换成可执行的sql语句（使用者通过ORM对象提交命令；将命令交给SQLAlchemy Core（Schema/Types SQL Expression Language）转换成SQL）
第二个阶段：将sql语句交给数据库执行（匹配使用者事先配置好的egine；egine从连接池中取出一个链接；基于该链接通过Dialect调用DB API，将SQL转交给它去执行）

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

#1、MySQL-Python    mysql+mysqldb://<user>:<password>@<host>[:<port>]/<dbname>#2、pymysql    mysql+pymysql://<username>:<password>@<host>/<dbname>[?<options>] #3、MySQL-Connector    mysql+mysqlconnector://<user>:<password>@<host>[:<port>]/<dbname>#4、cx_Oracle    oracle+cx_oracle://user:pass@host:port/dbname[?key=value&key=value...]安装： pip install SQLAlchemypip install pymysql #由于mysqldb依然不支持py3，所以这里我们用pymysql与sqlalchemy交互

二、sqlalchemy基本使用
注：
　　#类 ==>表
　　#对象 ==>表中的一行记录

<1>基本语法（增/删/改/查/回滚）

####用到的表结构1####from sqlalchemy import create_enginefrom sqlalchemy.ext.declarative import declarative_basefrom sqlalchemy import Column,Integer,String,ForeignKeyfrom sqlalchemy.orm import sessionmakerengine = create_engine("mysql+pymysql://root:[email protected]/solo",                       #mysql+mysqlconnector://<user>:<password>@<host>[:<port>]/<dbname>                                    encoding=‘utf-8‘, echo=True) #echo=True 打印程序运行详细信息Base=declarative_base()   #生成orm基类#多对一:假设多个员工可以属于一个部门,而多个部门不能有同一个员工(只有某些公司才把普通员工当成葫芦娃，身兼数职)#类   ==>表#对象 ==>表中的一行记录class Dep(Base):    __tablename__=‘dep‘    id=Column(Integer,primary_key=True,autoincrement=True)    dname=Column(String(64),nullable=False,index=True)class Emp(Base):    __tablename__=‘emp‘          #表名    id=Column(Integer,primary_key=True,autoincrement=True)    ename=Column(String(32),nullable=False,index=True)    dep_id=Column(Integer,ForeignKey(‘dep.id‘))def init_db():    Base.metadata.create_all(egine)def drop_db():    Base.metadata.drop_all(egine)drop_db()init_db()Session=sessionmaker(bind=egine) #Session_class现在不是实例，而是类session=Session()                #生成Session实例

①增

#增row_obj=Dep(dname=‘销售‘)  #生成你要创建的数据对象//按关键字传参,无需指定id,因其是自增长的session.add(row_obj)       #把要创建的数据对象添加到这个session里， 一会统一创建session.add_all([    Dep(dname=‘技术‘),    Dep(dname=‘运营‘),    Dep(dname=‘人事‘),])session.commit()    #提交，生成表

②删

#删session.query(Dep).filter(Dep.id > 3).delete()session.commit()

③改

#改session.query(Dep).filter(Dep.id > 0).update({‘dname‘:‘翔哥‘})session.query(Dep).filter(Dep.id > 0).update({‘dname‘:Dep.dname+‘_SB‘},synchronize_session=False)session.query(Dep).filter(Dep.id > 0).update({‘id‘:Dep.id*100},synchronize_session=‘evaluate‘)session.commit()

④查

#查所有,取所有字段res=session.query(Dep).all() #for row in res:print(row.id,row.dname)#查所有,取指定字段res=session.query(Dep.dname).order_by(Dep.id).all() #for row in res:print(row.dname)res=session.query(Dep.dname).first()print(res) # (‘翔哥_SB‘,)#过滤查res=session.query(Dep).filter(Dep.id > 1,Dep.id <1000) #逗号分隔,默认为andprint([(row.id,row.dname) for row in res])

⑤回滚

# session.add xxoo       //数据添加# Session.rollback()     //回滚# Session.query xxoo     //刚刚添加的数据不见了（也就是滚到了原来的状态）

<2>更多的查询操作

###用到的表结构2###from sqlalchemy import create_enginefrom sqlalchemy.ext.declarative import declarative_basefrom sqlalchemy import Column,Integer,String,ForeignKeyfrom sqlalchemy.orm import sessionmakerengine = create_engine("mysql+pymysql://root:[email protected]/solo",                       #mysql+mysqlconnector://<user>:<password>@<host>[:<port>]/<dbname>                                    encoding=‘utf-8‘, echo=True) #echo=True 打印程序运行详细信息Base=declarative_base()   #生成orm基类#多对一:假设多个员工可以属于一个部门,而多个部门不能有同一个员工(只有某些公司才把普通员工当成葫芦娃，身兼数职)#类   ==>表#对象 ==>表中的一行记录class Dep(Base):    __tablename__=‘dep‘    id=Column(Integer,primary_key=True,autoincrement=True)    dname=Column(String(64),nullable=False,index=True)class Emp(Base):    __tablename__=‘emp‘          #表名    id=Column(Integer,primary_key=True,autoincrement=True)    ename=Column(String(32),nullable=False,index=True)    dep_id=Column(Integer,ForeignKey(‘dep.id‘))def init_db():    Base.metadata.create_all(egine)def drop_db():    Base.metadata.drop_all(egine)drop_db()init_db()Session=sessionmaker(bind=egine) #Session_class现在不是实例，而是类session=Session()                #生成Session实例# 准备数据session.add_all([    Dep(dname=‘技术‘),    Dep(dname=‘销售‘),    Dep(dname=‘运营‘),    Dep(dname=‘人事‘),])session.add_all([    Emp(ename=‘葫芦娃‘,dep_id=1),    Emp(ename=‘李杰‘,dep_id=1),    Emp(ename=‘武配齐‘,dep_id=1),    Emp(ename=‘李伟‘,dep_id=2),    Emp(ename=‘李钢弹‘,dep_id=3),    Emp(ename=‘张二丫‘,dep_id=4),    Emp(ename=‘李坦克‘,dep_id=2),    Emp(ename=‘王大炮‘,dep_id=4),    Emp(ename=‘牛榴弹‘,dep_id=3)])session.commit()

①条件、通配符、limit、排序、分组、连表、组合

#一、条件sql=session.query(Emp).filter_by(ename=‘葫芦娃‘) #filter_by只能传参数:什么等于什么res=sql.all() #sql语句的执行结果res=session.query(Emp).filter(Emp.id>0,Emp.ename == ‘葫芦娃‘).all() #filter内传的是表达式,逗号分隔,默认为and,res=session.query(Emp).filter(Emp.id.between(1,3),Emp.ename == ‘葫芦娃‘).all()res=session.query(Emp).filter(Emp.id.in_([1,3,99,101]),Emp.ename == ‘葫芦娃‘).all()res=session.query(Emp).filter(~Emp.id.in_([1,3,99,101]),Emp.ename == ‘葫芦娃‘) #~代表取反,转换成sql就是关键字notfrom sqlalchemy import and_,or_res=session.query(Emp).filter(and_(Emp.id > 0,Emp.ename==‘葫芦娃‘)).all()res=session.query(Emp).filter(or_(Emp.id < 2,Emp.ename==‘功夫熊猫‘)).all()res=session.query(Emp).filter(    or_(        Emp.dep_id == 3,        and_(Emp.id > 1,Emp.ename==‘功夫熊猫‘),        Emp.ename != ‘‘    )).all()#二、通配符res=session.query(Emp).filter(Emp.ename.like(‘%芦_%‘)).all()res=session.query(Emp).filter(~Emp.ename.like(‘%芦_%‘)).all()#三、limitres=session.query(Emp)[0:5:2]#四、排序res=session.query(Emp).order_by(Emp.dep_id.desc()).all()res=session.query(Emp).order_by(Emp.dep_id.desc(),Emp.id.asc()).all()#五、分组from sqlalchemy.sql import funcres=session.query(Emp.dep_id).group_by(Emp.dep_id).all()res=session.query(    func.max(Emp.dep_id),    func.min(Emp.dep_id),    func.sum(Emp.dep_id),    func.avg(Emp.dep_id),    func.count(Emp.dep_id),).group_by(Emp.dep_id).all()res=session.query(    Emp.dep_id,    func.count(1),).group_by(Emp.dep_id).having(func.count(1) > 2).all()#六、连表#笛卡尔积res=session.query(Emp,Dep).all() #select * from emp,dep;#where条件res=session.query(Emp,Dep).filter(Emp.dep_id==Dep.id).all()# for row in res:#     emp_tb=row[0]#     dep_tb=row[1]#     print(emp_tb.id,emp_tb.ename,dep_tb.id,dep_tb.dname)#内连接res=session.query(Emp).join(Dep)#join默认为内连接,SQLAlchemy会自动帮我们通过foreign key字段去找关联关系#但是上述查询的结果均为Emp表的字段,这样链表还有毛线意义,于是我们修改为res=session.query(Emp.id,Emp.ename,Emp.dep_id,Dep.dname).join(Dep).all()#左连接:isouter=Trueres=session.query(Emp.id,Emp.ename,Emp.dep_id,Dep.dname).join(Dep,isouter=True).all()#右连接:同左连接,只是把两个表的位置换一下#七、组合q1=session.query(Emp.id,Emp.ename).filter(Emp.id > 0,Emp.id < 5)q2=session.query(Emp.id,Emp.ename).filter(    or_(        Emp.ename.like(‘%芦%‘),        Emp.ename.like(‘%伟%‘),    ))res1=q1.union(q2) #组合+去重res2=q1.union_all(q2) #组合,不去重print([i.ename for i in q1.all()]) #[‘葫芦娃‘, ‘李杰‘, ‘武配齐‘, ‘李伟‘]print([i.ename for i in q2.all()]) #[‘葫芦娃‘, ‘李伟‘]print([i.ename for i in res1.all()]) #[‘葫芦娃‘, ‘李杰‘, ‘武配齐‘, ‘李伟‘]print([i.ename for i in res2.all()]) #[‘葫芦娃‘, ‘李杰‘, ‘武配齐‘, ‘李伟‘, ‘李伟‘, ‘葫芦娃‘]

②子查询
注意：子查询的sql必须用括号包起来,尤其在形式三中需要注意这一点

形式一:子查询当做一张表来用,调用subquery()#示例:查出id大于2的员工,当做子查询的表使用#原生SQL:# select * from (select * from emp where id > 2);#ORM:res=session.query(    session.query(Emp).filter(Emp.id > 8).subquery()).all()形式二:子查询当做in的范围用,调用in_#示例:#查出销售部门的员工姓名#原生SQL:# select ename from emp where dep_id in (select id from dep where dname=‘销售‘);#ORM:res=session.query(Emp.ename).filter(Emp.dep_id.in_(    session.query(Dep.id).filter_by(dname=‘销售‘), #传的是参数    # session.query(Dep.id).filter(Dep.dname==‘销售‘) #传的是表达式)).all()形式三:子查询当做select后的字段,调用as_scalar()#示例:查询所有的员工姓名与部门名#原生SQL:# select ename as 员工姓名,(select dname from dep where id = emp.dep_id) as 部门名 from emp;#ORM:sub_sql=session.query(Dep.dname).filter(Dep.id==Emp.dep_id) #SELECT dep.dname FROM dep, emp WHERE dep.id = emp.dep_idsub_sql.as_scalar() #as_scalar的功能就是把上面的sub_sql加上了括号res=session.query(Emp.ename,sub_sql.as_scalar()).all()

③正查、反查

###用到的表结构3###from sqlalchemy import create_enginefrom sqlalchemy.ext.declarative import declarative_basefrom sqlalchemy import Column,Integer,String,ForeignKeyfrom sqlalchemy.orm import sessionmakerengine = create_engine("mysql+pymysql://root:[email protected]/solo",                       #mysql+mysqlconnector://<user>:<password>@<host>[:<port>]/<dbname>                                    encoding=‘utf-8‘, echo=True) #echo=True 打印程序运行详细信息Base=declarative_base()   #生成orm基类#多对一:假设多个员工可以属于一个部门,而多个部门不能有同一个员工(只有某些公司才把普通员工当成葫芦娃，身兼数职)#类   ==>表#对象 ==>表中的一行记录class Dep(Base):    __tablename__=‘dep‘    id=Column(Integer,primary_key=True,autoincrement=True)    dname=Column(String(64),nullable=False,index=True)class Emp(Base):    __tablename__=‘emp‘    id=Column(Integer,primary_key=True,autoincrement=True)    ename=Column(String(32),nullable=False,index=True)    dep_id=Column(Integer,ForeignKey(‘dep.id‘))    #在ForeignKey所在的类内添加relationship的字段,注意:    #1:Dep是类名    #2:depart字段不会再数据库表中生成字段    #3:depart用于Emp表查询Dep表(正向查询),而xxoo用于Dep表查询Emp表(反向查询),    depart=relationship(‘Dep‘,backref=‘xxoo‘) def init_db():    Base.metadata.create_all(egine)def drop_db():    Base.metadata.drop_all(egine)drop_db()init_db()Session=sessionmaker(bind=egine) #Session_class现在不是实例，而是类session=Session()                #生成Session实例# 准备数据session.add_all([    Dep(dname=‘技术‘),    Dep(dname=‘销售‘),    Dep(dname=‘运营‘),    Dep(dname=‘人事‘),])session.add_all([    Emp(ename=‘葫芦娃‘,dep_id=1),    Emp(ename=‘李杰‘,dep_id=1),    Emp(ename=‘武配齐‘,dep_id=1),    Emp(ename=‘李伟‘,dep_id=2),    Emp(ename=‘李钢弹‘,dep_id=3),    Emp(ename=‘张二丫‘,dep_id=4),    Emp(ename=‘李坦克‘,dep_id=2),    Emp(ename=‘王大炮‘,dep_id=4),    Emp(ename=‘牛榴弹‘,dep_id=3)])session.commit()

1.标准连表查询# 示例:查询员工名与其部门名res=session.query(Emp.ename,Dep.dname).join(Dep) #迭代器for row in res:    print(row[0],row[1]) #等同于print(row.ename,row.dname)2.基于relationship的正查、反查#SQLAlchemy的relationship在内部帮我们做好表的链接#查询员工名与其部门名(正向查)res=session.query(Emp)for row in res:    print(row.ename,row.id,row.depart.dname)#查询部门名以及该部门下的员工(反向查)res=session.query(Dep)for row in res:    # print(row.dname,row.xxoo)    print(row.dname,[r.ename for r in row.xxoo])

<3>关联问题

①外键关联（一对一）

# 外键关联###用到的表 ###from sqlalchemy import create_enginefrom sqlalchemy.ext.declarative import declarative_basefrom sqlalchemy import String,Column,Integer,ForeignKey,DATEfrom sqlalchemy.orm import sessionmaker,relationship engine = create_engine("mysql+pymysql://root:[email protected]/solo",,                       encoding="utf-8") Base = declarative_base() class Student(Base):    __tablename__ ="student"    id = Column(Integer,primary_key=True)    name = Column(String(32),nullable=False)    register_date = Column(DATE,nullable=False)     def __repr__(self):        return "<%s name:%s>"%(self.id,self.name) class StudyRecord(Base):    __tablename__ = "study_record"    id = Column(Integer,primary_key=True)    day = Column(Integer,nullable=False)    status = Column(String(32),nullable=False)    stu_id = Column(Integer,ForeignKey("student.id"))              #关联student表里的id     my_student = relationship("Student",backref="my_study_record") # Student为关联的类    def __repr__(self):        return "<%s name:%s>" % (self.id, self.name) Base.metadata.create_all(engine) Session_class = sessionmaker(bind=engine)session = Session_class() s1 = Student(name="solo",register_date="2016-10-26")s2 = Student(name="alex",register_date="2015-10-26")s3 = Student(name="eric",register_date="2014-10-26")s4 = Student(name="rain",register_date="2013-10-26") r1 = StudyRecord(day=1,status="YES",stu_id=1)r2 = StudyRecord(day=2,status="No",stu_id=1)r3 = StudyRecord(day=3,status="YES",stu_id=1)r4 = StudyRecord(day=1,status="YES",stu_id=2) session.add_all([s1,s2,s3,s4,r1,r2,r3,r4])session.commit()#查询#stu_obj = session.query(Student).filter(Student.name=="solo").first()#print(stu_obj)#<id:1 name:solo> #print(stu_obj.my_study_record)#[<name:solo day:1 status:YES>, <name:solo day:2 status:No>, <name:solo day:3 status:YES>]

②多外键关联（一对多）

###orm_many_fk.py文件####!/usr/bin/env python# -*- coding:utf-8 -*-# _author_soloLifrom sqlalchemy import create_enginefrom sqlalchemy import Integer,String,Column,ForeignKeyfrom sqlalchemy.ext.declarative import declarative_basefrom sqlalchemy.orm import sessionmaker,relationshipengine = create_engine("mysql+pymysql://root:[email protected]/solo",                       encoding="utf-8",echo= True)Base =  declarative_base()class Customer(Base):    __tablename__ = "customer"    id = Column(Integer,primary_key=True)    name = Column(String(32))    billing_address_id = Column(Integer,ForeignKey("address.id"))    #账单地址    shipping_address_id = Column(Integer, ForeignKey("address.id"))  #邮寄地址        #可以帮其他人买东西，也可以自己给自己买    def __repr__(self):       return "<name:%s billing_add:%s shipping_add:%s>"%(self.name,self.billing_address.street,                                                          self.shipping_address.street)    billing_address = relationship("Address",foreign_keys=[billing_address_id])    #必须写foreign_keys    shipping_address = relationship("Address",foreign_keys=[shipping_address_id])class Address(Base):    __tablename__ = ‘address‘    id = Column(Integer, primary_key=True)    street = Column(String(32))    city = Column(String(32))    state = Column(String(32))Base.metadata.create_all(engine)

###orm_api.py文件####规矩：创建数据表要与增删改查的操作表分开，进行导入操作#!/usr/bin/env python# -*- coding:utf-8 -*-# _author_soloLifrom sqlalchemy.orm import sessionmakerfrom test import orm_many_fk   #此项为导入上一个文件Session = sessionmaker(bind=orm_many_fk.engine)session = Session()a1 = orm_many_fk.Address(street="Tiantongyuan",city="ChangPing",state="BJ")a2 = orm_many_fk.Address(street="Wudaokou",city="HaiDian",state="BJ")a3 = orm_many_fk.Address(street="DongGuan",city="QinHuangDao",state="HB")session.add_all([a1,a2,a3])c1 = orm_many_fk.Customer(name="solo",billing_address_id=1,shipping_address_id=2)c2 = orm_many_fk.Customer(name="Alex",billing_address_id=3,shipping_address_id=3)session.add_all([c1,c2])cus_obj = session.query(orm_many_fk.Customer).filter_by(name="solo").first()print(cus_obj)session.commit()

③多外键关联（多对多）

现在来设计一个能描述“图书”与“作者”的关系的表结构，需求是
1.一本书可以有好几个作者一起出版
2.一个作者可以写好几本书

###many_to_many.py文件####创建表结构：#一本书可以有多个作者，一个作者又可以出版多本书#!/usr/bin/env python# -*- coding:utf-8 -*-# _author_soloLifrom sqlalchemy import Table, Column, Integer,String,DATE, ForeignKeyfrom sqlalchemy.orm import relationshipfrom sqlalchemy.ext.declarative import declarative_basefrom sqlalchemy import create_enginefrom sqlalchemy.orm import sessionmakerengine = create_engine("mysql+pymysql://root:[email protected]/solo",                       encoding="utf-8")Base = declarative_base()#创建book_m2m_author表，表不用用户操作，系统自动维护，自动添加数据book_m2m_author = Table(‘book_m2m_author‘, Base.metadata,                        Column(‘book_id‘,Integer,ForeignKey(‘books.id‘)),                        Column(‘author_id‘,Integer,ForeignKey(‘authors.id‘)),                        )class Book(Base):    __tablename__ = ‘books‘    id = Column(Integer,primary_key=True)    name = Column(String(64))    pub_date = Column(DATE)    #关联Author类，secondary表示通过book_m2m_author表进行查询关联数据，backref反向查询也一样    authors = relationship(‘Author‘,secondary=book_m2m_author,backref=‘books‘)

###many_to_many_api.py文件####!/usr/bin/env python# -*- coding:utf-8 -*-# _author_soloLifrom sqlalchemy.orm import sessionmakerfrom day04 import many_to_manySession = sessionmaker(bind=many_to_many.engine)session = Session()b1 = many_to_many.Book(name="learn python with Alex",pub_date="2014-05-02")b2 = many_to_many.Book(name="learn linux with Alex",pub_date="2015-05-02")b3 = many_to_many.Book(name="learn go with Alex",pub_date="2016-05-02")a1 = many_to_many.Author(name="Alex")a2 = many_to_many.Author(name="Jack")a3 = many_to_many.Author(name="Rain")#关键来了，创建关联关系b1.authors = [a1,a3]b3.authors = [a1,a2,a3]session.add_all([b1,b2,b3,a1,a2,a3])session.commit()author_obj = session.query(many_to_many.Author).filter_by(name="Alex").first()print(author_obj,author_obj.books)book_obj = session.query(many_to_many.Book).filter_by(id=2).first()print(book_obj,book_obj.authors)# Alex [learn python with Alex, learn go with Alex]# learn go with Alex [Alex, Jack, Rain]##通过书删除作者###author_obj =s.query(Author).filter_by(name="Jack").first()#book_obj = s.query(Book).filter_by(name="跟Alex学把妹").first()#book_obj.authors.remove(author_obj) #从一本书里删除一个作者#s.commit()##直接删除作者##　#删除作者时，会把这个作者跟所有书的关联关系数据也自动删除#author_obj =s.query(Author).filter_by(name="Alex").first()# print(author_obj.name , author_obj.books)#s.delete(author_obj)#s.commit()

<4>中文问题

engine = create_engine("mysql+pymysql://root:[email protected]/solo")  #修改查看数据库字符编码mysql> alter database solo character set utf8;Query OK, 1 row affected (0.00 sec) mysql>  show variables like ‘character_set_database‘;+------------------------+-------+| Variable_name          | Value |+------------------------+-------+| character_set_database | utf8  |

Python学习笔记整理总结【ORM（SQLAlchemy）】