SQLAchemy---ORM框架

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

优点：
1、隐藏了数据访问细节，‘封闭’的通用数据库交互，ORM的核心。他使我们的通用数据库交互变得简单易行，并且完全不用考虑sql语句，快速开发
2、ORM使我们高中固化数据结构变得更加简单易行
缺点：
1、无可避免的，自动化意味着映射和关联管理，代价是牺牲性能，现在的各种ORM框架都在尝试使用各种方法来减轻这块，效果还是很好的。

sqlalchemy安装

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
pip install SQLAlchemy<br><br>pip install pymysql #由于mysqldb依然不支持py3，所以这里我们用pymysql与sqlalchemy交互

sqlalchemy基本使用

需要创建的表是下面的样子：

CREATE TABLE t3 (
id INTEGER NOT NULL AUTO_INCREMENT,
name VARCHAR(32),
password VARCHAR(64),
PRIMARY KEY (id)
)

使用orm实现上面的表的创建

#方式1：一般使用这个
import sqlalchemy
from sqlalchemy import create_engine   #create_engine连接数据库的模块
from sqlalchemy.ext.declarative import declarative_base
from sqlalchemy import Column,Integer,String
# engine=create_engine("mysql+pymysql（使用的模块）://用户名:密码@主机/库名",encoding='utf-8',echo=True)
engine=create_engine("mysql+pymysql://root:123@localhost/db1",encoding='utf-8',echo=True) #echo=True的意思是打印info信息

Base=declarative_base() #生成orm基类

class User(Base):  #需要继承上面的基类
    __tablename__='t3' #声明需要创建的表名
    id=Column(Integer,primary_key=True) #id列的属性
    name=Column(String(32))
    password=Column(String(64))
#create_all的意思是把全部继承基类的儿子全部都创建了
Base.metadata.create_all(engine) #执行创建表结构的sql 


#方式2：
from sqlalchemy import Table,MetaData,Column,Integer,String,ForeignKey
from sqlalchemy.orm import mapper

metadata=MetaData()

user=Table('user',metadata,
           Column('id',Integer,primary_key=True),
           Column('name',String(50)),
           Column('fullname',String(50)),
           Column('password',String(12))
           )
class User(object):
    def __init__(self,name,fullname,password):
        self.name=name
        self.fullname=fullname
        self.password=password

mapper(User,user)

创建表数据

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:123@localhost/db1",encoding='utf-8',echo=True)

Base=declarative_base() #生成orm基类

class User(Base):
    __tablename__='t4'
    id=Column(Integer,primary_key=True)
    name=Column(String(32))
    password=Column(String(64))

Base.metadata.create_all(engine) #执行创建表结构的sql

#创建表数据：
Session_class=sessionmaker(bind=engine) #创建与数据库会话的session class，注意这里返回给session的是一个class，不是实例
Session=Session_class() #生成session实例

user_obj=User(name='lqx',password='231') #生成你要创建的数据对象
user_obj2=User(name='yft',password='12312312') #生成你要创建的数据对象
print(user_obj.name,user_obj.id)  #此时还没创建对象

Session.add(user_obj) #把要创建的数据对象添加到这个session里，一会统一创建
Session.add(user_obj2) #把要创建的数据对象添加到这个session里，一会统一创建
print(user_obj.name,user_obj.id) #此时依然还没创建

Session.commit() #commit之后，才统一提交，创建数据

常用语法：

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  #创建表数据的时候使用的模块
from sqlalchemy import func  #分组时使用的模块
engine=create_engine("mysql+pymysql://root:123@localhost/db1",encoding='utf-8',echo=False)

Base=declarative_base() #生成orm基类

class User(Base):
    __tablename__='t4'
    id=Column(Integer,primary_key=True)
    name=Column(String(32))
    password=Column(String(64))
    #指定查询的时候打印的格式：[<id='3',name='lqx',password='231'>]
    def __repr__(self):
        return "<id='%s',name='%s',password='%s'>" %(self.id,self.name,self.password)

Base.metadata.create_all(engine) #执行创建表结构的sql

#创建表数据：
Session_class=sessionmaker(bind=engine) #创建与数据库会话的session class，注意这里返回给session的是一个class，不是实例
Session=Session_class() #生成session实例

user_obj=User(name='lqx',password='231') #生成你要创建的数据对象
user_obj2=User(name='yft',password='12312312') #生成你要创建的数据对象
print(user_obj.name,user_obj.id)  #此时还没创建对象

Session.add(user_obj) #把要创建的数据对象添加到这个session里，一会统一创建
Session.add(user_obj2) #把要创建的数据对象添加到这个session里，一会统一创建
print(user_obj.name,user_obj.id) #此时依然还没创建

Session.commit() #commit之后，才统一提交，创建数据

#数据查询：
my_user=Session.query(User).filter_by().all() #查看全部数据
# my_user=Session.query(User).filter(name=='lqx').first() #按条件查询数据,取第一个结果
# my_user=Session.query(User).filter(User.id>2).all() #按条件查询数据
# my_user=Session.query(User).filter(User.id>1).filter(User.id<4).first() #按照多条件查询
print(dir(my_user)) #把得到的数据映射称为一个对象，这样调用每个字段就可以跟调用对象属性一样便捷了
print(my_user)
print(my_user.id,my_user.name,my_user.password)
# >>>1 lqx 231

