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