Exemple #1
0
class CVESQL:
    def __init__(self):
        self.host = mysql_host
        self.port = mysql_port
        self.username = mysql_username
        self.password = mysql_password
        self.dbname = mysql_db
        self.chrset = mysql_chrset
        self.conn = PooledDB(MySQLdb,
                             host=self.host,
                             user=self.username,
                             passwd=self.password,
                             db=self.dbname,
                             port=int(self.port),
                             charset=self.chrset).connection()

    def getbysql(self, sql, args):
        '''
                Get by sql
                :param sql:
                :param param:
                :return:
                '''
        try:
            cursor = self.conn.cursor()
            cursor.execute(sql, args)
            res = cursor.fetchall()
            cursor.close()
            return res
        except Exception as e:
            print e
            return "None"
        finally:
            cursor.close()

    def insertbysql(self, sql, args):
        '''
        :param sql:
        :param args:
        :return:
        '''

        try:
            cursor = self.conn.cursor()
            #import pdb;pdb.set_trace()
            cursor.execute(sql, args)
            print "Inserting data >>>", args
            self.conn.commit()
        except Exception as e:
            print "Error encountered >>> ", e
        finally:
            cursor.close()

    def __del__(self):
        self.conn.close()
Exemple #2
0
class heimdalldb(object):

    def __init__(self):
        '''
        Read db config
        '''
        config = ConfigParser.ConfigParser()
        config.read('./utils/dbcfg.conf')
        self.host = config.get("MYSQLDB", "host")
        self.port = config.get("MYSQLDB", "port")
        self.username = config.get("MYSQLDB", "username")
        self.password = config.get("MYSQLDB", "password")
        self.dbname = config.get("MYSQLDB", "dbname")
        self.chrset = config.get("MYSQLDB", "chrset")
        self.conn = PooledDB(MySQLdb,host=self.host,user=self.username,passwd=self.password,db=self.dbname,port=int(self.port),charset=self.chrset).connection()

    def insertbysql(self, sql):
        '''
        insert by sql
        '''
        try:
            cursor = self.conn.cursor()
            cursor.execute(sql)
            self.conn.commit()
        except Exception as e:
            return "None"
        finally:
            cursor.close()

    def getbysql(self, sql):
        '''
        Get by sql
        :param sql:
        :param param:
        :return:
        '''
        try:
            cursor = self.conn.cursor()
            cursor.execute(sql)
            res = cursor.fetchall()
            cursor.close()
            return res
        except Exception as e:
            return "None"
        finally:
            cursor.close()


    def __del__(self):
        self.conn.close()
