#encoding:utf8
import re
def TypeDM_Oracle2Mysql(datatype):
res_dict = {"BINARY_DOUBLE": "double", "BINARY_FLOAT": "float", "BLOB": "longblob",
"VARCHAR2": "varchar", "CLOB": "longtext", "DATE": "datetime",
"LONG": "longtext", "NCHAR": "char", "NVARCHAR2": "varchar",
"NCLOB": "longtext", "NUMBER": "decimal", "RAW": "longblob", "TIMESTAMP": "datetime",
"ROWID": "char", "UROWID": "varchar", "REAL": "float", "BOOLEAN": "bit",
"BFILE": "varchar", "LONG RAW": "longblob"}
if datatype not in res_dict:
new_data_type = datatype
else:
new_data_type = res_dict[datatype]
return new_data_type
def get_create_sql(pre_fore_sql, res_list,tab_name,tab_comment):
create_tab_sql = 'CREATE TABLE IF NOT EXISTS `' + tab_name + '` (\n'
for data in res_list:
field_str = '`' + data['COLUMN_NAME'] + '` ' + data['COLUMN_TYPE'] + ' '
# 是否为空
if data['IS_NULLABLE'] == 'N' or data['COLUMN_NAME'] in pre_fore_sql:
field_str += 'NOT NULL '
if data['COLUMN_COMMENT']:
field_str += 'COMMENT \'' + data['COLUMN_COMMENT'] + '\''
field_str += ',\n'
create_tab_sql += field_str
if ',' not in pre_fore_sql:
create_tab_sql = create_tab_sql[:-2] + '\n)ENGINE=InnoDB DEFAULT CHARSET=utf8;'
else:
#pre_fore_sql = pre_fore_sql[:-2].replace(',', ',\n')
create_tab_sql = create_tab_sql + pre_fore_sql + '\n)ENGINE=InnoDB DEFAULT CHARSET=utf8;'
if tab_comment:
# create_tab_sql = create_tab_sql+type(tab_comment)
create_tab_sql = create_tab_sql[:-1] +' COMMENT =\''+tab_comment+'\';'
return create_tab_sql
def get_sql_file(text):
f=open('mysql.sql','w')
tabname_comment=dict()
tabfield_comment = {}
# 找表名和表注释
tabname_pattern = re.compile('COMMENT\sON\sTABLE\s\".*?\"\.\"(.*?)\"\sIS\s\'(.*?)\';')
name_comments = re.findall(tabname_pattern, text);
for res in name_comments:
tabname_comment[res[0]] = res[1]
field_pattern = re.compile('COMMENT\sON\sCOLUMN\s\".*?\"\.\"(.*?)\"\.\"(.*?)\"\sIS\s\'(.*?)\';')
field_comments = re.findall(field_pattern, text)
for res in field_comments:
if res[0] not in tabfield_comment.keys():
tabfield_comment[res[0]] = {}
tabfield_comment[res[0]][res[1]] = res[2]
# 找表名和表字段
#pattern = re.compile('CREATE\sTABLE\s\"(.*?)\"\.\"(.*?)\"#(.*?\)\)).*?;')
pattern = re.compile('CREATE\sTABLE\s\"(.*?)\"\.\"(.*?)\"\s\(\s*(.*?)\r?\n\).*?;',re.S)
tab_reses = re.findall(pattern,text);
for tab_res in tab_reses:
tab_name = tab_res[1]
all_fields_list = tab_res[2].split("\n")
if tab_name in tabname_comment:
tab_comment=tabname_comment[tab_name]
else:
tab_comment=''
if tab_name in tabfield_comment:
field_comment=tabfield_comment[tab_name]
else:
field_comment=''
fields_dict_list = []
pre_fore_sql = ""
pre_list = []
fore_sql = []
unique_sql = []
for field in all_fields_list:
field_dict = {"COLUMN_NAME": "", "COLUMN_TYPE": "", "IS_NULLABLE": "", "COLUMN_COMMENT": ""}
if 'NOT NULL' in field:
field_dict["IS_NULLABLE"] = "N"
#res = re.split('[" ]', field.strip())
#res = re.findall('"(.*?)"\s(.*?\s)|(.*?\(.*?\)).*?', field)
res = re.findall('\"(\w*?)\"\s(.*?\))\s?.*', field.strip())
if len(res)==0:
res = re.findall('\"(\w*?)\"\s(\w+)\s?.*,?', field.strip())
if res:
field_dict["COLUMN_NAME"] = res[0][0]
if len(field_comment)>0 and res[0][0] in field_comment.keys():
field_dict['COLUMN_COMMENT']=field_comment[res[0][0]]
column_type_before = res[0][1]
if '(' in column_type_before:
datatype_list = column_type_before.split('(')
new_datatype = TypeDM_Oracle2Mysql(datatype_list[0])
if new_datatype == 'varchar':
#data_length = datatype_list[1].split(' ')[0]
res = re.findall('(\d+)',datatype_list[1])
data_length = res[0]
if int(data_length) > 255:
new_datatype = 'text'
new_columntype = new_datatype
else:
new_columntype = new_datatype + '(' + data_length+')'
elif new_datatype == 'decimal':
data_length = datatype_list[1]
new_columntype = new_datatype + '(' + data_length
# if int(data_length) > 255:
# new_datatype = 'text'
else:
new_columntype = TypeDM_Oracle2Mysql(column_type_before)
field_dict["COLUMN_TYPE"] = new_columntype
fields_dict_list.append(field_dict)
if len(pre_list) != 0:
pre_list = [x.replace(' ', '') for x in pre_list]
pre_fore_sql = pre_fore_sql + "PRIMARY KEY (`" + ('`,`').join(pre_list) + "`),"
if len(fore_sql) != 0:
pre_fore_sql = pre_fore_sql + (',').join(fore_sql) + ','
if len(unique_sql) != 0:
pre_fore_sql = pre_fore_sql + (',').join(unique_sql) + ','
create_sql = get_create_sql(pre_fore_sql, fields_dict_list, tab_name,tab_comment)
f.write(create_sql+'\n\n\n')
#add_key(text)
#加主键
#ALTER TABLE "DBSJGX"."DDS_BFL" ADD CONSTRAINT "DDS_BFL_PK" PRIMARY KEY ("TNAME");
pattern = re.compile('ALTER\sTABLE\s\"(.*?)\"\.\"(.*?)\"\sADD\sCONSTRAINT\s\"(.*?)\"\sPRIMARY\sKEY\s(.*);')
pk_reses = re.findall(pattern,text);
for pk in pk_reses:
pk_sql = 'ALTER TABLE '+pk[1]+' ADD PRIMARY KEY '+ pk[3].replace('"','')+';'
f.write(pk_sql+'\n\n\n')
#加外键
#ALTER TABLE "DBSJGX"."HQBZ_ZZWZCB_MT" ADD CONSTRAINT "HQBZ_ZZWZCB_M_RELATIONSHIP_743" FOREIGN KEY ("GMQFNM", "WZNM", "DWNM") REFERENCES "DBSJGX"."HQBZ_ZZWZCB" ("GMQFNM", "WZNM", "DWNM")
#ALTER TABLE "DBSJGX"."HQBZ_GDYS_MT" ADD CONSTRAINT "HQBZ_GDYS_MT_RELATIONSHIP_1" FOREIGN KEY ("GDNM") REFERENCES "DBSJGX"."HQBZ_GDYS" ("GDNM")
pattern = re.compile('ALTER\sTABLE\s\"(.*?)\"\.\"(.*?)\"\sADD\sCONSTRAINT\s\"(.*?)\"\sFOREIGN\sKEY\s\((.*?)\)\sREFERENCES\s.*\.\"(.*)\"\s\((.*)\).*')
fk_reses = re.findall(pattern,text);
for fk in fk_reses:
tname=fk[1]
key_name = fk[2]
col_name = fk[3]
ftname = fk[4]
fcol_name = fk[5]
pk_sql = 'ALTER TABLE '+fk[1]+' ADD FOREIGN KEY ('+ fk[3].replace('"','')+') REFERENCES '+fk[4]+'('+fk[5].replace('"','')+');'
f.write(pk_sql+'\n\n\n')
f.close()
with open('C://Python27//dbsjgx.sql','r') as f:
#with open('./test1.sql','r') as f:
#text = f.read().replace('\r\n', '#')
text = f.read()
get_sql_file(text)
你好，我是有问必答小助手，非常抱歉，本次您提出的有问必答问题，目前超出我们的服务范围，暂时无法为您解答。
首次提问人员可免费体验一次有问必答服务。目前首次提问的问题服务范围为：编程语言、Java开发、python、数据库、前端开发 领域专业技术问题，为您提供问题的解决思路和指导。不提供源码代写、项目文档代写、论文代写、安装包资源发送或安装指导等服务。
我们后续会持续优化，扩大我们的服务范围，为您带来更好地服务。
实在说，加个有偿付费比较合理