一、介绍
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 SQLAlchemy pip install pymysql #由于mysqldb依然不支持py3，所以这里我们用pymysql与sqlalchemy交互
二、sqlalchemy基本使用
注：
#类 ==>表
#对象 ==>表中的一行记录
<1>基本语法（增/删/改/查/回滚）
####用到的表结构1#### from sqlalchemy import create_engine from sqlalchemy.ext.declarative import declarative_base from sqlalchemy import Column,Integer,String,ForeignKey from sqlalchemy.orm import sessionmaker engine = create_engine("mysql+pymysql://root:12344321@localhost/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) #逗号分隔,默认为and print([(row.id,row.dname) for row in res])
⑤回滚
# session.add xxoo //数据添加 # Session.rollback() //回滚 # Session.query xxoo //刚刚添加的数据不见了（也就是滚到了原来的状态）
<2>更多的查询操作
###用到的表结构2### from sqlalchemy import create_engine from sqlalchemy.ext.declarative import declarative_base from sqlalchemy import Column,Integer,String,ForeignKey from sqlalchemy.orm import sessionmaker engine = create_engine("mysql+pymysql://root:12344321@localhost/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就是关键字not from 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() #三、limit res=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 func res=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=True res=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_id sub_sql.as_scalar() #as_scalar的功能就是把上面的sub_sql加上了括号 res=session.query(Emp.ename,sub_sql.as_scalar()).all()
③正查、反查
###用到的表结构3### from sqlalchemy import create_engine from sqlalchemy.ext.declarative import declarative_base from sqlalchemy import Column,Integer,String,ForeignKey from sqlalchemy.orm import sessionmaker engine = create_engine("mysql+pymysql://root:12344321@localhost/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_engine from sqlalchemy.ext.declarative import declarative_base from sqlalchemy import String,Column,Integer,ForeignKey,DATE from sqlalchemy.orm import sessionmaker,relationship engine = create_engine("mysql+pymysql://root:12344321@localhost/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_soloLi from sqlalchemy import create_engine from sqlalchemy import Integer,String,Column,ForeignKey from sqlalchemy.ext.declarative import declarative_base from sqlalchemy.orm import sessionmaker,relationship engine = create_engine("mysql+pymysql://root:12344321@localhost/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_soloLi from sqlalchemy.orm import sessionmaker from 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_soloLi from sqlalchemy import Table, Column, Integer,String,DATE, ForeignKey from sqlalchemy.orm import relationship from sqlalchemy.ext.declarative import declarative_base from sqlalchemy import create_engine from sqlalchemy.orm import sessionmaker engine = create_engine("mysql+pymysql://root:12344321@localhost/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_soloLi from sqlalchemy.orm import sessionmaker from day04 import many_to_many Session = 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:12344321@localhost/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 |