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')
