sqlalchemy.orm表的 增，删，改，查

1.表的 增，删，改，查

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/python13", max_overflow=0, pool_size=5)
Connection = sessionmaker(bind=engine)

con = Connection()  # 每次执行数据库操作时，都需要创建一个Connection
# 1 单增（add）
# obj1  = Users(name="lsb1",age=12)
# con.add(obj1)
# con.commit()  #必须提交才能生效
# con.close()   #关闭链接

# 2 多个增加（add_all）：可以对一个表添加多个数据或者对多个表添加多个数据
# con.add_all([
#         Users(name="lsb1",age=12),
#         Users(name="esb",age=40),
#         Users(name="jsb",age=30),
#         Users(name="tsb",age=12),
#         #Host(name = "tsb",time=123213)
# ])
# con.commit()  #必须提交才能生效
# con.close()  #关闭链接

# 3 删除delete
# con.query(Users).delete()
# con.commit()  #必须提交才能生效
# con.close()   #关闭链接

# 4 改update
# con.query(Users).update({"name":"sb","age":14})
# F查询。如果基于原来的之上 加上一个字符串，要设置synchronize_session属性为False
# con.query(Users).update({Users.name:Users.name +" is true","age":1},synchronize_session=False)
# F查询。如果基于原来的之上 加上一个数字，不用设置synchronize_session
# con.query(Users).update({Users.age:Users.age + 10})
# con.commit()  #必须提交才能生效
# con.close()   #关闭链接

# 5查 (查是不需要commit,也能拿到结果，所有commit可写可不写)

# r1 = con.query(Users).all()     #查询所有

# r1  = con.query(Users).first()  #查单条记录

# r1  = con.query(Users.age,Users.name.label("sb")).first()  #查具体哪些字段

# r1 = con.query(Users).filter(Users.name == "tsb").first()                            # 过滤用filter(传表达式)
# r1 = con.query(Users).filter(Users.name == "tsb").update({"name": "sb", "age": 14})  # 指定一条记录 改（先过滤再修改）
r1 = con.query(Users).filter_by(name = "esb").first()                                  # 过滤用filter_by(传参数)

print(r1)
con.commit() #必须提交才能生效 
con.close() #关闭链接

2.orm的单表查询

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

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

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

# 一、单条件查询（用参数）
# ret =  session.query(Users).filter_by(name = "esb").all()

# 二、多条件查询（用表达式）
# ret  = session.query(Users).filter(Users.name == "sb",Users.age ==14 ).first()
# print(ret.age,ret.name)

# 三、区间查询（用between）:顾头顾尾
# ret  = session.query(Users).filter(Users.age.between(30,40)).all()
# print(ret)

# 四、列表查询（用 in_）：相当于django中的__in
# ret =session.query(Users).filter(Users.id.in_([9,11,13])).all()
# print(ret)

# 五、非查询（用 ~）：取反查询
# ret1 = session.query(Users).filter(~Users.id.in_([9,11,13])).all()
# print(ret1)


from sqlalchemy import or_,and_
# 六、或查询（用 or_） 
# ret =  session.query(Users).filter(or_(Users.id == 9,Users.name=="jsb")).all()

# 七、与查询（用 and_）
# ret =  session.query(Users).filter(and_(Users.id == 9,Users.name=="lsb1")).all()

# 八、or和and 整合查询
# ret =  session.query(Users).filter(or_(
#     Users.id == 9,
#     and_(Users.name=="jsb",Users.id==13),
#
#     )
# ).all()

# 九、模糊查询：like查询
# 1、必须以b开头
# ret = session.query(Users).filter(Users.name.like("b%")).all()
# 2、第二字母是b
# ret = session.query(Users).filter(Users.name.like("_b%")).all()
# 3、不以b开头
# ret = session.query(Users).filter(~Users.name.like("b%")).all()

