Posted at

テーブル定義を自動出力

More than 1 year has passed since last update.

今更すぎるけど

https://qiita.com/arc279/items/0a3895a04fc814512baf

sqlalchemyMetaData がイケてるので書き直した。

pip install -U sqlalchemy pymysql psycopg2-binary jinja2 click


コード


generate_doc_from_schema.py

import os

import click
from jinja2 import Template
from sqlalchemy import create_engine, MetaData

template = Template("""
<fieldset>
<legend>{{table.name}}</legend>

<table>
<thead>
<tr>
<th>name</th>
<th>type</th>
<th>default</th>
<th>nullable</th>
<th>autoincrement</th>
<th>primary_key</th>
<th>foreign_keys</th>
</tr>
</thead>

<tbody>
{% for column in table.columns %}
<tr>
<td>{{ column.name }}</td>
<td>{{ column.type }}</td>
<td>{{ column.default }}</td>
<td>{{ column.nullable }}</td>
<td>{{ column.autoincrement }}</td>
<td>{{ column.primary_key }}</td>
<td>{{ column.foreign_keys }}</td>
</tr>
{
% endfor %}
</tbody>
</table>
</fieldset>
"""
)

def render(metadata, fp):
for table in metadata.tables:
print(template.render(table=metadata.tables[table]), file=fp)

@click.group()
def cmd():
pass

@cmd.command()
@click.option('--pg-host', envvar='PGHOST')
@click.option('--pg-port', envvar='PGPORT')
@click.option('--pg-user', envvar='PGUSER')
@click.option('--pg-password', envvar='PGPASSWORD')
@click.option('--pg-database', envvar='PGDATABASE')
@click.option('--output', default=os.sys.stdout, type=click.File('w'))
def psql(**kwargs):
opts = type("options", (object,), kwargs)

def get_engine(host, port, user, password, database, schema='postgresql+psycopg2'):
url = '{schema}://{user}:{password}@{host}:{port}/{database}'.format(
**locals())
return create_engine(url, echo=False)

engine = get_engine(
opts.pg_host,
opts.pg_port,
opts.pg_user,
opts.pg_password,
opts.pg_database)
metadata = MetaData()
metadata.reflect(engine)

render(metadata, opts.output)

@cmd.command()
@click.option('--my-host', envvar='MYSQL_HOST')
@click.option('--my-port', envvar='MYSQL_TCP_PORT')
@click.option('--my-user', envvar='USER')
@click.option('--my-password', envvar='MYSQL_PWD')
@click.option('--my-database', required=True)
@click.option('--output', default=os.sys.stdout, type=click.File('w'))
def mysql(**kwargs):
opts = type("options", (object,), kwargs)

def get_engine(host, port, user, password, database, schema='mysql+pymysql', charset='utf8'):
url = '{schema}://{user}:{password}@{host}:{port}/{database}?charset={charset}'.format(
**locals())
return create_engine(url, echo=False)

engine = get_engine(
opts.my_host,
opts.my_port,
opts.my_user,
opts.my_password,
opts.my_database)
metadata = MetaData()
metadata.reflect(engine)

render(metadata, opts.output)

if __name__ == '__main__':
cmd()



実行例

他RDBでも


  • pipでアダプタ入れて


  • sqlalchemy.engine.base.Engine 作って

  • MetaData#reflect

すればだいたいいけるはず。

あとはtemplateを適当にいじるとよいのでは。


mysql


bash

python generate_doc_from_schema.py mysql \

--my-host 127.0.0.1 \
--my-port 3306 \
--my-user root \
--my-password hogehoge \
--my-database your_database


psql


bash

export PGHOST=127.0.0.1

export PGPORT=5432
export PGUSER=root
export PGPASSWORD=hogehoge
export PGDATABASE=your_database
python generate_doc_from_schema.py psql

xlwt とか使えばexcelに出力したりもできるけど大変めんどくさい。