python操作mysql数据库—坑吭

一、原始代码

#coding=utf-8

import MySQLdb
from cProfile import Profile


class MySQLUtils():
    def __init__(self):
        self.connect = MySQLdb.connect(host="192.168.111.1", port=3306, user="root", passwd="root", db="test", charset='utf8')
        self.cursor = self.connect.cursor()
        self.connect.autocommit(on=True)

    def finish(self):
        self.connect.close()

    def execute(self,sql,values):
        self.cursor.execute(sql,values)

    def fetch_one(self):
        return self.cursor.fetchone()


    def fetch_all(self):
        return self.cursor.fetchall()


def mysql_execute(sql,values):
    dbutils = MySQLUtils()
    dbutils.execute(sql,values)
    all_result = dbutils.fetch_all()
    dbutils.finish()
    return all_result

sql = 'insert into scan_domain(top_level_domain,domain) values(%s,%s)'

def execute_test():
    for i in range(0, 100000):
        mysql_execute(sql, ['aaa.com', 'hao.aaa.com'])

prof = Profile()
prof.enable()
execute_test()
prof.create_stats()
prof.print_stats()

 

二、改进一版，使用数据库连接池

#coding=utf-8

import MySQLdb
from DBUtils.PooledDB import PooledDB
from cProfile import Profile


class MySQLUtils():

    __pool = None

    def __init__(self):
        self.connect = MySQLUtils.__get_connect()
        self.cursor = self.connect.cursor()

    @staticmethod
    def __get_connect():
        if MySQLUtils.__pool is None:
            MySQLUtils.__pool = PooledDB(creator=MySQLdb, mincached=1, maxcached=20,
                              host='127.0.0.1', port=3306, user='root', passwd='root',
                              db='test', use_unicode=False, charset='utf8')
        return MySQLUtils.__pool.connection()

    def finish(self):
        self.cursor.close()
        self.connect.close()
        MySQLUtils.__pool.close()

    def execute(self,sql,values=[]):
        count = self.cursor.execute(sql,values)
        self.connect.commit()
        if count:
            return list(self.fetch_all())

    def fetch_one(self):
        return self.cursor.fetchone()

    def fetch_all(self):
        return self.cursor.fetchall()



prof = Profile()
sql = 'insert into scan_domain(top_level_domain,domain) values(%s,%s)'
prof.enable()
dbutils = MySQLUtils()

def execute_test():
    for i in range(0,100000):
        result = dbutils.execute(sql=sql, values=['aaa.com', 'hao.aaa.com'])
execute_test()
dbutils.finish()
prof.create_stats()
prof.print_stats()

 

 

三、细节

#coding=utf-8

import MySQLdb
from DBUtils.PooledDB import PooledDB
from cProfile import Profile


class MySQLUtils():

    __pool = None

    def __init__(self):
        self.connect = MySQLUtils.__get_connect()
        self.cursor = self.connect.cursor()

    @staticmethod
    def __get_connect():
        if MySQLUtils.__pool is None:
            MySQLUtils.__pool = PooledDB(creator=MySQLdb, mincached=1, maxcached=20,
                              host='127.0.0.1', port=3306, user='root', passwd='root',
                              db='test', use_unicode=False, charset='utf8')
        return MySQLUtils.__pool.connection()

    def finish(self):
        self.connect.commit()
        self.cursor.close()
        self.connect.close()
        MySQLUtils.__pool.close()

    def execute(self,sql,values=[]):
        count = self.cursor.execute(sql,values)
        # self.connect.commit()
        if count:
            return list(self.fetch_all())

    def fetch_one(self):
        return self.cursor.fetchone()

    def fetch_all(self):
        return self.cursor.fetchall()



prof = Profile()
sql = 'insert into scan_domain(top_level_domain,domain) values(%s,%s)'
prof.enable()
dbutils = MySQLUtils()

def execute_test():
    for i in range(0,100000):
        result = dbutils.execute(sql=sql, values=['aaa.com', 'hao.aaa.com'])
execute_test()
dbutils.finish()
prof.create_stats()
prof.print_stats()