20Flask的orm操作

Flask的orm操作

一对多

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
from sqlalchemy.engine.result import ResultProxy
from models import Users, Hobby, Person

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

# session.add_all([
#     Hobby(caption='乒乓球'),
#     Hobby(caption='羽毛球'),
#     Person(name='张三', hobby_id=1),
#     Person(name='李四', hobby_id=2),
# ])

# person = Person(name='张九', hobby=Hobby(caption='姑娘'))
# session.add(person)
#添加二
# hb = Hobby(caption='人妖')
# hb.pers = [Person(name='文飞'), Person(name='博雅')]
# session.add(hb)
#
# session.commit()
"""

# 使用relationship正向查询
"""
# v = session.query(Person).first()
# print(v.name)
# print(v.hobby.caption)
"""

# 使用relationship反向查询
"""
# v = session.query(Hobby).first()
# print(v.caption)
# print(v.pers)

#方式一，自己链表
# person_list=session.query(Person).join(Hobby,isouter=True)
# print(person_list)
# person_list=session.query(Person,Hobby).join(Hobby,isouter=True)
# print(person_list)
# for row in person_list:
#     # print(row.name,row.caption)
#     print(row[0].name,row[1].caption)
#
# #方式二：通过relationship
#
person_list=session.query(Person).all()
for row in person_list:
    print(row.name,row.hobby.caption)
# #查询喜欢姑娘的所有人
# obj=session.query(Hobby).filter(Hobby.id==1).first()
# persons=obj.pers
# print(persons)
# session.close()
#如果没有建立外键

# ret = session.query(Person).join(Hobby,Person.nid==Hobby.id, isouter=True)
# print(ret)

单表查询

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

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

session = Session()
#　条件
# ret = session.query(Users).filter_by(name='lqz').all()
#表达式，and条件连接
# ret = session.query(Users).filter(Users.id > 7, Users.name == 'jason').all()
# print(ret)
# ret = session.query(Users).filter(Users.id.between(3, 5), Users.name == 'jason').all()
# print(ret)
#注意下划线
# ret = session.query(Users).filter(Users.id.in_([1,3,4])).all()
# print(ret)
#~非，除。。外
# ret = session.query(Users).filter(~Users.id.in_([1,3,4])).all()
# print(ret)

# #二次筛选
# ret = session.query(Users).filter(Users.id.in_(session.query(Users.id).filter_by(name='jason'))).all()
# print(ret)
from sqlalchemy import and_, or_
#or_包裹的都是or条件，and_包裹的都是and条件
#ret = session.query(Users).filter(and_(Users.id > 3, Users.name == 'eric')).all()
# ret = session.query(Users).filter(or_(Users.id >6 , Users.name == 'jason')).all()
# print(ret)
# ret = session.query(Users).filter(
#     or_(
#         Users.id < 2,
#         and_(Users.name == 'eric', Users.id > 3),
#         Users.age != ""
#     )).all()


# 通配符，以e开头，不以e开头
# ret = session.query(Users).filter(Users.name.like('_q%')).all()
# print(ret)
# ret = session.query(Users).filter(~Users.name.like('j%')).all()
# print(ret)

# 限制，用于分页，区间
# ret = session.query(Users)[1:9]
# print(ret)
#
# # 排序，根据name降序排列（从大到小）
# ret = session.query(Users).order_by(Users.name.desc())
# print(ret)


# #第一个条件重复后，再按第二个条件升序排
# ret = session.query(Users).order_by(Users.name.desc(), Users.id.asc())
# print(ret)
#
# # 分组
from sqlalchemy.sql import func
#
# ret = session.query(Users).group_by(Users.name).all()
# print(ret)
# #分组之后取最大id，id之和，最小id
# ret = session.query(
#     func.max(Users.id),
#     func.sum(Users.id),
#     func.min(Users.id)).group_by(Users.name).all()
# print(ret)
# #haviing筛选
# 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)
# # print(ret)

增删改

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

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

session = Session()

# ################ 添加 ################

# obj1 = Users(name="wupeiqi",age=40)
# session.add(obj1)
#
# session.add_all([
#     Users(name="tank"),
#     Users(name="jason"),
#     #Hosts(name="c1.com"),
# ])
# session.commit()


# ################ 删除 ################

# session.query(Users).filter(Users.id == 5).delete()
# session.commit()

# ################ 修改 ################

#传字典
#session.query(Users).filter(Users.id ==4 ).update({"age" : 30,})
#类似于django的F查询
#session.query(Users).filter(Users.id == 3).update({Users.name: Users.name + "sb"}, synchronize_session=False)
#session.query(Users).filter(Users.id == 7).update({"age": Users.age + 16}, synchronize_session="evaluate")
# session.commit()

# ################ 查询 ################

# r1 = session.query(Users).all()
# print(r1)
#只取age列，把name重命名为xx
# r2 = session.query(Users.name.label('sb'), Users.age).filter(Users.id==8).first()
# print(r2.sb)
#filter传的是表达式，filter_by传的是参数
# r3 = session.query(Users).filter(Users.name == "jason").all()
# print(r3)
# r4 = session.query(Users).filter_by(name='jason').all()
# print(r4)
# r5 = session.query(Users).filter_by(name='lqz').first()
#:value 和:name 相当于占位符，用params传参数
#r6 = session.query(Users).filter(text("id<:value and name=:name")).params(value=224, name='fred').order_by(Users.id).all()
#自定义查询sql
#r7 = session.query(Users).from_statement(text("SELECT * FROM users where name=:name")).params(name='ed').all()


#增，删，改都要commit()
session.close()

多对多

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
from sqlalchemy.engine.result import ResultProxy
from models import Users,  Hobby, Person, Girl, Boy2Girl, Boy

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

# session.add_all([
#     Girl(name='c1.com'),
#     Girl(name='c2.com'),
#     Boy(hostname='A组'),
#     Boy(hostname='B组'),
# ])
# session.commit()

# s2g = Boy2Girl(girl_id=1,boy_id=1)
# session.add(s2g)
# session.commit()
#
#
# gp = Girl(name='C组')
# gp.boys = [Boy(hostname='c3.com'),Boy(hostname='c4.com')]
# session.add(gp)
# session.commit()
#
#
# ser = Boy(hostname='c6.com')
# ser.girl = [Girl(name='F组'),Girl(name='G组')]
# session.add(ser)
# session.commit()



# 使用relationship正向查询

# v = session.query(Boy).first()
# print(v.hostname)
# print(v.girl)


# 使用relationship反向查询

# v = session.query(Girl).first()
# print(v.name)
# print(v.boys)
#
#
#
# session.close()