python操作mysql数据库

参考：http://www.runoob.com/python/python-mysql.html

 插入时一定要有db.commit(),否则数据不会入库，代码也不报错。

# coding:utf-8


import pymysql

def creat_table():
    # 打开数据库连接（数据库ip,用户名,密码,数据库库名,字符编码）
    db = pymysql.connect("localhost", "root", "***", "***", charset='utf8' )

    #使用cursor()方法获取操作游标
    cursor = db.cursor()

    #使用execute()方法执行SQL语句
    cursor.execute("DROP TABLE IF EXISTS EMPLOYEE")

    #创建employee表语句
    sql = '''
        CREATE TABLE EMPLOYEE(
        FIRST_NAME CHAR(20) NOT NULL ,
        LAST_NAME CHAR(20),
        AGE INT,
        SEX CHAR(1),
        INCOME FLOAT 
        )
        '''

    #执行创建表语句
    cursor.execute(sql)

    # 关闭数据库连接
    db.close()

def inser_data():

    fname = 'Allen'
    lname = "johnson"
    age = 20
    sex = "M"
    income = 50000

    db = pymysql.connect("127.0.0.1", "root", "***", "***", charset='utf8')
    cursor = db.cursor()
    sql ="insert into employee (FIRST_NAME,LAST_NAME,AGE,SEX,INCOME) values ('%s', '%s', '%s', '%s', '%s')" % (fname, lname, age, sex, income)
    # 变量方式不能插入？？？？注意%s也要'%s'引起来
    # sql = "insert into employee values (%s,%s,%s,%s,%s)" % (fname,lname,age,sex,income)

    try:
        cursor.execute(sql)
        db.commit()
    except:
        db.rollback()
        print "插入数据出错DDD"
    db.close()

inser_data()

def query_data():

    db = pymysql.connect("localhost","root","***","****",charset='utf8')
    cursor = db.cursor()

    sql = "select * from employee where income > %s" % (1000)

    try:
        cursor.execute(sql)
        #获取所有符合查询的列表
        results = cursor.fetchall()
        # results = cursor.fetchone()
        #直接打印results返回一个元组，元组的元素为符合条件的一行数据，该数据也是一个元组
        print results

        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=%s,sex=%s,income=%s" % 
             (fname, lname, age, sex, income )
    except:
        print 'error'

    db.close()

# query_data()