Dump database table schema as reST text.

dbschema2rest.py

#!/usr/bin/env python
# -*- coding: utf-8 -*-

"""Dump database table schema as reST text.

This script requires configuration file which include title text and database
connection string.

Example configuration file is defined below in JSON ::

    {
        "development": {
            "title": "Database Table Schema on development env",
            "database": {
                "url": "mysql+mysqlconnector://server/dbname"
            }
        },
        "staging": {
            "title": "Database Table Schema on staging env",
            "database": {
                "url": "mysql+mysqlconnector://server/dbname"
            }
        },
        "production": {
            "title": "Database Table Schema on production env",
            "database": {
                "url": "mysql+mysqlconnector://server/dbname"
            }
        }
    }

Requirements::

    mysql-connector-python
    SQLAlchemy
    jinja2
    clitool
"""

from jinja2 import Template
from sqlalchemy import MetaData, create_engine
from sqlalchemy.orm import relationship, sessionmaker
from sqlalchemy.ext.declarative import declarative_base


from clitool.cli import climain, cliconfig

template = Template("""
=======================================
{{ title }}
=======================================

Connection::

    {{ url }}

.. contents::

{% for table in tables %}

{{ table.name }}
-----------------------------------

{% if table.primary_key %}
PK::

    {{ table.primary_key }}
{% endif %}

{% if table.foreign_keys %}
FK::

    {{ table.foreign_keys }}
{% endif %}

.. list-table::
   :header-rows: 1

   * - Name
     - Type
     - Nullable
     - Primary Key
     - Default
     - Foreign Keys
     - Description
{% for col in table.columns %}
   * - {{ col.name }}
     - {{ col.type }}
     - {% if col.nullable %}{{ col.nullable }}{% endif %}
     - {% if col.primary_key %}{{ col.primary_key }}{% endif %}
     - {% if col.default %}{{ col.default }}{% endif %}
     - {% if col.foreign_keys %}{{ col.foreign_keys }}{% endif %}
     - {% if col.description != col.name %}{{ col.description }}{% endif %}
{% endfor %}
{% endfor %}
""")


Base = declarative_base()
Session = sessionmaker()


class SessionFactory(object):

    def __init__(self, dsl, auto=False):
        if auto:
            engine = create_engine(dsl, echo=True)
            Base.metadata.create_all(engine)
        else:
            engine = create_engine(dsl)
        Session.configure(bind=engine)

    def create(self):
        return Session()


@climain
def main(config, output, output_encoding, **kwargs):
    cfg = cliconfig(config)
    db = cfg['database']
    title = cfg.get('title', '')
    session = SessionFactory(str(db['url'])).create()
    engine = session.get_bind()
    metadata = MetaData(bind=engine)
    metadata.reflect()
    output.write(template.render(title=title,
                                 url=db['url'],
                                 tables=metadata.sorted_tables))

if __name__ == '__main__':
    main()

# vim: set et ts=4 sw=4 cindent fileencoding=utf-8 :