Exemple #3
0
class MyPymysqlPool(object):
    def __init__(self,
                 dbhost=conf.get('mysql', 'DB_HOST'),
                 dbport=conf.getint('mysql', 'DB_PORT'),
                 dbname=conf.get('mysql', 'DB_NAME'),
                 dbuser=conf.get('mysql', 'DB_USER'),
                 dbpasswd=conf.get('mysql', 'DB_PASSWORD'),
                 charset='utf8',
                 maxconnections=20):
        """
        数据库构造函数,从连接池中取出连接,并生成操作游标
        :param dbhost: 数据库地址
        :param dbport: 数据库端口
        :param dbname: 数据库名称
        :param dbuser:  数据库账号
        :param dbpasswd: 数据库密码
        :param charset: 数据库字符集
        :param maxconnections:连接池通常允许的最大连接数,0或None表示任意数量的连接
        """
        # mincached 最少的空闲连接数,如果空闲连接数小于这个数,pool会创建一个新的连接,0表示启动时没有连接
        # maxcached 最大的空闲连接数,如果空闲连接数大于这个数,pool会关闭空闲连接,0或None表示无限制的池大小
        # use_unicode=False, cursorclass=DictCursor(返回字典格式)
        # ping:确定何时使用ping()检查连接(0=None=never,1=default=每当从池中获取时,2=创建游标时,4 =执行查询时,7=always)
        self.conn = PooledDB(creator=pymysql,
                             mincached=0,
                             maxcached=5,
                             maxconnections=maxconnections,
                             host=dbhost,
                             port=dbport,
                             user=dbuser,
                             passwd=dbpasswd,
                             db=dbname,
                             charset=charset,
                             ping=4).connection()
        self.cursor = self.conn.cursor()

    def close_conn(self):
        """
         关闭游标 ,关闭连接
        """
        # 关闭游标
        self.cursor.close()
        # 关闭连接
        self.conn.close()

    def commit_query(self, query):
        """
        提交查询语句
        :param query:  数据库查询语句
        :return: 返回执行行数
        """
        # self.conn.ping(reconnect=True)
        try:
            # 执行sql语句
            rows_num = self.cursor.execute(query)
            # 提交到数据库执行
            self.conn.commit()
            num = int(rows_num) if rows_num not in (None, 'NoneType', 'null',
                                                    'Null', '', ' ') else 0
            LOG.debug("The result rows count is : {}".format(num))
            return num
        except BaseException as e:
            LOG.error("The mysql {0} exec sql error: {1}".format(query, e))
            # 发生错误时回滚
            self.conn.rollback()
        finally:
            self.close_conn()

    def executed_one(self, query):
        """
        查询一条数据
        :param query: 数据库查询语句
        :return: 返回字符串,默认取第一列第一行
        """
        LOG.debug("The mysql exec :" + query)
        num = self.commit_query(query)
        result = ''
        if num > 0:
            result = self.cursor.fetchone()[0]
        LOG.debug("The result is : {}".format(result))
        return result

    def executed_all(self, query):
        """
        查询所有数据
        :param query: 数据库查询语句
        :return: 二维数组
        """
        LOG.debug("The mysql exec :" + query)
        num = self.commit_query(query)
        result = list()
        if num > 0:
            result = [list(fc) for fc in self.cursor.fetchall()]
        LOG.debug("The result is : {}".format(result))
        return result

    def executed_rows(self, query):
        """
        获取执行sql的总数
        :param query: 数据库查询语句
        :return: 返回执行行数
        """
        LOG.debug("The mysql exec :" + query)
        num = self.commit_query(query)
        LOG.debug("The result is :" + str(num))
        return num

    def executed_many(self, query, value_tuple):
        """
        批量执行 SQL语句,用于批量插入或批量更新
        :param query:  插入语句/更新语句
        :param value_tuple:  二维元组
        :return:
        """
        try:
            # 执行sql语句
            rows_num = self.cursor.executemany(query, value_tuple)
            # 提交到数据库执行
            self.conn.commit()
            num = int(rows_num) if rows_num else 0
            LOG.debug("The result rows count is : {}".format(num))
            return num
        except BaseException as e:
            LOG.error("The mysql {0} exec sql error: {1}".format(query, e))
            # 发生错误时回滚
            self.conn.rollback()

    def select(self, table, column='*', *condition):
        """
        查询所有数据
        :param table: 需要查询的表
        :param column: 需要查询的列,为空时查询所有, col1,col2
        :param condition: 查询条件
        :return: 返回数组,元素是字典
        """
        query_list = ["SELECT", column, "FROM", table, ";"]
        if len(condition) > 0:
            query_list.insert(-1, 'WHERE')
            query_list.insert(-1, ' AND '.join(condition))
        query = ' '.join(query_list)
        return self.executed_all(query)

    def select_join(self, table, column, equivalent, *condition):
        """
        查询有关联的表len(table.split(,)) = len(equivalent) + 1
        :param table: 需要join的表,txx1,txx2,txx3
        :param column: 需要查询的列名, txx1.col1,txx2.col2,...
        :param equivalent: on等式 txx1.col1 = tx2.col2,txx3.col1=txx4.clo3
        :param condition: 查询条件 txx.xxcol1="xx" and txx.xxcol2 = "xx"
        :return:  二维数组
        """
        tables, equivalents = table.split(','), equivalent.split(',')
        if len(tables) == (len(equivalents) + 1):
            join_sql = list()
            for i in range(len(equivalents)):
                if i == 0:
                    join_sql.extend([
                        tables[i], "JOIN ", tables[i + 1], "ON", equivalents[i]
                    ])
                else:
                    join_sql.extend(
                        ["JOIN ", tables[i + 1], "ON ", equivalents[i]])
            join_sql.append(';')
            query_list = ["SELECT", column, "FROM"]
            query_list.extend(join_sql)
            if len(condition) > 0:
                query_list.insert(-1, 'WHERE')
                query_list.insert(-1, ' AND '.join(condition))
            query = ' '.join(query_list)
        else:
            raise IndexError('table and equivalent the index not equality!')
        return self.executed_all(query)

    def select_col(self, table, column, *condition):
        """
        单独查询某列
        :param table: 需要查询的表
        :param column: 需要查询的列,只能查询一列
        :param condition: 查询条件
        :return:  返回数组,展示一列的查询结果
        """
        query_list = ["SELECT", column, "FROM", table, ";"]
        if len(condition) > 0:
            query_list.insert(-1, 'WHERE')
            query_list.insert(-1, ' AND '.join(condition))
        query = ' '.join(query_list)
        return [result[0] for result in self.executed_all(query)]

    def select_one(self, table, column, *condition):
        """
        单独查询某个值
        :param table: 需要查询的表
        :param column: 需要查询的列,只能查询一列
        :param condition: 查询条件
        :return: 返回对应字段值,多条数据时只取第一条
        """
        query_list = ["SELECT", column, "FROM", table, "LIMIT 1;"]
        if len(condition) > 0:
            query_list.insert(-1, 'WHERE')
            query_list.insert(-1, ' AND '.join(condition))
        query = ' '.join(query_list)
        return self.executed_one(query)

    def select_rows(self, table, column='*', *condition):
        """
        查询执行行数
        :param table: 需要查询的表
        :param column: 需要查询的列,为空时查询所有, col1,col2
        :param condition: 查询条件
        :return: 返回查询条数
        """
        query_list = ["SELECT", column, "FROM", table, ";"]
        if len(condition) > 0:
            query_list.insert(-1, 'WHERE')
            query_list.insert(-1, ' AND '.join(condition))
        query = ' '.join(query_list)
        return self.executed_rows(query)

    def insert(self, table, value_dict):
        """
        插入语句
        :param table:  需要插入的表
        :param value_dict: 插入信息 {"列名1":值1,"列名2":值2}
        :return: 返回执行条数
        """
        values = ','.join([QUOTATION(value) for value in value_dict.values()])
        clos = ','.join(value_dict.keys())
        query = ' '.join([
            "INSERT", "INTO ", table, "(", clos, ")", "VALUE", "(", values,
            ")", ";"
        ])
        return self.executed_rows(query)

    def insert_many(self, table, col_list, values_tuple):
        """
        插入多条语句  'insert into test (sdf,asd,aaa) VALUES ("%s","%s","%s")', ((1,2,3),(2,3,4),(3,4,5))
        :param table: 表名
        :param col_list: 列名,数组或元组 (sdf,asd,aaa)
        :param values_tuple: 插入值,二维元组,二级元组或数组长度必须和列名数组长度一致 ((1,2,3),(2,3,4),(3,4,5))
        :return:  执行行数
        """
        col_num = len(col_list)
        query = ' '.join([
            "INSERT", "INTO ", table, "(", ','.join(col_list), ")", "VALUE",
            "(", (',%s' * col_num).lstrip(','), ")", ";"
        ])
        LOG.debug("The mysql exec :" + query)
        values_list = list()
        for value in values_tuple:
            if col_num != len(value):
                raise IndexError(
                    'the index of col_list and the index of values_tuple is not equality!'
                )
            values_list.append(tuple(value))
        return self.executed_many(query, values_list)

    def update_many(self, table, col_list, values_tuple, col_condition_list):
        """
        更新多条语句,元组顺序一致,查询条件在 元组最后
        update user_tb set name=%s, sex=%s where id=%s and s_id=%s,
        (('小孙', '男', 2, 1),
        ('小白', '女', 3, 2),
        ('小猪', '男', 4, 1),
        ('小牛', '男', 5, 3),
        ('小唐', '女', 6, 2))
        :param table: 表名
        :param col_list: 列名,数组或元组 (sdf,asd,aaa)
        :param values_tuple: 插入值,二维元组,二级元组或数组长度必须和列名数组长度一致 ((1,2,3),(2,3,4),(3,4,5))
        :param col_condition_list: 查询条件列名
        :return:  执行行数
        """
        query_list = list()
        query_list.append("UPDATE")
        query_list.append(table)
        query_list.append("SET")
        query_list.append(','.join(
            ['{}=%s'.format(col_1) for col_1 in col_list]))
        if col_condition_list:
            query_list.append('WHERE')
            query_list.append(' AND '.join(
                ['{}=%s'.format(col_2) for col_2 in col_condition_list]))
        query_list.append(';')
        values_list = list()
        col = col_list
        col.extend(col_condition_list)
        col_num = len(col)
        for value in values_tuple:
            if col_num != len(value):
                raise IndexError(
                    'the index of col_list and the index of values_tuple is not equality!'
                )
            values_list.append(tuple(value))
        query = ' '.join(query_list)
        LOG.debug("The mysql exec :" + query)
        return self.executed_many(query, values_list)

    def insert_update_many(self, table, col_list, values_tuple):
        """
        插入或更新多条语句
        'insert into test_tbl (id,dr) values  (1,'2'),(2,'3'),...(x,'y') on duplicate key update dr=values(dr);
        这个语法和适合用在需要判断记录是否存在,不存在则插入存在则更新的记录
        :param table: 表名
        :param col_list: 列名,数组或元组 (sdf,asd,aaa)
        :param values_tuple: 插入值,二维元组,二级元组或数组长度必须和列名数组长度一致 ((1,2,3),(2,3,4),(3,4,5))
        :return:  执行行数
        """
        col_condition = ','.join(
            ['{0}=VALUES({0})'.format(t) for t in col_list])
        col_num = len(col_list)
        for value in values_tuple:
            if col_num != len(value):
                raise IndexError(
                    'the index of col_list and the index of values_tuple is not equality!'
                )
        query = [
            "INSERT", "INTO ", table, "(", ','.join(col_list), ")", "VALUE",
            ','.join(values_tuple), 'ON', 'DUPLICATE', 'KEY', 'UPDATE',
            col_condition, ";"
        ]
        self.executed_rows(' '.join(query))

    def update_case(self, table, col_list, values_tuple, col_condition,
                    condition_tuple, *condition):
        """
        更新数据库语句
        :param table: 需要更新的表
        :param col_list:  更新列名列表
        :param values_tuple:  更新值二维元组
        :param col_condition:  CASE列名
        :param condition_tuple:  CASE值,元组
        :param condition: 查询条件
        :return:  返回执行条数
        """
        query_list = ["UPDATE", table, "SET"]
        for values in values_tuple:
            if len(col_list) != len(values):
                raise IndexError(
                    'the index of col_list and the index of values_tuple is not equality!'
                )
        update_value = list()
        for i in range(len(col_list)):
            case_value = list()
            case_value.append('{0} = CASE {1}'.format(col_list[i],
                                                      col_condition))
            for value in values_tuple:
                case_value.append('WHEN {0} THEN {1}'.format(
                    col_list[i], value[i]))
            case_value.append('END')
            update_value.append(' '.join(case_value))
        query_list.append(','.join(update_value))
        query_list.append('WHERE')
        query_list.append(col_condition)
        query_list.append('IN {}'.format(condition_tuple))
        if len(condition) > 0:
            query_list.append('AND')
            query_list.append(' AND '.join(condition))
        query_list.append(';')
        query = ' '.join(query_list)
        return self.executed_rows(query)

    def update(self, table, value, *condition):
        """
        更新数据库语句
        :param table: 需要更新的表
        :param value:  更新值 列名1=值1,列名2=值2
        :param condition: 查询条件
        :return:  返回执行条数
        """
        query_list = ["UPDATE", table, "SET", value, ";"]
        if len(condition) > 0:
            query_list.insert(-1, 'WHERE')
            query_list.insert(-1, ' AND '.join(condition))
        query = ' '.join(query_list)
        return self.executed_rows(query)

    def update_dict(self, table, value_dict, *condition):
        """
        更新数据库语句
        :param table: 需要更新的表
        :param value_dict:  更新值 {"列名1":值1,"列名2":值2}
        :param condition: 查询条件
        :return:  返回执行条数
        """
        # 判断value是否需要添加引号
        value_list = [
            col + '=' + QUOTATION(value) for col, value in value_dict.items()
        ]
        value = ','.join(value_list)
        query_list = ["UPDATE", table, "SET", value, ";"]
        if len(condition) > 0:
            query_list.insert(-1, 'WHERE')
            query_list.insert(-1, ' AND '.join(condition))
        query = ' '.join(query_list)
        return self.executed_rows(query)

    def delete(self, table, condition):
        """
        删除语句
        :param table: 需要删除的表
        :param condition: 查询条件
        :return: 返回执行条数
        """
        query_list = ["DELETE", "FROM", table, "WHERE", condition, ";"]
        query = ' '.join(query_list)
        return self.executed_rows(query)

    def drop_database(self, database_name):
        """
        删除数据库
        :param database_name:  要删除的数据库名称
        :return: 返回执行条数
        """
        query_list = ['drop', 'database', database_name, ';']
        query = ' '.join(query_list)
        return self.executed_rows(query)
