from random import randint
from sqlalchemy import Column, Integer, String,ForeignKey,func
from sqlalchemy.orm import relationship,backref
from util_db import Base, Session
class User(Base):
__tablename__ = 't_user'
id = Column(Integer, primary_key = True, autoincrement=True)
name = Column(String(32))
age = Column(Integer)
def __repr__(self):
return f'<User: id={self.id} name = {self.name} age = {self.age}>'
class News(Base):
__tablename__ = 't_news'
id = Column(Integer, primary_key = True, autoincrement=True)
title = Column(String(32), nullable = False)
content = Column(String(32), nullable = False)
read_count = Column(Integer)
# uid = Column(Integer, ForeignKey('t_user.id'))
uid = Column(Integer) # 测试2:不去建立外键和不去关联
# user = relationship('User', backref = backref('newss'))
def __repr__(self) -> str:
return f'<News: id={self.id} title={self.title} content={self.content} read_count={self.read_count}>'
def create_data():
Base.metadata.drop_all()
Base.metadata.create_all()
with Session() as ses:
for i in range(2):
user = User(name=f'name{i}', age = randint(6,30))
ses.add(user)
for i in range(10):
news = News(title=f'title{i}',content=f'info{i}',read_count=randint(0,1000),uid = randint(1,2))
# 没有级联操作了,单独提交
ses.add(news)
ses.commit()
def query_join():
# 找到所有用户 新闻数量, 按照发表新闻数量进行排序
'''
SELECT u.name, count(n.id) news中的id是新闻的编号,uid才是作者user的编号
FROM t_news n JOIN t_user u
ON n.uid = u.id
GROUP BY u.id
ORDER BY count(n.id)
'''
with Session() as ses:
# join里面放对象类名时,默认以外键为关联条件
rs = ses.query(User.name, func.count(News.id)).join(News).group_by(User.id).order_by(News.id).all()
print(rs)
def query_join_byself():
# 自己手动去指定关联关系
with Session() as ses:
rs = ses.query(User.name, func.count(News.id)).join(News, News.uid== User.id).group_by(User.id).order_by(News.id).all()
print(rs)
if __name__ == "__main__":
create_data()
# query_join()
query_join_byself()
109.SQLAlchemy中join的使用
于 2022-09-27 17:44:12 首次发布