pip install mysql-connector
# coding=utf-8
import json
import os
import sys
import threading
import time
import mysql.connector
conn = None
reload(sys)
sys.setdefaultencoding('utf8')
env = 'dev_qiye'
doris_config = {
'dev_qiye': {
'host': '127.0.0.1',
'port': 8130,
'user': 'root',
'password': ''
}
}
def get_create_db_sqls(db_names, curr):
white_list = {'information_schema': 1}
sqls_map = {}
for idb in db_names:
db_name = idb[0]
if white_list.has_key(db_name):
continue
curr.execute('show create database {}'.format(db_name))
_sqls = curr.fetchall()
for _q in _sqls:
sqls_map[_q[0]] = _q[1].lower().replace('create database', 'create database if not exists')
return sqls_map
def clean_table_sql(table_create_sql):
arr = table_create_sql.split('\n')
new_arr = []
for line in arr:
if line.find('dynamic_partition.replication_num') > -1:
continue
if line.find('dynamic_partition.time_zone') > -1:
continue
new_arr.append(line)
return '\n'.join(new_arr)
def dump_structures():
create_db_sqls = {}
create_table_sqls = {}
conn = mysql.connector.connect(**doris_config[env])
curr = conn.cursor()
sql_show_databases = 'show databases;'
curr.execute(sql_show_databases)
all_db_names_arr = curr.fetchall()
result_arr = []
if len(all_db_names_arr) <= 0:
pass
else:
create_db_sqls = get_create_db_sqls(all_db_names_arr, curr)
for db, c_db_sql in create_db_sqls.items():
curr.execute('show tables from {}'.format(db))
_tables = curr.fetchall()
for _t in _tables:
curr.execute('show create table {}.{}'.format(db, _t[0]))
table_sql = curr.fetchall()
full_table_name = '{}.{}'.format(db, table_sql[0][0])
full_table_sql = table_sql[0][1].replace(table_sql[0][0], '{}`.`{}'.format(db, table_sql[0][0]),
1).lower().replace('create table',
'create table if not exists')
create_table_sqls[full_table_name] = full_table_sql
result_arr.append("### start create database \n\n")
for db, sql in create_db_sqls.items():
_sql = '{};\n'.format(sql)
result_arr.append(_sql)
result_arr.append("### start create table \n\n")
for tbl, sql in create_table_sqls.items():
_sql = clean_table_sql(sql)
result_arr.append('{}\n'.format(_sql))
with open('dump_structures_doris.sql', 'w') as f:
f.write(''.join(result_arr))
return create_db_sqls, create_table_sqls
if __name__ == '__main__':
dump_structures()
结果输出在在和脚本同级目录的文件中: dump_structures_doris.sql
欢迎添加微信,互相学习↑↑↑ -_-
白老虎
programming is not only to solve problems, ways to think
grafana 级连 菜单 templating (variables) 配置
AI 机器人 抓取 微信 聊天中的 百度网盘 分享地址和密码
rocketmq 集群搭建 (2master + 2slave + 2namesrv)