Exemple #4
0
class Connection(object):
    """A lightweight wrapper around MySQLdb DB-API connections.

    The main value we provide is wrapping rows in a dict/object so that
    columns can be accessed by name. Typical usage:

        db = database.Connection("localhost", "mydatabase")
        for article in db.query("SELECT * FROM articles"):
            print article.title

    Cursors are hidden by the implementation, but other than that, the methods
    are very similar to the DB-API.

    We explicitly set the timezone to UTC and the character encoding to
    UTF-8 on all connections to avoid time zone and encoding errors.
    """
    def __init__(self, host, database, user=None, password=None,
                 max_idle_time=7*3600):
        self.host = host
        self.database = database
        self.max_idle_time = max_idle_time

        args = dict(conv=CONVERSIONS, use_unicode=True, charset="utf8",
                    db=database, init_command='SET time_zone = "+0:00"',
                    sql_mode="TRADITIONAL")
        if user is not None:
            args["user"] = user
        if password is not None:
            args["passwd"] = password

        # We accept a path to a MySQL socket file or a host(:port) string
        if "/" in host:
            args["unix_socket"] = host
        else:
            self.socket = None
            pair = host.split(":")
            if len(pair) == 2:
                args["host"] = pair[0]
                args["port"] = int(pair[1])
            else:
                args["host"] = host
                args["port"] = 3306

        self._db = None
        self._db_args = args
        self._last_use_time = time.time()
        try:
            self.reconnect()
        except:
            logging.error("Cannot connect to MySQL on %s", self.host,
                          exc_info=True)

    def __del__(self):
        self.close()

    def close(self):
        """Closes this database connection."""
        if getattr(self, "_db", None) is not None:
            self._db.close()
            self._db = None

    def reconnect(self):
        """Closes the existing database connection and re-opens it."""
        self.close()
        #self._db = MySQLdb.connect(**self._db_args)
        self._db = PooledDB(creator=MySQLdb, **self._db_args).connection(0)
        #self._db.autocommit(True)

    def iter(self, query, *parameters):
        """Returns an iterator for the given query and parameters."""
        self._ensure_connected()
        cursor = MySQLdb.cursors.SSCursor(self._db)
        try:
            self._execute(cursor, query, parameters)
            column_names = [d[0] for d in cursor.description]
            for row in cursor:
                yield Row(zip(column_names, row))
        finally:
            cursor.close()

    def query(self, query, *parameters):
        """Returns a row list for the given query and parameters."""
        cursor = self._cursor()
        try:
            self._execute(cursor, query, parameters)
            column_names = [d[0] for d in cursor.description]
            return [Row(itertools.izip(column_names, row)) for row in cursor]
        finally:
            cursor.close()

    def get(self, query, *parameters):
        """Returns the first row returned for the given query."""
        rows = self.query(query, *parameters)
        if not rows:
            return None
        elif len(rows) > 1:
            raise Exception("Multiple rows returned for Database.get() query")
        else:
            return rows[0]

    def execute(self, query, *parameters):
        """Executes the given query, returning the lastrowid from the query."""
        cursor = self._cursor()
        try:
            self._execute(cursor, query, parameters)
            return cursor.lastrowid
        finally:
            cursor.close()

    def executemany(self, query, parameters):
        """Executes the given query against all the given param sequences.

        We return the lastrowid from the query.
        """
        cursor = self._cursor()
        try:
            cursor.executemany(query, parameters)
            return cursor.lastrowid
        finally:
            cursor.close()

    def _ensure_connected(self):
        # Mysql by default closes client connections that are idle for
        # 8 hours, but the client library does not report this fact until
        # you try to perform a query and it fails.  Protect against this
        # case by preemptively closing and reopening the connection
        # if it has been idle for too long (7 hours by default).
        if (self._db is None or
            (time.time() - self._last_use_time > self.max_idle_time)):
            self.reconnect()
        self._last_use_time = time.time()

    def _cursor(self):
        self._ensure_connected()
        return self._db.cursor()

    def _execute(self, cursor, query, parameters):
        try:
            cursor.execute(query, parameters)
            self._db.commit()
            return
        except OperationalError:
            logging.error("Error connecting to MySQL on %s", self.host)
            self.close()
            raise
