将mysql数据库的建表语句修改成green plum数据库中可执行的脚本

表语句修改成green plum数据库中可执行的脚本"/>

将mysql数据库的建表语句修改成green plum数据库中可执行的脚本

#用来获取输入的对应的表名称的建表语句
#首先输入需要获取的mysql或者sql server的数据库表名称，到对应的数据库中抓出建表语句，
#然后，将建表语句进行对应的修改，形成可以在pg中可用的语句

#连接mysql数据
import pymysql
import sys
import reclass MysqlDB:def __init__(self):self.hostname = ''self.port = 3306self.username = ''self.password = ''self.database = ''def connectmysql(self):try:condb = pymysql.connect(host=self.hostname, port=self.port, user=self.username, passwd=self.password, db=self.database)return  condbexcept Exception:info = sys.exc_info()print("连接异常", info[1])def insertTable(self):# 获取数据库连接condb = self.connectmysql()# 使用cursor() 方法创建一个游标对象 cursorcursor = condb.cursor()try:# 执行sql语句sql = "select * from cn_customer.bank"cursor.execute(sql)# 提交到数据库执行condbmit()except Exception:  # 方法一：捕获所有异常# 如果发生异常，则回滚info = sys.exc_info()print("发生异常", info[1])condb.rollback()finally:# 最终关闭数据库连接condb.close()def _convert_type(self, data_type):"""Normalize MySQL `data_type`"""# if 'varchar' in data_type:#     return 'varchar'if 'int' in  data_type:return 'int4'# elif 'char' in data_type:#     return 'char'elif data_type in ('bit(1)', 'tinyint(1)', 'tinyint(1) unsigned'):return 'boolean'elif re.search(r'smallint.* unsigned', data_type) or 'mediumint' in data_type:return 'integer'elif 'smallint' in data_type:return 'tinyint'elif 'tinyint' in data_type or 'year(' in data_type:return 'tinyint'elif 'bigint' in data_type and 'unsigned' in data_type:return 'numeric'elif re.search(r'int.* unsigned', data_type) or \('bigint' in data_type and 'unsigned' not in data_type):return 'bigint'elif 'float' in data_type:return 'float'elif 'decimal' in data_type:return 'decimal'elif 'double' in data_type:return 'double precision'else:return data_type#获取的table_columns名称，长度等信息def load_columns(self,table_name):# 获取数据库连接condb = self.connectmysql()# 使用cursor() 方法创建一个游标对象 cursorcursor = condb.cursor()fields = []cursor.execute('EXPLAIN `%s`' % table_name)table_info = cursor.fetchall()for res in table_info:if res[2] == 'YES':table_null = 'not null'else:table_null = ''desc = {'column_name': res[0].lower(),'table_name': table_name.lower(),'type': self._convert_type(res[1]).lower(),# 'length': int(length) if length else None,# 'decimals': precision_match.group(2) if precision_match else None,'null': table_null ,'primary_key': res[3] == 'PRI',# 'auto_increment': res[5] == 'auto_increment',# 'default': res[4] if not res[4] == 'NULL' else None,}fields.append(desc)#print(fields)self.postgres_create(fields)#在postgresql中创建表def postgres_create(self,fields):table_name = 'dw_stg.stg_cus_dim_'+ fields[0]['table_name']columns = []primary_key=[]for field in fields:if field['primary_key']:primary_key.append(field['column_name'])table_column = field['column_name'] + ' ' + field['type'] + ' ' + field['null'] + ','+ '\n'columns.append(table_column)create_columns = ''.join(columns) + 'ord_loc varchar(10),\nmodify_date_etl timestamp default now(),\nload_dt timestamp default now(),\n'create_primary_key = ','.join(primary_key)create_sql = "create table %s (\n%sprimary key(%s)\n)\ndistributed by (%s)" %(table_name,create_columns,create_primary_key,create_primary_key)print(create_sql)def main():mysqldb = MysqlDB()connect_result = mysqldb.load_columns('bank')main()