#修改数据：
data=Session.query(User).filter(User.id>1).filter(User.id<4).first() #按照多条件查询
# print(dir(Session))
data.name='jack liu' #要修改的值
data.password='lkjlkj'
Session.commit()  #这一步才能统一提交，创建数据

#回滚数据：
my_user=Session.query(User).filter(User.id==1).first() #得到查询的结果的，并且返回一个对象
my_user.name='Jack' #面向对象赋值

fake_user=User(name='rain',password='123') #添加一条数据
Session.add(fake_user)

print(Session.query(User).filter(User.name.in_(['Jack','rain'])).all())  #这里看session里有你刚添加和修改的数据

Session.rollback() #回滚
print('after rollback')
print(Session.query(User).filter(User.name.in_(['Jack','rain'])).all())
Session.commit() #现在才能统一提交，创建数据

#统计和分组
counter=Session.query(User).filter(User.name.like('l%')).count()
print(counter)
#分组：(统计被分组的name的个数),以name分组
print(Session.query(func.count(User.name),User.name).group_by(User.name).all())
# >>>[(1, 'jack liu'), (2, 'lqx'), (2, 'qwe'), (1, 'yft')]

简单的表结构、增删改查

#表结构
from sqlalchemy import create_engine
from sqlalchemy.ext.declarative import declarative_base
from sqlalchemy import Column,Integer,String,ForeignKey
from sqlalchemy.orm import sessionmaker

# egine=create_engine('mysql+pymysql://root:123@localhost:3306/db1?charset=utf8',max_overflow=5,echo=True)
egine=create_engine('mysql+pymysql://root:123@localhost:3306/db1?charset=utf8',max_overflow=5)
Base=declarative_base() #生成一个基类
#创建需要创建的表和表结构，
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掉全部绑定父类的子类所创建的表

drop_db()
init_db()
Session=sessionmaker(bind=egine)
session=Session()

#增：
row_obj=Dep(dname='销售') #按关键字传参，无需指定id,因其是自增长度的
session.add(row_obj)
session.add_all([
    Dep(dname='技术'),
    Dep(dname='运营'),
    Dep(dname='人事')
])
session.commit()  #只有执行commit之后才能把要操作的数据提交给数据库

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

#改：
session.query(Dep).filter(Dep.id>0).update({'dname':Dep.dname+'_SB'},synchronize_session=False)
session.query(Dep).filter(Dep.id==1).update({'dname':'乌龟'},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()
# print(dir(res[1])) #列表中的值都是对象
print(res[1].dname)
# #查所有，取指定字段
res=session.query(Dep.dname).order_by(Dep.id).all()
print(res)
res=session.query(Dep.dname).first() #取查到的值的第一个
print(res)

#过滤查
res=session.query(Dep).filter(Dep.id>1,Dep.id<1000) #,分割，默认为and
print([(row.id,row.dname) for row in res])

其他查询

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

egine=create_engine('mysql+pymysql://root:123@localhost:3306/db1?charset=utf8',max_overflow=5)
Base=declarative_base()

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=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
print((res[0].ename,res[0].id))
res=session.query(Emp).filter(Emp.id.between(1,3),Emp.ename=='林海峰').all()
res=session.query(Emp).filter(Emp.id.in_([1,2,44,101]),Emp.ename=='林海峰').all()
res=session.query(Emp).filter(~Emp.id>0,Emp.ename=='林海峰') #~代表取反,转换成sql就是关键字not

from sqlalchemy import and_,or_
res=session.query(Emp).filter(and_(Emp.id > 0,Emp.ename=='林海峰')).all() #and的用法
print([(ros.ename,ros.id) for ros in res])
res=session.query(Emp).filter(or_(Emp.id > 0,Emp.ename=='林海峰')).all()  #or的用法
print([(ros.ename,ros.id) for ros in res])

res=session.query(Emp).filter(
    or_(
        Emp.dep_id==3,
        and_(Emp.id >2,Emp.ename=='李杰'),
        Emp.ename !='4'
    )
)
print([(ros.ename,ros.id) for ros in res])

#通配符：
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()]) #['林海峰', '李杰', '武配齐', '元昊', '元昊', '林海峰']

子查询的三种形式：

# 三 子查询
# 形式一: 子查询当做一张表来用, 调用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()

正查反查

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

egine=create_engine('mysql+pymysql://root@127.0.0.1:3306/db1?charset=utf8',max_overflow=5)

Base=declarative_base()

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=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()

标准连表查询

# 二 标准连表查询
# 示例:查询员工名与其部门名
res=session.query(Emp.ename,Dep.dname).join(Dep) #迭代器
for row in res:
    print(row[0],row[1]) #等同于print(row.ename,row.dname)

基于relationship的正查、反查

# 三 基于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])