class Db(object):
    def __init__(self, db_host, db_port, db_name, db_user, db_passwd, charset):
        self.conn = PooledDB(
            creator=pymysql,
            maxconnections=10,  # 连接池允许的最大连接数, 0和none表示没有限制
            mincached=2,  # 初始化时,连接池至少创建的空闲连接,0表示不创建
            maxcached=5,  # 连接池空闲的最多连接数,0和none表示不限制
            blocking=True,  # 连接池中如果没有可用共享连接后是否阻塞等待,True表示等待,反之则为报错弹出
            host= db_host,
            port=int(db_port),
            user= db_user,
            passwd= db_passwd,
            database= db_name,
            charset= charset
        ).connection()
        self.cursor = self.conn.cursor()

    # 新增数据
    def db_insert(self, tableName, dataDict):
        str_field = ""
        str_value = ""
        for filed,value in dataDict.items():
            str_field += "`" + filed + "`,"
            if (type(value) == type("kkk")):
                str_value += "'" + str(value) + "'" + ","
            elif(type(value) == type(123)):
                str_value += str(value) + ","
        sql = "INSERT INTO `"+ tableName +"`(" + str_field[:-1] + ")VALUE(" + str_value[:-1] + ")"
        self.cursor.execute(sql)
        self.conn.commit()
        get_rows = self.cursor.rowcount
        if get_rows == 1 :
            return True
        else:
            return False
        # print(str_value)

    # 更新数据
    def db_updata(self):
        pass;

    # 提取数据 return 元组
    def db_getdata(self, tableName, field):
        sql = "SELECT " + field + " FROM " + tableName;
        print(sql)
        self.cursor.execute(sql)
        data_tuple= self.cursor.fetchall()
        return data_tuple

    # 删除数据
    def db_deldata(self):
        pass;

    # 查询数据
    def db_selectdata(self):
        pass;

    # 回收数据库资源
    def __del__(self):
        self.cursor.close()
        self.conn.close()
