python连接MySQL数据库脚本

代码如下（示例）：

# -*- coding: UTF-8 -*-
import MySQLdb
import traceback
import time, logging, json, datetime, csv, cStringIO, codecs, decimal, re
from DBUtils.PooledDB import PooledDB
g_dbIp = '127.0.0.1'
g_user = 'root'
g_password = '123456'
g_dbName = 'XXX'


class CJsonEncoder(json.JSONEncoder):

    def default(self, obj):
        if isinstance(obj, datetime.datetime):
            return obj.strftime('%Y-%m-%d %H:%M:%S')
        elif isinstance(obj, datetime.date):
            return obj.strftime('%Y-%m-%d')
        elif isinstance(obj, decimal.Decimal):
            return int(obj)
        else:
            return json.JSONEncoder.default(self, obj)


g__pool = PooledDB(creator=MySQLdb, mincached=3, maxcached=20, host=g_dbIp, user=g_user, passwd=g_password, maxconnections=20, blocking=True, db=g_dbName, use_unicode=True, charset='utf8')

class MysqlDbo(object):
    def write(self, sql):
        conn = g__pool.connection()
        cursor = conn.cursor()
        try:
            cursor.execute(sql)
            conn.commit()
        except:
            conn.rollback()

        cursor.close()
        conn.close()


    def read(self, sql):
        conn = g__pool.connection()
        cursor = conn.cursor()
        data = '[]'
        try:
            rs = cursor.execute(sql)
            rows = cursor.fetchall()
            data = json.dumps(rows, cls=CJsonEncoder)
        except:
            print("read fail")

        cursor.close()
        conn.close()
        return data


if __name__ == '__main__':
    dbo = MysqlDbo()
    a = dbo.read('select protocol,count(*) as count from traffics where protocol in ("ARP","ICMP", "SYN", "PAYLOAD") group by protocol having count>0')