def main():
conn = pymysql.connect(host="localhost",
user="******",
password="******",
database="platform",
charset="utf8")
cursor = conn.cursor()
sql = 'select * from mass_terrace'
result = None
try:
cursor.execute(sql)
result = cursor.fetchall()
except:
print("Error: unable to fetch data")
analytics = GoogleConfig.get_ga()
print("right")
for row in result:
#获取数据库各平台id和GA视图ID
#通过视图ID获取当天流量,并拼接平台ID入库
t1 = (datetime.datetime.now() -
datetime.timedelta(days=1)).strftime('%Y-%m-%d')
t2 = (datetime.datetime.now() -
datetime.timedelta(days=1)).strftime('%Y-%m-%d')
response = GaGet.getMass_report(analytics, t1, t2, row[3])
#入库,放入response 和 平台ID
TranUtil.insert_massResponse(response, row[0])
cursor.close()
conn.close()
def check_en(analytics, conn, cursor, terraceId, viewId):
sql = 'select * from mass_entry a where a.terraceId = "%d" and a.ifCheck is null' % (terraceId)
entryResult = None
try:
cursor.execute(sql)
entryResult = cursor.fetchall()
except:
print("Error: unable to fetch data")
for eRow in entryResult:
print(eRow[3])
dateddd = eRow[3].strftime('%Y-%m-%d')
#如果为领峰系，先用领峰系自己的核对方式，查不到再用皇御系方式核对
if terraceId == 1:
response = GaGet.get_user_message_by_account_igold(analytics, viewId, dateddd, eRow[18] + '')
data = response.get('reports', [])[0].get('data', {})
if isinstance(data.get('rowCount'), int) is False:
response = GaGet.get_user_message_igold(analytics, viewId, dateddd, (eRow[17] + '')[:-1])
data = response.get('reports', [])[0].get('data', {})
if isinstance(data.get('rowCount'), int) is False:
response = GaGet.get_user_message(analytics, viewId, dateddd, (eRow[17] + '')[:-1])
data = response.get('reports', [])[0].get('data', {})
if isinstance(data.get('rowCount'), int) is False:
response = GaGet.get_user_message_by_account(analytics, viewId, dateddd, eRow[18] + '')
data = response.get('reports', [])[0].get('data', {})
elif terraceId == 3:
response = GaGet.get_user_message_by_account_acetop(analytics, viewId, dateddd, eRow[18] + '')
data = response.get('reports', [])[0].get('data', {})
if isinstance(data.get('rowCount'), int) is False:
response = GaGet.get_user_message(analytics, viewId, dateddd, (eRow[17] + '')[:-1])
data = response.get('reports', [])[0].get('data', {})
if isinstance(data.get('rowCount'), int) is False:
response = GaGet.get_user_message_by_account(analytics, viewId, dateddd, eRow[18] + '')
data = response.get('reports', [])[0].get('data', {})
else:
response = GaGet.get_user_message(analytics, viewId, dateddd, (eRow[17] + '')[:-1])
data = response.get('reports', [])[0].get('data', {})
if isinstance(data.get('rowCount'), int) is False:
response = GaGet.get_user_message_by_account(analytics, viewId, dateddd, eRow[18] + '')
data = response.get('reports', [])[0].get('data', {})
#如果确实查不到，到库中修改为已核对
if isinstance(data.get('rowCount'), int) is False:
updateSql = "UPDATE mass_entry SET ifCheck = 1 where id = '%d'" % (eRow[0])
try:
cursor.execute(updateSql)
conn.commit()
except:
conn.rollback()
continue
#查出来的话，就选取查出数据的第一条，判定是否为.com 或 not set ，如果是，就取另外一条，如果没有其他的，就依然取第一条
dataRows = data.get('rowCount', int)
trueRowNum = 0
if dataRows > 1:
for i in range(dataRows-1, -1, -1):
checkRow = data.get('rows', [])[i].get('dimensions', [])[1]
if checkRow.find(".com") > -1 or checkRow.find("not set") > -1 or checkRow.find("(none)") > -1:
continue
else:
trueRowNum = i
break
rowData = data.get('rows', [])[trueRowNum]
checkData = rowData.get('dimensions', [])
if len(checkData) > 1:
source = checkData[1].split('/')[0].rstrip()
medium = checkData[1].split('/')[1].lstrip()
updateSql = "UPDATE mass_entry SET extensionSource = '%s', source = '%s', medium = '%s', plan = '%s'," \
" unit = '%s', keyword = '%s', ifCheck = 1 WHERE id = %d" % (
checkData[1], source, medium, checkData[2], checkData[3], checkData[4], eRow[0])
try:
cursor.execute(updateSql)
conn.commit()
except:
conn.rollback()
print(checkData)
def check_re(analytics, conn, cursor, terraceId, viewId):
sql = 'select * from mass_register a where a.terraceId = "%d" and a.ifCheck is null' % (terraceId)
registerResult = None
try:
cursor.execute(sql)
registerResult = cursor.fetchall()
except:
print("Error: unable to fetch data")
for rRow in registerResult:
print(rRow[1])
dateddd = rRow[1].strftime('%Y-%m-%d')
# 如果为领峰系，先用领峰系自己的核对方式，查不到再用皇御系方式核对
if terraceId == 1:
response = GaGet.get_user_message_by_account_igold(analytics, viewId, dateddd, rRow[10] + '')
data = response.get('reports', [])[0].get('data', {})
if isinstance(data.get('rowCount'), int) is False:
response = GaGet.get_user_message_igold(analytics, viewId, dateddd, (rRow[9] + '')[:-1])
data = response.get('reports', [])[0].get('data', {})
if isinstance(data.get('rowCount'), int) is False:
response = GaGet.get_user_message(analytics, viewId, dateddd, (rRow[9] + '')[:-1])
data = response.get('reports', [])[0].get('data', {})
if isinstance(data.get('rowCount'), int) is False:
response = GaGet.get_user_message_by_account(analytics, viewId, dateddd, rRow[10] + '')
data = response.get('reports', [])[0].get('data', {})
elif terraceId == 3:
response = GaGet.get_user_message_by_account_acetop(analytics, viewId, dateddd, rRow[10] + '')
data = response.get('reports', [])[0].get('data', {})
if isinstance(data.get('rowCount'), int) is False:
response = GaGet.get_user_message(analytics, viewId, dateddd, (rRow[9] + '')[:-1])
data = response.get('reports', [])[0].get('data', {})
if isinstance(data.get('rowCount'), int) is False:
response = GaGet.get_user_message_by_account(analytics, viewId, dateddd, rRow[10] + '')
data = response.get('reports', [])[0].get('data', {})
else:
response = GaGet.get_user_message(analytics, viewId, dateddd, (rRow[9] + '')[:-1])
data = response.get('reports', [])[0].get('data', {})
if isinstance(data.get('rowCount'), int) is False:
response = GaGet.get_user_message_by_account(analytics, viewId, dateddd, rRow[10] + '')
data = response.get('reports', [])[0].get('data', {})
# 如果确实查不到，到库中修改为已核对
if isinstance(data.get('rowCount'), int) is False:
updateSql = "UPDATE mass_register SET ifCheck = 1 where id = '%d'" % (rRow[0])
try:
cursor.execute(updateSql)
conn.commit()
except:
conn.rollback()
continue
# 查出来的话，就选取查出数据的第一条，判定是否为.com 或 not set ，如果是，就取另外一条，如果没有其他的，就依然取第一条
dataRows = data.get('rowCount', int)
trueRowNum = 0
if dataRows > 1:
for i in range(dataRows-1, -1, -1):
checkRow = data.get('rows', [])[i].get('dimensions', [])[1]
if checkRow.find(".com") > -1 or checkRow.find("not set") > -1 or checkRow.find("(none)") > -1:
continue
else:
trueRowNum = i
break
rowData = data.get('rows', [])[trueRowNum]
checkData = rowData.get('dimensions', [])
if len(checkData) > 1:
source = checkData[1].split('/')[0].rstrip()
medium = checkData[1].split('/')[1].lstrip()
isTran = ""
if "ref|org" in medium and terraceId == 3:
print("is SEO")
# 到acetop GA中核对
response = GaGet.get_user_message_by_account_acetop_owm(analytics,'181239932', dateddd, rRow[10] + '')
data = response.get('reports', [])[0].get('data', {})
if isinstance(data.get('rowCount'), int) is True:
cRowDates = data.get('rows', [])
for cRowDate in cRowDates:
if checkRow.get('dimensions', [])[0].find("800.igoldhk.com") > -1:
isTran = "igold"
updateSql = "UPDATE mass_register SET mass = '%s', source = '%s', medium = '%s', plan = '%s', unit = '%s', keyword = '%s', ifCheck = 1 WHERE id = %d" % (
checkData[1], source, medium, checkData[2], checkData[3], checkData[4], rRow[0])
try:
cursor.execute(updateSql)
conn.commit()
except:
conn.rollback()
print(checkData)
def main():
analytics = GoogleConfig.get_ga()
print(111)
conn = pymysql.connect(host="localhost",
user="******",
password="******",
database="platform",
charset="utf8")
cursor = conn.cursor()
sql = 'select * from mass_returncall a where a.ifCheck is null'
registerResult = None
try:
cursor.execute(sql)
registerResult = cursor.fetchall()
except:
print("Error: unable to fetch data")
for rRow in registerResult:
print(rRow[3])
dateddd = rRow[3].strftime('%Y-%m-%d')
terraceId = rRow[19]
if terraceId == 1:
viewId = '185946517'
elif terraceId == 2:
viewId = '162478221'
elif terraceId == 3:
viewId = '185946517'
elif terraceId == 4:
viewId = '197372064'
if terraceId == 1:
response = GaGet.get_user_message_by_account_igold(
analytics, viewId, dateddd, rRow[18] + '')
data = response.get('reports', [])[0].get('data', {})
if isinstance(data.get('rowCount'), int) is False:
response = GaGet.get_user_message_igold(
analytics, viewId, dateddd, (rRow[17] + '')[:-1])
data = response.get('reports', [])[0].get('data', {})
if isinstance(data.get('rowCount'), int) is False:
response = GaGet.get_user_message(analytics, viewId, dateddd,
(rRow[17] + '')[:-1])
data = response.get('reports', [])[0].get('data', {})
if isinstance(data.get('rowCount'), int) is False:
response = GaGet.get_user_message_by_account(
analytics, viewId, dateddd, rRow[18] + '')
data = response.get('reports', [])[0].get('data', {})
elif terraceId == 3:
response = GaGet.get_user_message_by_account_acetop(
analytics, viewId, dateddd, rRow[18] + '')
data = response.get('reports', [])[0].get('data', {})
if isinstance(data.get('rowCount'), int) is False:
response = GaGet.get_user_message(analytics, viewId, dateddd,
(rRow[17] + '')[:-1])
data = response.get('reports', [])[0].get('data', {})
if isinstance(data.get('rowCount'), int) is False:
response = GaGet.get_user_message_by_account(
analytics, viewId, dateddd, rRow[18] + '')
data = response.get('reports', [])[0].get('data', {})
else:
response = GaGet.get_user_message(analytics, viewId, dateddd,
(rRow[17] + '')[:-1])
data = response.get('reports', [])[0].get('data', {})
if isinstance(data.get('rowCount'), int) is False:
response = GaGet.get_user_message_by_account(
analytics, viewId, dateddd, rRow[18] + '')
data = response.get('reports', [])[0].get('data', {})
# 如果确实查不到，到库中修改为已核对
if isinstance(data.get('rowCount'), int) is False:
updateSql = "UPDATE mass_returncall SET ifCheck = 1 where id = '%d'" % (
rRow[0])
try:
cursor.execute(updateSql)
conn.commit()
except:
conn.rollback()
continue
# 查出来的话，就选取查出数据的第一条，判定是否为.com 或 not set ，如果是，就取另外一条，如果没有其他的，就依然取第一条
dataRows = data.get('rowCount', int)
trurRowNum = 0
if dataRows > 1:
for i in range(0, dataRows):
checkRow = data.get('rows', [])[i].get('dimensions', [])[1]
if checkRow.find(".com") > -1 or checkRow.find(
"not set") > -1 or checkRow.find("(none)") > -1:
continue
else:
trurRowNum = i
break
rowData = data.get('rows', [])[trurRowNum]
checkData = rowData.get('dimensions', [])
if len(checkData) > 1:
source = checkData[1].split('/')[0].rstrip()
medium = checkData[1].split('/')[1].lstrip()
updateSql = "UPDATE mass_returncall SET extensionSource = '%s', source = '%s', medium = '%s', plan = '%s'," \
" unit = '%s', keyword = '%s', ifCheck = 1 WHERE id = %d" % (
checkData[1], source, medium, checkData[2], checkData[3], checkData[4], rRow[0])
try:
cursor.execute(updateSql)
conn.commit()
except:
conn.rollback()
print(checkData)
def check_en(analytics, conn, cursor, terraceId, viewId, t1 ,t2):
try:
sql = 'select * from mass_entry a where a.terraceId = "%d" and a.entryDate >= "%s" and a.entryDate<= "%s" and a.ifCheck is null order by a.registerDate' % (terraceId, t1, t2)
entryResult = None
try:
cursor.execute(sql)
entryResult = cursor.fetchall()
except:
logger.info("Error: unable to fetch data")
daysRows = {}
daysList = []
for eRow in entryResult:
dateKey = eRow[3].strftime('%Y-%m-%d')
daysList.append(dateKey)
daysList = list(set(daysList))
for daysKey in daysList:
daysRows[daysKey] = []
for eRow in entryResult:
entDate = eRow[3].strftime('%Y-%m-%d')
daysRows[entDate].append(eRow)
for key in daysRows:
logger.info(key)
oneDayRows = daysRows[key]
if terraceId == 1:
accounts = ""
for rr in oneDayRows:
accounts = accounts + rr[18] + "|"
if accounts != "":
response = GaGet.get_user_message_by_account_igold(analytics, viewId, key, accounts[:-1])
#处理获得的数据
checkRullE(response, oneDayRows, conn, cursor, 18)
elif terraceId == 3:
accounts = ""
for rr in oneDayRows:
accounts = accounts + rr[18] + "|"
if accounts != "":
response = GaGet.get_user_message_by_account_acetop(analytics, viewId, key, accounts[:-1])
checkRullE(response, oneDayRows, conn, cursor, 18)
gaIds = ""
for rr in oneDayRows:
if rr[17] == "undefined.undefined":
continue
gaIds = gaIds + rr[17] + "|"
if gaIds != "":
response = GaGet.get_user_message(analytics, viewId, key, gaIds[:-1])
checkRullE(response, oneDayRows, conn, cursor, 17)
accounts = ""
for rr in oneDayRows:
accounts = accounts + rr[18] + "|"
if accounts != "":
response = GaGet.get_user_message_by_account(analytics, viewId, key, accounts[:-1])
checkRullE(response, oneDayRows, conn, cursor, 18)
if len(oneDayRows) > 0:
checkIds = ""
for rr in oneDayRows:
checkIds = checkIds + str(rr[0]) + ","
updateSql = "UPDATE mass_entry SET ifCheck = 1 where id in (%s)" % (checkIds[:-1])
try:
cursor.execute(updateSql)
conn.commit()
except:
conn.rollback()
except:
logger.error("ga入金核对出现问题,重新请求")
check_en(analytics, conn, cursor, terraceId, viewId, t1, t2)
def check_re(analytics, conn, cursor, terraceId, viewId, t1, t2):
try:
sql = 'select * from mass_register a where a.terraceId = "%d" and a.registerDate >= "%s" and a.registerDate<= "%s" and a.ifCheck is null order by a.registerDate' % (terraceId, t1, t2)
registerResult = None
try:
cursor.execute(sql)
registerResult = cursor.fetchall()
except:
logger.info("Error: unable to fetch data")
t1Date = datetime.datetime.strptime(t1, '%Y-%m-%d %H:%M:%S')
t2Date = datetime.datetime.strptime(t2, '%Y-%m-%d %H:%M:%S')
days = (t2Date-t1Date).days+1
daysRows = {}
for day in range(days):
dateDate = (t1Date + datetime.timedelta(days=day)).strftime('%Y-%m-%d')
daysRows[dateDate] = []
for rRow in registerResult:
regDate = rRow[1].strftime('%Y-%m-%d')
daysRows[regDate].append(rRow)
for key in daysRows:
logger.info(key)
oneDayRows = daysRows[key]
if terraceId == 1:
accounts = ""
for rr in oneDayRows:
accounts = accounts + rr[10] + "|"
if accounts != "":
response = GaGet.get_user_message_by_account_igold(analytics, viewId, key, accounts[:-1])
#处理获得的数据
checkRull(response, oneDayRows, conn, cursor, 10)
elif terraceId == 3:
accounts = ""
for rr in oneDayRows:
accounts = accounts + rr[10] + "|"
if accounts != "":
response = GaGet.get_user_message_by_account_acetop(analytics, viewId, key, accounts[:-1])
checkRull(response, oneDayRows, conn, cursor, 10)
gaIds = ""
for rr in oneDayRows:
if rr[9] == "undefined.undefined":
continue
gaIds = gaIds + rr[9] + "|"
if gaIds != "":
response = GaGet.get_user_message(analytics, viewId, key, gaIds[:-1])
checkRull(response, oneDayRows, conn, cursor, 9)
accounts = ""
for rr in oneDayRows:
accounts = accounts + rr[10] + "|"
if accounts != "":
response = GaGet.get_user_message_by_account(analytics, viewId, key, accounts[:-1])
checkRull(response, oneDayRows, conn, cursor, 10)
if len(oneDayRows) > 0:
checkIds = ""
for rr in oneDayRows:
checkIds = checkIds + str(rr[0]) + ","
updateSql = "UPDATE mass_register SET ifCheck = 1 where id in (%s)" % (checkIds[:-1])
try:
cursor.execute(updateSql)
conn.commit()
except:
conn.rollback()
except:
logger.error("ga注册核对出现问题,重新请求")
check_re(analytics, conn, cursor, terraceId, viewId, t1, t2)
def check_re(analytics, conn, cursor, terraceId, viewId):
sql = 'select * from mass_register_all a where a.terraceId = "%d" and a.ifCheck is null order by a.registerDate' % (
terraceId)
registerResult = None
try:
cursor.execute(sql)
registerResult = cursor.fetchall()
except:
print("Error: unable to fetch data")
reDate = None
oneDayRows = []
for index, rRow in enumerate(registerResult):
if index == 0:
reDate = rRow[1].strftime('%Y-%m-%d')
oneDayRows.append(rRow)
else:
if reDate == rRow[1].strftime(
'%Y-%m-%d') and index != len(registerResult) - 1:
oneDayRows.append(rRow)
else:
if index == len(registerResult) - 1 and reDate == rRow[
1].strftime('%Y-%m-%d'):
oneDayRows.append(rRow)
print(reDate)
if terraceId == 1:
#第一种方式
accounts = ""
for rr in oneDayRows:
accounts = accounts + rr[10] + "|"
response = GaGet.get_user_message_by_account_igold(
analytics, viewId, reDate, accounts[:-1])
data = response.get('reports', [])[0].get('data', {})
if isinstance(data.get('rowCount'), int) is True:
rowDatas = data.get('rows', [])
deleteList = []
for rIndex, rr in enumerate(oneDayRows):
trueRow = None
for i, rowData in enumerate(rowDatas):
if rr[10] in rowData.get('dimensions', [])[0]:
checkRow = rowData.get('dimensions', [])[1]
if checkRow.find(
".com") > -1 or checkRow.find(
"not set"
) > -1 or checkRow.find(
"(none)") > -1:
trueRow = i
continue
else:
trueRow = i
break
if trueRow is None:
continue
rowData = rowDatas[trueRow]
checkData = rowData.get('dimensions', [])
if len(checkData) > 1:
source = checkData[1].split('/')[0].rstrip()
medium = checkData[1].split('/')[1].lstrip()
updateSql = "UPDATE mass_register_all SET mass = '%s', source = '%s', medium = '%s', plan = '%s', unit = '%s', keyword = '%s', ifCheck = 1 WHERE id = %d" % (
checkData[1], source, medium, checkData[2],
checkData[3], checkData[4], rr[0])
try:
cursor.execute(updateSql)
conn.commit()
except:
conn.rollback()
deleteList.append(rIndex)
print(checkData)
deleteList.sort(reverse=True)
for delIndex in deleteList:
del oneDayRows[delIndex]
#第二种方式
gaIds = ""
for rr in oneDayRows:
gaIds = gaIds + rr[9] + "|"
response = GaGet.get_user_message_igold(
analytics, viewId, reDate, gaIds[:-1])
data = response.get('reports', [])[0].get('data', {})
if isinstance(data.get('rowCount'), int) is True:
rowDatas = data.get('rows', [])
deleteList = []
for rIndex, rr in enumerate(oneDayRows):
trueRow = None
for i, rowData in enumerate(rowDatas):
if rr[9] in rowData.get('dimensions', [])[0]:
checkRow = rowData.get('dimensions', [])[1]
if checkRow.find(
".com") > -1 or checkRow.find(
"not set"
) > -1 or checkRow.find(
"(none)") > -1:
trueRow = i
continue
else:
trueRow = i
break
if trueRow is None:
continue
rowData = rowDatas[trueRow]
checkData = rowData.get('dimensions', [])
if len(checkData) > 1:
source = checkData[1].split('/')[0].rstrip()
medium = checkData[1].split('/')[1].lstrip()
updateSql = "UPDATE mass_register_all SET mass = '%s', source = '%s', medium = '%s', plan = '%s', unit = '%s', keyword = '%s', ifCheck = 1 WHERE id = %d" % (
checkData[1], source, medium, checkData[2],
checkData[3], checkData[4], rr[0])
try:
cursor.execute(updateSql)
conn.commit()
except:
conn.rollback()
deleteList.append(rIndex)
print(checkData)
deleteList.sort(reverse=True)
for delIndex in deleteList:
del oneDayRows[delIndex]
#第三种方法
gaIds = ""
for rr in oneDayRows:
gaIds = gaIds + rr[9] + "|"
response = GaGet.get_user_message(analytics, viewId,
reDate, gaIds[:-1])
data = response.get('reports', [])[0].get('data', {})
if isinstance(data.get('rowCount'), int) is True:
rowDatas = data.get('rows', [])
deleteList = []
for rIndex, rr in enumerate(oneDayRows):
trueRow = None
for i, rowData in enumerate(rowDatas):
if rr[9] in rowData.get('dimensions', [])[0]:
checkRow = rowData.get('dimensions', [])[1]
if checkRow.find(
".com") > -1 or checkRow.find(
"not set"
) > -1 or checkRow.find(
"(none)") > -1:
trueRow = i
continue
else:
trueRow = i
break
if trueRow is None:
continue
rowData = rowDatas[trueRow]
checkData = rowData.get('dimensions', [])
if len(checkData) > 1:
source = checkData[1].split('/')[0].rstrip()
medium = checkData[1].split('/')[1].lstrip()
updateSql = "UPDATE mass_register_all SET mass = '%s', source = '%s', medium = '%s', plan = '%s', unit = '%s', keyword = '%s', ifCheck = 1 WHERE id = %d" % (
checkData[1], source, medium, checkData[2],
checkData[3], checkData[4], rr[0])
try:
cursor.execute(updateSql)
conn.commit()
except:
conn.rollback()
deleteList.append(rIndex)
print(checkData)
deleteList.sort(reverse=True)
for delIndex in deleteList:
del oneDayRows[delIndex]
# 第四种方式
accounts = ""
for rr in oneDayRows:
accounts = accounts + rr[10] + "|"
response = GaGet.get_user_message_by_account(
analytics, viewId, reDate, accounts[:-1])
data = response.get('reports', [])[0].get('data', {})
if isinstance(data.get('rowCount'), int) is True:
rowDatas = data.get('rows', [])
deleteList = []
for rIndex, rr in enumerate(oneDayRows):
trueRow = None
for i, rowData in enumerate(rowDatas):
if rr[10] in rowData.get('dimensions', [])[0]:
checkRow = rowData.get('dimensions', [])[1]
if checkRow.find(
".com") > -1 or checkRow.find(
"not set"
) > -1 or checkRow.find(
"(none)") > -1:
trueRow = i
continue
else:
trueRow = i
break
if trueRow is None:
continue
rowData = rowDatas[trueRow]
checkData = rowData.get('dimensions', [])
if len(checkData) > 1:
source = checkData[1].split('/')[0].rstrip()
medium = checkData[1].split('/')[1].lstrip()
updateSql = "UPDATE mass_register_all SET mass = '%s', source = '%s', medium = '%s', plan = '%s', unit = '%s', keyword = '%s', ifCheck = 1 WHERE id = %d" % (
checkData[1], source, medium, checkData[2],
checkData[3], checkData[4], rr[0])
try:
cursor.execute(updateSql)
conn.commit()
except:
conn.rollback()
deleteList.append(rIndex)
print(checkData)
deleteList.sort(reverse=True)
for delIndex in deleteList:
del oneDayRows[delIndex]
if len(oneDayRows) > 0:
checkIds = ""
for rr in oneDayRows:
checkIds = checkIds + str(rr[0]) + ","
updateSql = "UPDATE mass_register_all SET ifCheck = 1 where id in (%s)" % (
checkIds[:-1])
try:
cursor.execute(updateSql)
conn.commit()
except:
conn.rollback()
elif terraceId == 2:
gaIds = ""
for rr in oneDayRows:
gaIds = gaIds + rr[9] + "|"
response = GaGet.get_user_message(analytics, viewId,
reDate, gaIds[:-1])
data = response.get('reports', [])[0].get('data', {})
if isinstance(data.get('rowCount'), int) is True:
rowDatas = data.get('rows', [])
deleteList = []
for rIndex, rr in enumerate(oneDayRows):
trueRow = None
for i, rowData in enumerate(rowDatas):
if rr[9] in rowData.get('dimensions', [])[0]:
checkRow = rowData.get('dimensions', [])[1]
if checkRow.find(
".com") > -1 or checkRow.find(
"not set"
) > -1 or checkRow.find(
"(none)") > -1:
trueRow = i
continue
else:
trueRow = i
break
if trueRow is None:
continue
rowData = rowDatas[trueRow]
checkData = rowData.get('dimensions', [])
if len(checkData) > 1:
source = checkData[1].split('/')[0].rstrip()
medium = checkData[1].split('/')[1].lstrip()
updateSql = "UPDATE mass_register_all SET mass = '%s', source = '%s', medium = '%s', plan = '%s', unit = '%s', keyword = '%s', ifCheck = 1 WHERE id = %d" % (
checkData[1], source, medium, checkData[2],
checkData[3], checkData[4], rr[0])
try:
cursor.execute(updateSql)
conn.commit()
except:
conn.rollback()
deleteList.append(rIndex)
print(checkData)
deleteList.sort(reverse=True)
for delIndex in deleteList:
del oneDayRows[delIndex]
# 第四种方式
accounts = ""
for rr in oneDayRows:
accounts = accounts + rr[10] + "|"
response = GaGet.get_user_message_by_account(
analytics, viewId, reDate, accounts[:-1])
data = response.get('reports', [])[0].get('data', {})
if isinstance(data.get('rowCount'), int) is True:
rowDatas = data.get('rows', [])
deleteList = []
for rIndex, rr in enumerate(oneDayRows):
trueRow = None
for i, rowData in enumerate(rowDatas):
if rr[10] in rowData.get('dimensions', [])[0]:
checkRow = rowData.get('dimensions', [])[1]
if checkRow.find(
".com") > -1 or checkRow.find(
"not set"
) > -1 or checkRow.find(
"(none)") > -1:
trueRow = i
continue
else:
trueRow = i
break
if trueRow is None:
continue
rowData = rowDatas[trueRow]
checkData = rowData.get('dimensions', [])
if len(checkData) > 1:
source = checkData[1].split('/')[0].rstrip()
medium = checkData[1].split('/')[1].lstrip()
updateSql = "UPDATE mass_register_all SET mass = '%s', source = '%s', medium = '%s', plan = '%s', unit = '%s', keyword = '%s', ifCheck = 1 WHERE id = %d" % (
checkData[1], source, medium, checkData[2],
checkData[3], checkData[4], rr[0])
try:
cursor.execute(updateSql)
conn.commit()
except:
conn.rollback()
deleteList.append(rIndex)
print(checkData)
deleteList.sort(reverse=True)
for delIndex in deleteList:
del oneDayRows[delIndex]
if len(oneDayRows) > 0:
checkIds = ""
for rr in oneDayRows:
checkIds = checkIds + str(rr[0]) + ","
updateSql = "UPDATE mass_register_all SET ifCheck = 1 where id in (%s)" % (
checkIds[:-1])
try:
cursor.execute(updateSql)
conn.commit()
except:
conn.rollback()
elif terraceId == 3:
# 第一种方式
accounts = ""
for rr in oneDayRows:
accounts = accounts + rr[10] + "|"
response = GaGet.get_user_message_by_account_acetop(
analytics, viewId, reDate, accounts[:-1])
data = response.get('reports', [])[0].get('data', {})
if isinstance(data.get('rowCount'), int) is True:
rowDatas = data.get('rows', [])
deleteList = []
for rIndex, rr in enumerate(oneDayRows):
trueRow = None
for i, rowData in enumerate(rowDatas):
if rr[10] in rowData.get('dimensions', [])[0]:
checkRow = rowData.get('dimensions', [])[1]
if checkRow.find(
".com") > -1 or checkRow.find(
"not set"
) > -1 or checkRow.find(
"(none)") > -1:
trueRow = i
continue
else:
trueRow = i
break
if trueRow is None:
continue
rowData = rowDatas[trueRow]
checkData = rowData.get('dimensions', [])
if len(checkData) > 1:
source = checkData[1].split('/')[0].rstrip()
medium = checkData[1].split('/')[1].lstrip()
updateSql = "UPDATE mass_register_all SET mass = '%s', source = '%s', medium = '%s', plan = '%s', unit = '%s', keyword = '%s', ifCheck = 1 WHERE id = %d" % (
checkData[1], source, medium, checkData[2],
checkData[3], checkData[4], rr[0])
try:
cursor.execute(updateSql)
conn.commit()
except:
conn.rollback()
deleteList.append(rIndex)
print(checkData)
deleteList.sort(reverse=True)
for delIndex in deleteList:
del oneDayRows[delIndex]
# 第三种方法
gaIds = ""
for rr in oneDayRows:
gaIds = gaIds + rr[9] + "|"
response = GaGet.get_user_message(analytics, viewId,
reDate, gaIds[:-1])
data = response.get('reports', [])[0].get('data', {})
if isinstance(data.get('rowCount'), int) is True:
rowDatas = data.get('rows', [])
deleteList = []
for rIndex, rr in enumerate(oneDayRows):
trueRow = None
for i, rowData in enumerate(rowDatas):
if rr[9] in rowData.get('dimensions', [])[0]:
checkRow = rowData.get('dimensions', [])[1]
if checkRow.find(
".com") > -1 or checkRow.find(
"not set"
) > -1 or checkRow.find(
"(none)") > -1:
trueRow = i
continue
else:
trueRow = i
break
if trueRow is None:
continue
rowData = rowDatas[trueRow]
checkData = rowData.get('dimensions', [])
if len(checkData) > 1:
source = checkData[1].split('/')[0].rstrip()
medium = checkData[1].split('/')[1].lstrip()
updateSql = "UPDATE mass_register_all SET mass = '%s', source = '%s', medium = '%s', plan = '%s', unit = '%s', keyword = '%s', ifCheck = 1 WHERE id = %d" % (
checkData[1], source, medium, checkData[2],
checkData[3], checkData[4], rr[0])
try:
cursor.execute(updateSql)
conn.commit()
except:
conn.rollback()
deleteList.append(rIndex)
print(checkData)
deleteList.sort(reverse=True)
for delIndex in deleteList:
del oneDayRows[delIndex]
# 第四种方式
accounts = ""
for rr in oneDayRows:
accounts = accounts + rr[10] + "|"
response = GaGet.get_user_message_by_account(
analytics, viewId, reDate, accounts[:-1])
data = response.get('reports', [])[0].get('data', {})
if isinstance(data.get('rowCount'), int) is True:
rowDatas = data.get('rows', [])
deleteList = []
for rIndex, rr in enumerate(oneDayRows):
trueRow = None
for i, rowData in enumerate(rowDatas):
if rr[10] in rowData.get('dimensions', [])[0]:
checkRow = rowData.get('dimensions', [])[1]
if checkRow.find(
".com") > -1 or checkRow.find(
"not set"
) > -1 or checkRow.find(
"(none)") > -1:
trueRow = i
continue
else:
trueRow = i
break
if trueRow is None:
continue
rowData = rowDatas[trueRow]
checkData = rowData.get('dimensions', [])
if len(checkData) > 1:
source = checkData[1].split('/')[0].rstrip()
medium = checkData[1].split('/')[1].lstrip()
updateSql = "UPDATE mass_register_all SET mass = '%s', source = '%s', medium = '%s', plan = '%s', unit = '%s', keyword = '%s', ifCheck = 1 WHERE id = %d" % (
checkData[1], source, medium, checkData[2],
checkData[3], checkData[4], rr[0])
try:
cursor.execute(updateSql)
conn.commit()
except:
conn.rollback()
deleteList.append(rIndex)
print(checkData)
deleteList.sort(reverse=True)
for delIndex in deleteList:
del oneDayRows[delIndex]
if len(oneDayRows) > 0:
checkIds = ""
for rr in oneDayRows:
checkIds = checkIds + str(rr[0]) + ","
updateSql = "UPDATE mass_register_all SET ifCheck = 1 where id in (%s)" % (
checkIds[:-1])
try:
cursor.execute(updateSql)
conn.commit()
except:
conn.rollback()
elif terraceId == 4:
gaIds = ""
for rr in oneDayRows:
gaIds = gaIds + rr[9] + "|"
response = GaGet.get_user_message(analytics, viewId,
reDate, gaIds[:-1])
data = response.get('reports', [])[0].get('data', {})
if isinstance(data.get('rowCount'), int) is True:
rowDatas = data.get('rows', [])
deleteList = []
for rIndex, rr in enumerate(oneDayRows):
trueRow = None
for i, rowData in enumerate(rowDatas):
if rr[9] in rowData.get('dimensions', [])[0]:
checkRow = rowData.get('dimensions', [])[1]
if checkRow.find(
".com") > -1 or checkRow.find(
"not set"
) > -1 or checkRow.find(
"(none)") > -1:
trueRow = i
continue
else:
trueRow = i
break
if trueRow is None:
continue
rowData = rowDatas[trueRow]
checkData = rowData.get('dimensions', [])
if len(checkData) > 1:
source = checkData[1].split('/')[0].rstrip()
medium = checkData[1].split('/')[1].lstrip()
updateSql = "UPDATE mass_register_all SET mass = '%s', source = '%s', medium = '%s', plan = '%s', unit = '%s', keyword = '%s', ifCheck = 1 WHERE id = %d" % (
checkData[1], source, medium, checkData[2],
checkData[3], checkData[4], rr[0])
try:
cursor.execute(updateSql)
conn.commit()
except:
conn.rollback()
deleteList.append(rIndex)
print(checkData)
deleteList.sort(reverse=True)
for delIndex in deleteList:
del oneDayRows[delIndex]
# 第四种方式
accounts = ""
for rr in oneDayRows:
accounts = accounts + rr[10] + "|"
response = GaGet.get_user_message_by_account(
analytics, viewId, reDate, accounts[:-1])
data = response.get('reports', [])[0].get('data', {})
if isinstance(data.get('rowCount'), int) is True:
rowDatas = data.get('rows', [])
deleteList = []
for rIndex, rr in enumerate(oneDayRows):
trueRow = None
for i, rowData in enumerate(rowDatas):
if rr[10] in rowData.get('dimensions', [])[0]:
checkRow = rowData.get('dimensions', [])[1]
if checkRow.find(
".com") > -1 or checkRow.find(
"not set"
) > -1 or checkRow.find(
"(none)") > -1:
trueRow = i
continue
else:
trueRow = i
break
if trueRow is None:
continue
rowData = rowDatas[trueRow]
checkData = rowData.get('dimensions', [])
if len(checkData) > 1:
source = checkData[1].split('/')[0].rstrip()
medium = checkData[1].split('/')[1].lstrip()
updateSql = "UPDATE mass_register_all SET mass = '%s', source = '%s', medium = '%s', plan = '%s', unit = '%s', keyword = '%s', ifCheck = 1 WHERE id = %d" % (
checkData[1], source, medium, checkData[2],
checkData[3], checkData[4], rr[0])
try:
cursor.execute(updateSql)
conn.commit()
except:
conn.rollback()
deleteList.append(rIndex)
print(checkData)
deleteList.sort(reverse=True)
for delIndex in deleteList:
del oneDayRows[delIndex]
if len(oneDayRows) > 0:
checkIds = ""
for rr in oneDayRows:
checkIds = checkIds + str(rr[0]) + ","
updateSql = "UPDATE mass_register_all SET ifCheck = 1 where id in (%s)" % (
checkIds[:-1])
try:
cursor.execute(updateSql)
conn.commit()
except:
conn.rollback()
#重新赋日期
if index != len(registerResult) - 1:
reDate = rRow[1].strftime('%Y-%m-%d')
oneDayRows.clear()
oneDayRows.append(rRow)