Exemple #6
0
class OperationDB:
    _instance_lock = threading.Lock()

    def __init__(self, db_type=settings.DB_TYPE, db_name=None, env="dev"):
        self.db_type = db_type
        # if db_type == 'oracle':
        #     tns = cx_Oracle.makedsn(host,port,ins_name)
        #     self.db = cx_Oracle.connect(username,passwd,tns)
        #     cx_Oracle.connect()
        # else:
        #     # 创建数据库连接
        #     self.db = pymysql.connect(
        #         host = host,
        #         port = port,
        #         user = username,
        #         passwd = passwd,
        #         db = ins_name,
        #         charset = 'utf8',
        #         # 加上cursorclass之后就可以直接把字段名捞出来,和字段值组成键值对的形式
        #         cursorclass = pymysql.cursors.DictCursor
        #     )
        db_config = set_db_config(db_type, db_name, env)
        if self.db_type == "mysql":
            self.db = PooledDB(pymysql, 5, **db_config).connection()
        elif self.db_type == "sqlite":
            self.db = PooledDB(sqlite3, 5, **db_config).connection()
        elif self.db_type == "redis":
            pool = redis.ConnectionPool(**db_config)
            self.db = redis.Redis(connection_pool=pool)
        else:
            self.db = PooledDB(cx_Oracle, 5, **db_config).connection()
        # 创建游标
        if self.db_type != "redis":
            self.cur = self.db.cursor()

    def __new__(cls, *args, **kwargs):
        '''
        实现单例模式
        :param args:
        :param kwargs:
        :return:
        '''
        if not hasattr(cls, "_instance"):
            with cls._instance_lock:
                if not hasattr(cls, "_instance"):
                    cls._instance = super().__new__(cls)
        return cls._instance

    #获取一条数据
    def search_one(self, sql, param=None):
        if self.db_type == "sqlite":
            self.cur.execute(sql)
            res = self.cur.fetchone()
        elif self.db_type == "redis":
            #注意此时的sql并不是sql语句了,而是一个key值
            res = self.db.get(sql).decode("utf-8")
        else:
            self.cur.execute(sql, param)
            res = self.cur.fetchone()
        if res and (self.db_type == 'oracle' or self.db_type == "sqlite"):
            res = self.makeDictFactory(*res)
        return res

    #获取所有数据
    def search_all(self, sql, param=None):
        self.cur.execute(sql, param)
        res = self.cur.fetchall()
        if self.db_type == 'oracle':
            res = self.makeDictFactory(*res)
        return res

    #新增/删除/更新数据
    def sql_DML(self, sql, param=None):
        try:
            self.cur.execute(sql, param)
            self.db.commit()
        except:
            self.db.rollback()

    #将返回的结果和字段名映射成字典
    def makeDictFactory(self, *args):
        columnNames = [d[0] for d in self.cur.description]
        if isinstance(args[0], list):
            return [dict(z) for z in [zip(columnNames, data) for data in args]]
        return dict(zip(columnNames, args))

    #关闭游标和数据库连接
    def close(self):
        self.cur.close()
        self.db.close()
