python开发_python操作mysql数据库
如果你还没有准备好开发环境，你不妨花上一小点时间去看看：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()
你还可以参考：
