import pymysql

'''
    HOST:数据库IP地址
    PORT:数据库端口
    USER:用户名称
    PASSWD:用户密码
    DB_NAME:数据库名称
    CHARSET:编码格式（中文可默认 utf8mb4 ）
'''

HOST = '192.168.11.214'
PORT = 3306
USER = 'root'
PASSWD = 'my_passwd'
DB_NAME = 'syncthing_manage'
CHARSET = 'utf8mb4'


conn = pymysql.connect(host=HOST, port=PORT, user=USER,
                       passwd=PASSWD, db=DB_NAME, charset=CHARSET)


def process_rely(parmas={}, rely_old=[]):
    _rely = []
    _keys = list(parmas.keys())
    for k in rely_old:
        for bl in _keys:
            if str(parmas[k]).find(bl) > -1:
                if bl not in _rely:
                    if k not in _rely:
                        _rely.append(bl)
                    else:
                        i = _rely.index(k)
                        _rely.insert(i, bl)
                else:
                    if k in _rely:
                        i = _rely.index(k)
                        j = _rely.index(bl)
                        if i < j:
                            del _rely[j]
                            _rely.insert(i, bl)
        if k not in _rely:
            _rely.append(k)
    return _rely


cur = conn.cursor()
cur.execute('select TABLE_NAME, VIEW_DEFINITION from  information_schema.VIEWS where TABLE_SCHEMA = %s ', DB_NAME)
rs = cur.fetchall()
cur.close()
conn.close()

ps = {}
for al in rs:
    ps['`' + al[0] + '`'] = al[1]

rely = process_rely(ps, list(ps.keys()))
# rely = process_rely(ps, rely1)

file_object = open('view.sql', 'w')
for al in rely:
    file_object.write('DROP VIEW IF EXISTS ' + al + ';\n')
    file_object.write('CREATE ALGORITHM=UNDEFINED DEFINER=`root`@`%` SQL SECURITY DEFINER VIEW ' + al +
                      ' AS ' + ps[al] + ';\n\n')

file_object.close()