Exemple #7
0
class Connection(object):
    def __init__(self, database, host, user, mincached=5, maxcached=10, maxshared=0):
        self.database = database
        self.host = host
        self.user = user
        self.mincached = mincached
        self.maxcached = maxcached
        self.maxshared = maxshared

        args = dict(
            database=database,
            user=user,
            mincached=mincached,
            maxcached=maxcached,
            maxshared=maxshared,
        )

        self.max_idle_time = 7 * 3600
        self._db = None
        self._db_args = args
        self._last_use_time = time.time()

        try:
            self.reconnect()
        except:
            logging.error("Cannot connect to postgres on %s", self.host,
                exc_info=True)

    def __del__(self):
        self.close()

    def close(self):
        if getattr(self, "_db", None) is not None:
            self._db.close()
            self._db = None

    def reconnect(self):
        """Closes the existing database connection and re-opens it."""
        self.close()
        self._db = PooledDB(psycopg2, **self._db_args).connection()


    def _ensure_connected(self):
        # PostgreSQL by default closes client connections that are idle for
        # 8 hours, but the client library does not report this fact until
        # you try to perform a query and it fails.  Protect against this
        # case by preemptively closing and reopening the connection
        # if it has been idle for too long (7 hours by default).
        if (self._db is None or
            (time.time() - self._last_use_time > self.max_idle_time)):
            self.reconnect()
            self._last_use_time = time.time()

    def _cursor(self):
        self._ensure_connected()
        return self._db.cursor()


    def _execute(self, cursor, query, parameters):
        try:
            cursor.execute(query, parameters)
            self._db.commit()
            return
        except OperationalError:
            logging.error("Error connecting to postgres on %s", self.host)
            self.close()
            raise

    def execute_lastrowid(self, query, parameters):
        """Executes the given query, returning the lastrowid from the query."""
        cursor = self._cursor()
        try:
            self._execute(cursor, query, parameters)
            return cursor.lastrowid
        finally:
            cursor.close()

    def execute_rowcount(self, query, parameters):
        """Executes the given query, returning the rowcount from the query."""
        cursor = self._cursor()
        try:
            self._execute(cursor, query, parameters)
            return cursor.rowcount
        finally:
            cursor.close()


    def executemany(self, query, parameters):
        """Executes the given query against all the given param sequences.

        We return the lastrowid from the query.
        """
        return self.executemany_lastrowid(query, parameters)

    def executemany_lastrowid(self, query, parameters):
        """Executes the given query against all the given param sequences.
        We return the lastrowid from the query.
        """
        cursor = self._cursor()
        try:
            cursor.executemany(query, parameters)
            return cursor.lastrowid
        finally:
            cursor.close()

    def executemany_rowcount(self, query, parameters):
        """Executes the given query against all the given param sequences.

        We return the rowcount from the query.
        """
        cursor = self._cursor()
        try:
            cursor.executemany(query, parameters)
            return cursor.rowcount
        finally:
            cursor.close()

    def query(self, query, parameters=[]):
        """Returns a row list for the given query and parameters."""
        cursor = self._cursor()
        try:
            self._execute(cursor, query, parameters)
            column_names = [d[0] for d in cursor.description]
            return [Row(itertools.izip(column_names, row)) for row in cursor]
        finally:
            cursor.close()

    def get(self, query, parameters=[]):
        """Returns the first row returned for the given query."""
        rows = self.query(query, parameters)
        if not rows:
            return None
        elif len(rows) > 1:
            raise Exception("Multiple rows returned for Database.get() query")
        else:
            return rows[0]

    def execute(self, query, parameters=[]):
        """Executes the given query, returning the lastrowid from the query."""
        return self.execute_lastrowid(query, parameters)
