SQLALchemy之ORM操作

1、仍然要创建引擎

2、创建session会话

（1）方式一

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

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

（2）方式二

基于threading.local的session

session = scoped_session(Session)

3、插入记录

（1）添加一条

obj1 = Users(name="wupeiqi")
session.add(obj1)

session.commit()

（2）添加多条

session.add_all([
    Users(name="wupeiqi"),
    Users(name="alex"),
    Hosts(name="c1.com"),
])
session.commit()

4、删除记录

delete方法

session.query(Users).filter(Users.id > 2).delete()
session.commit()

5、更新记录

update方法

（1）字典形式传参

# 字典中key就是定义类中字段名，value就是要更新的值
session.query(Users).filter(Users.id > 0).update({"name" : "099"})

session.commit()

（2）实现字段的相加

# 相加时类名.字段 = 类名.字段 + xxx

# 字符串类型的相加，注意必须要加参数synchronize_session=False
session.query(Users).filter(Users.id > 0).update({Users.name: Users.name + "099"}, synchronize_session=False)

# 数字类型的相加，注意必须加参数synchronize_session="evaluate"
session.query(Users).filter(Users.id > 0).update({"age": Users.age + 1}, synchronize_session="evaluate")
session.commit()

6、查询记录

（1）查询所有数据

》》》query中要写类名，不是__tablename__参数值

》》》返回列表，元素就是一个个记录对象

》》》循环列表，记录对象.字段名就可以取出字段值

r1 = session.query(Users).all()

（2）查询指定数据

》》》query中写类名.字段名，就可以取出我们指定的字段了

》》》类名.字段名.label("ssss")   是给这个字段取一个别名，类似mysql中的as

》》》返回列表，元素是一个个记录对象，记录对象.字段名或者.ssss就可以取出指定的字段

r2 = session.query(Users.name.label('xx'), Users.age).all()

（3）加条件的查询

1）单个条件

》》》filter中写表示式，就类似python中的判断条件，比如类名.字段名 == "xxxx"；类名.id  > 2

》》》filter_by中写参数，比如类名.字段名 = "sss"

》》》仍然返回一个个记录对象的列表

r3 = session.query(Users).filter(Users.name == "alex").all()

r4 = session.query(Users).filter_by(name='alex').all()

2）多个条件，条件之间默认是and关系

ret = session.query(Users).filter(Users.id > 1, Users.name == 'eric').all()

3）between，在1，2，3中的某个值

ret = session.query(Users).filter(Users.id.between(1, 3), Users.name == 'eric').all()

4）.in_的用法

ret = session.query(Users).filter(Users.id.in_([1,3,4])).all()

5）~是 非 的意思，相反

ret = session.query(Users).filter(~Users.id.in_([1,3,4])).all()

6）查询语句的返回值也可以作为查询条件

ret = session.query(Users).filter(Users.id.in_(session.query(Users.id).filter_by(name='eric'))).all()

7）表示与或非的查询条件

from sqlalchemy import and_, or_
ret = session.query(Users).filter(and_(Users.id > 3, Users.name == 'eric')).all()
ret = session.query(Users).filter(or_(Users.id < 2, Users.name == 'eric')).all()
ret = session.query(Users).filter(
    or_(
        Users.id < 2,
        and_(Users.name == 'eric', Users.id > 3),
        Users.extra != ""
    )).all()

8）通配符，%代表所有

ret = session.query(Users).filter(Users.name.like('e%')).all()
ret = session.query(Users).filter(~Users.name.like('e%')).all()

9）限制，切片

ret = session.query(Users)[1:2]

（4）first()

查询后返回记录对象

r5 = session.query(Users).filter_by(name='alex').first()

（5）带占位符的过滤条件查询

》》》text中的:name和:value代表占位符，params括号中传参

》》》仍然返回一个个记录对象的列表

r6 = session.query(Users).filter(text("id<:value and name=:name")).params(value=224, name='fred')all()

（6）排序

》》》order_by（类名.字段名）  默认按照字段值的升序排序

r6 = session.query(Users).filter(text("id<:value and name=:name")).params(value=224, name='fred').order_by(Users.id).all()

》》》类名.字段名.desc() 是降序   asc()是升序

》》》逗号隔开，代表第一个相同的话，就按第二个排序

ret = session.query(Users).order_by(Users.name.desc()).all()
ret = session.query(Users).order_by(Users.name.desc(), Users.id.asc()).all()

（7）分组

1）首先引入聚合函数

from sqlalchemy.sql import func

2）把某个字段作为分组依据

ret = session.query(Users).group_by(Users.extra).all()

3）使用聚合函数

ret = session.query(func.max(Users.id),func.sum(Users.id),func.min(Users.id)).group_by(Users.name).all()

