【Flask】Sqlalchemy join

### join：
1. join分为left join（左外连接）和right join（右外连接）以及内连接（等值连接）。
2. 参考的网页：http://www.jb51.net/article/15386.htm
3. 在sqlalchemy中，使用join来完成内连接。在写join的时候，如果不写join的条件，那么默认将使用外键来作为条件连接。
4. query查找出来什么值，不会取决于join后面的东西，而是取决于query方法中传了什么参数。就跟原生sql中的select 后面那一个一样。
比如现在要实现一个功能，要查找所有用户，按照发表文章的数量来进行排序。示例代码如下：

 # coding:utf-8

 # Author: liangjun.chen

 from datetime import datetime

 from sqlalchemy import create_engine, Column, Integer, String, DateTime, Text, ForeignKey, func

 from sqlalchemy.ext.declarative import declarative_base

 from sqlalchemy.orm import sessionmaker, relationship, backref

 HOSTNAME = '127.0.0.1'

 PORT = 3306

 DATABASE = 'sqlalchemy_first'

 USERNAME = 'root'

 PASSWORD = ''

 DB_URI = 'mysql+pymysql://{username}:{password}@{host}:{port}/{dbname}?charset=utf8'.format(

         username=USERNAME, password=PASSWORD, host=HOSTNAME, port=PORT, dbname=DATABASE

 )

 engine = create_engine(DB_URI)

 Base = declarative_base(engine)

 Session = sessionmaker(engine)

 session = Session()

 # article

 class Article(Base):

     __tablename__ = 'article'

     id = Column(Integer, primary_key=True, autoincrement=True)

     title = Column(String(50), nullable=False)

     create_time = Column(DateTime,nullable=False, default=datetime.now)

     uid = Column(Integer, ForeignKey("user.id"))

     # relation 正向引用, backref反向引用

     author = relationship("User", backref=backref('articles', lazy='dynamic'))

     def __repr__(self):

         return "Article <title: {}>".format(self.title)

 class User(Base):

     __tablename__ = 'user'

     id = Column(Integer, primary_key=True, autoincrement=True)

     username = Column(String(50), nullable=False)

 # Base.metadata.drop_all()

 # Base.metadata.create_all()

 #

 # user1 = User(username='saber')

 # user2 = User(username='slamdunk')

 #

 #

 # for _ in xrange(1):

 #     article = Article(title='article{}'.format(_))

 #     article.author = user1

 #     session.add(article)

 # session.commit()

 #

 # for _ in xrange(3):

 #     article = Article(title='article{}'.format(_))

 #     article.author = user2

 #     session.add(article)

 # session.commit()

 # 找到所有用户, 按照发表文章数量进行排序

 result = session.query(User.username, func.count(Article.id)).join(Article, Article.uid==User.id).group_by(User.id).order_by(func.count(Article.id).desc()).all()

 print result

巴特西

【Flask】Sqlalchemy join

最新文章

热门文章