Exemple #8
0
class Connection(object):
    """A lightweight wrapper around MySQLdb DB-API connections.

    The main value we provide is wrapping rows in a dict/object so that
    columns can be accessed by name. Typical usage:

        db = database.Connection("localhost", "mydatabase")
        for article in db.query("SELECT * FROM articles"):
            print article.title

    Cursors are hidden by the implementation, but other than that, the methods
    are very similar to the DB-API.

    We explicitly set the timezone to UTC and the character encoding to
    UTF-8 on all connections to avoid time zone and encoding errors.
    """
    def __init__(self,
                 host,
                 database,
                 user=None,
                 password=None,
                 max_idle_time=7 * 3600):
        self.host = host
        self.database = database
        self.max_idle_time = max_idle_time

        args = dict(conv=CONVERSIONS,
                    use_unicode=True,
                    charset="utf8",
                    db=database,
                    init_command='SET time_zone = "+0:00"',
                    sql_mode="TRADITIONAL")
        if user is not None:
            args["user"] = user
        if password is not None:
            args["passwd"] = password

        # We accept a path to a MySQL socket file or a host(:port) string
        if "/" in host:
            args["unix_socket"] = host
        else:
            self.socket = None
            pair = host.split(":")
            if len(pair) == 2:
                args["host"] = pair[0]
                args["port"] = int(pair[1])
            else:
                args["host"] = host
                args["port"] = 3306

        self._db = None
        self._db_args = args
        self._last_use_time = time.time()
        try:
            self.reconnect()
        except:
            logging.error("Cannot connect to MySQL on %s",
                          self.host,
                          exc_info=True)

    def __del__(self):
        self.close()

    def close(self):
        """Closes this database connection."""
        if getattr(self, "_db", None) is not None:
            self._db.close()
            self._db = None

    def reconnect(self):
        """Closes the existing database connection and re-opens it."""
        self.close()
        #self._db = MySQLdb.connect(**self._db_args)
        self._db = PooledDB(creator=MySQLdb, **self._db_args).connection(0)
        #self._db.autocommit(True)

    def iter(self, query, *parameters):
        """Returns an iterator for the given query and parameters."""
        self._ensure_connected()
        cursor = MySQLdb.cursors.SSCursor(self._db)
        try:
            self._execute(cursor, query, parameters)
            column_names = [d[0] for d in cursor.description]
            for row in cursor:
                yield Row(zip(column_names, row))
        finally:
            cursor.close()

    def query(self, query, *parameters):
        """Returns a row list for the given query and parameters."""
        cursor = self._cursor()
        try:
            self._execute(cursor, query, parameters)
            column_names = [d[0] for d in cursor.description]
            return [Row(itertools.izip(column_names, row)) for row in cursor]
        finally:
            cursor.close()

    def get(self, query, *parameters):
        """Returns the first row returned for the given query."""
        rows = self.query(query, *parameters)
        if not rows:
            return None
        elif len(rows) > 1:
            raise Exception("Multiple rows returned for Database.get() query")
        else:
            return rows[0]

    def execute(self, query, *parameters):
        """Executes the given query, returning the lastrowid from the query."""
        cursor = self._cursor()
        try:
            self._execute(cursor, query, parameters)
            return cursor.lastrowid
        finally:
            cursor.close()

    def executemany(self, query, parameters):
        """Executes the given query against all the given param sequences.

        We return the lastrowid from the query.
        """
        cursor = self._cursor()
        try:
            cursor.executemany(query, parameters)
            return cursor.lastrowid
        finally:
            cursor.close()

    def _ensure_connected(self):
        # Mysql by default closes client connections that are idle for
        # 8 hours, but the client library does not report this fact until
        # you try to perform a query and it fails.  Protect against this
        # case by preemptively closing and reopening the connection
        # if it has been idle for too long (7 hours by default).
        if (self._db is None
                or (time.time() - self._last_use_time > self.max_idle_time)):
            self.reconnect()
        self._last_use_time = time.time()

    def _cursor(self):
        self._ensure_connected()
        return self._db.cursor()

    def _execute(self, cursor, query, parameters):
        try:
            cursor.execute(query, parameters)
            self._db.commit()
            return
        except OperationalError:
            logging.error("Error connecting to MySQL on %s", self.host)
            self.close()
            raise
Exemple #9
0
class Connection(object):
    def __init__(self,
                 database,
                 host,
                 user,
                 mincached=5,
                 maxcached=10,
                 maxshared=0):
        self.database = database
        self.host = host
        self.user = user
        self.mincached = mincached
        self.maxcached = maxcached
        self.maxshared = maxshared

        args = dict(
            database=database,
            user=user,
            mincached=mincached,
            maxcached=maxcached,
            maxshared=maxshared,
        )

        self.max_idle_time = 7 * 3600
        self._db = None
        self._db_args = args
        self._last_use_time = time.time()

        try:
            self.reconnect()
        except:
            logging.error("Cannot connect to postgres on %s",
                          self.host,
                          exc_info=True)

    def __del__(self):
        self.close()

    def close(self):
        if getattr(self, "_db", None) is not None:
            self._db.close()
            self._db = None

    def reconnect(self):
        """Closes the existing database connection and re-opens it."""
        self.close()
        self._db = PooledDB(psycopg2, **self._db_args).connection()

    def _ensure_connected(self):
        # PostgreSQL by default closes client connections that are idle for
        # 8 hours, but the client library does not report this fact until
        # you try to perform a query and it fails.  Protect against this
        # case by preemptively closing and reopening the connection
        # if it has been idle for too long (7 hours by default).
        if (self._db is None
                or (time.time() - self._last_use_time > self.max_idle_time)):
            self.reconnect()
            self._last_use_time = time.time()

    def _cursor(self):
        self._ensure_connected()
        return self._db.cursor()

    def _execute(self, cursor, query, parameters):
        try:
            cursor.execute(query, parameters)
            self._db.commit()
            return
        except OperationalError:
            logging.error("Error connecting to postgres on %s", self.host)
            self.close()
            raise

    def execute_lastrowid(self, query, parameters):
        """Executes the given query, returning the lastrowid from the query."""
        cursor = self._cursor()
        try:
            self._execute(cursor, query, parameters)
            return cursor.lastrowid
        finally:
            cursor.close()

    def execute_rowcount(self, query, parameters):
        """Executes the given query, returning the rowcount from the query."""
        cursor = self._cursor()
        try:
            self._execute(cursor, query, parameters)
            return cursor.rowcount
        finally:
            cursor.close()

    def executemany(self, query, parameters):
        """Executes the given query against all the given param sequences.

        We return the lastrowid from the query.
        """
        return self.executemany_lastrowid(query, parameters)

    def executemany_lastrowid(self, query, parameters):
        """Executes the given query against all the given param sequences.
        We return the lastrowid from the query.
        """
        cursor = self._cursor()
        try:
            cursor.executemany(query, parameters)
            return cursor.lastrowid
        finally:
            cursor.close()

    def executemany_rowcount(self, query, parameters):
        """Executes the given query against all the given param sequences.

        We return the rowcount from the query.
        """
        cursor = self._cursor()
        try:
            cursor.executemany(query, parameters)
            return cursor.rowcount
        finally:
            cursor.close()

    def query(self, query, parameters=[]):
        """Returns a row list for the given query and parameters."""
        cursor = self._cursor()
        try:
            self._execute(cursor, query, parameters)
            column_names = [d[0] for d in cursor.description]
            return [Row(itertools.izip(column_names, row)) for row in cursor]
        finally:
            cursor.close()

    def get(self, query, parameters=[]):
        """Returns the first row returned for the given query."""
        rows = self.query(query, parameters)
        if not rows:
            return None
        elif len(rows) > 1:
            raise Exception("Multiple rows returned for Database.get() query")
        else:
            return rows[0]

    def execute(self, query, parameters=[]):
        """Executes the given query, returning the lastrowid from the query."""
        return self.execute_lastrowid(query, parameters)
