How to Build a Flask Blog with SQLAlchemy ORM: Step‑by‑Step Guide

SQLAlchemy is a Python ORM library that provides an object‑relational mapping framework for database programming.

Quick‑start documentation: http://www.pythondoc.com/flask-sqlalchemy/quickstart.html

Install with: pip install sqlalchemy Configure Flask to use SQLAlchemy by adding a Config class:

class Config:
    SECRET_KEY = "abc_caonima_wocao"
    SQLALCHEMY_COMMIT_ON_TEARDOWN = True
    SQLALCHEMY_TRACK_MODIFICATIONS = True
    DEBUG = True
    SQLALCHEMY_DATABASE_URI = 'mysql+pymysql://%s:%s@%s:%s/%s' % (DB_USERNAME, DB_PASSWORD, DB_HOST, DB_PORT, DB_NAME)

Initialize the extension in the application:

app = Flask(__name__, template_folder='templates', static_folder='static')
app.config.from_object(Config)

db = SQLAlchemy(app)
db.init_app(app)

Define database models. Example Article model with fields such as id, title, author, img_url, content, tag, uuid, create_time and a relationship to Article_Detail:

class Article(db.Model):
    __tablename__ = 'article'
    id = db.Column(db.Integer, primary_key=True)
    title = db.Column(db.String(32))
    author = db.Column(db.String(32))
    img_url = db.Column(db.Text, nullable=False)
    content = db.Column(db.Text, nullable=False)
    tag = db.Column(db.String(64), nullable=True)
    uuid = db.Column(db.Text, nullable=False)
    create_time = db.Column(db.DateTime, nullable=True, default=datetime.now)
    uid = db.relationship('Article_Detail', backref='article')
    def __repr__(self):
        return '<Article %r>' % self.title

The corresponding Article_Detail model stores the article body and links back to Article via a foreign key:

class Article_Detail(db.Model):
    __tablename__ = 'article_detail'
    id = db.Column(db.Integer, primary_key=True)
    d_content = db.Column(db.Text, nullable=False)
    uid = db.Column(db.Integer, db.ForeignKey('article.id'))
    def __repr__(self):
        return '<Article_Detail %r>' % self.title

Typical CRUD routes:

@home.route('/')
def index():
    init_list = Article.query.order_by(db.desc(Article.create_time)).all()
    return render_template('home/index.html', datas=init_list)

@home.route('/article/<uuid>')
def query_detail(uuid):
    article_res = Article.query.filter_by(uuid=uuid).first()
    article_detail_res = Article_Detail.query.filter_by(uid=article_res.id).first()
    return render_template('article/{}.html'.format(uuid), a_data=article_res, d_data=article_detail_res)

Database migration is handled with flask_migrate. A minimal manager script:

# coding:utf8
from datetime import datetime
from flask_migrate import Migrate, MigrateCommand
from app import app
from app.models import db
from flask_script import Manager, Server

manage = Manager(app)
Migrate(app, db)
manage.add_command('db', MigrateCommand)

if __name__ == "__main__":
    manage.run()

Typical migration commands:

python manage.py db init

python manage.py db migrate

python manage.py db upgrade

Adding comments to an article uses a Flask route that validates a submitted form and inserts a Comment record:

@home.route('/postComment/<uuid>', methods=['GET', 'POST'])
def postComment(uuid):
    form = CommentForm()
    if request.method == "POST" and form.validate_on_submit():
        try:
            add_comment = Comment(theme=form.theme.data, email=form.email.data,
                                 content=form.content.data, uuid=uuid)
            db.session.add(add_comment)
            db.session.commit()
            return "评论成功"
        except Exception as e:
            db.session.rollback()
            return "评论失败"
    else:
        return redirect('/article/{}'.format(uuid))

Database rollback is essential for recovering from errors; Flask‑Migrate provides commands to revert migrations when needed.