Yesterday I got a work to convert a MS SQL CREATE TABLE script to MySql. There were some other stuffs like DROP PROCEDURE, ALTER TABLE in the script. But my client wanted me to convert only the CREATE TABLE parts. So rather converting this manually, I made a python script to achieve the task.
The script was like as follows:-
The convert.py as follows:
The script was like as follows:-
I found that every statement has been ended with GO command. I selected it as a delimiter for the script. At first, I read the whole file in my buffer. Then I extracted only the CREATE TABLE ... GO parts. I found there were 233 tables. Then I modified the CREATE TABLE parts by adding ` before column and table names, replacing [ and ] and also adding longblob and decimal(19,4) data type instead of the image and money data types.exec sp_dboption N'testdb', N'autoclose', N'true'GOexec sp_dboption N'testdb', N'bulkcopy', N'false'GO/****** Object: Stored Procedure dbo.testproc Script Date: 11/01/2003 15:25:29 ******/if exists (select * from dbo.sysobjects where id = object_id(N'[dbo].[testproc]') and OBJECTPROPERTY(id, N'IsProcedure') = 1)drop procedure [dbo].[testproc]GOCREATE TABLE [dbo].[table1] ([OrgCode] [varchar] (5) NOT NULL ,[OrgName] [varchar] (60) NOT NULL ,[Logo] [image] NULL ,[Col1] [bit] NOT NULL ,[Col2] [varchar] (25) NOT NULL ,[col3] [timestamp] NULL) ON [PRIMARY]GO
/****** Object: Table [dbo].[table2] Script Date: 11/01/2003 15:27:18 ******/CREATE TABLE [dbo].[table2] ([ORG_CODE] [varchar] (5) NOT NULL ,[col1] [varchar] (10) NOT NULL ,[col2] [timestamp] NULL) ON [PRIMARY]GO
The convert.py as follows:
#!/usr/bin/python -tt
import sys
import commands
import os
import re
def make_list_create_table(script_text):
list = script_text.split('\nGO')
list_create_table = []
for i in range(len(list)):
match = re.search('CREATE TABLE',list[i])
if match:
list_create_table.append(list[i])
return list_create_table
def convert_create_table_for_mysql(list_create_table):
for i in range(len(list_create_table)):
s = list_create_table[i]
m = re.search('create table(.*)\([\r\n a-zA-Z0-9,\t_\)\( \[\]]+\)',s,flags=re.DOTALL|re.IGNORECASE)
if m:
s = m.group()
#replace [] from column name and add `
s = re.sub(r'''([\(,]+[\r\n\t ]*)\[([ a-zA-Z_0-9]+)\]''', r'''\1`\2`''', s, flags=re.DOTALL|re.IGNORECASE)
#replace all other []
s = s.replace('[','')
s = s.replace(']','')
#replace dbo. and add ` for table name
s = re.sub(r'''dbo.([a-zA-Z0-9_]+) ''', r'''`\1`''',s, flags=re.DOTALL|re.IGNORECASE)
s = s + ' ENGINE=InnoDB DEFAULT CHARSET=latin1;\n\n'
list_create_table[i] = s
else:
print "!!!not match!!!"
return list_create_table
def write_file(file_path,str):
f = open(file_path,'w')
f.writelines(str)
f.close
return
def data_type_conversion(list_create_table):
for i in range(len(list_create_table)):
s = list_create_table[i]
s = s.replace(' image ',' longblob ');
s = s.replace(' money ',' decimal (19,4) ');
#s = s.replace(' numeric ',' decimal ');
list_create_table[i] = s
return list_create_table
def main():
file_path = 'E:\mssql_scriptsql'
f = open(file_path,'r')
script_text = f.read()
f.close
list_create_table = make_list_create_table(script_text)
list_create_table = convert_create_table_for_mysql(list_create_table)
list_create_table = data_type_conversion(list_create_table)
write_file('e:\out.sql',list_create_table)
return
if __name__ == '__main__':
main()