class ConnectionPoolV1(object):
    def __init__(self, host: str, port: int, user: str, passwd: str, db: str,
                 charset: str, _lock):
        """
        数据库连接池 V1版本
        :param host: 要连接的主机IP地址,比如 "127.0.0.1"
        :param port: 要连接的主机端口,比如 3306
        :param user: 数据库用户名,比如 "root"
        :param passwd: 数据库用户密码,比如 "123456"
        :param db: 要使用的数据库名,比如 "my_database"
        :param charset: 默认字符集,比如 "utf8mb4"
        """
        DeprecationWarning("现在V1版本的数据库连接池由于没有加锁会出错,被迫加了锁,性能降低")
        self.conn = PooledDB(creator=pymysql,
                             mincached=1,
                             maxcached=20,
                             host=host,
                             port=port,
                             user=user,
                             passwd=passwd,
                             db=db,
                             charset=charset).connection()
        self.cur = self.conn.cursor(cursor=pymysql.cursors.DictCursor)
        self._lock = _lock

    def insert(self, sql, arg_tuple) -> int:
        """
        执行插入SQL语句
        :param sql: SQL模板语句
        :param arg_tuple: SQL模板语句参数
        :return: 被影响的行数
        """
        # with self._lock:
        #     _ = self.cur.execute(sql, arg_tuple)
        #     self.conn.commit()
        self._lock.acquire()
        _ = self.cur.execute(sql, arg_tuple)
        self.conn.commit()
        self._lock.release()
        return _

    def delete(self, sql, arg_tuple) -> int:
        """
        执行删除SQL语句
        :param sql: SQL模板语句
        :param arg_tuple: SQL模板语句参数
        :return: 被影响的行数
        """
        # with self._lock:
        #     _ = self.cur.execute(sql, arg_tuple)
        #     self.conn.commit()
        self._lock.acquire()
        _ = self.cur.execute(sql, arg_tuple)
        self.conn.commit()
        self._lock.release()
        return _

    def update(self, sql, arg_tuple) -> int:
        """
        执行更改SQL语句
        :param sql: SQL模板语句
        :param arg_tuple: SQL模板语句参数
        :return: 被影响的行数
        """
        # with self._lock:
        #     _ = self.cur.execute(sql, arg_tuple)
        #     self.conn.commit()
        self._lock.acquire()
        _ = self.cur.execute(sql, arg_tuple)
        self.conn.commit()
        self._lock.release()
        return _

    def fetch_all(self, sql: str, arg_tuple: tuple):
        """
        执行查询语句并返回所有查询结果(如果有的话),若无结果返回空tuple
        :param sql: SQL模板语句,比如 SELECT `id` FROM `tbl_user` WHERE `tbl_user`.`user_name`=%s;
        :param arg_tuple: 构建真正查询语句时,传递给SQL模板语句的tuple,比如('alice',)
        :return: 查询结果list,list中的每一个元素(dict类型)对应数据库里的一行
        """
        # with self._lock:
        #     self.cur.execute(sql, arg_tuple)
        self._lock.acquire()
        self.cur.execute(sql, arg_tuple)
        self.conn.commit()
        self._lock.release()
        return self.cur.fetchall()

    def fetch_one(self, sql: str, arg_tuple: tuple):
        """
        执行查询语句并返回第一条查询结果(如果有的话),若无结果返回 * None *
        :param sql: SQL模板语句,比如 SELECT `id` FROM `tbl_user` WHERE `tbl_user`.`user_name`=%s;
        :param arg_tuple: 构建真正查询语句时,传递给SQL模板语句的tuple,比如('alice',)
        :return: 查询结果dict
        """
        # with self._lock:
        #     self.cur.execute(sql, arg_tuple)
        self._lock.acquire()
        self.cur.execute(sql, arg_tuple)
        self.conn.commit()
        self._lock.release()
        return self.cur.fetchone()

    def close(self):
        """关闭数据库连接池,估计暂时用不到"""
        self.conn.close()
        self.cur.close()