MySQL数据库操作学习(Python)

首先安装mysql数据库和python与mysql连接的工具mysqldb。注意修改编码时在my.cnf文件里mysqld标签下只需加入后面两行，default-character-set = utf8不需要加上，否则报错。过程参考：http://blog.csdn.net/zhyz_zheng/article/details/7986606  。Windows下安装MySQLdb模块参考：http://www.codegood.com/archives/129。以下为python连接数据库并做相关建表和插入操作的例子。

#-*- coding:utf-8 -*-
import sys
import MySQLdb
reload(sys)
sys.setdefaultencoding('utf-8')

def getdata():
	try:
		conn = MySQLdb.connect(host='localhost',user='root',passwd='123456',db='test',port=3306,charset='utf8')
		try:
			cur = conn.cursor()
			table = r"create table person(name varchar(10),uid int(5),pwd varchar(30))"
			cur.execute(table)
			data = r"insert into person values('蓝',8,'兰')"
			cur.execute(data)
			#操作完成后一定要commit()才会写入数据库
			conn.commit()
			cur.execute("desc mysql")
			allTable = cur.fetchall()
			sql = r"select * from person"
			cur.execute(sql)
			allPerson = cur.fetchall()
		finally:
			cur.close()
			conn.close()
	except Exception,e:
		print '数据库错误:',e
		return
	#print allTable
	for rec in allPerson:
		print rec[0],rec[1],rec[2]

if __name__=='__main__':
	getdata()

若要批量插入多条变量数据可以调用executemany(sql,param)操作

sql = r"insert into chuaizhe values(%s,%s,%s,%s,%s,%s,%s,%s,%s)"
param = ((name,fee,support,detail,tag,score,follow,chuai,office))
cur.execute(sql,param)
#批量插入数据
#param = ((name,fee,support,detail,tag,score,follow,chuai,office),(name2,fee2,support2,detail2,tag2,score2,follow2,chuai2,office2))
#cur.executemany(sql,param)
conn.commit()