def open(self): if self.__connection: raise MySQLdb.MySQLError("connection already connected.") try: self.__connection = MySQLdb.connect(*self.args, **self.kwargs) except Exception: logger.error("数据库连接异常, 请设置:sql_annotation.conn.connection 的连接信息.") raise DatabaseConnectionError if self.__cursor: raise MySQLdb.MySQLError("cursor already opened.") self.__cursor = self.__connection.cursor(MySQLdb.cursors.DictCursor)
def open(self): """ 打开连接 :return: """ if self.__connection: raise MySQLdb.MySQLError("connection already connected.") self.__connection = MySQLdb.connect(*self.__args, **self.__kwargs) if self.__cursor: raise MySQLdb.MySQLError("cursor already opened.") self.__cursor = self.__connection.cursor(MySQLdb.cursors.DictCursor) logger.info("connection opened.")
def connect(self): count = 0 while 1: if self._conn: try: return self._conn.ping() except MySQLdb.MySQLError: pass try: self.close() except MySQLdb.MySQLError: pass try: count += 1 self._conn = MySQLdb.connect(host=self.host, port=self.port, user=self.user, passwd=self.password, db=self.db, charset=self.charset) self._cursor = self._conn.cursor(MySQLdb.cursors.DictCursor) except MySQLdb.MySQLError, e: log.error(u'reconnect failed {}'.format(e)) if 0 < self.max_retry < count: raise MySQLdb.MySQLError('connection failed') time.sleep(5)
def open_conn(self, count=0): """ 建立数据库连接,如果连接超过一定次数,则返回一个None,表示建立数据库连接失败 推荐不再使用该方法,转而使用MyConn类 :param count: 数据库连接尝试次数 :return: None """ if count >= 3: raise MySQLdb.MySQLError( "database connect have tried over 3 times") try: conn = MySQLdb.connect(host='%s' % self.host, user='******' % self.user, passwd='%s' % self.password, db='%s' % self.schema, port=self.port, charset="utf8", use_unicode="True") return conn except MySQLdb.MySQLError as conn_error: error_code = conn_error.args[0] # 如果是网路错误 if error_code >= 1158 & error_code <= 1161: return self.open_conn(count + 1) elif error_code == 1081: raise conn_error
def __execute(self, sql, commit=False): if not (self.__connection and self.__cursor): raise MySQLdb.MySQLError("connection already closed.") count = self.__cursor.execute(sql) result = self.__cursor.fetchall() self.__connection.commit() if commit else None return count if commit else result
def __execute(self, sql, commit=False): """ 执行SQL :param sql: :param commit: :return:tuple result or row numbers """ if not (self.__connection and self.__cursor): raise MySQLdb.MySQLError("connection already closed.") count = self.__cursor.execute(sql) # 返回总条数 result = self.__cursor.fetchall() self.__connection.commit() if commit else None return count if commit else result
def _execute(self, sql, args, dict_result_bool=False, is_many_bool=False): if args is not None: assert isinstance(args, tuple) or isinstance(args, list) cur = transaction_context.get_cursor(dict_result=dict_result_bool) if not is_many_bool: affected_row_count_int = cur.execute(sql, args) else: affected_row_count_int = cur.executemany(sql, args) warns = filter(lambda m: m[0] == MySQLdb.Warning, cur.messages) if len(warns) > 0: warns_message = ';'.join([w[1][2] for w in warns]) raise MySQLdb.MySQLError(warns_message) return affected_row_count_int, cur
def do_sql_script(self, sql_scr_str): if self.conn is None: if self.connect_db() == 0: raise MySQLdb.MySQLError('can not connect to database file') self.conn.executescript(sql_scr_str) return 1
class MysqlHandler(object): """mysql句柄, 对常用操作进行了封装. 支持单条sql语句insert,select,update,delete Members: __pool PooledDB object """ def __init__(self, config): """Initialize Args: config A dict object contains keys: MYSQL_{HOST,PORT,USER,PASSWD,DB,CHARSET} """ self.__pool = MysqlPoolFactory().get_pool( host=config['MYSQL_HOST'], port=int(config['MYSQL_PORT']), user=config['MYSQL_USER'], passwd=config['MYSQL_PASSWD'], db=config['MYSQL_DB'], charset=config['MYSQL_CHARSET'], cursorclass=DictCursor ) @classmethod def from_settings(cls, settings): '''通过配置创建MysqlHandler对象''' config = { 'MYSQL_HOST': settings.get('MYSQL_HOST'), 'MYSQL_PORT': settings.getint('MYSQL_PORT'), 'MYSQL_USER': settings.get('MYSQL_USER'), 'MYSQL_PASSWD': settings.get('MYSQL_PASSWD'), 'MYSQL_DB': settings.get('MYSQL_DB'), 'MYSQL_CHARSET': settings.get('MYSQL_CHARSET'), } return cls(config) @classmethod def from_config(cls, config): '''通过配置创建MysqlHandler对象''' return cls(config) def __get_connection(self): '''Pick one connection object @thread-safe''' conn = self.__pool.dedicated_connection() return conn def __put_connection(self, conn): '''缓存mysql connection, 调用conn.close效果与cache_connection相同''' self.__pool.cache_connection(conn) def _do_query(self, func, sql, values=None, retry_times=0, retry_interval=1): '''执行query 遇到mysql连接错误时, 按指定间隔重试 Args: func 实际调用的方法 sql sql语句 values sql参数 retry_times 重试次数,0表示无限次重试 retry_interval 表示间隔秒数 Return: return of func() Raises: MySQLdb.MySQLError ''' loop_times = retry_times if retry_times > 0 else sys.maxint while loop_times > 0: try: return func(sql, values) except MySQLdb.OperationalError, e: log.error('%s', sys.exc_info()) errid, errmsg = e.args if errid != 2014: # 不是mysql连接失败错误 raise e time.sleep(retry_interval) raise MySQLdb.MySQLError('mysql connection error')