def find():
val = sessionObj.query(NewAuthor).first()
print(val)
print('1、通过查找作者来反向查找当前用户有所少篇新闻:', val.NewWorks)
# val = sessionObj.query(NewWorks).first()
val = sessionObj.query(NewWorks).filter(NewWorks.id == 1).first()
print(val)
print('2、通过查找新闻来反向查找出当前新闻的作者:', val.authorInfo)
def update():
"""修改数据"""
result4 = sessionObj.query(Person).first()
# 修改name字段值
result4.name = 'Lucy'
# 提交修改
sessionObj.commit()
def find():
"""通过用户信息表反向查找出作者名称和所在出版社"""
info = sessionObj.query(NewAuthorInfo).first()
print('1、作者信息:', info)
print('2、作者:', info.newAuthor)
print('3、该作者所处出版社:', info.pressData)
print("*" * 60)
"""通过查找作者名字表反向查找出作者详细信息"""
info = sessionObj.query(NewAuthor).first()
print('4、作者:', info)
print('5、作者信息:', info.newAuthorInfo)
print("*" * 60)
info = sessionObj.query(NewPress).first()
print('6、出版社:', info)
print("7、该出版社人员:", info.NewAuthorInfo)
def select():
# 查看全表时不添加all()显示原生sql语句
sql = sessionObj.query(Person)
# print(sql)
# 查询所有数据
result = sessionObj.query(Person).all()
for result in result:
# print(result)
pass
# 条件查询之filter_by
result1 = sessionObj.query(Person).filter_by(name='jack').all()
# print(result1)
# 条件查询之filter
result2 = sessionObj.query(Person).filter(Person.name == 'jack').all()
# print(result2)
# 条件查询之get依据id查询,不存在不会报错
result3 = sessionObj.query(Person).get(2)
# print(result3)
# 条件查询之first()查询第一条
result4 = sessionObj.query(Person).first()
print(result4)
def insert():
author1 = NewAuthor(name='赖志添', age=23, gender=True)
author2 = NewAuthor(name='陈辰', age=25, gender=False)
sessionObj.add(author2)
sessionObj.commit()
val = sessionObj.query(NewAuthor).first()
works = NewWorks(context='测试一对多关系', author=val.name, Author_id=val.id)
works = NewWorks(context='通过反向查找获取一个用户有多少篇新闻',
author=val.name,
Author_id=val.id)
sessionObj.add(works)
sessionObj.commit()
def delete():
val = sessionObj.query(NewAuthor).first()
# 子表NewWorks中关联字段Author_id设置了不可为空,所以不可删除相关联的主表数据
sessionObj.delete(val)
sessionObj.commit()
def update():
val = sessionObj.query(NewAuthor).first()
val.newInfo = []
sessionObj.commit()
from sqlalchemy import func
from sqlalchemy_demo.create_tableModel import Person
from sqlalchemy_demo.db_tool import sessionObj
"""
query查询函数的使用及可专递参数
1、模型名(对应为所有查询)
2、模型中的属性
3、聚合函数
"""
all = sessionObj.query(Person).all() # 模型名=全表查询
print(all)
single = sessionObj.query(Person).filter_by(age=23).all() # 某属性查询
# print(single)
# 聚合函数查询
sql = sessionObj.query(func.count(Person.id)) # 只查看原生sql语句
count = sessionObj.query(func.count(Person.id)).first()
max = sessionObj.query(func.max(Person.age)).first()
min = sessionObj.query(func.min(Person.age)).first()
avg = sessionObj.query(func.avg(Person.age)).first()
sum = sessionObj.query(func.sum(Person.age)).first()
# print(sum)
if __name__ == "__main__":
pass
def delete():
result4 = sessionObj.query(Person).first()
# 删除查询出的记录
sessionObj.delete(result4)
# 提交删除
sessionObj.commit()
# 等值查询 equal ==
# 非等值查询 equal !=
# 模糊查询 like("%a%")
# 模糊查询之不区分大小写 ilike("%a%")
# 多值查询 in_(['a', 'b', 'c'])
# not in ~模型.属性.in_(['a', 'f','g'])
# is null ==None
# is null is_(None)
# is not null !=None
# is not null isnot(None)
# and filter(_and(条件一,条件二))
# and filter(条件一,条件二)
# or filter(or_(条件一,条件二))
equal = sessionObj.query(Person).filter(Person.name == 'jack').first()
print(equal)
equal = sessionObj.query(Person).filter(Person.name != 'jack').all()
print(equal)
equal = sessionObj.query(Person).filter(Person.name.like('%志%')).all()
print(equal)
equal = sessionObj.query(Person).filter(Person.name.in_(['jack', 'Tom'])).all()
print(equal)
equal = sessionObj.query(Person).filter(
~Person.name.in_(['jack', 'Tom'])).all()
print(equal)