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
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
欢迎加群互相学习,共同进步。QQ群:iOS: 58099570 | Android: 330987132 | Go:217696290 | Python:336880185 | 做人要厚道,转载请注明出处!http://www.cnblogs.com/sunshine-anycall/archive/2010/05/23/1742355.html