python mysql数据库的操作

#-*- coding:utf-8 -*-import sysimport mysql.connectorimport chardetreload(sys)sys.setdefaultencoding('utf-8')#连接mysql数据库def testMySqlDatabase():    print 'mysql数据库'    conn = mysql.connector.connect(user='root', password='123456yu', database='test', use_unicode=True)    cursor = conn.cursor()    # 创建user表:    cursor.execute('create table user (id varchar(20) primary key, name varchar(20))')    # 插入一行记录，注意MySQL的占位符是%s:    cursor.execute('insert into user (id, name) values (%s, %s)', ['1', 'Michael'])    print cursor.rowcount    conn.commit()    cursor.close()    cursor = conn.cursor()    cursor.execute('select * from user where id = %s', ('1',))    values = cursor.fetchall()    print values,type(values)    cursor.close()    conn.close()#testMySqlDatabase()#测试新建一个表，执行数据库的插入操作def testCreatNewDatabase():    print '创建一个新的数据库，执行数据库的插入操作'    db = mysql.connector.connect(user='root', password='123456yu', database='newStudent', use_unicode=True)    cursor = db.cursor()    # 如果数据表已经存在使用 execute() 方法删除表。    #cursor.execute("DROP TABLE IF EXISTS EMPLOYEE")    '''创建数据库的语句'''    #employee : first_name, last_name, age, sex, income    sql = """CREATE TABLE EMPLOYEE (         id int unsigned not null auto_increment primary key,         FIRST_NAME  CHAR(20) NOT NULL,         LAST_NAME  CHAR(20),         AGE INT,         SEX CHAR(1),         INCOME FLOAT )"""    #cursor.execute(sql)    # 关闭数据库连接    #db.close()    '''普通的插入数据'''    sql = """INSERT INTO EMPLOYEE(FIRST_NAME,             LAST_NAME, AGE, SEX, INCOME)             VALUES ('Mac', 'Mohan', 20, 'M', 2000)"""    '''可以传值得插入数据'''    sql = "INSERT INTO EMPLOYEE(FIRST_NAME, \           LAST_NAME, AGE, SEX, INCOME) \           VALUES ('%s', '%s', '%d', '%c', '%d' )" % \          ('TOm', 'Tohan', 26, 'M', 8000)    '''传入参数的插入数据'''    first_name = 'johnny'    last_name = 'tiancai'    age = 27    seg = 'M'    income = 10000    sql = "INSERT INTO EMPLOYEE(FIRST_NAME, \               LAST_NAME, AGE, SEX, INCOME) \               VALUES ('%s', '%s', '%d', '%c', '%d' )" % \          (first_name, last_name, age, seg, income)    try:        # 执行sql语句        cursor.execute(sql)        # 提交到数据库执行        db.commit()    except:        # Rollback in case there is any error        db.rollback()    # 关闭数据库连接    db.close()#testCreatNewDatabase()#数据库查询操作def testSelectNewDatabase():    print "数据库查询操作"    db = mysql.connector.connect(host='localhost',port='3306',user='root',password='123456yu',                                 database='newStudent',use_unicode=True)    cursor = db.cursor()    # SQL 查询语句    sql = "SELECT * FROM EMPLOYEE \            WHERE sex = '%s'" % ('M')           # WHERE INCOME > '%d'" % (3000)    try:        cursor.execute(sql)        results = cursor.fetchall()        print results        print results[0][1]        for row in results:            fname = row[1]            #print  type(fname)            lname = row[2]            age = row[3]            sex = row[4]            income = row[5]            # 打印结果            print "fname=%s,lname=%s,age=%d,sex=%s,income=%d" % \                  (fname, lname, age, sex, income)    except:        print "Error: unable to fecth data"    db.close()testSelectNewDatabase()#执行数据库数据的更新def testUpdateNewDatabase():    print '数据的更新'    db = mysql.connector.connect(host='localhost',port='3306',user='root',password='123456yu',                                 database='newStudent',use_unicode=True)    cursor = db.cursor()    income = 8000    age = 34    sex = 'M'    sql = "UPDATE EMPLOYEE SET AGE = '%d',SEX = '%s'" % (age,sex) +"WHERE INCOME = '%s'" % income    try:        cursor.execute(sql)        db.commit()    except:        print 'Error: not update'        db.rollback()    db.close()#testUpdateNewDatabase()#对数据库的删除操作def testDeleteNewDatabase():    print '删除操作'    db = mysql.connector.connect(host='localhost',port='3306',user='root',password='123456yu',                                 database='newStudent',use_unicode=True)    cursor = db.cursor()    # SQL 删除语句    sql = "DELETE FROM EMPLOYEE WHERE AGE < '%d'" % (27)    try:        # 执行SQL语句        cursor.execute(sql)        # 提交修改        db.commit()    except:        # 发生错误时回滚        db.rollback()    # 关闭连接    db.close()#testDeleteNewDatabase()def testChineseZiFu():    print '中文占有多少字符'    str = '4月28日晚上21:50－21:57左右在靠着13栋公寓干道的车棚外丢失一辆亮黑+红色捷安特山地车经监控显示21:55:40一名背书包的男子取走了我的车，然后骑向一食堂或一期打卡点，因天很黑，没看清楚人那位同学应该是错把我的车误认你的了，但是能打开锁我就很纳闷，我的锁孔那没有遮盖一个月前我把车停在后门车道，随后消失有突然出现在13栋干道车棚外，很纳闷'    print type(str)    print chardet.detect(str)    a=1    print sys.getsizeof(a)    print len(str)*3#testChineseZiFu()