def __init__(self, clusterobj, dbname=''):
self.ip = clusterobj.foreign_ip
self.user = clusterobj.plat_user
self.passwd = clusterobj.plat_user_pass
self.db = dbname
self.port = int(clusterobj.foreign_port)
self._sql = MySQL(self.ip, self.port, self.db, self.user, self.passwd)
def put(self, request, format=None):
try:
data_id = request.data['id']
foreign_ip = request.data['foreign_ip']
foreign_port = request.data['foreign_port']
plat_user = request.data['plat_user']
plat_user_passwd = request.data['plat_user_pass']
except Exception as e:
print(e)
json_data = {'code': 500, 'msg': '数据有错误获取不到id'}
return Response(json_data,status=500)
else:
DATA_MODEL = self.get_object(data_id)
s = MysqlClusterSerializer(DATA_MODEL, data=request.data)
if s.is_valid(raise_exception=True):
try:
testconn = MySQL(ip=foreign_ip, port=foreign_port, user=plat_user, passwd=plat_user_passwd)
testconn.close()
msg = '数据库连接成功'
s.save()
json_data = {'code': 200, 'msg': '更新成功'}
return Response(json_data)
except Exception as e:
json_data = {'code': 500, 'msg': '更新失败，新的用户名密码或者网络不通过'}
return Response(json_data)
json_data = {'code': 500, 'msg': '更新失败,数据格式和内部表不一致'}
return Response(json_data, status=status.HTTP_500_INTERNAL_SERVER_ERROR)
def post(self, request, format=None):
s = MysqlClusterSerializer(data=request.data)
foreign_ip = request.data['foreign_ip']
foreign_port = request.data['foreign_port']
plat_user = request.data['plat_user']
plat_user_passwd = request.data['plat_user_pass']
msg = ""
if s.is_valid(): # 验证
try:
testconn = MySQL(ip=foreign_ip,
port=foreign_port,
user=plat_user,
passwd=plat_user_passwd)
testconn.close()
msg = '数据库连接成功'
except Exception as e:
print(e)
json_data = {'code': 500, 'msg': '数据添加失败，测试连接失败'}
return Response(json_data, content_type="application/json")
s.save() #连接成功后保存数据
json_data = {'code': 200, 'msg': '数据保存成功,' + msg}
json_data['data'] = s.data
return Response(json_data, content_type="application/json")
# return api_response.JsonResponse(s.data,code=status.HTTP_200_OK,msg='success')
json_data = {'code': 500, 'msg': '数据添加失败，请检查数据格式'}
return Response(json_data, content_type="application/json")
def mysql_update_instance(self):
if self.arch == '主从':
rs = self._conn.getSlaveIP()
conn_slave = MySQL(ip=rs[1],
port=self.port,
user=self.user,
passwd=self.passwd)
rs2 = conn_slave.getReplStatus()
myinstance = self._cluster.mysql_instance
if rs2[3] == self.port: #代表主从是一致的
slave = myinstance.get_or_create(dbcluster_id=self.cluster_id,
m_ip=rs[1],
port=self.port)
slave[0].master_ip = rs2[1]
slave[0].master_port = rs2[3]
slave[0].role = u'从库'
# slave[0].db_status=u'仅从库'
slave[0].save()
master = myinstance.get_or_create(dbcluster_id=self.cluster_id,
m_ip=rs2[1],
port=self.port)
master[0].vist_ip = self.ip
master[0].role = u'主库'
master[0].db_status = u'服务中'
master[0].save()
return True
def initdb(self):
self.db = MySQL(config.MOBGI_HOUSEAD['host'],
config.MOBGI_HOUSEAD['user'],
config.MYSQL['passwd'],
port=config.MOBGI_HOUSEAD['port'],
db=config.MOBGI_HOUSEAD['db'])
return self.db
class MySQLTask(TaskThread):
def __init__(self, *args, **kwargs):
self._mysql = MySQL(**kwargs["db_config"])
super(MySQLTask,self).__init__(*args, **kwargs)
self.daemon = True
def get_handler(self):
return self._mysql
def close(self):
self._mysql.close()
def initdb(self):
self.houseadDb = MySQL(config.MOBGI_HOUSEAD['host'],
config.MOBGI_HOUSEAD['user'],
config.MOBGI_HOUSEAD['passwd'],
port=config.MOBGI_HOUSEAD['port'],
db=config.MOBGI_HOUSEAD['db'])
self.mobgiCharge = MySQL(config.MOBGI_CHARGE['host'],
config.MOBGI_CHARGE['user'],
config.MOBGI_CHARGE['passwd'],
port=config.MOBGI_CHARGE['port'],
db=config.MOBGI_CHARGE['db'])
return self.houseadDb, self.mobgiCharge
def __init__(self, clusterid):
self._cluster = MySQLCluster.objects.get(id=clusterid)
self.cluster_id = clusterid
self.ip = self._cluster.foreign_ip
self.port = self._cluster.foreign_port
self.user = self._cluster.plat_user
self.arch = self._cluster.arch
self.passwd = self._cluster.plat_user_pass
self._conn = MySQL(ip=self.ip,
port=self.port,
user=self.user,
passwd=self.passwd)
def trysql(self, sql):
try:
self.mylog("select sql:" + sql)
self.clickdb.queryNotCatch(sql)
except MySQLdb.Error as m:
self.mylog('masterdb reconnecting now')
self.clickdb = MySQL(self.host,
self.user,
self.passwd,
port=self.port,
db=self.db)
self.clickdb.query(sql)
return self.clickdb.fetchAll()
class MainExample:
def __init__(self):
# Selenium headless
self.crawl = Crawl(delay=20, telegram_notify=False, gui=True)
self.aws = Resource()
self.mysql = MySQL()
@context_manager
def transaction(self, keyword, control_instances):
# Base page, 생략된 결과를 포함하여 검색
self.crawl.fetch('https://www.google.co.kr/search?q={}&filter=0'.format(keyword))
# 페이지 탐색
self.navigate_page(5)
# AWS 'Auxiliary' 인스턴스 실행
self.aws.ec2_on_off(control_instances)
def navigate_page(self, max_page):
# paginate pattern
# nav > tbody > tr > td.cur > span
# nav > tbody > tr > td:nth-child([next]) > a > span
cur_page = None
for _ in range(max_page):
# <a> 태그를 가진 Selenium Web Element 들의 객체 리스트
a_tags_elements = self.crawl.chrome.browser.find_elements_by_css_selector('#rso div.rc h3.r a')
# <a> 태그 추출
a_tags = [a.get_attribute('href') for a in a_tags_elements]
# 현재 페이지 데이터베이스 저장
self.mysql.upload_page_lists(a_tags)
# 현재 페이지 찾기
paginate = self.crawl.chrome.browser.find_elements_by_css_selector('#nav > tbody > tr > td')
for i, e in enumerate(paginate):
if e.get_attribute('class') == 'cur':
cur_page = i
break
# 다음 페이지 없음
try:
next_element = paginate[cur_page + 1].find_element_by_css_selector('a')
next_page = next_element.get_attribute('href')
except NoSuchElementException:
break
# 다음 페이지 요청
self.crawl.fetch(next_page)
def query(self, force=False):
print('[INFO] Querying data:')
for table, columns in self.metadata.items():
ti = time()
self[table] = MySQL.query("SELECT {} FROM {}".format(
', '.join(columns), table))
self[table].rename(columns={'id': '{}_id'.format(table[:-1])},
inplace=True)
print('[INFO] > {} - {}s'.format(table, round(time() - ti, 2)))
def main():
"""
Youtube Main Crawler. Expected to run once a day
"""
api_list = [
os.environ['API_1'], os.environ['API_2'], os.environ['API_3'],
os.environ['API_4'], os.environ['API_5']
]
yc = YoutubeCrawler(api_list, processes=50)
conn = MySQL(host=os.environ['host'],
db=os.environ['db'],
user=os.environ['user'],
port=os.environ['port'],
passwd=os.environ['pw'],
charset='utf8mb4',
auto_commit=False)
conn_02 = MySQL(host=os.environ['host'],
db=os.environ['db'],
user=os.environ['user'],
port=os.environ['port'],
passwd=os.environ['pw'],
charset='utf8mb4',
auto_commit=False)
upsert_cd = 'INSERT INTO YOUTUBE.t_ch_desc \
(ch_title, ch_id, ch_desc, ch_published_at, ch_thumb) \
VALUES (%(title)s, %(ch_id)s, %(description)s, %(publishedAt)s, %(ch_thumb)s) \
ON DUPLICATE KEY UPDATE ch_title=%(title)s, ch_desc=%(description)s, ch_published_at=%(publishedAt)s, ch_thumb=%(ch_thumb)s'
upsert_cc = 'INSERT INTO YOUTUBE.t_ch_cstats \
(ch_id, ch_n_sub, ch_n_view, ch_n_video, ch_n_cmt) \
VALUES (%(ch_id)s, %(subscriberCount)s, %(viewCount)s, %(videoCount)s, %(comment_count)s) \
ON DUPLICATE KEY UPDATE ch_n_sub=%(subscriberCount)s, ch_n_view=%(viewCount)s, ch_n_video=%(videoCount)s, ch_n_cmt=%(comment_count)s'
insert_vid_desc = 'INSERT IGNORE INTO YOUTUBE.t_ch_vid_desc \
(ch_id, upload_id, vid_id, vid_title, vid_desc, vid_published_at, vid_th) \
VALUES (%(ch_id)s, %(upload_id)s, %(vid_id)s, %(vid_title)s, %(vid_desc)s, %(vid_published_at)s, %(vid_th)s) \
'
check = update_check()
youtube_main_crawler(**check)
class AuxExample:
def __init__(self):
# Selenium headless
self.crawl = Crawl(delay=20, telegram_notify=False, gui=False)
self.mysql = MySQL()
@context_manager
def transaction(self):
while True:
# 데이터베이스에서 URL 가져오기
url = self.mysql.select_one_for_update()
if not url:
break
# 해당 URL 요청
html = self.crawl.fetch(url)
# raw_html 테이블에 업로드
self.mysql.upload_html(url, html)
# 정상적으로 완료된 경우 자동으로 인스턴스 종료
os.system('poweroff')
def __get_mysql():
"""
加载MySQL数据库连接池
"""
if services.mysql is None:
services.mysql = dict()
with open('%s/mysql.json' % account_name, 'r') as f:
mysql_account = json.load(f)
for name, info in mysql_account.items():
try:
services.mysql[name] = MySQL(**info)
except mysql_repetitive:
services.logger.exception('MySQL数据库（%s）重复连接！' % name)
class MySQL_OPS(MySQL):
def __init__(self, clusterobj, dbname=''):
self.ip = clusterobj.foreign_ip
self.user = clusterobj.plat_user
self.passwd = clusterobj.plat_user_pass
self.db = dbname
self.port = int(clusterobj.foreign_port)
self._sql = MySQL(self.ip, self.port, self.db, self.user, self.passwd)
# self._conn = self.connect(self.ip, self.port, self.db, self.user, self.passwd)
# self._cursor = self._conn.cursor()
def getBinaryLog(self):
result, col = self._sql.getBinaryLog()
return result, col
def getTables(self):
if self.db:
sql = 'show tables;'
rc, rs = self._sql.queryAll(sql=sql)
return rc, rs
else:
rc = 0
rs = None
return rc, rs
def MyfastSQL_result(request):
if request.method=='GET':
id = request.GET['id']
Myfastsql = MysqlFastSQL.objects.get(id=id)
sql = request.GET['sql']
cluster = MySQLCluster.objects.get(id=request.GET['mysqlcluster_id'])
if Myfastsql.exec_posi == u'从库':
pass
else:
try:
ip = cluster.foreign_ip
port = cluster.foreign_port
user = cluster.plat_user
arch = cluster.arch
passwd = cluster.plat_user_pass
conn = MySQL(ip=ip, port=port, user=user, passwd=passwd)
count, datalist, colName = conn.execute(sql=sql)
return render(request, 'dbops/mysql/MysqlfastSQL_result.html', locals())
except Exception as e:
colName=[u'报错']
datalist=[[u'请检查sql语句的错误，查不出来']]
return render(request, 'dbops/mysql/MysqlfastSQL_result.html', locals())
import os
from utils.mysql import MySQL
from YoutubeData.YoutubeCrawler import YoutubeCrawler
api_list = [
os.environ['API_1'], os.environ['API_2'], os.environ['API_3'],
os.environ['API_4'], os.environ['API_5']
]
conn = MySQL(host=os.environ['host'],
db=os.environ['db'],
user=os.environ['user'],
port=os.environ['port'],
passwd=os.environ['pw'],
charset='utf8mb4',
auto_commit=False)
def th_url_join(video_list, thumb, t):
for video in video_list:
th_list = []
for key in video[thumb].keys():
th_list.append(video[thumb][key]['url'])
video[thumb] = ', '.join(th_list)
video[t] = video[t][:-1]
return video_list
class RecoveryExpiredVirtualMoney(object):
"""回调广告商通知激活"""
def __init__(self):
self.initdb()
self.logfileHandle=open(config.LOGPATH + "recovery_expired_virtualmoney_setting.txt", 'a')
def initdb(self):
self.db = MySQL(config.MOBGI_HOUSEAD['host'], config.MOBGI_HOUSEAD['user'], config.MOBGI_HOUSEAD['passwd'], port=config.MOBGI_HOUSEAD['port'], db=config.MOBGI_HOUSEAD['db'])
return self.db
def mylog(self,msg):
print time.strftime("%Y-%m-%d %H:%M:%S",time.localtime())+ " " + msg
self.logfileHandle.write(time.strftime("%Y-%m-%d %H:%M:%S",time.localtime())+" " + msg + '\n')
def getRows(self,select_sql):
try:
self.db.queryNotCatch(select_sql)
except MySQLdb.Error as m:
self.mylog("db reconnect!")
self.initdb()
self.db.query(select_sql)
result = self.db.fetchAll();
return result
#新增记录
def insertRows(self,insert_sql):
try:
self.db.queryNotCatch(insert_sql)
return self.db.commit()
except MySQLdb.Error as m:
self.mylog("db reconnect!")
self.initdb()
self.db.query(insert_sql)
return self.db.commit()
#更新记录
def updateRows(self,update_sql):
try:
self.db.queryNotCatch(update_sql)
return self.db.commit()
except MySQLdb.Error as m:
self.mylog("db reconnect!")
self.initdb()
self.db.query(update_sql)
return self.db.commit()
def getExpiredVirtualdetail(self):
curtime = str(time.time())
expired_sql = "select * from advertiser_account_virtual_detail where status='normal' and expire_time < '" + curtime + "'"
expired_result = self.getRows(expired_sql)
return expired_result
def getAccount(self, uid, account_type):
account_sql = "select * from advertiser_account_detail where uid= '" + str(uid) + "' and account_type='" + account_type +"'"
account_result = self.getRows(account_sql)
if len(account_result) == 0:
self.mylog('cant get account_result. sql:' + account_sql)
return False
else:
return account_result[0]
def run(self):
self.mylog('start RecoveryExpiredVirtualMoney.')
self.expired_records = self.getExpiredVirtualdetail()
if len(self.expired_records) == 0:
self.mylog('len(self.expired_records) == 0')
self.mylog('sleep ' + str(config.SLEEPSECONDS) + ' seconds!\n')
time.sleep(config.SLEEPSECONDS)
return False;
curtime = time.time()
self.mylog('curtime:' + str(curtime))
if config.REDIS.has_key('password') is True:
poolr=redis.ConnectionPool(host=config.REDIS["host"],port=config.REDIS["port"],password=config.REDIS["password"])
else:
poolr=redis.ConnectionPool(host=config.REDIS["host"],port=config.REDIS["port"])
r=redis.Redis(connection_pool=poolr)
for item in self.expired_records:
accountdetail = self.getAccount(item['uid'], item['account_type'])
self.mylog('account detail: ' + 'uid:' + accountdetail['uid'] + ", account_type:" + accountdetail['account_type']+ ", balance:" + accountdetail['balance'])
self.mylog( 'account virtual detail: ' + 'id:' + item['id'] + ", uid:" + item['uid']+ ", account_type:" + item['account_type']+ ", balance:" + item['balance']+ ", status:" + item['status']+ ", taskdetailid:" + item['taskdetailid'] + ", expire_time:" + item['expire_time'])
if float(accountdetail['balance'])<float(item['balance']):
new_balance = 0
real_fee = float(accountdetail['balance'])
else:
new_balance = float(accountdetail['balance']) - float(item['balance'])
real_fee = float(item['balance'])
#(1)更改虚拟帐呢余额
update_account_detail_sql = 'update advertiser_account_detail set balance = "' + str(new_balance) + '" where uid="' + str(accountdetail['uid']) + '" and account_type="' +accountdetail['account_type'] + '"'
self.updateRows(update_account_detail_sql)
self.mylog('real_fee:'+ str(real_fee) + ', new_balance:' + str(new_balance) )
self.mylog('update_account_detail_sql:' + update_account_detail_sql)
#(2)更改帐户虚拟金过期状态
update_virtual_detail_sql = 'update advertiser_account_virtual_detail set status = "expired", operator="py_script",update_time="' + str(curtime) + '" where id="' + str(item['id'] + '"')
self.updateRows(update_virtual_detail_sql)
self.mylog('update_virtual_detail_sql:' + update_virtual_detail_sql)
#(3)添加帐户流水
account_log_value_str = "'" + item['uid'] + "', '" + item['account_type'] + "', 'recovery', '" + str(real_fee) + "', '虚拟金过期回收', '" + str(curtime)+ "'"
add_account_log_sql = 'insert into advertiser_account_log(uid, account_type, operate_type, trade_balance, description, create_time) value(' +account_log_value_str+ ')'
self.updateRows('set names utf8;')#处理编码问题
self.insertRows(add_account_log_sql)
self.mylog('add_account_log_sql:' + add_account_log_sql)
#(4)删除帐户余额缓存
total_balance_rediskey = REDIS.REDIS_CACHEKEY['ACCOUNT_TOTAL_BALANCE'] + '_'+ item['uid']
r.delete(total_balance_rediskey)
self.mylog('delete redis cache:' + total_balance_rediskey + "\n")
self.mylog('end RecoveryExpiredVirtualMoney.')
def select(self, table, fields=['*'], where_dict={}, callback=None):
sql = MySQL.construct_select(table, fields, where_dict)
return (sql,)
class AccountConsume(object):
CHACHE_EPRIE = 900
"""回调广告商通知激活"""
def __init__(self):
self.initdb()
self.logfileHandle = open(config.LOGPATH + "account_consume.txt", 'a')
self.confname = config.CONSUME_CONF
self.consume_log_fieldstr = '`uid`, `account_type`, `balance_before`, `balance`, `batchdeductionid`, `price`, `real_price`, `need_price`, `create_time`'
self.CONSUME_LOG_FIELD = "(" + self.consume_log_fieldstr + ")"
def initdb(self):
self.houseadDb = MySQL(config.MOBGI_HOUSEAD['host'],
config.MOBGI_HOUSEAD['user'],
config.MOBGI_HOUSEAD['passwd'],
port=config.MOBGI_HOUSEAD['port'],
db=config.MOBGI_HOUSEAD['db'])
self.mobgiCharge = MySQL(config.MOBGI_CHARGE['host'],
config.MOBGI_CHARGE['user'],
config.MOBGI_CHARGE['passwd'],
port=config.MOBGI_CHARGE['port'],
db=config.MOBGI_CHARGE['db'])
return self.houseadDb, self.mobgiCharge
def mylog(self, msg):
print time.strftime("%Y-%m-%d %H:%M:%S", time.localtime()) + " " + msg
self.logfileHandle.write(
time.strftime("%Y-%m-%d %H:%M:%S", time.localtime()) + " " + msg +
'\n')
def getRows(self, select_sql, db):
try:
db.queryNotCatch(select_sql)
except MySQLdb.Error as m:
self.mylog("db reconnect!")
self.initdb()
db.query(select_sql)
result = db.fetchAll()
return result
def getOne(self, sql, db):
try:
db.fetchRow(sql)
except MySQLdb.Error as m:
self.mylog("db reconnect!")
self.initdb()
db.query(sql)
result = db.fetchRow()
return result
# 新增记录
def insertRows(self, insert_sql, db):
self.cur_sql = insert_sql
try:
db.queryNotCatch(insert_sql)
# return self.db.commit()
except MySQLdb.Error as m:
self.mylog("db reconnect!")
self.initdb()
db.queryNotCatch(insert_sql)
return db.commit()
# 更新记录
def updateRows(self, update_sql, db):
self.cur_sql = update_sql
try:
db.queryNotCatch(update_sql)
# return self.db.commit()
except MySQLdb.Error as m:
self.mylog("db reconnect!")
self.initdb()
db.queryNotCatch(update_sql)
return db.commit()
def getUidByAdid(self, ad_id):
ad_sql = "select account_id from delivery_ad_conf_list where id= '" + ad_id + "'"
ad_result = self.getRows(ad_sql, self.houseadDb)
if len(ad_result) == 0:
self.mylog('cant get account_id. sql:' + ad_sql)
return False
else:
return ad_result[0]['account_id']
def getUidUnitidByOriginalityid(self, originality_id):
key = config.REDIS_CACHEKEY['ORIGINALITY_INFO'] + originality_id
if self.redisCache.exists(key) is False:
sql = "select account_id,unit_id, ad_id from delivery_originality_relation where id= '" + originality_id + "'"
result = self.getRows(sql, self.houseadDb)
if len(result) == 0:
self.mylog('cant get account_id. sql:' + sql)
return False, False, False
cacheData = result[0]
self.redisCache.hmset(key, cacheData)
self.redisCache.expire(key, self.CHACHE_EPRIE)
else:
cacheData = self.redisCache.hgetall(key)
return cacheData['account_id'], cacheData['unit_id'], cacheData[
'ad_id']
def getAccountDayconsumptionlimitByUid(self, uid):
key = config.REDIS_CACHEKEY['ACCOUNT_CONSUME_LIMIT'] + uid
if self.redisCache.exists(key) is False:
sql = "select day_consumption_limit from advertiser_account_consumption_limit where uid= '" + uid + "'"
result = self.getRows(sql, self.houseadDb)
if len(result) == 0:
self.mylog('cant get day_consumption_limit. sql:' + sql)
return 0
cacheData = result[0]
self.redisCache.hmset(key, cacheData)
self.redisCache.expire(key, self.CHACHE_EPRIE)
else:
cacheData = self.redisCache.hgetall(key)
return cacheData['day_consumption_limit']
def getUnitInfoByUnitid(self, unit_id):
# limit_range
key = config.REDIS_CACHEKEY['UNIT_INFO'] + unit_id
if self.redisCache.exists(key) is False:
sql = "select limit_type, limit_range,unit_type from delivery_unit_conf where id= '" + unit_id + "'"
result = self.getRows(sql, self.houseadDb)
if len(result) == 0:
self.mylog('cant get unit_info. sql:' + sql)
return False
cacheData = result[0]
self.redisCache.hmset(key, cacheData)
self.redisCache.expire(key, self.CHACHE_EPRIE)
else:
cacheData = self.redisCache.hgetall(key)
return cacheData
def getAdInfoByid(self, adId):
# limit_range
key = config.REDIS_CACHEKEY['AD_INFO'] + adId
if self.redisCache.exists(key) is False:
sql = "select * from delivery_ad_conf_list where id= '" + adId + "'"
result = self.getRows(sql, self.houseadDb)
if len(result) == 0:
self.mylog('cant get ad_info. sql:' + sql)
return False
cacheData = result[0]
self.redisCache.hmset(key, cacheData)
self.redisCache.expire(key, self.CHACHE_EPRIE)
else:
cacheData = self.redisCache.hgetall(key)
return cacheData
def getAccountdetailByUid(self, uid):
sql = "select * from advertiser_account_detail where uid= '" + uid + "'"
result = self.getRows(sql, self.houseadDb)
if len(result) == 0:
self.mylog('cant get account_detail. sql:' + sql)
return False
else:
retrunData = {}
for account in result:
retrunData[account['account_type']] = account
return retrunData
def getVirtualAccountdetailByUidType(self, uid, account_type):
virtual_account_sql = "select * from advertiser_account_virtual_detail where uid= '" + uid + "' and account_type='" + account_type + "' and status='normal' and balance !='0' order by expire_time asc"
virtual_account_result = self.getRows(virtual_account_sql,
self.houseadDb)
if len(virtual_account_result) == 0:
self.mylog('cant get virtual_account_detail. sql:' +
virtual_account_sql)
return False
else:
return virtual_account_result
# 获取帐户所有子帐户的今日消费
def getAccountDayConsumptions(self, uid, date):
result = {}
get_uid_date_consumption_sql = "select * from advertiser_account_day_consumption where uid='" + str(
uid) + "' and date='" + str(date) + "'"
day_consumption_result = self.getRows(get_uid_date_consumption_sql,
self.houseadDb)
if len(day_consumption_result) == 0:
self.mylog('cant get day_consumption_result. sql:' +
get_uid_date_consumption_sql)
return result
else:
for consumption_item in day_consumption_result:
result[consumption_item['account_type']] = consumption_item
return result
# 获取指定子帐户的日消耗额
def getAccountUidAccounttypeDayConsumption(self, uid, account_type, date):
get_uid_accounttype_date_consumption_sql = "select * from advertiser_account_day_consumption where uid='" + str(
uid) + "' and account_type='" + str(
account_type) + "' and date='" + str(date) + "'"
day_consumption_result = self.getRows(
get_uid_accounttype_date_consumption_sql, self.houseadDb)
if len(day_consumption_result) == 0:
self.mylog(
'cant get uid_accounttype_day_consumption_result. sql:' +
get_uid_accounttype_date_consumption_sql)
return False
else:
return day_consumption_result[0]['consumption']
# 获取指定帐号的今日消费总额
def getUidConsumptiontotal(self, uid, date):
day_consumption_results = self.getAccountDayConsumptions(uid, date)
if len(day_consumption_results) == 0:
consumption = 0
else:
consumption = 0
for consumption_item in day_consumption_results:
consumption += float(
day_consumption_results[consumption_item]['consumption'])
return consumption
def getUnitOriginalityDayConsumption(self, unit_id, originality_id, date):
sql = "select * from advertiser_unit_originality_day_consumption where unit_id='" + str(
unit_id) + "' and originality_id='" + str(
originality_id) + "' and date='" + str(date) + "'"
results = self.getRows(sql, self.houseadDb)
if len(results) == 0:
self.mylog(
'cant get unit_originality_day_consumption_result. sql:' + sql)
return False
else:
return results[0]['consumption']
def getUnitConsumptiontotal(self, unit_id, date):
results = {}
sql = "select * from advertiser_unit_originality_day_consumption where unit_id='" + str(
unit_id) + "' and date='" + str(date) + "'"
results = self.getRows(sql, self.houseadDb)
if len(results) == 0:
consumption = 0
else:
consumption = 0
for item in results:
consumption += float(item['consumption'])
return consumption
def getAdInfoConsumptiontotal(self, adId, date):
results = {}
sql = "select * from advertiser_unit_originality_day_consumption where ad_id='" + str(
adId) + "' and date='" + str(date) + "'"
results = self.getRows(sql, self.houseadDb)
if len(results) == 0:
consumption = 0
else:
consumption = 0
for item in results:
consumption += float(item['consumption'])
return consumption
# 虚拟金详情扣费(同步扣除虚拟帐号详情的金额,优先扣除到期时间最近的虚拟金.)
def updateVirtualAccountDetail(self, uid, account_type, real_fee):
if account_type != 'redisCache':
virtual_account_details = self.getVirtualAccountdetailByUidType(
uid, account_type)
if virtual_account_details != False:
need_virtual_fee = float(real_fee)
for virtual_item in virtual_account_details:
if float(
virtual_item['balance']) > float(need_virtual_fee):
real_virtual_fee = need_virtual_fee
new_virtual_balance = float(
virtual_item['balance']) - real_virtual_fee
need_virtual_fee = 0
update_virtual_account_sql = "update advertiser_account_virtual_detail set balance='" + str(
new_virtual_balance
) + "' where uid='" + str(
uid
) + "' and account_type='" + account_type + "' and taskdetailid='" + virtual_item[
'taskdetailid'] + "'"
else:
real_virtual_fee = float(virtual_item['balance'])
new_virtual_balance = 0
need_virtual_fee = need_virtual_fee - real_virtual_fee
update_virtual_account_sql = "update advertiser_account_virtual_detail set balance='" + str(
new_virtual_balance
) + "', status='runout' where uid='" + str(
uid
) + "' and account_type='" + account_type + "' and taskdetailid='" + virtual_item[
'taskdetailid'] + "'"
self.updateRows(update_virtual_account_sql, self.houseadDb)
self.mylog('uid:' + str(uid) + ", " + 'account_type:' +
account_type + ", " + 'taskdetailid:' +
virtual_item['taskdetailid'] + ', balance: ' +
str(new_virtual_balance) +
',current real_virtual_fee:' +
str(real_virtual_fee) + ', need_virtual_fee:' +
str(need_virtual_fee))
self.mylog(update_virtual_account_sql)
if need_virtual_fee == 0:
break
# 更新具体子帐号的日消耗
def updateAccountDayConsumption(self, uid, date, account_type, real_fee):
old_accounttype_consumption = self.getAccountUidAccounttypeDayConsumption(
uid, account_type, date)
if old_accounttype_consumption != False:
new_accounttype_consumption = float(
old_accounttype_consumption) + real_fee
update_uid_date_consumption_sql = "update advertiser_account_day_consumption set consumption='" + str(
new_accounttype_consumption) + "', update_time='" + str(
self.curtime) + "' where uid='" + str(
uid) + "' and account_type='" + str(
account_type) + "' and date='" + str(date) + "'"
self.updateRows(update_uid_date_consumption_sql, self.houseadDb)
self.mylog(update_uid_date_consumption_sql)
else:
new_accounttype_consumption = real_fee
add_uid_date_consumption_sql = "insert into advertiser_account_day_consumption" \
"(`uid`, `account_type`, `date`, `consumption`, `create_time`, `update_time`) " \
"value" \
"('" + str(uid) + "','" + account_type + "', '" + str(date) + "', '" + str(
new_accounttype_consumption) + "','" + str(self.curtime) + "','" + str(self.curtime) + "')"
self.insertRows(add_uid_date_consumption_sql, self.houseadDb)
self.mylog(add_uid_date_consumption_sql)
self.mylog('uid:' + str(uid) + ", date:" + str(date) +
", account_type:" + str(account_type) +
", account_consumption:" + str(new_accounttype_consumption))
def updateAdDayConsumption(self, unit_id, ad_id, date, originality_id,
real_fee):
old_originality_consumption = self.getUnitOriginalityDayConsumption(
unit_id, originality_id, date)
if old_originality_consumption != False:
new_originality_consumption = float(
old_originality_consumption) + real_fee
update_unit_date_consumption_sql = "update advertiser_unit_originality_day_consumption set consumption='" + str(
new_originality_consumption) + "', update_time='" + str(
self.curtime) + "' where unit_id='" + str(
unit_id) + "' and originality_id='" + str(
originality_id) + "' and date='" + str(date) + "'"
self.updateRows(update_unit_date_consumption_sql, self.houseadDb)
self.mylog(update_unit_date_consumption_sql)
else:
new_originality_consumption = real_fee
add_unit_date_consumption_sql = "insert into advertiser_unit_originality_day_consumption" \
"(`unit_id`, `ad_id`, `originality_id`, `date`, `consumption`, `create_time`, `update_time`) " \
"value" \
"('" + str(unit_id) + "','" + ad_id + "','" + originality_id + "', '" + str(
date) + "', '" + str(new_originality_consumption) + "','" + str(self.curtime) + "','" + str(
self.curtime) + "')"
self.insertRows(add_unit_date_consumption_sql, self.houseadDb)
self.mylog(add_unit_date_consumption_sql)
self.mylog('unit_id:' + str(unit_id) + ", date:" + str(date) +
", originality_id:" + str(originality_id) +
", account_consumption:" + str(new_originality_consumption))
def getLastConfigId(self):
cf = self.getConfigObject()
minId = cf.getint("account_consume", 'batchid')
return minId
def getConfigObject(self):
# 读取配置中已经处理过了的processid
cf = ConfigParser.ConfigParser()
cf.read(config.CONSUME_CONF)
return cf
def getChargeDataCurrentId(self, lastId):
sql = 'select * from adx_charge_minute where id > ' + str(
lastId) + ' order by id desc limit 1'
result = self.getRows(sql, self.mobgiCharge)
if len(result) <= 0:
return 0
return result[0]['id']
def initRedis(self):
if config.REDIS.has_key('password') is True:
poolr = redis.ConnectionPool(host=config.REDIS["host"],
port=config.REDIS["port"],
password=config.REDIS["password"])
else:
poolr = redis.ConnectionPool(host=config.REDIS["host"],
port=config.REDIS["port"])
self.redisCache = redis.Redis(connection_pool=poolr)
def getChargeDataList(self, lastId, currentId):
sql = 'select * from adx_charge_minute where id > ' + str(
lastId) + ' and id<=' + str(
currentId) + ' order by id asc limit ' + str(
config.PROCESS_BATCH_NUM)
result = self.getRows(sql, self.mobgiCharge)
return result
def updateMinuteCharge(self, is_mobgi, is_charged, id):
sql = "update adx_charge_minute set is_charged= '" + str(
is_charged) + "' ,is_mobgi ='" + str(
is_mobgi) + "' ,update_time='" + str(
self.curDatetime) + "' where id='" + str(id) + "'"
self.updateRows(sql, self.mobgiCharge)
self.mylog(sql)
def run(self):
self.mylog('start AccountConsume.')
lastId = self.getLastConfigId()
currentId = self.getChargeDataCurrentId(lastId)
# print new_result
if currentId <= 0:
self.mylog("len(news_result) = 0")
self.mylog('sleep ' + str(config.SLEEPSECONDS) + ' seconds.')
self.mylog('end AccountConsume. \n')
time.sleep(config.SLEEPSECONDS)
return True
while (int(lastId) < int(currentId)):
self.initRedis()
num = 0
chargeDataList = self.getChargeDataList(lastId, currentId)
originalityIdReluidList = {}
originalityIdRelUnitIdList = {}
originalityIdRelAdIdList = {}
accountConsumptionLimitList = {} # 今日消耗限额
accountConsumptionList = {} # 今日消耗
unitInfo = {}
adInfo = {}
unitIdConsumptionList = {}
adInfoConsumptionList = {}
for item in chargeDataList:
self.cur_sql = 'none'
try:
# 预防多次扣除
if item['is_charged'] == '1':
self.mylog('id:' + str(item['id'] + ', is_charged:' +
item['is_charged']) +
', continue!')
continue
# 定位上报数据的帐号id
if originalityIdReluidList.has_key(item['originality_id']):
uid = originalityIdReluidList[item['originality_id']]
unitId = originalityIdRelUnitIdList[
item['originality_id']]
adId = originalityIdRelAdIdList[item['originality_id']]
else:
uid, unitId, adId = self.getUidUnitidByOriginalityid(
item['originality_id'])
if uid != False:
originalityIdReluidList[
item['originality_id']] = uid
originalityIdRelUnitIdList[
item['originality_id']] = unitId
originalityIdRelAdIdList[
item['originality_id']] = adId
else:
self.mylog('uid unit_id ad_id is False, continue!')
continue
# 获取账号消费限制
if accountConsumptionLimitList.has_key(uid):
accountConsumptionLimit = accountConsumptionLimitList[
uid]
else:
accountConsumptionLimit = self.getAccountDayconsumptionlimitByUid(
uid)
accountConsumptionLimitList[
uid] = accountConsumptionLimit
# 获取单元信息
if unitInfo.has_key(unitId):
unitIdConsumptionLimit = unitInfo[unitId][
'limit_range']
else:
unitInfoCacheData = self.getUnitInfoByUnitid(unitId)
if unitInfoCacheData != False:
unitInfo[unitId] = unitInfoCacheData
unitIdConsumptionLimit = unitInfoCacheData[
'limit_range']
else:
self.mylog('unitId:' + str(unitId) +
', unitInfo is False, continue!')
continue
# 获取广告信息todo
# 获取单元信息
if adInfo.has_key(adId) is False:
adInfoInfoCacheData = self.getAdInfoByid(adId)
if adInfoInfoCacheData != False:
adInfo[adId] = adInfoInfoCacheData
else:
self.mylog('adId:' + str(adId) +
', adInfo is False, continue!')
continue
# 获取帐号信息（需要锁表）
accountDetailInfo = self.getAccountdetailByUid(uid)
if accountDetailInfo == False:
self.mylog('accountDetailInfo is False, continue!')
continue
# 账号的总金额
accountTotalAmount = 0.0
# print accountDetailInfo
for accounType in config.DEDUCTION_ORDER:
if accountDetailInfo.has_key(accounType):
accountTotalAmount += float(
accountDetailInfo[accounType]['balance'])
self.mylog('uid:' + str(uid) + "," +
'accountTotalAmount:' +
str(accountTotalAmount) +
', deductibleAmount = ' + str(item['amount']))
if accountTotalAmount == 0:
self.mylog('关闭广告\n')
continue
# 定位日期
startTimeStamp = int(
time.mktime(
time.strptime(item['create_time'][0:10],
"%Y-%m-%d")))
date = time.strftime("%Y%m%d",
time.localtime(startTimeStamp))
self.curtime = str(time.time())
self.curDatetime = time.strftime("%Y-%m-%d %H:%M:%S")
# 内外部标志
isMobgi = int(unitInfo[unitId]['unit_type']) - 1
self.updateMinuteCharge(isMobgi, 0, item['id'])
# 账号每天的消耗
if accountConsumptionList.has_key(uid):
accountDayConsumption = accountConsumptionList[uid]
else:
accountDayConsumption = self.getUidConsumptiontotal(
uid, date)
accountConsumptionList[uid] = accountDayConsumption
if float(accountConsumptionLimit) != 0.0 and float(
accountDayConsumption) >= float(
accountConsumptionLimit):
self.mylog('accountConsumptionLimit:' +
str(accountConsumptionLimit) +
', accountDayConsumption:' +
str(accountDayConsumption))
self.mylog('\n')
continue
# 广告计划每天消耗
if unitIdConsumptionList.has_key(unitId):
unitIdDayConsumption = unitIdConsumptionList[unitId]
else:
unitIdDayConsumption = self.getUnitConsumptiontotal(
unitId, date)
unitIdConsumptionList[unitId] = unitIdDayConsumption
# 达到限制金额
if unitInfo[unitId]['limit_type'] == '1' and float(
unitIdDayConsumption) >= float(
unitIdConsumptionLimit):
self.mylog('unitIdDayConsumption:' +
str(unitIdDayConsumption) +
', unitIdConsumptionLimit:' +
str(unitIdConsumptionLimit))
self.mylog('\n')
continue
# 广告活动每天消耗
if adInfoConsumptionList.has_key(adId):
adInfoDayConsumption = adInfoConsumptionList[adId]
else:
adInfoDayConsumption = self.getAdInfoConsumptiontotal(
adId, date)
adInfoConsumptionList[adId] = adInfoDayConsumption
# 达到限制金额
if adInfo[adId]['ad_limit_type'] == '1' and float(
adInfoDayConsumption) >= float(
adInfo[adId]['ad_limit_amount']):
self.mylog('adInfoDayConsumption:' +
str(adInfoDayConsumption) +
', adInfoConsumptionLimit:' +
str(adInfo[adId]['ad_limit_amount']))
self.mylog('\n')
continue
# 应扣的总金额，应扣金额
deductibleTotalAmount = 0
deductibleAmount = float(item['amount'])
consume_log_sql_valuelist = []
for accounType in config.DEDUCTION_ORDER:
if accountDetailInfo.has_key(accounType):
if float(accountDetailInfo[accounType]
['balance']) == 0:
self.mylog('uid:' + str(uid) + ", " +
'account_type:' + accounType +
', balance = 0, continue')
continue
self.mylog(
'uid:' + str(uid) + ", " + 'account_type:' +
accounType + ', balance: ' +
accountDetailInfo[accounType]['balance'])
if float(accountDetailInfo[accounType]
['balance']) > deductibleAmount:
# 当次扣费金额
curDeductibleAmount = deductibleAmount
deductibleTotalAmount += curDeductibleAmount
# 当前的余额
curBalance = float(
accountDetailInfo[accounType]
['balance']) - curDeductibleAmount
deductibleAmount = 0
else:
curDeductibleAmount = float(
accountDetailInfo[accounType]['balance'])
deductibleTotalAmount += curDeductibleAmount
curBalance = 0
deductibleAmount = deductibleAmount - curDeductibleAmount
update_account_sql = "update advertiser_account_detail set balance='" + str(
curBalance) + "' where uid='" + str(
uid
) + "' and account_type='" + accounType + "'"
consume_log_sql_valueitem = "('" + str(
uid
) + "', '" + accounType + "', '" + accountDetailInfo[
accounType]['balance'] + "', '" + str(
curBalance
) + "', '" + item['id'] + "', '" + item[
'amount'] + "', '" + str(
curDeductibleAmount) + "', '" + str(
deductibleAmount) + "', '" + str(
self.curtime) + "')"
consume_log_sql_valuelist.append(
consume_log_sql_valueitem)
self.updateRows(update_account_sql, self.houseadDb)
self.mylog('uid:' + str(uid) + ", " +
'account_type:' + accounType +
', curBalance: ' + str(curBalance) +
', deductibleTotalAmount: ' +
str(deductibleTotalAmount) +
',curDeductibleAmount:' +
str(curDeductibleAmount) +
', deductibleAmount:' +
str(deductibleAmount))
self.mylog(update_account_sql)
# 同步扣除虚拟帐号详情的金额,优先扣除到期时间最近的虚拟金.
self.updateVirtualAccountDetail(
uid, accounType, curDeductibleAmount)
# 更新日消耗数据
# (1.1)更新db日消耗数据
self.updateAccountDayConsumption(
uid, date, accounType, curDeductibleAmount)
# (1.2)更新db下的单元创意的消耗数据
self.updateAdDayConsumption(
unitId, adId, date, item['originality_id'],
curDeductibleAmount)
if deductibleAmount == 0:
break
else:
continue
# 更新redis缓存,帐号余额
accountTotalBalance = accountTotalAmount - deductibleTotalAmount
accountTotalBalanceRedisKey = config.REDIS_CACHEKEY[
'ACCOUNT_TOTAL_BALANCE'] + '_' + str(uid)
self.redisCache.set(accountTotalBalanceRedisKey,
accountTotalBalance)
self.redisCache.expire(accountTotalBalanceRedisKey, 600)
# 更新批量数据的是否外部订单状态,是否扣费过
self.updateMinuteCharge(isMobgi, 1, item['id'])
# (2.1)更新redis缓存,日账号消耗数据
currentAccountDayConsumption = accountDayConsumption + float(
deductibleTotalAmount)
accountConsumptionList[uid] = currentAccountDayConsumption
accountDayConsumptionRediskey = config.REDIS_CACHEKEY[
'ACCOUNT_DAY_CONSUMPTION'] + '_' + str(
uid) + '_' + str(date)
self.redisCache.set(accountDayConsumptionRediskey,
currentAccountDayConsumption)
self.redisCache.expire(accountDayConsumptionRediskey, 600)
self.mylog('uid:' + str(uid) + ", date:" + str(date) +
", consumption:" +
str(currentAccountDayConsumption))
# (2.2)更新redis单元日消耗数据
curUnitIdConsumption = unitIdDayConsumption + float(
deductibleTotalAmount)
unitIdConsumptionList[unitId] = curUnitIdConsumption
unitIdDayConsumptionRedisKey = config.REDIS_CACHEKEY[
'UNIT_DAY_CONSUMPTION'] + '_' + str(
unitId) + '_' + str(date)
self.redisCache.set(unitIdDayConsumptionRedisKey,
curUnitIdConsumption)
self.redisCache.expire(unitIdDayConsumptionRedisKey, 600)
self.mylog('unitId:' + str(unitId) + ", date:" +
str(date) + ", consumption:" +
str(curUnitIdConsumption))
curAdinfoConsumption = adInfoDayConsumption + float(
deductibleTotalAmount)
adInfoConsumptionList[adId] = curAdinfoConsumption
adInfoDayConsumptionRedisKey = config.REDIS_CACHEKEY[
'ADINFO_DAY_CONSUMPTION'] + '_' + str(
adId) + '_' + str(date)
self.redisCache.set(adInfoDayConsumptionRedisKey,
curAdinfoConsumption)
self.redisCache.expire(adInfoDayConsumptionRedisKey, 600)
self.mylog('adId:' + str(adId) + ", date:" + str(date) +
", consumption:" + str(curAdinfoConsumption))
# 添加各个帐号消耗日志
if len(consume_log_sql_valuelist) != 0:
consume_log_sql_valuestr = ','.join(
consume_log_sql_valuelist)
consume_log_sql = 'insert into advertiser_account_consume_log ' + self.CONSUME_LOG_FIELD + 'values' + consume_log_sql_valuestr + ';'
self.insertRows(consume_log_sql, self.houseadDb)
# 提交db操作的增,改
self.houseadDb.commit()
self.mobgiCharge.commit()
except MySQLdb.Error as e:
self.mylog('exception: ' + "Mysql Error %d: %s" %
(e.args[0], e.args[1]))
self.mylog('sql:' + self.cur_sql)
self.mylog('rollback now!')
self.houseadDb.rollback()
self.mobgiCharge.rollback()
if accountTotalAmount - float(item['amount']) <= 0:
# need to do
self.mylog('关闭广告\n')
self.mylog('finish\n')
# print account_detail_info
lastId = int(item['id'])
cf = self.getConfigObject()
cf.set("account_consume", 'batchid', lastId)
cf.write(open(self.confname, "w"))
self.mylog('end AccountConsume.')
def __init__(self):
# Selenium headless
self.crawl = Crawl(delay=20, telegram_notify=False, gui=False)
self.mysql = MySQL()
def update(self, table, update_dict, where_dict={}, callback=None):
sql = MySQL.construct_update(table, update_dict, where_dict)
return (sql, )
class ImportDirect(object):
"""回调广告商通知激活"""
def __init__(self, filename, filetype):
self.initdb()
self.logfileHandle = open(config.LOGPATH + "import_direct.txt", 'a')
self.import_direct_fieldstr = '`imei`, `appkey`, `app_interest`, `pay_ability`, `game_frequency`'
self.filename = filename
self.filetype = filetype
def initdb(self):
self.db = MySQL(config.MOBGI_HOUSEAD['host'],
config.MOBGI_HOUSEAD['user'],
config.MYSQL['passwd'],
port=config.MOBGI_HOUSEAD['port'],
db=config.MOBGI_HOUSEAD['db'])
return self.db
def mylog(self, msg):
print time.strftime("%Y-%m-%d %H:%M:%S", time.localtime()) + " " + msg
self.logfileHandle.write(
time.strftime("%Y-%m-%d %H:%M:%S", time.localtime()) + " " + msg +
'\n')
def getRows(self, select_sql):
try:
self.db.queryNotCatch(select_sql)
except MySQLdb.Error as m:
self.mylog("db reconnect!")
self.initdb()
self.db.query(select_sql)
result = self.db.fetchAll()
return result
#新增记录
def insertRows(self, insert_sql):
self.cur_sql = insert_sql
try:
self.db.queryNotCatch(insert_sql)
return self.db.commit()
except MySQLdb.Error as m:
self.mylog("db reconnect!")
self.initdb()
self.db.queryNotCatch(insert_sql)
return self.db.commit()
def run(self):
self.mylog('start ImportDirect.')
appkey = config.APPKEY[self.filetype]
self.mylog('filename:' + self.filename)
data = xlrd.open_workbook(self.filename)
table = data.sheets()[0]
nrows = table.nrows #行数
ncols = table.ncols #列数
print nrows, ncols
for i in range(0, nrows):
imei = table.cell(i, 0).value
if type(imei) is types.FloatType: #科学计数转成int型
imei = int(imei)
interest = table.cell(i, 1).value
user_type = table.cell(i, 2).value
pay_type = table.cell(i, 3).value
for key in config.INTEREST:
# print key, config.INTEREST[key]
interest = interest.replace(str(key),
str(config.INTEREST[key]))
select_sql = 'select * from delivery_device_direct where imei="' + str(
imei) + '" and appkey= "' + appkey + '"'
result = self.getRows(select_sql)
if len(result):
self.mylog('imei:' + str(imei) + ', appkey:' + appkey +
" exist!")
else:
curTime = time.time()
insert_sql = 'insert into delivery_device_direct(`imei`, `appkey`, `app_interest`, `pay_ability`, `game_frequency`, `create_time`, `update_time`) value("' + str(
imei) + '", "' + str(appkey) + '","' + str(
interest) + '","' + str(pay_type) + '","' + str(
user_type) + '","' + str(curTime) + '","' + str(
curTime) + '");'
print insert_sql
self.insertRows(insert_sql)
print i
self.mylog('end ImportDirect.')
def delete(self, table, where_dict={}, callback=None):
sql = MySQL.construct_delete(table, where_dict)
return (sql,)
class ThirdReportRecordStore(object):
def __init__(self):
# 数据库参数
self.host = third_party_setting.MYSQL_REQUST['host']
self.user = third_party_setting.MYSQL_REQUST['user']
self.passwd = third_party_setting.MYSQL_REQUST['passwd']
self.port = third_party_setting.MYSQL_REQUST['port']
self.db = third_party_setting.MYSQL_REQUST['db']
self.table = third_party_setting.MYSQL_REQUST['table']
# 文件存放路径
self.sqlpath = os.path.join(os.getcwd(),
third_party_setting.SQLFILEPATH)
self.fieldlist = third_party_setting.FIELD_LIST
# 不用logging日志是因为上个目录已经使用logging了，非继承关系被覆盖
def mylog(self, msg):
print time.strftime("%Y-%m-%d %H:%M:%S", time.localtime()) + " " + msg
self.logfileHandle.write(
time.strftime("%Y-%m-%d %H:%M:%S", time.localtime()) + " " + msg +
'\n')
# 重连db
def trysql(self, sql):
try:
self.mylog("select sql:" + sql)
self.clickdb.queryNotCatch(sql)
except MySQLdb.Error as m:
self.mylog('masterdb reconnecting now')
self.clickdb = MySQL(self.host,
self.user,
self.passwd,
port=self.port,
db=self.db)
self.clickdb.query(sql)
return self.clickdb.fetchAll()
def saveDataToFile(self, streamlist):
try:
MIN = time.strftime("%Y%m%d%H%M", time.localtime())
cf = ConfigParser.ConfigParser()
confname = 'third_report_record.conf'
cf.read(confname)
curtable = 'record_id'
reocrd_id = cf.getint("third_report_data", curtable)
reocrd_id = int(reocrd_id)
#按年_月_日_时_分创建文件,一分钟一个文件
self.filename = self.sqlpath + "sql_content_" + MIN + ".sql"
file_object = codecs.open(self.filename, 'a', 'utf-8')
for stream in streamlist:
print stream
sqlcontent = ''
valueStr = ""
# 增加id
valueStr += str(reocrd_id) + '\t'
reocrd_id += 1
for field_key in self.fieldlist:
if stream.has_key(field_key):
valueStr += str(stream[field_key]) + '\t'
else:
valueStr += "" + '\t'
sqlcontent += valueStr.strip('\t') + "\n"
print sqlcontent
if sqlcontent != "":
file_object.write(sqlcontent)
file_object.close()
cf.set("third_report_data", curtable, reocrd_id)
cf.write(open(confname, "w"))
except Exception, e:
self.mylog("step1 save data to file failed! error:" + str(e))
def count(self, table, where_dict={}, callback=None):
sql = MySQL.construct_count(table, where_dict)
return (sql,)
def mysql_update_instance(self):
if self.arch == '主从':
rs = self._conn.getSlaveIP()
conn_slave = MySQL(ip=rs[1],
port=self.port,
user=self.user,
passwd=self.passwd)
slavestatus = conn_slave.getReplStatus() #嗲用的是show slave status
myinstance = self._cluster.mysql_instance
if slavestatus[3] == self.port: #代表主从是一致的
slave = myinstance.get_or_create(dbcluster_id=self.cluster_id,
m_ip=rs[1],
port=self.port)
slave[0].master_ip = slavestatus[1]
slave[0].master_port = slavestatus[3]
slave[0].role = u'从库'
# slave[0].db_status=u'仅从库'
slave[0].save()
master = myinstance.get_or_create(dbcluster_id=self.cluster_id,
m_ip=slavestatus[1],
port=self.port)
master[0].vist_ip = self.ip
master[0].role = u'主库'
master[0].db_status = u'服务中'
master[0].save()
else:
print(u'主从添加实例不对阿')
elif self.arch == u'单库':
myinstance = self._cluster.mysql_instance
single = myinstance.get_or_create(dbcluster_id=self.cluster_id,
m_ip=self.ip,
port=self.port)
single[0].vist_ip = self.ip
single[0].role = u'单库'
single[0].db_status = u'服务中'
single[0].save()
elif self.arch == u'innodb_cluster':
count, result, columns = self._conn.getInnoClusterInstance()
myinstance = self._cluster.mysql_instance
masterip = ''
master_port = ''
for i in range(count):
ip = result[i][2].split(':')[0]
port = result[i][1]
role_0 = result[i][3]
db_status = result[i][4]
clusertI = myinstance.get_or_create(
dbcluster_id=self.cluster_id, m_ip=ip, port=port)
clusertI[0].vist_ip = ip
if role_0 == u'PRIMARY':
clusertI[0].role = u'主库'
masterip = ip
master_port = port
else:
clusertI[0].role = u'从库'
clusertI[0].master_ip = masterip
clusertI[0].master_port = master_port
if db_status == u'ONLINE':
clusertI[0].db_status = u'服务中'
else:
clusertI[0].db_status = u'故障'
clusertI[0].save()
return True
def insert(self, table, value_dict, callback=None):
sql = MySQL.construct_insert(table, value_dict)
return (sql,)
def __init__(self, *args, **kwargs):
self._mysql = MySQL(**kwargs["db_config"])
super(MySQLTask,self).__init__(*args, **kwargs)
self.daemon = True
class MySQL_update(object):
def __init__(self, clusterid):
self._cluster = MySQLCluster.objects.get(id=clusterid)
self.cluster_id = clusterid
self.ip = self._cluster.foreign_ip
self.port = self._cluster.foreign_port
self.user = self._cluster.plat_user
self.arch = self._cluster.arch
self.passwd = self._cluster.plat_user_pass
self._conn = MySQL(ip=self.ip,
port=self.port,
user=self.user,
passwd=self.passwd)
def mysql_update_db(self):
#取出数据库信息
sql = '''
select
table_schema as 'databases',
sum(table_rows) as 'allrows',
sum(truncate(data_length / 1024 / 1024, 2)) as 'dbsize(MB)',
sum(truncate(index_length / 1024 / 1024, 2)) as 'indsize(MB)'
from information_schema.tables where TABLE_SCHEMA not in (
'mysql','sys','information_schema','performance_schema')
group by table_schema order
by sum(data_length) desc, sum(index_length) desc;
'''
rc, rs = self._conn.queryAll(sql=sql)
mysqldb = self._cluster.mysql_db
for ii in rs: #获取用户，更新用户
aa = mysqldb.get_or_create(dbname=ii[0],
dbcluster_id=self.cluster_id)
# aa[0].objects.update(db_rows=ii[1],db_size=ii[2],index_size=ii[3])
aa[0].db_rows = ii[1]
aa[0].db_size = ii[2]
aa[0].index_size = ii[3]
aa[0].save()
return True
def mysql_update_user(self):
#取出用户信息,这里过滤DBA使用的用户
sql = '''
select
user,
host from mysql.user where user not in('root','dba','backup','monitor','slave') and user not like 'mysql%' and user not like 'repli%';
'''
rc, rs = self._conn.queryAll(sql=sql)
mysql_user = self._cluster.mysql_user
for ii in rs:
sql2 = "show grants for %s@`%s`;" % (ii[0], ii[1])
rc2, rs2 = self._conn.queryAll(sql=sql2)
priv = ''
for jj in rs2:
priv = priv + jj[0] + ' ; '
aa = mysql_user.get_or_create(db_user=ii[0],
db_host=ii[1],
dbcluster_id=self.cluster_id)
aa[0].privlige = priv
aa[0].save()
return True
#
def mysql_update_instance(self):
if self.arch == '主从':
rs = self._conn.getSlaveIP()
conn_slave = MySQL(ip=rs[1],
port=self.port,
user=self.user,
passwd=self.passwd)
rs2 = conn_slave.getReplStatus()
myinstance = self._cluster.mysql_instance
if rs2[3] == self.port: #代表主从是一致的
slave = myinstance.get_or_create(dbcluster_id=self.cluster_id,
m_ip=rs[1],
port=self.port)
slave[0].master_ip = rs2[1]
slave[0].master_port = rs2[3]
slave[0].role = u'从库'
# slave[0].db_status=u'仅从库'
slave[0].save()
master = myinstance.get_or_create(dbcluster_id=self.cluster_id,
m_ip=rs2[1],
port=self.port)
master[0].vist_ip = self.ip
master[0].role = u'主库'
master[0].db_status = u'服务中'
master[0].save()
return True
#!coding=utf8
import time
import io
import utils.datetime as datetime
from utils.mysql import MySQL
pds = []
dns_db = MySQL(host="127.0.0.1",
user="******",
passwd="rootofmysql",
port=3307,
db="IPCIS_DNS_DB")
def read_data():
# 从文件中读取恶意域名信息
with io.open("../data/3mths.txt", "r", encoding="latin1") as f:
for line in f.readlines():
temp = line.strip().split("=")
temp[0] = temp[0].strip()
pds.append(temp[0])
def create_db():
sql = (
"CREATE TABLE IF NOT EXISTS domain_static ( domain_id INT(10) UNSIGNED NOT NULL DEFAULT 0,"
"primary_domain VARCHAR(128) NOT NULL DEFAULT '', "
"is_dga INT(10) UNSIGNED NOT NULL, "
"ttl INT(10) UNSIGNED NOT NULL, "
"credit INT(10) UNSIGNED, "