1 import MySQLdb as my
2 import pyodbc as ms
3 import os
4
5 # create sql sentence from a column's definitions
6 def createSql(line):
7 result = ''
8 column_def_list = line.split(',')
9 result = column_def_list[0] # column name
10
11 # column type
12 if column_def_list[2] == 'utf8_general_ci':
13 result = result + ' n' + column_def_list[1]
14 else:
15 if column_def_list[1].lower().find('int') > -1:
16 result = result + ' int'
17 else:
18 result = result + ' ' + column_def_list[1]
19
20 # column can be null or not
21 if column_def_list[3].find('NO') > -1:
22 result = result + ' NOT NULL'
23 else:
24 result = result + ' NULL'
25
26 # is primary key
27 if column_def_list[4].find('PRI') > -1:
28 result = result + ' PRIMARY KEY'
29 # is auto increment
30 if column_def_list[6].find('auto_increment') > -1:
31 result = result + ' IDENTITY(1,1)'
32
33 result = result + ', '
34
35 return result
36
37 # transfer db and data in the db
38 try:
39 config_file_path = '''config/db.txt'''
40 config_tables_path = '''config/tables.txt'''
41 isExists = os.path.isfile(config_file_path) # check if file's there
42
43 if not isExists:
44 # if config file does not exist, throw an exception.
45 raise Exception,'You need to config databases where data from and to.'
46
47 # read the config file
48 config_file = open(config_file_path,'r') #from
49 config_info = config_file.readlines()
50 from_list = config_info[0].split(':')
51 from_db_info = from_list[1].split(',')
52
53 to_list = config_info[1] #to
54 to_db_info = to_list.split(':')[1]
55 #to_db_info = to_db_info[0:len(to_db_info)-1]
56
57 # check if there's enough info
58 if len(from_db_info)< 4:
59 raise Exception, 'No enough parameters in db which data will from'
60
61 from_server = from_db_info[0].split('=')[1] # server
62 from_user = from_db_info[1].split('=')[1] # user
63 from_pwd = from_db_info[2].split('=')[1] # pwd
64 from_db = from_db_info[3].split('=')[1] # pwd
65 from_db = from_db[0:len(from_db)-1]
66
67 # connect to db which data is from
68 db = my.connect(from_server,from_user,from_pwd,from_db)
69
70 cursor = db.cursor()
71 cursor.execute("show tables")
72 results=cursor.fetchall()
73
74 tables_file = open(config_tables_path,'w')
75
76 #write the table definitions into a txt file
77 for row in results:
78 tb_name = row[0]
79 tables_file.write('table name: ' + tb_name + '\r\n')
80 cursor.execute("SHOW FULL COLUMNS FROM " + tb_name)
81 defs = cursor.fetchall()
82 for el in defs:
83 col_def = ''
84 count_col = 0
85 for col in el:
86 temp_el = str(col)
87 if count_col == 8:
88 col_def = col_def + temp_el
89
90 col_def = col_def + temp_el + ','
91 count_col = count_col + 1
92
93 # write the table info into a txt file
94 tables_file.write(col_def + '\r\n')
95
96 tables_file.close()
97
98 tables_file = open(config_tables_path,'r')
99 print to_db_info
100 #connect to ms sqlserver
101 ms_conn = ms.connect(to_db_info)
102 ms_cursor = ms_conn.cursor()
103 index = 0
104 create_sql = ''
105 current_tb_name = ''
106 for line in tables_file.xreadlines():
107 if line.find('table') > -1:
108 current_tb_name = line.split(':')[1]
109 if(len(create_sql) > 0):
110 create_sql = create_sql + ')'
111 print create_sql #test
112 ms_cursor.execute(create_sql)
113 create_sql = ''
114
115 create_sql = 'create table ' + current_tb_name + '('
116
117 else:
118 create_sql = create_sql + createSql(line)
119
120 # execute last create sentence
121 if len(create_sql) > 0:
122 ms_cursor.execute(create_sql)
123
124 print '==================DONE================'
125
126 except Exception, diag:
127 print diag
128 '''exception handling'''
129 finally:
130 if(db != None):
131 db.close()
132 if(ms_conn != None):
133 ms_conn.close()
134 if(config_file != None):
135 config_file.close()
136 if(tables_file != None):
137 tables_file.close()
138
139
140 #def dimension():
141 # test=[[1,1],[2,2],[3,3]]
142 # return len(test)
143
144 #print dimension()
145
