machine_learn/util/mysqlutil.py

#!/usr/bin/python
# -*- coding:utf-8 -*-

import sys
import os

sys.path.append(os.path.abspath('..'))
from util.config import config
import pymysql
from sqlalchemy import create_engine


class MysqlConfig(object):
    HOST = config.get('mysql', 'host')
    USER = config.get('mysql', 'user')
    PASSWORD = config.get('mysql', 'password')
    DB = config.get('mysql', 'db')


engine = create_engine('mysql+pymysql://{}:{}@{}/{}?charset=utf8'.format(
    MysqlConfig.USER,
    MysqlConfig.PASSWORD,
    MysqlConfig.HOST,
    MysqlConfig.DB,
))


class Mysql(object):

    def __init__(self):
        # 数据库构造函数，从连接池中取出连接，并生成操作游标
        # self._conn = Mysql.__GetConnect()
        self.host = MysqlConfig.HOST
        self.user = MysqlConfig.USER
        self.pwd = MysqlConfig.PASSWORD
        self.db = MysqlConfig.DB

    __pool = None

    # @staticmethod
    def __GetConnect(self):
        """
        @summary: 静态方法，从连接池中取出连接
        @return MySQLdb.connection
        """
        if not self.db:
            raise (NameError, "没有设置数据库信息")
        self.conn = pymysql.connect(host=self.host, user=self.user, password=self.pwd, database=self.db)
        db= self.conn
        if not db:
            raise (NameError, "连接数据库失败")
        else:
            return db

    def insert_batch(self, sql, data):
        db = self.__GetConnect()
        cur = db.cursor()
        try:
            cur.executemany(sql, data)
            db.commit()
        except Exception as e:
            db.rollback()
            print(e)
        finally:
            cur.close()
            db.close()

    def select_list(self, sql):
        db = self.__GetConnect()
        cur = db.cursor(cursor=pymysql.cursors.DictCursor)
        results = []
        try:
            cur.execute(sql)
            results = cur.fetchall()
            db.commit()
            # print(results)
        except Exception as e:
            print(e)
        finally:
            cur.close()
            db.close()

        return results


if __name__ == '__main__':
    sql = 'select name from `ai-callcenter-dev`.callcenter_ai_terminology limit 10'
    mysql = Mysql()
    result = mysql.select_list(sql)
    for item in result:
        print(item['name'])