Simple python script for SQLite tables documenting

pysqlitedoc.py

# table_comments.json
#
# {
#     "customers": {
#         "comment": "Customers table",
#         "fields": {
#             "City": "City field",
#             "Country": "Country field"
#         }
#     }
# }

import sqlite3
import json

db = sqlite3.connect("chinook.db")
json_file = open("table_comments.json", "r", encoding='utf-8')
output = open("result.html", "w", encoding='utf-8')

comments = json.load(json_file)
json_file.close()
db.row_factory = sqlite3.Row

for tabledef in db.execute("SELECT * FROM sqlite_master WHERE type = 'table' AND name NOT LIKE 'sqlite\\_%' ESCAPE '\\' "):
    tablename = tabledef["name"]
    output.write("<h3>{}</h3>\n".format(tablename))

    try:
        output.write("<p>{}</p>\n".format(comments.get(tablename).get("comment", "")))
    except AttributeError:
        pass

    output.write("<table border=\"1\" cellspacing=\"0\">\n")
    output.write(""" <tr>
  <th>Field name</th>
  <th>Type</th>
  <th>Primary</th>
  <th>Required</th>
  <th>Default</th>
  <th>Description</b>
 </tr>
""")

    for field in db.execute("PRAGMA table_info({})".format(tablename)):
        output.write(" <tr>\n")
        output.write("  <td>{}</td>\n".format(field["name"]))
        output.write("  <td>{}</td>\n".format(field["type"]))
        output.write("  <td style=\"text-align:center\">{}</td>\n".format("*" if field["pk"] == 1 else ""))
        output.write("  <td style=\"text-align:center\">{}</td>\n".format("*" if field["notnull"] == 1 else ""))
        output.write("  <td style=\"text-align:center\">{}</td>\n".format("NULL" if field["dflt_value"] is None else field["dflt_value"]))

        try:
            output.write("  <td>{}</td>\n".format(comments.get(tablename).get("fields").get(field["name"], "")))
        except AttributeError:
            output.write("  <td></td>\n")

        output.write(" </tr>\n")

    output.write("</table>\n")

output.close()