python 数据库操作

  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