menu Chancel's blog
rss_feed
Chancel's blog
时来天地皆同力

Python带出数据库视图

作者:Chancel Yang, 时间:2021 Jun 15, 阅读:38

代码如下,请按规则补下信息,并运行该脚本,即可获取按顺序导出的视图

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

[[replyMessage== null?"发表评论":"发表评论 @ " + replyMessage.m_author]]

account_circle
email
web_asset
textsms

评论列表([[messageResponse.total]])

还没有可以显示的留言...
[[messageItem.m_author]] [[messageItem.m_author]]
[[messageItem.create_time]]
[[getEnviron(messageItem.m_environ)]]
[[subMessage.m_author]] [[subMessage.m_author]] @ [[subMessage.parent_message.m_author]] [[subMessage.parent_message.m_author]]
[[subMessage.create_time]]
[[getEnviron(messageItem.m_environ)]]