python MySQLdb使用

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

import MySQLdb
import json
import codecs
# 这个自己改一下啊
from utils.JsonUtil import get_json_from_file

def byteify(input):
    """
    the string of json typed unicode to str in python
    This function coming from stack overflow
    :param input: {u'first_name': u'Guido', u'last_name': u'jack'}
    :return:      {'first_name': 'Guido', 'last_name': 'jack'}
    """
    if isinstance(input, dict):
        return {byteify(key): byteify(value)
                for key, value in input.iteritems()}
    elif isinstance(input, list):
        return [byteify(element) for element in input]
    elif isinstance(input, unicode):
        return input.encode('utf-8')
    else:
        return input

def get_json_from_file(filename):
    with open(filename) as jf:
        jsondata = json.load(jf)

    return byteify(jsondata)

class DbBase(object):
    def __init__(self, **kwargs):
        self.db_config_file = kwargs['db_config_file']
        self.config_db(self.db_config_file)

    def config_db(self, db_config_file):
        data = get_json_from_file(db_config_file)
        host = data['host']
        user = data['user']
        pwd = data['pwd']
        db = data['db']
        port = data['port']
        self.tb_audit_mobile = data['tb_audit_mobile']
        self.conn = MySQLdb.connect(host=host, port=port, user=user, passwd=pwd, db=db, charset="utf8", use_unicode=True)
        self.cursor = self.conn.cursor()

class DbAuditTestService(DbBase):
    def __init__(self, **kwargs):
        super(DbAuditTestService, self).__init__(**kwargs)

    def getAdTestURl(self, beg, end):
        sql = """select url, source from tb_name where create_date BETWEEN '%s' and '%s' """ % (beg, end)
        self.cursor.execute(sql)
        res = [row for row in self.cursor]
        return res

    def insert(self, lst_row):
    """batch insert, use ignore 避免索引唯一问题"""
        try:
            insert_sql = 'INSERT ignore INTO tb_ms_mobile_report_test (appid, source) VALUES (%s, %s)'
            self.cursor.executemany(insert_sql, lst_row)
            self.conn.commit()

        except MySQLdb.OperationalError as e:
            logger.info('%s' % e)
            self.cursor.close()
            self.conn.close()
            self.config_db(self.db_config_file)

    def update_ip_info(self, ip_info):
        """
        batch update 
            [[voilate_right_rate, ip]]
        :param ip_info:
        :return:
        """
        query = """
                update tb_ms_audit_ip_info set
                ip_right_rate=%s
                where submit_ip=%s """
        self.cursor.executemany(query, ip_info)
        self.conn.commit()

def insert_all():
"""批量操作的示例"""
    db_audit = DbAuditService(db_config_file='../config/mysql_police_audit.json')

    size = db_audit.count()
    db_audit_test = DbAuditTestService(db_config_file='../config/mysql_local_audit.json')
    batch_size = 2000
    for k in xrange(100000, size, batch_size):
        logger.info('query limit %s ~ %s' % (k, batch_size))
        lst_row = db_audit.query_limit(k, batch_size)
        logger.info('convert_rows ')
        lst_row = convert_rows(lst_row)
        db_audit_test.insert(lst_row)