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 connection_pool():
pool = ConnectionPool(**config)
pool.connect()
return pool
def connection_pool():
# Return a connection pool instance
pool = ConnectionPool(**config)
pool.connect()
return pool