——、先看mysql的join链接方法

#encoding: utf-8from sqlalchemy import create_engine,Column,Integer,String,Float,func,and_,or_,Enum,\    ForeignKey,DateTimefrom sqlalchemy.ext.declarative import declarative_basefrom sqlalchemy.orm import sessionmaker,relationshipfrom random import randintfrom datetime import datetimeHOSTNAME = '127.0.0.1'PORT = 3306DATABASE = 'first_sqlalchemy'USERNAME = 'root'PASSWORD = '123456'#dialect+driver://username:password@host:port/databaseDB_URI = "mysql+pymysql://{username}:{password}@{host}:{port}/" \         "{db}?charset=utf8".format(username=USERNAME,password=PASSWORD,host=HOSTNAME,port=PORT,db=DATABASE)engine = create_engine(DB_URI)Base = declarative_base(engine)# Session = sessionmaker(engine)# session = Session()session = sessionmaker(engine)() #Session(**local_kw)class User(Base):    __tablename__ = 'user'    id = Column(Integer,primary_key=True,autoincrement=True)    username = Column(String(32),nullable=False)    # articles = relationship("Article") #获取用户发布所有文章    def __repr__(self):        return '
     
      '%self.usernameclass Article(Base):    __tablename__ = 'article'    id = Column(Integer,primary_key=True,autoincrement=True)    title = Column(String(50),nullable=False)    create_time = Column(DateTime,default=datetime.now)    uid = Column(Integer,ForeignKey('user.id',ondelete='RESTRICT'))    author = relationship('User',backref='articles') #sqlalchemy orm提供的    def __repr__(self):        return '
      
       '%self.title# Base.metadata.drop_all()## Base.metadata.create_all()## user1 = User(username='zhiliao')# user2 = User(username='ketang')## for x in range(1):#     article = Article(title='title%s'%x)#     article.author = user1#     session.add(article)# session.commit()### for x in range(1,3):#     article = Article(title='title%s'%x)#     article.author = user2#     session.add(article)# session.commit()#找到所有的用户，根据文章的数量进行排序result = session.query(User.username,func.count(Article.id)).join(Article,User.id==Article.uid).\    group_by(User.id).order_by(func.count(Article.id).desc()).all()print(result)#[('ketang', 2), ('zhiliao', 1)]'''SELECT user.username AS user_username, count(article.id) AS count_1 FROM user INNER JOIN article ON user.id = article.uid GROUP BY user.id ORDER BY count(article.id)'''