# 十、排序查询（用order_by）
# 1.desc 从大到小排序 (降序)
# ret = session.query(Users).filter(Users.id>1).order_by(Users.id.desc()).all()
# 2.asc 从小到大排序 (升序)
# ret = session.query(Users).filter(Users.id>1).order_by(Users.id.asc()).all()
# 3.多条件排序,先以年纪从大到小排，如果年龄相同，再以id从小到大排
# ret = session.query(Users).filter(Users.id>1).order_by(Users.age.desc(),Users.id.asc()).all()
# print(ret)

# 十一、分组查询(用 group_by)
# ret  = session.query(Users).group_by(Users.name).all()
# 十二、聚合查询
from sqlalchemy.sql import func  # 再分组的时候如果要用聚合操作，就要导入func
# 1、选出组内最小年龄要大于等于30的组
# ret  = session.query(Users).group_by(Users.name).having(func.min(Users.age)>=30).all()

# 2、选出组内最小年龄要大于等于30的组,查询组内的最小年龄，最大年纪，年纪之和，
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)>=30).all()

print(ret)

3.多表查询
3.1一对多

#表
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="pres")
    def __repr__(self):
        return self.name


#查询
from sqlalchemy.orm import sessionmaker
from sqlalchemy import create_engine
from models import Hobby,Person

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

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

# 1 添加,没有用关联关系
# session.add_all([
#     Hobby(catption="淫诗"),
#     Hobby(catption="推背"),
#     Person(name="tank",hobby_id=1),
#     Person(name="jason",hobby_id=2)
# ])# session.commit()# session.close()

# 2 添加,用关联关系
# 01、给Person表中的name字段添加一个数据，并关联他的 爱好
# preson = Person(name="egon",hobby=Hobby(catption="相亲"))
# session.add(preson)
# session.commit()# session.close()

# 02、给Hobby表中catption字段添加一个数据，并关联他的 主人
# hobb = Hobby(catption="人妖")
# pres 是Person表中 字段hobby =relationship("Hobby",backref="pres") 用于反向查询
# hobb.pres = [Person(name="owen"),Person(name="sean")]
# session.add(hobb)
# session.commit()# session.close()

# 3 正向查询
# pr = session.query(Person).filter( Person.name == "tank").first()
# print(pr.name)
# print(pr.hobby.catption)


# 4 反向查
# v = session.query(Hobby).filter(Hobby.catption=="人妖").first()
# print(v.catption)
# print(v.pres)


# 5 自己连表查询(isouter=True表示是left join，不填默认为inner join)
person_list1 = session.query(Person).join(Hobby,Person.hobby_id==Hobby.id).all()       # inner join
person_list = session.query(Hobby).join(Person,Person.hobby_id==Hobby.id,isouter=True) # left join

print(person_list)

session.close()

3.2多对多

#表
# 一个男孩可以喜欢多个女孩，一个女孩也可以喜欢多个男孩
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)

    def __repr__(self):
        return self.name

class Boy(Base):
    __tablename__ = "boy"
    id = Column(Integer, primary_key=True)
    name = Column(String(100), nullable=False)
    #secondary=boy2girl 中间表的表名
    girl = relationship("Girl",secondary="boy2girl",backref = "boys")

    def __repr__(self):
        return self.name

#查询
from sqlalchemy.orm import sessionmaker
from sqlalchemy import create_engine
from models import Boy,Boy2Girl,Girl

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

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

#添加,没有用关联
# session.add_all([
#     Boy(name="tank"),
#     Boy(name="sean"),
#     Girl(name="仓老师"),
#     Girl(name="小泽老师")
# ])

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

# session.commit()
# session.close()

#添加,用关联
# 01、给Boy表中的name字段 添加一个数据，并关联他喜欢的 人
# boy = Boy(name="亚峰")
# boy.girl=[Girl(name="迪丽热巴"),Girl(name="三上")]
# session.add(boy)
# session.commit()

# 01、给Girl表中的name字段 添加一个数据，并关联她喜欢的 人
# girl = Girl(name="丹丹")
# girl.boys=[Boy(name="吴彦祖"),Boy(name="鹿晗")]
# session.add(girl)
# session.commit()


# 正向查：使用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)