How to add comments to table or column in mysql using SQLAlchemy?

Question

I want to add comments to the table and columns created of a model. I tried the doc parameter of the Column class, like the following:

class Notice(db.Model):
    __tablename__ = "tb_notice"
    __table_args__ = {'mysql_engine': 'MyISAM'}

    seqno = db.Column(db.Integer, primary_key=True, autoincrement=True, doc="seqno")
    title = db.Column(db.String(200), nullable=False, doc="notice title")
    detail = db.Column(db.TEXT, nullable=True, doc="notice detail ")

But it didn't work, the comments weren't added to the SQL creation statement, and I wonder how to add a comment to the table.

Answer 1

According to the documentation of doc parameter:

doc¶ – optional String that can be used by the ORM or similar to document attributes on the Python side. This attribute does not render SQL comments; use the Column.comment parameter for this purpose.

And the comment parameter:

comment¶ – Optional string that will render an SQL comment on table creation.

Please note that the comment is added in version 1.2 of SQlAlchemy

And for adding a comment for the table, you just pass additional comment attribute (according to the Table class documentation) to your __table_args__ dictionary. Which is also added in version 1.2

The code would be something like this:

class Notice(db.Model):
    
    __tablename__ = "tb_notice"
    __table_args__ = {
        'mysql_engine': 'MyISAM',
        'comment': 'Notice table'
    }

    seqno = db.Column(db.Integer, primary_key=True, autoincrement=True, doc="seqno",
                      comment='Integer representing the sequence number')
    title = db.Column(db.String(200), nullable=False, doc="notice title",
                      comment='Title of the notice, represented as a string')
    detail = db.Column(db.TEXT, nullable=True, doc="notice detail",
                       comment='Notice detail description')

The doc attribute acts as a docstring of your class:

print(Notice.title.__doc__)

will outputs: notice title

Now the corresponding SQL table creation statement would be:

CREATE TABLE `tb_notice` (
  `seqno` int(11) NOT NULL COMMENT 'Integer representing the sequence number',
  `title` varchar(200) NOT NULL COMMENT 'Title of the notice, represented as a string',
  `detail` text COMMENT 'Notice detail description'
) ENGINE=MyISAM DEFAULT CHARSET=utf32 COMMENT='Notice table';

You can see that comments were added correctly to both the table and the columns.

Answer 2

in new 1.2 version you can do

class Notice(db.Model):
    __tablename__ = "tb_notice"
    __table_args__ = {
        'mysql_engine': 'MyISAM'
        'comment': 'yeah comment'
    }