python操作mysql数据库
python操作mysql数据库,查询出来数据库的值,并保存到文件中:
1 #-*-coding:utf-8-*- 2 import sys 3 import MySQLdb #引入mysql模块 4 reload(sys) 5 sys.setdefaultencoding('utf-8') #这两段话是改变python的编码 6 conn = MySQLdb.connect(user='root',passwd='root',host='127.0.0.1',charset='utf8') #连接数据库 7 conn.select_db('huang') #选择数据库 8 cur = conn.cursor() #查询出来的数据保存在一个cursor中 9 cur.execute("insert into user(username,passwd) values('kk','kk')") #插入一条数据 10 s = cur.execute('select * from user') #查询 11 file = open('1.txt','r+') #打开一个文件 12 desc = cur.description #cursor的头部 13 username = str(desc[0][0]) 14 passwd = str(desc[1][0]) 15 id = str(desc[2][0]) 16 length = len(username) 17 file.write(username+' '*(15-length)) 18 length = len(passwd) 19 file.write(passwd+' '*(15-length)) 20 length = len(id) 21 file.write(id+' '*(15-length)+'\n') #表头部分 22 for n in cur.fetchmany(s): #取出来查询出来的值 23 for x in n: 24 x = str(x) 25 print x 26 length = len(x) 27 file.write(str(x)+' '*(15-length)) 28 file.write('\n') 29 file.close() #关闭 30 conn.commit() 31 cur.close() 32 conn.close()
数据库中的表:
1 /* 2 Navicat MySQL Data Transfer 3 4 Source Server : localhost 5 Source Server Version : 50067 6 Source Host : localhost:3306 7 Source Database : huang 8 9 Target Server Type : MYSQL 10 Target Server Version : 50067 11 File Encoding : 65001 12 13 Date: 2014-04-13 11:34:22 14 */ 15 16 SET FOREIGN_KEY_CHECKS=0; 17 18 -- ---------------------------- 19 -- Table structure for `user` 20 -- ---------------------------- 21 DROP TABLE IF EXISTS `user`; 22 CREATE TABLE `user` ( 23 `username` varchar(50) default NULL, 24 `passwd` varchar(50) default NULL, 25 `id` int(15) NOT NULL auto_increment, 26 PRIMARY KEY (`id`) 27 ) ENGINE=InnoDB AUTO_INCREMENT=71 DEFAULT CHARSET=utf8; 28 29 -- ---------------------------- 30 -- Records of user 31 -- ---------------------------- 32 INSERT INTO `user` VALUES ('admin', 'admin', '1'); 33 INSERT INTO `user` VALUES ('huangteng', 'huangteng', '2'); 34 INSERT INTO `user` VALUES ('kk', 'kk', '44'); 35 INSERT INTO `user` VALUES ('荒唐', 'kk', '45'); 36 INSERT INTO `user` VALUES ('kk', 'kk', '46');
查询出来写到文件中的结果:
username passwd id
admin admin 1
huangteng huangteng 2
kk kk 44
荒唐 kk 45
kk kk 46
第一次做练习,感觉python写程序真的好厉害啊,比Java简单,但是实现的功能也不比Java少