(python实现)mysql5.6导出互相依赖的视图

1.python安装;

网站:https://www.python.org/,下载的为windows 安装版,下载后默认安装,将python 加入环境变量.

2.pymysql安装;

github地址:https://github.com/PyMySQL/PyMySQL,下载压缩包,解压到任意目录,然后执行py setup.py install,出现最后会出现Finished dependencies………… 说明导入成功

3.eclipse+python dev插件

新建pydev project项目,然后将python的类库和pymysql的类库导入,然后将第4步的代码复制进去,执行就会在该文件的同级目录下生产一个view.sql,即需要的视图.

4.代码

import pymysql
conn = pymysql.connect(host='127.0.0.1', port=3307, user='root',passwd='root', db='demo')
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 ', 'demo')
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()