def connection_pool():
# Return a connection pool instance
"""
with connection_pool().connection() as conn:
cur = conn.cursor()
#print(data)
"""
pool = ConnectionPool(**config)
pool.connect()
return pool
class MysqlClient(object):
# init the mysql database
def __init__(self, db):
if db == 'tts_tob_qly_v2':
self.pool = ConnectionPool(**config1)
elif db == 'tts_qly_analysis':
self.pool = ConnectionPool(**config2)
elif db == 'tts_douyin':
self.pool = ConnectionPool(**config3)
self.pool.connect()
def find_qlypv_history(self):
SQL = "select rdate, num from qly_stat_day where stat_type='plugin_pv' order by rdate"
return self.query(SQL)
def find_order(self):
SQL = "select date_format(modify_time, '%Y-%m-%d') as rdate, sum(pay_amount)/100 as sum_pay, count(*) as count from t_tob_zhishu100_user_order where order_status in (2, 3, 5) and shop_id not in (11211211) and pay_amount>100 group by date_format(modify_time, '%Y-%m-%d')"
return self.query(SQL)
def find_web_report(self,starttime,endtime):
SQL = 'SELECT * from douyin_web_report where rdate>=' + '"' + starttime + '"' + ' and rdate <= ' + '"' + endtime + '"'
return self.query(SQL)
def find_plugin_hour(self, starttime, endtime):
SQL = 'SELECT * from plugin_hour_analyse where ctime >=' + '"' + starttime + '"' + ' and ctime <= ' + '"' + endtime + '"' + ' order by ctime'
return self.query(SQL)
def find_plugin_day(self, starttime, endtime):
SQL = 'SELECT * from plugin_day_analyse where ctime >=' + '"' + starttime + '"' + ' and ctime <= ' + '"' + endtime + '"' + ' order by ctime'
return self.query(SQL)
def get_today_order_status(self):
time_str = datetime.datetime.now().strftime("%Y-%m-%d 00:00:00")
SQL = 'SELECT SUM(pay_amount) as total_count from doushop_user_order where order_status =2 and create_time > ' + '"' + time_str + '"'
return self.query(SQL)
def query(self, sql):
result = []
try:
connection = self.pool.borrow_connection()
result = pd.read_sql(sql, connection)
except Exception as e:
logger.error(str(e))
finally:
self.pool.return_connection(connection)
return result
def __init__(self,
host='localhost',
port=9306,
user='',
password='',
database='',
pool_name=None):
self.__pool = ConnectionPool(host=host,
port=port,
user=user,
password=password,
database=database,
pool_name=pool_name)
self._host = host
self._port = port
self._user = user
self._password = password
self._database = database
self._pool_name = pool_name
def get_vesync_database_connection_pool(
database_info: dict) -> ConnectionPool:
"""
Create database connection and return instance of connection.
:return: instance of Pool
"""
connection_pool = ConnectionPool(size=2,
name='connection_pool',
**database_info)
return connection_pool
def __init__(self, db):
if db == 'tts_tob_qly_v2':
self.pool = ConnectionPool(**config1)
elif db == 'tts_qly_analysis':
self.pool = ConnectionPool(**config2)
elif db == 'tts_douyin':
self.pool = ConnectionPool(**config3)
self.pool.connect()
class SphinxEngine:
def __init__(self,
host='localhost',
port=9306,
user='',
password='',
database='',
pool_name=None):
self.__pool = ConnectionPool(host=host,
port=port,
user=user,
password=password,
database=database,
pool_name=pool_name)
self._host = host
self._port = port
self._user = user
self._password = password
self._database = database
self._pool_name = pool_name
def __execute(self, raw_query):
with self.__pool.cursor() as cursor:
return cursor.execute(raw_query)
@property
def is_ready(self):
return self.__execute('SELECT 1')
@property
def all_indices(self):
res = self.__execute('SHOW TABLES')
return [row['Index'] for row in res]
@property
def meta(self):
res = self.__execute('SHOW META')
return {row['Variable_name']: row['Value'] for row in res}
def get_index_meta(self, index_name):
res = self.__execute('SHOW INDEX {} STATUS'.format(index_name))
return {row['Variable_name']: row['Value'] for row in res}
def connection_pool():
# Return a connection pool instance
pool = ConnectionPool(size=5, name='test', **config, autocommit=True)
return pool
def __init__(self, **dbConfig):
self.pool = ConnectionPool(**dbConfig)
class dbAccess:
def __init__(self, **dbConfig):
self.pool = ConnectionPool(**dbConfig)
def selectGatewayConfig(self, taskid):
sql = "select DISTINCT task_gateway from tb_task WHERE task_id='%s'" % (
taskid)
records = None
with self.pool.cursor() as cursor:
try:
cursor.execute(sql)
records = cursor.fetchall()
except:
logger.error("Error: unable to fetch gateway data.")
logger.debug("selectGatewayConfig by taskid results: %s", records)
return records
def getGatewayAttr(self, gwId):
sql = "select gw_uuid, gw_username, gw_linename, gw_type, gw_status, \
gw_concurrency from tb_gateway WHERE gw_uuid='%s'and gw_status=True" % (
gwId)
records = None
with self.pool.cursor() as cursor:
try:
cursor.execute(sql)
records = cursor.fetchall()
except:
logger.error("Error: unable to fetch gateway attributes.")
if records != None and len(records) > 0:
return records[0]
else:
return None
def selectDialTask(self):
sql = "select distinct task_id from tb_task WHERE task_status = 1"
records = None
with self.pool.cursor() as cursor:
try:
cursor.execute(sql)
records = cursor.fetchall()
except:
logger.error("Error: unable to fetch dial task data.")
return records
def selectDialTaskById(self, taskid):
sql = "select task_uuid, task_id, task_userid, task_username, task_status, \
task_maxcall, task_pausesecond, task_cachecount, task_timegroup, \
task_gatewaytype, task_gateway, task_dialext, task_hangup from tb_task \
WHERE task_id = '%s'" % (taskid)
records = None
with self.pool.cursor() as cursor:
try:
cursor.execute(sql)
records = cursor.fetchall()
except:
logger.error("Error: unable to fetch dial task data.")
logger.debug("selectDialTask by taskid results: %s", records)
if records != None and len(records) > 0:
return records[0]
else:
return None
def selectDialTaskStatus(self, task_id):
sql = "select task_status from tb_task WHERE task_id='%s'" % (task_id)
records = None
with self.pool.cursor() as cursor:
try:
cursor.execute(sql)
records = cursor.fetchall()
except:
logger.error("Error: unable to fetch dial task data.")
if records != None and len(records) > 0:
return records[0].get('task_status')
else:
return None
def selectPhoneStatus(self, Phone):
sql = "select cr_uuid, cr_taskid, cr_mobile, cr_status, cr_calltime \
from tb_callrecord where cr_mobile='%s' and cr_status=2;" % (
Phone)
records = None
with self.pool.cursor() as cursor:
try:
cursor.execute(sql)
records = cursor.fetchall()
except:
logger.error("Error: unable to fetch dial task data.")
if records != None and len(records) > 0:
return records
else:
return None
def selectCallRecordsToDial(self, taskid, cacheNum):
sql = "select cr_uuid, cr_taskid, cr_mobile, cr_status, cr_calltime from \
tb_callrecord WHERE cr_taskid = '%s' and cr_status = 0 \
order by cr_uuid limit %d" % (taskid, cacheNum)
records = None
with self.pool.cursor() as cursor:
try:
cursor.execute(sql)
records = cursor.fetchall()
except:
logger.error("Error: unable to fetch call records data.")
return records
def selectOneCallRecord(self, uuid):
sql = "select cr_uuid, cr_taskid, cr_mobile, cr_status, cr_calltime from \
tb_callrecord WHERE cr_uuid = %d" % (uuid)
record = None
with self.pool.cursor() as cursor:
try:
cursor.execute(sql)
record = cursor.fetchall()
except:
logger.error("Error: unable to fetch a call record data.")
return record[0]
def selectGw(self, uuid):
sql = "select cr_uuid, cr_taskid, cr_mobile, cr_status, cr_calltime from \
tb_callrecord WHERE cr_uuid = %d" % (uuid)
record = None
with self.pool.cursor() as cursor:
try:
cursor.execute(sql)
record = cursor.fetchall()
logger.debug(record)
except:
logger.error("Error: unable to fetch gw record data.")
return record
def updateDialTime(self, rec):
state = rec.get('cr_status')
uuid = rec.get('cr_uuid')
sql = "UPDATE tb_callrecord SET cr_calltime=NOW(3), cr_status=%d WHERE cr_uuid=%d" % (
state, uuid)
logger.debug("updateDialTime sql: %s", sql)
with self.pool.cursor() as cursor:
try:
cursor.execute(sql)
except:
logger.error("Error: unable to update dial time.")
return
def updateDialFailedTime(self, rec):
state = rec.get('cr_status')
uuid = rec.get('cr_uuid')
sql = "UPDATE tb_callrecord SET cr_endtime=NOW(3), cr_status=%d WHERE cr_uuid=%d" % (
state, uuid)
logger.debug("updateDialFailedTime sql: %s", sql)
with self.pool.cursor() as cursor:
try:
cursor.execute(sql)
except:
logger.error("Error: unable to update failed call time data.")
return
def updateCallrecordToAlloc(self, uuid):
sql = "update tb_callrecord set cr_status = 1 where cr_uuid=%d" % (
uuid)
with self.pool.cursor() as cursor:
try:
cursor.execute(sql)
except:
logger.error("Error: unable to update tb_callrecord to alloc.")
return
def updateCallrecordToUnalloc(self, uuid):
sql = "update tb_callrecord set cr_status = 0 where cr_uuid=%d" % (
uuid)
with self.pool.cursor() as cursor:
try:
cursor.execute(sql)
except:
logger.error(
"Error: unable to update tb_callrecord to unalloc.")
return
def selectAllTasksById(self, taskid):
sql = "select task_uuid from tb_task WHERE task_id='%s'" % (taskid)
records = None
with self.pool.cursor() as cursor:
try:
cursor.execute(sql)
records = cursor.fetchall()
except:
logger.error("Error: unable to selectAllTasksById by %s." %
(taskid))
return records
def selectVerbalContent(self, taskid):
sql = "select vb_content from tb_verbal where vb_uuid =\
(select task_verbal from tb_task where task_id='%s')" % (taskid)
records = None
with self.pool.cursor() as cursor:
try:
cursor.execute(sql)
records = cursor.fetchall()
except Exception as e:
logger.error(
"Error: unable to selectVerbalContent by %s :%s." %
(taskid, e))
if records == None:
return None
else:
return records[0]
def selectSgw(self, uuid):
sql = "SELECT sgw_url,sgw_appid,sgw_appkey FROM tb_smsgateway WHERE sgw_uuid='%s';" % (
uuid)
record = None
with self.pool.cursor() as cursor:
try:
cursor.execute(sql)
record = cursor.fetchall()
except:
logger.error(
"Error: unable to fetch tb_smsgateway record data.")
if record == None:
return None
else:
return record[0]
def selectManager(self):
sql = "SELECT mi_name,mi_phone,now() \
mi_msg,mi_time FROM tb_manager_info;"
records = None
with self.pool.cursor() as cursor:
try:
cursor.execute(sql)
records = cursor.fetchall()
except:
logger.error(
"Error: unable to fetch tb_manager_info record data.")
return records
def selectLatestTime(self):
sql = "SELECT task_id FROM tb_task WHERE task_status = 1 \
and task_id not in (\
SELECT distinct cr_taskid FROM tb_callrecord WHERE cr_taskid in (SELECT task_id FROM tb_task WHERE task_status = 1)\
AND cr_status = 10 AND cr_calltime < NOW(3) AND cr_calltime > DATE_SUB(NOW(3), INTERVAL 20 MINUTE))\
and task_id in (\
SELECT distinct cr_taskid FROM tb_callrecord b WHERE cr_taskid in (SELECT task_id FROM tb_task WHERE task_status = 1)\
AND cr_calltime < DATE_SUB(NOW(3), INTERVAL 20 MINUTE) AND cr_calltime > DATE_SUB(NOW(3), INTERVAL 80 MINUTE));"
records = None
with self.pool.cursor() as cursor:
try:
cursor.execute(sql)
records = cursor.fetchall()
except:
logger.error("Error: unable to fetch tb_task record data.")
return records
def updateTbTask(self, taskid, state):
tasks = self.selectAllTasksById(taskid)
if tasks == None or len(tasks) == 0:
logger.warning("Warning, no task need to update status.")
return
for i in range(len(tasks)):
sql = "UPDATE tb_task SET task_status=%s WHERE task_uuid='%d'" % (
state, tasks[i].get('task_uuid'))
with self.pool.cursor() as cursor:
try:
cursor.execute(sql)
except:
logger.error("Error: unable to update call task status.")
return
def connection_pool():
# Return a connection pool instance
pool = ConnectionPool(**config)
# pool.connect()
return pool
def connection_pool():
pool = ConnectionPool(**dict(config.items("db")))
return pool
def conn_pool():
# pool = MySQLConnectionPool(**config)
pool = ConnectionPool(**config)
# pool.connect()
# print(pool)
return pool
def connection_pool(): # return a connection pool instance pool = ConnectionPool(**config) return pool
def connection_pool():
pool = ConnectionPool(**config)
pool.connect()
return pool