python操作mysql数据库的相关操作实例
|
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
41
42
43
44
45
46
47
48
49
50
51
52
53
54
55
56
57
58
59
60
61
62
63
64
65
66
67
68
69
70
71
72
73
74
75
76
77
78
79
80
81
82
83
84
85
86
87
88
89
90
91
92
93
94
95
96
97
98
99
100
101
102
103
104
105
106
107
108
109
110
111
112
113
114
115
116
117
118
119
120
121
122
123
124
125
126
127
128
129
130
131
132
133
134
135
136
137
138
139
140
141
142
143
144
145
146
147
148
149
150
151
152
153
154
155
156
157
158
159
160
161
162
163
164
165
166
167
168
169
170
171
|
# -*- coding: utf-8 -*-#python operate mysql databaseimport MySQLdb #数据库名称DATABASE_NAME= ''#host = 'localhost' or '172.0.0.1'HOST= ''#端口号PORT= ''#用户名称USER_NAME= ''#数据库密码PASSWORD= ''#数据库编码CHAR_SET= '' #初始化参数def init(): global DATABASE_NAME DATABASE_NAME= 'test' global HOST HOST= 'localhost' global PORT PORT= '3306' global USER_NAME USER_NAME= 'root' global PASSWORD PASSWORD= 'root' global CHAR_SET CHAR_SET= 'utf8' #获取数据库连接def get_conn(): init() return MySQLdb.connect(host= HOST, user= USER_NAME, passwd= PASSWORD, db= DATABASE_NAME, charset= CHAR_SET) #获取cursordef get_cursor(conn): return conn.cursor() #关闭连接def conn_close(conn): if conn != None: conn.close() #关闭cursordef cursor_close(cursor): if cursor != None: cursor.close() #关闭所有def close(cursor, conn): cursor_close(cursor) conn_close(conn) #创建表def create_table(): sql= ''' CREATE TABLE `student` ( `id` int(11) NOT NULL, `name` varchar(20) NOT NULL, `age` int(11) DEFAULT NULL, PRIMARY KEY (`id`), UNIQUE KEY `name` (`name`) ) ENGINE=InnoDB DEFAULT CHARSET=utf8 ''' conn= get_conn() cursor= get_cursor(conn) result= cursor.execute(sql) conn.commit() close(cursor, conn) return result #查询表信息def query_table(table_name): if table_name != '': sql= 'select * from ' + table_name conn= get_conn() cursor= get_cursor(conn) result= cursor.execute(sql) for rowin cursor.fetchall(): print(row) #for r in row: #循环每一条数据 #print(r) close(cursor, conn) else: print('table name is empty!') #插入数据def insert_table(): sql= 'insert into student(id, name, age) values(%s, %s, %s)' params= ('1','Hongten_a','21') conn= get_conn() cursor= get_cursor(conn) result= cursor.execute(sql, params) conn.commit() close(cursor, conn) return result #更新数据def update_table(): sql= 'update student set name = %s where id = 1' params= ('HONGTEN') conn= get_conn() cursor= get_cursor(conn) result= cursor.execute(sql, params) conn.commit() close(cursor, conn) return result #删除数据def delete_data(): sql= 'delete from student where id = %s' params= ('1') conn= get_conn() cursor= get_cursor(conn) result= cursor.execute(sql, params) conn.commit() close(cursor, conn) return result #数据库连接信息 def print_info(): print('数据库连接信息:' + DATABASE_NAME+ HOST+ PORT+ USER_NAME+ PASSWORD+ CHAR_SET) #打印出数据库中表情况def show_databases(): sql= 'show databases' conn= get_conn() cursor= get_cursor(conn) result= cursor.execute(sql) for rowin cursor.fetchall(): print(row) #数据库中表情况def show_tables(): sql= 'show tables' conn= get_conn() cursor= get_cursor(conn) result= cursor.execute(sql) for rowin cursor.fetchall(): print(row) def main(): show_tables() #创建表 result= create_table() print(result) #查询表 query_table('student') #插入数据 print(insert_table()) print('插入数据后....') query_table('student') #更新数据 print(update_table()) print('更新数据后....') query_table('student') #删除数据 delete_data() print('删除数据后....') query_table('student') print_info() #数据库中表情况 show_tables() if __name__== '__main__': main() |
