user_type = relationship("UserType", backref=‘xxoo‘)

#执行建立数据库
def creat_db():
engine = create_engine("mysql+pymysql://root:123456@127.0.0.1:3306/day6?charset=utf8", max_overflow=5)
Base.metadata.create_all(engine)
def drop_db():
engine = create_engine("mysql+pymysql://root:123456@127.0.0.1:3306/day6?charset=utf8", max_overflow=5)
Base.metadata.drop_all(engine)

#由dbAPI 连接到数据库
engine = create_engine("mysql+pymysql://root:123456@127.0.0.1:3306/day6?charset=utf8", max_overflow=5)
creat_db()
#Session取到链接对表操作
Session = sessionmaker(bind=engine)
session = Session()

#类》表
#对象》行
#增加----------------------------------------------------------------------------------------------------

# a = [
# UserType(title =‘白银‘),
# UserType(title =‘黄钻‘),
# UserType(title =‘黑钻‘),
# UserType(title =‘普通‘),
# ]
# session.add_all(a)

# #查-----------------------------------------------
# print(session.query(UserType).all()) #生成sql语句

# user_type_list = session.query(UserType).all()
# for row in user_type_list:
# print(row.id, row.title)

# select xxx UserType where
# user_type_list = session.query(UserType).filter(UserType.id>2)
# for row in user_type_list:
# print(row.id, row.title)

#删除----------------------------------------------------------
# session.query(UserType.id, UserType.title).filter(UserType.id==3).delete()

#修改------------------------------------------------------------
# session.query(UserType.id, UserType.title).filter(UserType.id>3).update({"title":"黄钻6"}) #范围性修改
# session.query(UserType.id, UserType.title).filter(UserType.id>3).update({UserType.title:UserType.title+‘099‘} # 条件修改
# , synchronize_session=False)
# session.query(UserType.id, UserType.title).filter(UserType.id>3).update({"number":UserType.number+1}
# , synchronize_session="evaluate")


# -------------------------------------------------------------------------
# 分组，排序，连表，通配符，子查询，limit，union，索引，where，

# 连表操作-----------------------------------------------------------------
# ret = session.query(User, UserType)
# select * from user,usertype;

# ret = session.query(Users, UserType).filter(User.usertype_id==UserType.id)
# select * from user,usertype whre user.usertype_id = usertype.id

# result = session.query(User).join(UserType)
# print(result)

# result = session.query(User).join(UserType,isouter=True)
# print(result)

# 临时表--------------------------------------------------------------
# select id , (select id from b where a.id = b.xx) from a
# a = session.query(User.id, session.query(UserType).filter(User.user_type_id==UserType.id).as_scalar())
# print(a)

# select * from (select * from tb) as b
# a = session.query(UserType).filter(UserType.id>2).subquery()
# r = session.query(a).all()
# print(r)

#relationship的添加 在 django中也同样适应
# 1正向操作
# question1. 获取用户信息以及与其关联的用户类型名称(FK,Relationship=>正向操作)
# user_list = session.query(Users,UserType).join(UserType,isouter=True)
# print(user_list)
# for row in user_list:
# print(row[0].id,row[0].name,row[0].email,row[0].user_type_id,row[1].title)

# user_list = session.query(Users.name,UserType.title).join(UserType,isouter=True).all()
# for row in user_list:
# print(row[0],row[1],row.name,row.title)


# user_list = session.query(Users)
# for row in user_list:
# print(row.name,row.id,row.user_type.title)



#2.反向操作
#question: 获取用户类型
# type_list = session.query(UserType)
# for i in type_list:
# print(i.id, i.title, session.query(User).filter(User.user_type_id == i.id).all())

# type_list = session.query(UserType)
# for i in type_list:
# print(i.id, i.title, i.xxoo)
#

session.commit()
session.close()

---

总结：关于sqlalchemy的使用方法简单的了解就好，重点是

理解relationship的用法和sql语句在框架中的转化格式，对学习django打下基础

-学习文献https://www.cnblogs.com/wupeiqi/articles/5713330.html 

　　　　　https://pythonav.com/index/

Mysql学习日记-08ORM框架（结束）

标签：框架   反向   --   sync   好处   djang   var   django   msql