describe_tables.py

#!/usr/bin/env python
import pymysql.cursors
"""
Stupid, yet handy.
Dump a description of all tables in a database.
Edit the variables below the "if __name__" line
writes a markdown report suitable for whatever suits you.
"""


class DescribeTables(object):

    def connection(self):
        return pymysql.connect(
                user=USERNAME,
                password=PASSWORD,
                host=HOST,
                db=DB,
                port=PORT,
                charset=CHARSET,
                cursorclass=pymysql.cursors.DictCursor
                )

    def get_tables(self):
        """get all the tables"""
        connection = self.connection()
        with connection.cursor() as cursor:
            cursor.execute("SHOW TABLES")
            for row in cursor.fetchall():
                yield row

    def describe_table(self, table_name):
        """describe each table and spit out a markdown-formatted report"""
        l = ["## %s"%table_name]
        l.append("|".join(fieldnames))
        l.append("|".join(["---" for i in fieldnames]))
        connection = self.connection()
        with connection.cursor() as cursor:
            cursor.execute("DESCRIBE %s"%table_name)
            for field in cursor.fetchall():
                l.append("|".join([str(field[k]) for k in fieldnames]))
        return "\n".join(l)

    def tables(self):
        """title the report, acquire and join all tables, then write .md file"""
        l = ["# %s"%DB]
        tables = self.get_tables()
        for table in tables:
            table_name = table["Tables_in_%s"%DB]
            l.append(self.describe_table(table_name))
        with open("%s.md"%DB, "w") as f:
            f.write("\n".join(l))

if __name__ == "__main__":
    
    fieldnames = [
            u'Field',
            u'Type',
            u'Default',
            u'Null',
            u'Key',
            u'Extra',
            ]

    # edit these
    USERNAME="the_db_user"
    PASSWORD="correct horse battery staple"
    HOST="localhost"
    DB="fancydb"
    PORT=3306
    CHARSET="utf8mb4"

    describe = DescribeTables()
    describe.tables()

Posted in answer to a question on stack overflow. Not my best work. :-D