(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()