SQLAlchemy 小计2 (索引)

针对PostgreSQL

  1. 函数索引
    class test_table(db.Model):
            username = db.Column(String(20))
    
            __table_args_ = (Index('idx_username', func.lower('username'), unique=True), )
            # 生成SQL如下:
            # create unique index idx_username on test_table  lower(username)
    
  2. 指定索引类型
    __table_args__ = (Index('idx_point', 'point', postgresql_using='gist'), )
    # 生成SQL:
    # create index idx_point on table_name using(point)
    
  3. 条件索引
    __table_args__ = (Index('idx_where', 'column', postgresql_where = model.c.column > 10), )
    # 生成SQL:
    # create index idx_where on table_name (column) where column > 10
    

SQLAlchmey 创建复合索引

关键字:__table_args__

class User(db.Model):
    """
    user model
    """
    __tablename__ = 'users'

    id = db.Column(BigInteger, primary_key=True)
    username = db.Column(String(30), nullable=False)
    password = db.Column(db.String(60), nullable=False)
    reg_dateline = db.Column(db.String(12), nullable=False)

    # table_args
        # 创建名为 idx_test 含字段 username和reg_dateline的 索引
    __table_args__ = ( Index('idx_test','username','reg_dateline') )