python 数据库操作
2023-09-14 08:59:42 时间
1 import MySQLdb as my
2 import pyodbc as ms
3 import os
4
5 # create sql sentence from a columns 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 files 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 theres 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 columns 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 files 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 theres 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
![复制代码](http://common.cnblogs.com/images/copycode.gif)
相关文章
- Python获取时间戳_python爬虫时间戳
- python人工智能学习笔记_[Python] 人工智能与自然语言处理学习笔记(1)[通俗易懂]
- 【说站】python中有哪些邮件模块
- python zipfile_Python 学习入门(16)—— zipfile
- python抛出异常和捕获异常_Python异常
- Python抛出异常_python抛出异常的作用
- 正则表达式Python_python正则表达式匹配字符串
- 【测试开发】python系列教程:Python 运算符
- 利用Python操作Mysql数据库
- python-Python与MongoDB数据库-使用Python执行MongoDB查询(三)
- python-Python与MongoDB数据库-处理MongoDB查询结果
- 详解MongoDB安装使用并实现Python操作数据库的步骤
- 搞定!Linux下快速设置Python环境变量(linux设置python环境变量)
- Python操作Redis实现数据持久化(python操作redis)
- 一步步学习:利用Python连接MySQL数据库(python连接mysql数据库)
- 使用Python连接SQL Server数据库(python连接sqlserver)
- Python在连接MSSQL数据库中的应用(python连mssql)
- Python在MSSQL中的应用实践(python与mssql)
- Python开发MySQL数据库,快速操作实现的mysqldb模块(mysql_db模块)
- python访问纯真IP数据库的代码
- python操作MySQL数据库的方法分享
- python数据库操作常用功能使用详解(创建表/插入数据/获取数据)