def delete(self, sql, params=None, cursor_mode=CursorMode.CURSOR_MODE):
if sql.upper().startswith('DROP') | sql.upper().startswith(
'CREATE') | sql.upper().startswith('INSERT') | sql.upper(
).startswith('SELECT') | sql.upper().startswith('UPDATE'):
raise DBError(key=DBErrorType.OPERATE_NOT_SUPPORT_ERROR,
message="Not support this operate")
conn = None
cursor = None
try:
delete_result = self.__execute(sql=sql,
params=params,
cursor_mode=cursor_mode)
conn = delete_result["conn"]
cursor = delete_result["cursor"]
self.__commit_connection()
return cursor.rowcount
except DBError as e:
raise e
except Exception as e:
reraise(DBError(e, sql=sql, params=params), sys.exc_info()[2])
finally:
if cursor:
cursor.close()
if conn:
self.__close_connection()
def get_executor(db_config,
pool_name="mysql-pool",
pool_size=32,
pool_reset_session=True):
key = MysqlFactory.__get_pool_key(db_config)
try:
return MysqlFactory.__db_connection_pools[key]
except KeyError:
try:
_db_config = db_config.copy()
if 'password' in _db_config:
_db_config['password'] = '******'
logger.info("Init mysql pool info - name: " + pool_name +
", size: " + str(pool_size) + ", reset session: " +
str(pool_reset_session) + ", dbconfig: " +
str(_db_config))
__mysql_connection_pool = mysql.connector.pooling.MySQLConnectionPool(
pool_name=pool_name,
pool_size=pool_size,
pool_reset_session=pool_reset_session,
**db_config)
MysqlFactory.__db_connection_pools[key] = MysqlExecutor(
__mysql_connection_pool,
True if Configer.get("db.show_sql") == "True" else False)
except Exception as e:
raise DBError(e)
return MysqlFactory.__db_connection_pools[key]
def build_update_sql(self, models=[], columns=[], where="", params=None):
if len(models) > 0:
# 批量更新
sql = ""
columns = models[0].get_attrs().keys()
if len(columns) <= 0:
raise DBError(key=DBErrorType.SQL_BUILD_ERROR,
message="Not found any columns to update.")
else:
for model in models:
params = []
set_columns = []
for column in columns:
if column != self.primary_key:
set_columns.append("`" + column + "`= %s")
params.append(model.get_attr(column))
separator = ", "
set_sql = separator.join(set_columns)
primary_value = model.get_attr(self.primary_key)
where = "`%s` = %s" % (self.primary_key, primary_value)
params.append(primary_value)
sql += "UPDATE " + self._assembly_from_sql(
) + " SET " + set_sql + (" WHERE " + where
if where != "" else "") + ";"
else:
if len(columns) <= 0:
raise DBError(key=DBErrorType.SQL_BUILD_ERROR,
message="Not found any columns to update.")
else:
set_columns = []
for column in columns:
if column != self.primary_key:
set_columns.append("`" + column + "`= %s")
separator = ", "
set_sql = separator.join(set_columns)
sql = "UPDATE " + self._assembly_from_sql() + " SET " + set_sql + (
" WHERE " + where if where != "" else "")
return sql, params
def insert(self,
sql,
params=None,
execute_mode=ExecuteMode.ONE_MODE,
cursor_mode=CursorMode.CURSOR_MODE):
if sql.upper().startswith('DROP') | sql.upper().startswith(
'CREATE') | sql.upper().startswith('SELECT') | sql.upper(
).startswith('UPDATE') | sql.upper().startswith('DELETE'):
raise DBError(key=DBErrorType.OPERATE_NOT_SUPPORT_ERROR,
message="Not support this operate")
conn = None
cursor = None
try:
insert_result = self.__execute(sql=sql,
params=params,
execute_mode=execute_mode,
cursor_mode=cursor_mode)
conn = insert_result["conn"]
cursor = insert_result["cursor"]
rowcount = cursor.rowcount
if execute_mode == ExecuteMode.ONE_MODE:
result = cursor.lastrowid
else:
result = rowcount
self.__commit_connection()
return result
except DBError as e:
raise e
except Exception as e:
reraise(DBError(e, sql=sql, params=params), sys.exc_info()[2])
finally:
if cursor:
cursor.close()
if conn:
self.__close_connection()
def select(self, sql, params=None, cursor_mode=CursorMode.CURSOR_MODE):
if sql.upper().startswith('DROP') | sql.upper().startswith(
'CREATE') | sql.upper().startswith('INSERT') | sql.upper(
).startswith('UPDATE') | sql.upper().startswith('DELETE'):
raise DBError(key=DBErrorType.OPERATE_NOT_SUPPORT_ERROR,
message="Not support this operate")
conn = None
cursor = None
try:
select_result = self.__execute(sql=sql,
params=params,
cursor_mode=cursor_mode)
conn = select_result["conn"]
cursor = select_result["cursor"]
result = []
column_values = cursor.fetchall()
if column_values is not None and len(column_values) > 0:
column_names = [i[0] for i in cursor.description]
for i, row in enumerate(column_values):
tmp = {}
for j, value in enumerate(row):
tmp[column_names[j]] = value
result.append(tmp)
return result
except DBError as e:
raise e
except Exception as e:
reraise(DBError(e, sql=sql, params=params), sys.exc_info()[2])
finally:
if cursor:
cursor.close()
if conn:
self.__close_connection()
def __get_connection(self):
"""
获取一个连接
"""
if self.__has_connection():
connection = self.__local_connection.mysql_connection
else:
connection = self.__mysql_connection_pool.get_connection()
self.__local_connection.mysql_connection = connection
if not connection.is_connected():
raise DBError(key=DBErrorType.NOT_CONNECT_ERROR,
message="Connection is not connect.")
if self.__has_transaction():
connection.autocommit = False
else:
connection.autocommit = True
logger.debug('Connection autocommit: {}'.format(connection.autocommit))
return connection
def __execute(self,
sql,
params=None,
execute_mode=ExecuteMode.ONE_MODE,
cursor_mode=CursorMode.CURSOR_MODE):
"""
通用sql执行工具
"""
if self.__show_sql:
if isinstance(params, list):
log_params = params[:5]
logger.info("Execute Sql: {}, params(top5): \n{}".format(
sql, ', \n'.join(
[str(param_tuple) for param_tuple in log_params])))
else:
logger.info("Execute Sql: {}, params: {}".format(sql, params))
conn = self.__get_connection()
if cursor_mode == CursorMode.CURSOR_MODE:
cursor_class = mysql.connector.cursor.MySQLCursor
elif cursor_mode == CursorMode.DICT_CURSOR_MODE:
cursor_class = mysql.connector.cursor.MySQLCursorDict
else:
raise DBError(key=DBErrorType.CURSOR_MODE_ERROR,
message="Cursor mode value is wrong.")
cursor = conn.cursor(buffered=True,
raw=None,
prepared=True,
cursor_class=cursor_class)
if execute_mode == ExecuteMode.ONE_MODE:
cursor.execute(sql, params)
else:
cursor.executemany(sql, params)
return {"conn": conn, "cursor": cursor}