basic_comments.py

#!/bin/env python

# -------------------------------------------------------------------------------
# This is a basic implementation of comments with a flat structure,
# as described in my article:
# https://blog.miguelgrinberg.com/post/implementing-user-comments-with-sqlalchemy
# -------------------------------------------------------------------------------

from datetime import datetime
from flask import Flask
from flask_sqlalchemy import SQLAlchemy

app = Flask(__name__)
app.config['SQLALCHEMY_DATABASE_URI'] = 'sqlite://'
app.config['SQLALCHEMY_TRACK_MODIFICATIONS'] = False

db = SQLAlchemy(app)


class Comment(db.Model):
    id = db.Column(db.Integer, primary_key=True)
    text = db.Column(db.String(140))
    author = db.Column(db.String(32))
    timestamp = db.Column(db.DateTime(), default=datetime.utcnow, index=True)


# create the database and insert a few comments
db.create_all()
c1 = Comment(text='hello1', author='bob')
c2 = Comment(text='hello2', author='alice')
c3 = Comment(text='hello3', author='bob')
c4 = Comment(text='hello4', author='alice')
db.session.add_all([c1, c2, c3, c4])
db.session.commit()


# display the comments
for comment in Comment.query.order_by(Comment.timestamp.asc()):
    print('{}: {}'.format(comment.author, comment.text))

comments_with_adjacency_list.py

#!/bin/env python

# -------------------------------------------------------------------------------
# This is an implementation of comments with replies using an adjacency list,
# as described in my article:
# https://blog.miguelgrinberg.com/post/implementing-user-comments-with-sqlalchemy
# -------------------------------------------------------------------------------

from datetime import datetime
from flask import Flask
from flask_sqlalchemy import SQLAlchemy

app = Flask(__name__)
app.config['SQLALCHEMY_DATABASE_URI'] = 'sqlite://'
app.config['SQLALCHEMY_TRACK_MODIFICATIONS'] = False

db = SQLAlchemy(app)


class Comment(db.Model):
    id = db.Column(db.Integer, primary_key=True)
    text = db.Column(db.String(140))
    author = db.Column(db.String(32))
    timestamp = db.Column(db.DateTime(), default=datetime.utcnow, index=True)
    parent_id = db.Column(db.Integer, db.ForeignKey('comment.id'))
    replies = db.relationship(
        'Comment', backref=db.backref('parent', remote_side=[id]),
        lazy='dynamic')

    def add_reply(self, text):
        return Comment(text=text, parent=self)


db.create_all()
c1 = Comment(text='hello1', author='alice')
c2 = Comment(text='hello2', author='bob')
c11 = Comment(text='reply11', author='bob', parent=c1)
c12 = Comment(text='reply12', author='susan', parent=c1)
c111 = Comment(text='reply111', author='susan', parent=c11)
c21 = Comment(text='reply21', author='alice', parent=c2)
db.session.add_all([c1, c2, c11, c12, c111, c21])
db.session.commit()


def display_comment(comment, level=0):
    print('{}{}: {}'.format('  ' * level, comment.author, comment.text))
    for reply in comment.replies:
        display_comment(reply, level + 1)

for comment in Comment.query.filter_by(parent=None):
    display_comment(comment)

comments_with_path.py

#!/bin/env python

# -------------------------------------------------------------------------------
# This is an implementation of comments with replies using a comment path,
# as described in my article:
# https://blog.miguelgrinberg.com/post/implementing-user-comments-with-sqlalchemy
# -------------------------------------------------------------------------------

from datetime import datetime
from flask import Flask
from flask_sqlalchemy import SQLAlchemy

app = Flask(__name__)
app.config['SQLALCHEMY_DATABASE_URI'] = 'sqlite://'
app.config['SQLALCHEMY_TRACK_MODIFICATIONS'] = False

db = SQLAlchemy(app)


class Comment(db.Model):
    _N = 6

    id = db.Column(db.Integer, primary_key=True)
    text = db.Column(db.String(140))
    author = db.Column(db.String(32))
    timestamp = db.Column(db.DateTime(), default=datetime.utcnow, index=True)
    path = db.Column(db.Text, index=True)
    parent_id = db.Column(db.Integer, db.ForeignKey('comment.id'))
    replies = db.relationship(
        'Comment', backref=db.backref('parent', remote_side=[id]),
        lazy='dynamic')

    def save(self):
        db.session.add(self)
        db.session.commit()
        prefix = self.parent.path + '.' if self.parent else ''
        self.path = prefix + '{:0{}d}'.format(self.id, self._N)
        db.session.commit()

    def level(self):
        return len(self.path) // self._N - 1


db.create_all()
c1 = Comment(text='hello1', author='alice')
c2 = Comment(text='hello2', author='bob')
c11 = Comment(text='reply11', author='bob', parent=c1)
c12 = Comment(text='reply12', author='susan', parent=c1)
c111 = Comment(text='reply111', author='susan', parent=c11)
c21 = Comment(text='reply21', author='alice', parent=c2)
for comment in [c1, c2, c11, c12, c111, c21]:
    comment.save()

for comment in Comment.query.order_by(Comment.path):
    print('{}{}: {}'.format('  ' * comment.level(), comment.author, comment.text))