数据库脚本工具

数据字典描述

注意

在每个表格前增加一行注视，格式： .. 开始标记 表名;主键（注意联合主键的写法）

表格前后保留一个空行

在每个表格后增加一行注释，.. 结束标记

.. start_table oms_stat_load;host_name,check_time

=====================  ==============      ==========   =================
属性                    类型（长度）         可否为空      字段描述
=====================  ==============      ==========   =================
host_name               varchar(64)         no           主机名
avg_1                   varchar(8)          no           一分钟负载
avg_5                   varchar(8)          no           五分钟负载
avg_15                  varchar(8)          no           十五分钟负载
check_time              varchar(19)         no           检测时间
=====================  ==============      ==========   ==================

.. end_table

数据库自动创建与sqlalchemy orm生成

脚本由python编写,依赖模块sqlautocode,可使用pip安装:

pip install sqlautocode

执行生成脚本:

python builddb.py

import os,sqlite3

def  create_sql(infile,outfile):

    print infile,outfile

    outfs = open(outfile,'wb')

    with open(infile,'rb') as infs:

        _start_table = False

        _end_table = False

        _current_table_args = None

        while 1:
            line = infs.next().strip()
            if not line:
                continue

            if '.. start_table' in line:
                _start_table = True
                _end_table = False
                _current_table_args = line[15:].strip().split(";")

                _commont_buf = "-- create table %s\r\n"%_current_table_args[0]
                outfs.write(_commont_buf)

                _table_begin_buf = "create table %s (\r\n"%_current_table_args[0]
                outfs.write(_table_begin_buf)

                line = infs.next()
                line = infs.next()  
                line = infs.next()                    
                continue

            if _start_table:
                
                cols = []
                print "start table process %s"%_current_table_args[0]
                while 1:
                    _line = infs.next()
                    if '.. end_table' in _line:
                        _end_table = True
                        _start_table = False

                        _cols_buf = ",\r\n".join(cols)
                        outfs.write(_cols_buf)
                        outfs.write(',\r\n')
                        outfs.write('\tprimary key ( %s )'%_current_table_args[1])
                        outfs.write('\r\n);\r\n')
               
                        print "end table process %s"%_current_table_args[0]
                        break
                    else:
                    
                        if  "==" in _line:
                            continue
                        else:
                            _cls = _line.split()
                            if _cls:
                                _bf = "    %s %s %s"%(_cls[0],_cls[1],(_cls[1]=='yes' and "null" or "not null"))
                                cols.append(_bf)

    print "done"

def build_db(doc_src,sql_file,db_file,model_file):
    try:
        create_sql(doc_src,sql_file)
    except StopIteration:
        pass
    try:
        os.unlink(db_file)
        os.unlink(model_file)
    except:
        pass
    con = sqlite3.connect(db_file)
    con.executescript(open(sql_file).read())
    os.popen("/usr/local/bin/sqlautocode sqlite:///%s -d -o %s"%(db_file,model_file))   

if __name__ == "__main__":
    build_db("database.rst","database.sql","database.sqlite3","db_models.py")