python2学习笔记之mysql连接，中文乱码的解决和文件api的使用

__author__ = 'Administrator'
# encoding=utf-8

#http://www.w3cschool.cc/python/python-mysql.html
import  MySQLdb

db=MySQLdb.connect(host="localhost",user="root",passwd="mysql0774mysql",db="itcast",charset="utf8")
#db=MySQLdb.connect("localhost","root","mysql0774mysql","itcast") #存在中文乱码
cursor=db.cursor()
cursor.execute("select VERSION()")
data=cursor.fetchone()
print("database version:%s" % data)

#创建数据库表
cursor.execute("drop table if EXISTS student")
sql="""create table student(first_name VARCHAR(20) NOT NULL,
last_name VARCHAR(20),age int,sex CHAR(1),income FLOAT )"""
cursor.execute(sql)

# SQL 插入语句
sql = """INSERT INTO student(first_name,
         last_name, AGE, SEX, income)
         VALUES ('Mac', 'Mohan中文', 20, 'M', 2000)"""
try:
   # 执行sql语句
   cursor.execute(sql)
   cursor.execute(sql)
   cursor.execute(sql)
   # 提交到数据库执行
   db.commit()
except:
   # Rollback in case there is any error
   db.rollback()

# SQL 更新语句
sql = "UPDATE student SET AGE = AGE + 1 WHERE SEX = '%c'" % ('M')
try:
   # 执行SQL语句
   cursor.execute(sql)
   # 提交到数据库执行
   db.commit()
except:
   # 发生错误时回滚
   db.rollback()

#获取数据
# SQL 查询语句
sql = "SELECT * FROM student \
       WHERE INCOME > '%d'" % (1000)
try:
   # 执行SQL语句
   cursor.execute(sql)
   # 获取所有记录列表
   results = cursor.fetchall()
   f=file("data.txt","w")
   for row in results:
       fname = row[0]
       lname = row[1]
       age = row[2]
       sex = row[3]
       income = row[4]
       print ("fname=%s,lname=%s,age=%d,sex=%s,income=%d" % (fname, lname, age, sex, income ))
        #f.write(lname.encode('utf-8'))
       f.write(fname.encode('utf-8'))
       f.write(lname.encode('utf-8'))
       f.write(str(age))
       f.write(sex.encode("utf-8"))
       f.write(str(income))
   f.close()

except:
   print "Error: unable to fecth data"


db.close()