Flask,Flask-SQLAlchemy

原文请点击这里

单表更新

db.session.query(Article).filter(Article.id==model.id).update({Article.status:-1})
db.session.commit()

或

art = Article.query.get(post_id)
art.view_times = art.view_times+_times
db.session.commit()

one to one

class UserProfile(db.Model):
    __tablename__ = 't_user_profile'
    id = db.Column(db.Integer, primary_key=True, autoincrement=True)
    parent_id = db.Column(db.Integer, db.ForeignKey('t_user.id'))
    user = db.relationship("User", back_populates="user_profile")

    nick_name = db.Column(db.String(12))
    address = db.Column(db.String(128))
    copyright = db.Column(db.String(256))

class User(UserMixin, db.Model):
    __tablename__ = 't_user'
    id = db.Column(db.Integer, primary_key=True, autoincrement=True)
    username = db.Column(db.String(64), unique=True)
    img = db.Column(db.String(200), default='/static/local/img/default.jpg')
    email = db.Column(db.String(100), unique=True)
    password_hash = db.Column(db.String(128), unique=False)
    is_super = db.Column(db.Integer, default=-1)
    is_active = db.Column(db.Integer, default=1)

    create_time = db.Column(db.DateTime, default=datetime.now())

    user_profile = db.relationship("UserProfile", uselist=False, back_populates="user")

many to many

articles = db.Table('t_columns_articles',
            db.Column('column_id', db.Integer, db.ForeignKey('t_column.id')),
            db.Column('article_id', db.Integer, db.ForeignKey('t_article.id'))
        )

class Article(db.Model):
    __tablename__ = 't_article'
    id = db.Column(db.Integer, primary_key=True, autoincrement=True)
    title = db.Column(db.String(100))
    tags = db.Column(db.String(200))
    content = db.Column(db.Text)

class Column(db.Model):
    __tablename__ = 't_column'
    id = db.Column(db.Integer, primary_key=True, autoincrement=True)
    name = db.Column(db.String(40))
    summary = db.Column(db.Text)
    status = db.Column(db.Integer, default=1)

执行sql

sql = text('''(select id,title,'pre' as 'pre_next' from t_article where id < %s and status=1 order by id desc limit 1) 
            union all 
            (select id,title,'next' from t_article where id > %s and status=1 order by id asc limit 1)''' % (post_id,post_id))
pre_next = db.session.connection().execute(sql).fetchall()

返回结果为列表形式，如

((1,'test blog','next'),(2,'test blog2','pre'))