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

import MySQLdb

try:

        #mysql连接

        conn = MySQLdb.connect(host='localhost',user='root',passwd='password',port=3306,charset='utf8')

        #创建游标

        cur = conn.cursor()

        #创建数据库

        cur.execute('create database if not exists  pythondb')

        #切换到数据库

        conn.select_db('pythondb')   

        #创建表

        cur.execute("create table student(id int,name varchar(20),age int)")

        #插入单条数据

        cur.execute("insert into student values(9,'张三',28)")

        #插入多条数据

        cur.execute("insert into student values(2,'shijingjing08',21),(3,'shijingjing09',30),(4,'shijingjing10',12)")

        #更新数据

        cur.execute("update student set age=27 where name='shijingjing07' ")

        #删除数据

        cur.execute("delete from student where name='shijingjing10' ")

        #查找数据

        count = cur.execute("select * from student ")

        print "总条数"

        print count

        result = cur.fetchone()

        print "查找一条"

        print result

        results = cur.fetchmany(5)

        print "查找多条"

        for r in results:

                print r

        cur.scroll(0,mode='absolute')

        #游标定位到表中第一条数据

        results = cur.fetchall()

        print "查找所有数据，第二列的值"

        for r in results:

                print r[1]

        #在再次执行select查询返回结果集时，需要执行nextset()操作，移动到下一结果集，否则会报2014错误

        cur.nextset()

        #执行存储过程的两种方法

        #cur.execute("call get_student('shijingjing',20)")

        cur.callproc("get_student",('shijingjing',20))

        print "执行存储过程"

        data=cur.fetchall()

        for d in data:

                print d

        #关闭游标

        cur.close()

        #提交

        conn.commit()

        #关闭数据库连接

        conn.close()

except MySQLdb.Error,e:

        print "Mysql Error %d:%s"%(e.args[0],e.args[1]) 

delimiter $

create procedure get_student(in p_name varchar(20),in p_age int)

     begin

          select * from student where name like concat(p_name,'%') and age>p_age;

     end;

$