SQLAlchemy

一、介绍

SQLAlchemy是一个基于Python实现的ORM框架。该框架建立在 DB API之上，使用关系对象映射进行数据库操作，简言之便是：将类和对象转换成SQL，然后使用数据API执行SQL并获取执行结果。

pip3 install sqlalchemy

组成部分：

Engine，框架的引擎
Connection Pooling ，数据库连接池
Dialect，选择连接数据库的DB API种类
Schema/Types，架构和类型
SQL Exprression Language，SQL表达式语言

SQLAlchemy本身无法操作数据库，其必须以来pymsql等第三方插件，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

django中数据库的表如何反向生成models

python manage.py inspectdb > app/models.py

二、orm简单使用（能创建表，删除表，不能修改表）

models.py

import datetime
from sqlalchemy import create_engine
from sqlalchemy.ext.declarative import declarative_base
from sqlalchemy import Column, Integer, String, Text, ForeignKey, DateTime, UniqueConstraint, Index
Base = declarative_base()

class Users(Base):
    __tablename__ = 'users'  # 数据库表名称
    id = Column(Integer, primary_key=True)  # id 主键
    name = Column(String(32), index=True, nullable=False)  # name列，索引，不可为空
    email = Column(String(32), unique=True)
    #datetime.datetime.now不能加括号，加了括号，以后永远是当前时间
    # ctime = Column(DateTime, default=datetime.datetime.now)
    # extra = Column(Text, nullable=True)

    __table_args__ = (
        # UniqueConstraint('id', 'name', name='uix_id_name'), #联合唯一
        # Index('ix_id_name', 'name', 'email'), #索引
    )

def init_db():
    """
    根据类创建数据库表
    :return:
    """
    engine = create_engine(
        # "什么数据库(mysql,orcal)+用什么去连接数据库(pymysql)://数据库用户名:密码@ip：端口/数据库名?charset=字符集"
        "mysql+pymysql://root:123@127.0.0.1:3306/day95?charset=utf8",
        max_overflow=0,  # 超过连接池大小外最多创建的连接
        pool_size=5,  # 连接池大小
        pool_timeout=30,  # 池中没有线程最多等待的时间，否则报错
        pool_recycle=-1  # 多久之后对线程池中的线程进行一次连接的回收（重置）
    )

    Base.metadata.create_all(engine)

def drop_db():
    """
    根据类删除数据库表
    :return:
    """
    engine = create_engine(
        "mysql+pymysql://root:123456@127.0.0.1:3306/aaa?charset=utf8",
        max_overflow=0,  # 超过连接池大小外最多创建的连接
        pool_size=5,  # 连接池大小
        pool_timeout=30,  # 池中没有线程最多等待的时间，否则报错
        pool_recycle=-1  # 多久之后对线程池中的线程进行一次连接的回收（重置）
    )

    Base.metadata.drop_all(engine)

if __name__ == '__main__':
    # drop_db()
    init_db()

orm1.py

from sqlalchemy.orm import sessionmaker
from sqlalchemy import create_engine
from models import Users
#"mysql+pymysql://root@127.0.0.1:3306/aaa"
engine = create_engine("mysql+pymysql://root:123@127.0.0.1:3306/day95", max_overflow=0, pool_size=5)
Connection = sessionmaker(bind=engine)

# 每次执行数据库操作时，都需要创建一个Connection
conn = Connection()

############# 执行ORM操作 #############
obj1 = Users(name="hank",age=18)  # 创建一个对象
conn.add(obj1)

# 必须提交事务才能生效
conn.commit()

# 关闭session，其实是将连接放回连接池
conn.close()

 三、基本操作（增删改查）

from sqlalchemy.orm import sessionmaker
from sqlalchemy import create_engine
from models import Users

engine = create_engine("mysql+pymysql://root:123@127.0.0.1:3306/day95", max_overflow=0, pool_size=5)
Connection = sessionmaker(bind=engine)

