#!/usr/local/python/bin

coding=utf-8
'''Implements a simple database interface
Example 0: Create connection:

# Set auto commit to false
db = DB(False, host = 'x', user = 'x', passwd = 'x', db = 'x')


Example 1: Select SQL
a. Select the first two rows from ip table:

# normal select
db.select('select * from ip limit 2')
# add a where condition:
db.select('select * from ip where name != %s limit 2', ('0'))


b. Select all results but get only the first two:

db.execute('select * from ip')
# get dict rows
db.get_rows(2, is_dict = True)


Example 2: Insert/Replace SQL
a. Insert a new record into ip table:

db.insert('ip', {'address':'192.168.0.1', 'name': 'vm-xxx'})
db.commit()


b. Insert multi-records into ip table:

db.multi_insert('ip', ('address','name'), [('192.168.0.1', 'vm-xxx'),
    ('192.168.0.2', 'vm-yyy'), ('192.168.0.3', 'vm-zzz')])
db.commit()


Example 3: Update SQL
a. Update the address of row whose name is vm-xxx:

db.update('ip', {'address':'192.168.0.1'}, {'name': 'vm-xxx'})
db.commit()


Example 4: Delete SQL
a. Delete the row whose name is 'vm-xxx':

db.delete('ip', {'name': 'vm-xxx'})
db.commit()

'''
Can be 'Prototype', 'Development', 'Product'
status = 'Development'
author = 'tuantuan.lv <dangoakchan@foxmail.com>'
import sys
import MySQLdb
from pypet.common import log
class DB():
    '''A simple database query interface.'''
    def init(self, auto_commit, **kwargs):
        if 'charset' not in kwargs:
            kwargs['charset'] = 'utf8'

    self.conn = MySQLdb.connect(**kwargs)
    self.cursor = self.conn.cursor()
    self.autocommit(auto_commit)

def execute(self, sql, args = None):
    return self.cursor.execute(sql, args)

def executemany(self, sql, args):
    '''Execute a multi-row query.'''
    return self.cursor.executemany(sql, args)

def select(self, sql, args = None):
    self.execute(sql, args)
    return self.get_rows()

def insert(self, table, column_dict):
    keys = '`,`'.join(column_dict.keys())
    values = column_dict.values()
    placeholder = ','.join([ '%s' for v in column_dict.values() ])
    ins_sql = 'INSERT INTO %(table)s (`%(keys)s`) VALUES (%(placeholder)s)'

    return self.execute(ins_sql % locals(), values)

def multi_insert(self, sql, args):
    '''Execute a multi-row insert, the same as executemany'''
    return self.cursor.executemany(sql, args)

def replace(self, table, column_dict):
    keys = '`,`'.join(column_dict.keys())
    values = column_dict.values()
    placeholder = ','.join([ '%s' for v in column_dict.values() ])
    repl_sql = 'REPLACE INTO %(table)s (`%(keys)s`) VALUES (%(placeholder)s)'

    return self.execute(repl_sql % locals(), values)

def update(self, table, column_dict, cond_dict):
    set_stmt = ','.join([ '%s=%%s' % k for k in column_dict.keys() ])
    cond_stmt = ','.join([ '%s=%%s' % k for k in cond_dict.keys() ])
    args = column_dict.values() + cond_dict.values()
    upd_sql = 'UPDATE %(table)s set %(set_stmt)s where %(cond_stmt)s'

    return self.execute(upd_sql % locals(), args)

def delete(self, table, cond_dict):
    cond_stmt = ','.join([ '%s=%%s' % k for k in cond_dict.keys() ])
    del_sql = 'DELETE FROM %(table)s where %(cond_stmt)s'

    return self.execute(del_sql % locals(), cond_dict.values())

def get_rows(self, size = None, is_dict = False):
    if size is None:
        rows = self.cursor.fetchall()
    else:
        rows = self.cursor.fetchmany(size)

    if rows is None:
        rows = []

    if is_dict:
        dict_rows = []
        dict_keys = [ r[0] for r in self.cursor.description ]

        for row in rows:
            print row, dict_keys
            print zip(dict_keys, row)
            dict_rows.append(dict(zip(dict_keys, row)))

        rows = dict_rows

    return rows

def get_rows_num(self):
    return self.cursor.rowcount

def get_mysql_version(self):
    MySQLdb.get_client_info()

def autocommit(self, flag):
    self.conn.autocommit(flag)

def commit(self):
    '''Commits the current transaction.'''
    self.conn.commit()

def __del__(self):
    #self.commit()
    self.close()

def close(self):
    self.cursor.close()
    self.conn.close()


vim: set expandtab smarttab shiftwidth=4 tabstop=4:</pre>