#-*- encoding:utf-8 -*-_
'''
Created on 2012-1-12
@author: xiaojay
'''
import MySQLdb
import MySQLdb.cursors

STORE_RESULT_MODE = 0
USE_RESULT_MODE = 1

CURSOR_MODE = 0
DICTCURSOR_MODE = 1
SSCURSOR_MODE = 2
SSDICTCURSOR_MODE = 3

FETCH_ONE = 0
FETCH_MANY = 1
FETCH_ALL = 2

class PyMysql:
    def __init__(self):
        self.conn = None
        pass

    def newConnection(self,host,user,passwd,defaultdb):
         """
          建立一个新连接，指定host、用户名、密码、默认数据库
          """
         self.conn = MySQLdb.Connect(host,user,passwd,defaultdb)
         if self.conn.open == False:
             raise None

    def closeConnnection(self):
         """
          关闭当前连接
          """
         self.conn.close()

    def query(self,sqltext,mode=STORE_RESULT_MODE):
         """
          作用：使用connection对象的query方法，并返回一个元组(影响行数(int),结果集(result))
          参数：sqltext：sql语句
               mode=STORE_RESULT_MODE（0） 表示返回store_result，mode=USESTORE_RESULT_MODE（1） 表示返回use_result
          返回：元组(影响行数(int),结果集(result)
          """
         if self.conn==None or self.conn.open==False :
             return -1
         self.conn.query(sqltext)
         if mode == 0 :
             result = self.conn.store_result()
         elif mode == 1 :
             result = self.conn.use_result()
         else :
             raise Exception("mode value is wrong.")
         return (self.conn.affected_rows(),result)

    def fetch_queryresult(self,result,maxrows=1,how=0,moreinfo=False):
         """
          参数:result： query后的结果集合
              maxrows： 返回的最大行数
              how： 以何种方式存储结果
               (0：tuple,1：dictionaries with columnname,2：dictionaries with table.columnname)
              moreinfo 表示是否获取更多额外信息（num_fields，num_rows,num_fields）
          返回：元组（数据集，附加信息（当moreinfo=False）或单一数据集（当moreinfo=True）
          """
         if result == None : return None
         dataset =  result.fetch_row(maxrows,how)
         if moreinfo is False :
             return dataset
         else:
             num_fields = result.num_fields()
             num_rows = result.num_rows()
             field_flags = result.field_flags()
             info = (num_fields,num_rows,field_flags)
             return (dataset,info)

    def execute(self,sqltext,args=None,mode=CURSOR_MODE,many=False):
         """
          作用：使用游标（cursor）的execute 执行query
          参数：sqltext： 表示sql语句
               args： sqltext的参数
               mode：以何种方式返回数据集
                  CURSOR_MODE = 0 ：use_result , tuple
                  DICTCURSOR_MODE = 1 ：  use_result, dict
                  SSCURSOR_MODE = 2 :  store_result, tuple
                  SSDICTCURSOR_MODE = 3 : store_result , dict
               many：是否执行多行操作（executemany）
          返回：元组（影响行数（int），游标（Cursor））
          """
         if mode == CURSOR_MODE :
             curclass = MySQLdb.cursors.Cursor
         elif mode == DICTCURSOR_MODE :
             curclass = MySQLdb.cursors.DictCursor
         elif mode == SSCURSOR_MODE :
             curclass = MySQLdb.cursors.SSCursor
         elif mode == SSDICTCURSOR_MODE :
             curclass = MySQLdb.cursors.SSDictCursor
         else :
             raise Exception("mode value is wrong")

         cur = self.conn.cursor(cursorclass=curclass)
         line = 0
         if many == False :
             if args == None :
                 line = cur.execute(sqltext)
             else :
                 line = cur.execute(sqltext,args)
         else:
             if args == None :
                 line = cur.executemany(sqltext)
             else :
                 line = cur.executemany(sqltext,args)
         return (line , cur )

    def fetch_executeresult(self,cursor,mode=FETCH_ONE,rows=1):
         """
          作用：提取cursor获取的数据集
          参数：cursor：游标
               mode：执行提取模式
                FETCH_ONE: 提取一个； FETCH_MANY :提取rows个 ；FETCH_ALL : 提取所有
               rows：提取行数
          返回：fetch数据集
          """
         if cursor == None :
             return
         if mode == FETCH_ONE :
             return cursor.fetchone()
         elif mode == FETCH_MANY :
             return cursor.fetchmany(rows)
         elif mode == FETCH_ALL :
             return cursor.fetchall()


if __name__=="__main__" :
    print help (PyMysql)

#-*- encoding:utf-8 -*-
import PyMysql

"""
authors 这张表很简单。
+--------------+-------------+------+-----+---------+----------------+
| Field        | Type        | Null | Key | Default | Extra          |
+--------------+-------------+------+-----+---------+----------------+
| author_id    | int(11)     | NO   | PRI | NULL    | auto_increment |
| author_last  | varchar(50) | YES  |     | NULL    |                |
| author_first | varchar(50) | YES  | MUL | NULL    |                |
| country      | varchar(50) | YES  |     | NULL    |                |
+--------------+-------------+------+-----+---------+----------------+
本文主要的所有操作都针对该表。
"""

def printAuthors(res,mode=0,lines=0):
    """
    格式化输出
    """
    print "*"*20, " lines: ",lines ," ","*"*20
    if mode==0  :
        for author_id , author_last , author_first , country in res :
            print "ID : %s , Author_last : %s , Author_First : %s , Country : %s" \
            % (author_id , author_last , author_first , country )
    else :
        for item in res :
            print "-----------"
            for key in item.keys():
                print key ," : ",item[key]


#建立连接
mysql = PyMysql.PyMysql()
mysql.newConnection(
        host="localhost",
        user="root",
        passwd="peterbbs",
        defaultdb="bookstore")
""
#定义sql语句
sqltext = "select * from authors order by author_id "
#调用query方法,得到result
lines , res = mysql.query(sqltext, mode=PyMysql.STORE_RESULT_MODE)
#提取数据
data = mysql.fetch_queryresult(res, maxrows=20, how=0, moreinfo=False)
#打印
printAuthors(data,0,lines)

#演示多行插入
sqltext = "insert into authors (author_last,author_first,country) values (%s,%s,%s)"
args = [('aaaaaa','bbbbbb','cccccc'),('dddddd','eeeeee','ffffff'),('gggggg','hhhhhh','iiiiii')]
lines ,cur = mysql.execute(sqltext,args,mode=PyMysql.DICTCURSOR_MODE,many=True)
print "*"*20, lines ,"行被插入 ","*"*20

sqltext = "select * from authors order by author_id "
#调用cursor.execute方法,得到result
lines ,cur = mysql.execute(sqltext,mode=PyMysql.DICTCURSOR_MODE)
#提取数据
data = mysql.fetch_executeresult(cur, mode=PyMysql.FETCH_MANY, rows=20)
#打印
printAuthors(data,1,lines)

#关闭连接
mysql.closeConnnection()