# 每次执行数据库操作时，都需要创建一个Connection
conn = Connection()

############# 执行ORM操作 #############
# 1、单增
obj1 = Users(name="sean",age=35)  # 创建一个对象
conn.add(obj1)

# 2、多个增加
conn.add_all([
    Users(name="bob",age=15),
    Users(name="paul",age=23),
    #Books(name="西游记")
])

# 3、改
conn.query(Users).update({'name':"sb","age":14}) # 把名字全部改成sb,年龄改为14
# 下面类似于django的F查询,这个后面必须配合synchronize_session
# 如果是字符串就用False，如果是数字就不用操作
conn.query(Users).update({Users.name:Users.name + " is dsb"},synchronize_session=False)
conn.query(Users).update({Users.age:Users.age + 1})

# 4、删
conn.query(Users).delete()  #query里面填表名

# 5、查
# 查询所有,返回的是列表
r1 = conn.query(Users).all()
# 查单个
r1 = conn.query(Users).first()
# 查哪些字段，把name重命名为xx
r1 = conn.query(Users.name.label("xx"),Users.age).first()
print(r1.xx)
# filter传的是表达式，filter_by传的是参数
r1 = conn.query(Users).filter(Users.name == "bob").first()
r1 = conn.query(Users).filter(Users.name == "bob").update({"age":14})  # 返回的是影响的行数
r1 = conn.query(Users).filter_by(name="paul").first()

# 打印原生sql语句
r1 = conn.query(Users)
print(r1)

#增，删，改都要commit()
conn.commit()

# 关闭session，其实是将连接放回连接池
conn.close()

四、单表查询操作

 

from sqlalchemy.orm import sessionmaker
from sqlalchemy import create_engine
from models import Users

engine = create_engine("mysql+pymysql://root:123@127.0.0.1:3306/day95", max_overflow=0, pool_size=5)
Session = sessionmaker(bind=engine)

# 每次执行数据库操作时，都需要创建一个Connection
session = Session()

# 1、条件
ret = session.query(Users).filter_by(name="bob").all()  # all() 返回列表套对象
# ret = session.query(Users).filter_by(name="paul").first()
# 2、表达式 ，and 条件连接
ret = session.query(Users).filter(Users.name == "bob",Users.age == 14).first()
# 3、表达式的between 条件 顾头也顾尾
ret = session.query(Users).filter(Users.age.between(15, 25)).all()
# 4、下划线in_
ret = session.query(Users).filter(Users.id.in_([12,14])).all()
# 5、~非，除...外
ret = session.query(Users).filter(~Users.id.in_([12,14])).all()
# 6、or_包裹的都是or条件，and_包裹的都是and条件，必须导包
from sqlalchemy import and_, or_
ret = session.query(Users).filter(or_(Users.id==12,Users.name=="hank")).all()
ret = session.query(Users).filter(and_(Users.id==12,Users.name=="bob")).all()
ret = session.query(Users).filter(
    or_(
        Users.id < 12,
        and_(Users.name == 'hank', Users.id == 14),
    )).all()

# 7、模糊匹配 like查询
# 必须以b开头
ret = session.query(Users).filter(Users.name.like('b%')).all()
# 第二个字母是b
ret = session.query(Users).filter(Users.name.like('_b%')).all()
# 不以b开头
ret = session.query(Users).filter(~Users.name.like('b%')).all()

# 8、排序
# desc()从大到小排序，降序
ret = session.query(Users).filter(Users.id>1).order_by(Users.id.desc()).all()
# asc()从小到大排序，升序
ret = session.query(Users).filter(Users.id>1).order_by(Users.id.asc()).all()
# 多条件排序，先以年纪从大到小排，如果年龄相同，然后再以id从小到大排
ret = session.query(Users).filter(Users.id>1).order_by(Users.age.asc(),Users.id.asc()).all()

# 9、分组查询
ret = session.query(Users).group_by(Users.name).all()  # 按名字分组