4）加过滤条件having

ret = session.query(
    func.max(Users.id),
    func.sum(Users.id),
    func.min(Users.id)).group_by(Users.name).having(func.min(Users.id) >2).all()

（8）组合，就是inner join的基础上，保留左表和右表的全部记录

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

（9）连表查询

1）filter中是连表条件，query中写的是显示哪些字段，写类名代表显示这个表的所有字段

ret = session.query(Users, Favor).filter(Users.id == Favor.nid).all()

2）inner join

》》》query括号中只有一个类，只显示这个表的所有字段

ret = session.query(Person).join(Favor).all()

3）left join

》》》加参数isouter=true

ret = session.query(Person).join(Favor, isouter=True).all()

注意：

》》》默认inner join

》》》query括号中写的是显示哪些字段

》》》可以不断的join，进行多张表的连表操作，query括号中就是要显示的字段，就写成类名.字段名这样的形式就可以，取别名用label

》》》默认是没有right join的

》》》默认连表条件是按foreignkey字段

》》》session.query().join()   这样打印出来的就是连表操作的原生sql语句

》》》若没有外键关联的字段，就要自己写连表条件

在join中写连表条件，也可以用and_(表达式1，表达式2) 或者or_(表达式3，表达式4)来表示连表条件

session.query(Person).join(Favor, Person.id == Favor.id, isouter=True).all()

7、执行原生sql语句

（1）利用text，使用占位符

r7 = session.query(Users).from_statement(text("SELECT * FROM users where name=:name")).params(name='ed').all()

（2）利用execute

# 查询
# cursor = session.execute('select * from users')
# result = cursor.fetchall()

# 添加
cursor = session.execute('insert into users(name) values(:value)',params={"value":'wupeiqi'})
session.commit()
print(cursor.lastrowid)

session.close()

8、基于relationship的查询和添加

1）一对多

首先在定义类中(表)添加relationship字段

backref用于反向查询

    # 与生成表结构无关，仅用于查询方便
    hobby = relationship("Hobby", backref='pers')

》》》》查询

正向查询：对象.hobby就是关联的对象，再.字段就获取到关联对象的字段值了

# 使用relationship正向查询

v = session.query(Person).first()
print(v.name)
print(v.hobby.caption)

反向查询：对象.pers（就是backref参数的值）就得到了和当前对象关联的所有记录对象的列表

# 使用relationship反向查询

v = session.query(Hobby).first()
print(v.caption)
print(v.pers)

》》》》添加

正向添加

实例对象时，relationship字段=另一个类的对象，这样就会在关联表中也自动添加一条记录

person = Person(name='张九', hobby=Hobby(caption='姑娘'))
session.add(person)

session.commit()

反向添加

实例对象.relationship字段 = [另一个类的对象1，另一个类的对象2 ]，这样就会在自动被关联的表中一次创建多条记录

hb = Hobby(caption='人妖')
hb.pers = [Person(name='文飞'), Person(name='博雅')]
session.add(hb)

session.commit()

2）多对多

首先在定义类中(表)添加relationship字段

backref用于反向查询

secondary是自己创的关系表

    # 与生成表结构无关，仅用于查询方便
    servers = relationship('Server', secondary='server2group', backref='groups')

》》》》查询

正向查询

# 使用relationship正向查询

v = session.query(Group).first()
print(v.name)
print(v.servers)

反向查询

# 使用relationship反向查询
"""
v = session.query(Server).first()
print(v.hostname)
print(v.groups)
"""

》》》》添加

正向添加

gp = Group(name='C组')
gp.servers = [Server(hostname='c3.com'),Server(hostname='c4.com')]
session.add(gp)
session.commit()

反向添加

ser = Server(hostname='c6.com')
ser.groups = [Group(name='F组'),Group(name='G组')]
session.add(ser)
session.commit()

9、其它操作

import time
import threading

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
from sqlalchemy.sql import text, func
from sqlalchemy.engine.result import ResultProxy
from db import Users, Hosts, Hobby, Person, Group, Server, Server2Group

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

# 关联子查询
subqry = session.query(func.count(Server.id).label("sid")).filter(Server.id == Group.id).correlate(Group).as_scalar()
result = session.query(Group.name, subqry)
"""
SELECT `group`.name AS group_name, (SELECT count(server.id) AS sid 
FROM server 
WHERE server.id = `group`.id) AS anon_1 
FROM `group`
"""


# 原生SQL
"""
# 查询
cursor = session.execute('select * from users')
result = cursor.fetchall()

# 添加
cursor = session.execute('insert into users(name) values(:value)',params={"value":'wupeiqi'})
session.commit()
print(cursor.lastrowid)
"""

session.close()