python mysql数据库的操作

#-*- coding:utf-8 -*-

import sys
import mysql.connector
import chardet


reload(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()