from sqlalchemy import func
# 选出组内成员年龄大于等于30的组
ret =session.query(Users).group_by(Users.name).having(func.min(Users.age)>=20).all()

# 选出组内最小年龄要大于12的组，查询组内的最小年龄，最大年龄，年纪之和
ret = session.query(
    func.min(Users.age),
    func.max(Users.age),
    func.sum(Users.age),
    Users.name
).group_by(Users.name).having(func.min(Users.age)>12).all()
print(ret)

五、表关系创建

一对多

# 一个人对应多个爱好

from sqlalchemy.orm import relationship  

class Hobby(Base):
    __tablename__ = "hobby"
    id = Column(Integer,primary_key=True)
    catption = Column(String(50),default="双色球")

class Person(Base):
    __tablename__ = "person"
    nid = Column(Integer,primary_key=True)
    name = Column(String(32))
    # hobby指的是tablename而不是Hobby类名
    hobby_id = Column(Integer, ForeignKey("hobby.id"))

    # 跟数据库无关，不会新增字段，只能用于快速链表操作
    # relationship的第一个参数是类名，第二个参数backref是反向查询的字段
    hobby = relationship("Hobby",backref="pers")

多对多

class Boy2Girl(Base):
    __tablename__ = 'boy2girl'
    id = Column(Integer, primary_key=True)
    girl_id = Column(Integer, ForeignKey('girl.id'))
    boy_id = Column(Integer, ForeignKey('boy.id'))

# 一个男孩喜欢多个女孩，一个女孩也可以喜欢多个男孩
class Girl(Base):
    __tablename__ = "girl"
    id = Column(Integer,primary_key=True)
    name = Column(String(100),nullable=False)

class Boy(Base):
    __tablename__ = "body"
    id = Column(Integer,primary_key=True)
    name = Column(String(100),nullable=False)

    # 与生成表结构无关，仅用于查询方便,放在哪个单表中都可以
    # secondary="第三张表的表名"
    girl = relationship('Girl', secondary='boy2girl', backref='boys')

六、ORM操作

一对多

# 添加方式一：没有用到关联关系
session.add_all([
    Hobby(caption="篮球"),
    Hobby(caption="下棋"),
    Person(name="tank",hobby_id=1),
    Person(name="sean",hobby_id=2),
])
# 添加方式二、用关联关系
person = Person(name="bob",hobby=Hobby(caption="相亲"))
session.add(person)

hobby = Hobby(caption="足球")
hobby.pres = [Person(name="owen"),Person(name="july")]
session.add(hobby)

# 正向查询
pr = session.query(Person).filter(Person.name == "tank").first()
print(pr.name)
print(pr.hobby.caption)  # hobby是外键字段

# 反向查询
v = session.query(Hobby).filter(Hobby.caption=="下棋").first()
print(v.caption)
print(v.pers)

# 自己链表
# isouter默认为False，为inner join , 设置为True就是left join
person_list = session.query(Person,Hobby).join(Hobby,Person.hobby_id==Hobby.id,isouter=True)
print(person_list)

多对多

# 添加
session.add_all([
    Boy(name="tank"),
    Boy(name="sean"),
    Girl(name="judy"),
    Girl(name="mary")
])

b2g = Boy2Girl(girl_id=2,boy_id=1)
session.add(b2g)

# 用关联关系添加
#　正向插入
boy = Boy(name="鲍勃")
boy.girl=[Girl(name="小菊"),Girl(name="小苍")]
session.add(boy)

# 反向插入
girl = Girl(name="三上")
girl.boys = [Boy(name="杰森"),Boy(name="杰瑞")]
session.add(girl)

# 使用relationship的关系
# 正向查
b = session.query(Boy).filter(Boy.name == "鲍勃").first()
print(b.name)
print(b.girl)

# 反向查
g = session.query(Girl).filter(Girl.name == "三上").first()
print(g.name)
print(g.boys)