def getThisMonthSpend(self): if self.local_dates.today.date().day == 1: return 0 if self.budget_group_id: query = """ select sum(cost) from campaign_performance where account_id = '%s' and date_range = 'THIS_MONTH' and campaign_id in (%s) """ % (self.account_id, ','.join([ "'" + campaign_id + "'" for campaign_id in self.budget_group_info['campaign_ids'] ])) rows = Database().executeQuery(query) for row in rows: cost = row[0] if cost is None: return 0 return cost query = "select sum(cost) from account_performance_reports where account_id = '%s' " % ( self.account_id) query += " and date >= '%s' and date <= '%s' " % ( self.local_dates.first_date_of_this_month, self.local_dates.yesterday) result = (Database()).createEngine().execute(query) for row in result: this_month_cost = row[0] if this_month_cost is None: return 0 return this_month_cost
def getUserIdsToProcess(self, first_run): """Get user_ids which need accounts based on whether accounts already exist. If first_run is false return all users. """ user_ids = Database().getValues("users", "id") if not first_run: return [user_id[0] for user_id in user_ids] user_ids_to_process = [] for user_id in user_ids: query = """ SELECT users.id FROM users join accounts on accounts.user_id = users.id where users.id = '%s' """ % (user_id[0]) result = Database().executeQuery(query) has_accounts = False for row in result: has_accounts = True break if has_accounts is False: user_ids_to_process.append(user_id[0]) return user_ids_to_process
def createBudgetCommanderTable(self): """Create budget commander table for testing. A migration will handle this in production""" if self.envvars["APP_ENV"] != "local": return if self.budgetCommanderTableExists(): return columns = [ "account_id", "email_sent", "email_addresses", "notify_via_email", "pause_campaigns", "enable_campaigns", "control_spend", "rollover_budget", "day_paused_campaigns", "month_paused_campaigns" ] data = [[ self.account_id, 0, "[[email protected], [email protected]]", 1, 0, 1, 1, 1, "", "" ]] df = pd.DataFrame(data, columns=columns) df.to_sql(name="budget_commander", con=(Database()).createEngine(), index=False, if_exists="replace") with (Database()).createEngine().connect() as con: con.execute( 'alter table `budget_commander` modify account_id varchar(255)' ) con.execute( 'ALTER TABLE `budget_commander` ADD PRIMARY KEY (`account_id`);' )
def dataFrameFromAdPerformanceReports(self, date_range): database = Database() table_name = 'advert_performance' columns = list( pd.read_sql( "select * from %s where id = '999999999'" % (table_name), database.createEngine()).columns) columns = ['advert_performance.' + column for column in columns] query = """ select adverts.ad_type,adverts.adgroup_id,adverts.final_urls, %s, adverts.headline_1, adverts.headline_2, adverts.headline_3, adverts.description, adverts.description_2, adverts.path_1, adverts.path_2 from advert_performance join adverts on adverts.id = advert_performance.advert_id where adverts.account_id = '%s' and advert_performance.date_range = '%s' """ % (','.join(columns), self.account_id, date_range) ad_performance_df_chunks = pd.read_sql_query(query, Database().createEngine(), chunksize=2000) return ad_performance_df_chunks
def __init__(self, download_dir='/tmp/download/'): super(DouyinDownloader, self).__init__() # self.arg = arg self.downloadCount = 0 self.uploadCount = 0 if download_dir.endswith('/'): self.downloadDir = download_dir else: self.downloadDir = download_dir + '/' # redis connect self.redisClient = redis.StrictRedis(host='115.159.157.98', port=17379, db=0, password='******') # mysql self.mysqlDB = Database(host='localhost', user='******', passwd='zx#Video2018', database='video') # nextcloud self.oc = owncloud.Client('http://127.0.0.1:18080') self.oc.login('zhangxu', 'zx@12346') self.oc.logout() self.oc2 = owncloud.Client('http://115.29.34.236:18181') self.oc2.login('zhangxu', 'zx@12346') self.oc2.logout()
def writeToDatabase(self, ad_count, priority, message, adgroup_id): Database().executeQuery( "update adgroups set ad_count = %s where id = '%s' and account_id = '%s'" % (ad_count, adgroup_id, self.account_id)) Database().executeQuery( "update adgroups set priority = %s where id = '%s' and account_id = '%s'" % (priority, adgroup_id, self.account_id)) Database().executeQuery( "update adgroups set message = '%s' where id = '%s' and account_id = '%s'" % (message, adgroup_id, self.account_id))
def keywords(account_id): """Delete keywords""" adgroup_ids = Database().getValues('adgroups', 'id') adgroup_ids = ['"%s"' % (adgroup_id[0]) for adgroup_id in adgroup_ids] query = """ delete from keywords where account_id = '%s' and adgroup_id not in (%s) """ % (account_id, ','.join(adgroup_ids)) Database().executeQuery(query)
def adgroups(account_id): """Delete ad groups""" campaign_ids = Database().getValues('campaigns', 'id') campaign_ids = ['"%s"' % (campaign_id[0]) for campaign_id in campaign_ids] query = """ delete from adgroups where account_id = '%s' and campaign_id not in (%s) """ % (account_id, ','.join(campaign_ids)) Database().executeQuery(query)
def __init__(self, appid, appsecret): self.appid = appid self.appsecret = appsecret # set the logger self.log_level = logging.DEBUG self.log_path = '/var/log/update_access_token.log' self.logger = logging.getLogger('Token') self.logger.setLevel(self.log_level) # create a handler for write the log to file. fh = logging.FileHandler(self.log_path) fh.setLevel(self.log_level) # create a handler for print the log info on console. ch = logging.StreamHandler() ch.setLevel(self.log_level) # set the log format formatter = logging.Formatter('%(asctime)s - %(name)s - %(levelname)s - %(message)s') fh.setFormatter(formatter) ch.setFormatter(formatter) # add the handlers to logger self.logger.addHandler(fh) self.logger.addHandler(ch) self.database = Database() self.logger.debug('update_access_token init over.')
def deleteExisting(self, date_range): """Initially remove all advert data for this account - we'll overwrite it""" deleteQuery = "delete from advert_performance where account_id = '%s' and date_range = '%s' " % ( self.account_id, date_range) Database().createEngine().execute(deleteQuery)
def addUserAccounts(self, user_id, first_run): if not Helpers().isActiveUser(user_id): Log("info", "this user isn't active. Exiting", 'user_id: %s' % (user_id)) return try: Log("info", "adding accounts for user id '%s'" % user_id) self.user_id = user_id accounts_df = self.getAccountsDf() if functions.dfIsEmpty(accounts_df): return accounts_df = accounts_df.drop_duplicates('google_id') accounts_df = self.dropDuplicates(accounts_df, first_run) if (accounts_df.shape[0] == 0 and first_run): Log('warning', "no unique google accounts were found for this user", "user_id (%s)" % (user_id), "") accounts_df.to_sql("accounts", Database().createEngine(), index=False, if_exists="append") except Exception as exception: Log("error", str(exception) + " (User id: %s)" % (user_id), traceback.format_exc()) Log("info", "finished adding account meta data")
def writeToPerformanceTable(self, df, report, account_id): delete_query = "delete from %s where account_id = '%s' " % ( self.performance_table_name, account_id) Database().executeQuery(delete_query) df["id"] = pd.Series([uuid.uuid1() for i in range(len(df))]).astype(str) report.writeDataframeToTable(df, self.performance_table_name)
def markAsSent(self): # update the budget_commander table mark the account's email as sent # email_sent field print("marking as sent") query = "update budget_commander set email_sent = 1 where account_id = '%s' " % ( self.account_id) (Database()).createEngine().execute(query)
def addDefaultBudgetCommanderSettings(self): """If budget commander settings aren't availble add the defaults""" settings = { "1": { "id": uuid.uuid4(), "created_at": datetime.now(), "updated_at": datetime.now(), "account_id": self.account_id, "notify_via_email": 0, "pause_campaigns": 0, "enable_campaigns": 0, "rollover_spend": 0, "control_spend": 0, "email_sent": 0, "emergency_stop": 0, "email_addresses": None, "day_paused_campaigns": None, "month_paused_campaigns": None, "excess_budget": None, } } # write to the db Database().appendRows("budget_commander", settings) return settings["1"]
def createNotification(account_id): #only when running for the first time... def isFirstRun(account_id): query = "select ad_performance_report_processed_at from accounts where id = '%s'" % ( account_id) results = Database().executeQuery(query) for result in results: return result[0] is None if not isFirstRun(account_id): Log('info', "the notification won't be created as this isn't the first run", '', account_id) return Log('info', "creating successful sync notification", '', account_id) user_id = Helpers().getUserIdFromAccountId(account_id) account_name = Helpers().getAccountNameFromId(account_id) account_google_id = Helpers().getAccountGoogleIdFromId(account_id) username = Helpers().getUsername(account_id) query = r""" insert into notifications (id, type, notifiable_id, notifiable_type, data, created_at, updated_at) values ('%s', 'App\\Notifications\\AccountSynced', '%s','App\\User', '{"message":"%s was synced successfully! Refesh the page to access the account."}', now(), now()) """ % (str(uuid.uuid4()), user_id, account_name) Database().executeQuery(query) sendEmail(account_name, account_google_id, username, account_id)
def update_batch_jobs(self, batch_jobs_data_frame): for index, row in batch_jobs_data_frame.iterrows(): query = "update batch_job set is_checking={0}, status='{1}' where id='{2}'".format( row["is_checking"], row["status"], row["id"] ) Database().executeQuery(query)
def process_service_type(self, service_type, action, attribute=None): """ Grab rows from the queue filtered by service type, action and attribute Process the rows (apply mutations to the API) Run each chunk once. The next process will pick up the next chunk. """ # Query data from mutations table query = self.mutations_query(service_type, action, attribute) mutations_data_frame_chunks = pd.read_sql(query, Database().createEngine(), chunksize=2000) # Iterate over chunks while True: try: mutations_data_frame = next(mutations_data_frame_chunks) except StopIteration: return # Mark the chunk data as is_processing equal to True self.set_data_frame_to_processing(mutations_data_frame) account_ids = mutations_data_frame.account_id.drop_duplicates().values for account_id in account_ids: account_data_frame = mutations_data_frame[mutations_data_frame.account_id == account_id].drop_duplicates() # Send mutations to google ads API result = self.process_account_mutations(account_data_frame, service_type) # Write data to DB self.handle_mutations_result(result, account_data_frame)
def __init__(self, download_dir = '/tmp/download/'): super(DouyinDownloader, self).__init__() # self.arg = arg self.downloadCount = 0 self.uploadCount = 0 if download_dir.endswith('/'): self.downloadDir = download_dir else: self.downloadDir = download_dir + '/' # redis connect self.redisClient = redis.StrictRedis(host='115.159.157.98', port=17379, db=0, password='******') # mysql self.mysqlDB = Database(host='localhost', user='******', passwd='zx#Video2018', database='video') # nextcloud self.oc = owncloud.Client('http://127.0.0.1:18080') self.oc.login('zhangxu', 'zx@12346') self.oc.logout() self.oc2 = owncloud.Client('http://115.29.34.236:18181') self.oc2.login('zhangxu', 'zx@12346') self.oc2.logout()
def getKeywordsDataframe(self): query = """ SELECT keywords.id as entity_id,adgroups.google_id as adgroup_google_id, keywords.google_id,keyword_performance.clicks, keyword_performance.conversions,keyword_performance.search_impression_share, keyword_performance.cost,keyword_performance.conversion_value,keywords.keyword_text ,keywords.keyword_match_type, keywords.cpc_bid, keywords.original_cpc_bid FROM keyword_performance join keywords on keywords.id = keyword_performance.keyword_id join adgroups on adgroups.id = keywords.adgroup_id where date_range = "last_30_days" and keywords.account_id = "%s" and keywords.status = "enabled" and keyword_performance.conversions = 0 and keyword_performance.clicks > 0 and keywords.google_id != "3000006" and keywords.google_id != "3000000" and keywords.bidding_strategy_type = "cpc" order by cost desc """ % (self.account_id) df = pd.read_sql(query, (Database()).createEngine()) df.cpc_bid = df.cpc_bid.astype("str") df.cpc_bid = df.cpc_bid.str.replace("--", "0") df.cpc_bid = df.cpc_bid.astype("float") df['forecast'] = (df.cost / 7) * self.local_dates.days_remaining_in_this_month df.forecast = df.forecast.astype("str") df.forecast = df.forecast.str.replace("--", "0") df.forecast = df.forecast.astype("float") return df
def getUserIdFromAccountId(self, account_id): query = "select user_id from accounts where id = '%s'" % (account_id) result = Database().executeQuery(query) for row in result: # print row return row[0]
def getTimezone(self): """Returns an account's tzinfo timezone""" query = 'SELECT timezone FROM accounts where id = "%s"' % ( self.account_id) timezone = pd.read_sql_query( query, Database().createEngine())["timezone"].values[0] return pytz.timezone(timezone)
def getRefreshTokenFromUserId(self, user_id): query = "select refresh_token from users where id = '%s'" % (user_id) result = Database().executeQuery(query) for row in result: if row[0] is None: return return row[0]
def getSyncedAccountIds(self): """Return a list of synced account ids""" query = "select id from accounts where is_synced = 1" result = Database().createEngine().execute(query) account_ids = [] for row in result: account_id = row[0] account_ids.append(account_id) return account_ids
def getAccountInfo(self): query = "select budget, name,google_id,currency_code from accounts where id = '%s' " % ( self.account_id) data = pd.read_sql(query, (Database()).createEngine()).to_dict() user_info = {} for key in data: user_info[key] = data[key][0] return user_info
def insert_batch_job(self, batch_job_status, batch_job_google_id): # Create batch job uuid_string = uuid.uuid1().__str__() query = "INSERT INTO batch_job (`id`, `status`, `google_id`) VALUES ('{0}','{1}', '{2}')".format( uuid_string, batch_job_status, batch_job_google_id ) Database().createEngine().execute(query) return uuid_string
def getCampaignIdsToPause(self): query = "select google_id from campaigns where account_id = '%s' " % (self.account_id) result = (Database()).createEngine().execute(query) campaign_ids = [] for row in result: campaign_ids.append(row[0]) self.campaign_ids_string = ",".join(campaign_ids)
def append_df_to_sql_table(df, table_name, engine=None): if dfIsEmpty(df): return df = df.replace([np.inf, -np.inf], 0) # df = df.replace(r'\s+', np.nan, regex=True) df = trimDfToTableColumns(df, table_name) df.to_sql(table_name, Database().createEngine(), if_exists='append', index=False)
def check(self): """ 1. Get batch jobs with status != Done 2. Check status of batch job 3. If status = Done, Get mutations with batch job id = current batch job id (order by created) 4. Update mutations with results """ batch_jobs_query = self.batch_jobs_query() batch_jobs_chunks = pd.read_sql(batch_jobs_query, Database().createEngine(), chunksize=10) # Iterate over chunks try: batch_jobs_data_frame = next(batch_jobs_chunks) except StopIteration: return # Mark the batch jobs data as is_checking equal to True self.set_batch_jobs_to_processing(batch_jobs_data_frame) for index, batch_job_data_frame in batch_jobs_data_frame.iterrows(): response = Update( batch_job_data_frame['account_id'], '' ).check_batch_job_result(batch_job_data_frame['google_id']) if response: mutations_query = self.mutations_query_by_batch_job(batch_job_data_frame['id']) mutations_data_frame_chunks = pd.read_sql(mutations_query, Database().createEngine(), chunksize=2000) # Iterate over chunks try: mutations_data_frame = next(mutations_data_frame_chunks) except StopIteration: continue mutations_data_frame["is_processing"] = 0 self.update_batch_job_mutations(mutations_data_frame, response) # TODO: support all status for batch job batch_jobs_data_frame.at[index, "status"] = 'DONE' batch_jobs_data_frame["is_checking"] = 0 self.update_batch_jobs(batch_jobs_data_frame)
def update_mutations(self, account_data_frame, message, is_success): for i, row in account_data_frame.iterrows(): if row['batch_job_id'] is None: row['batch_job_id'] = 'null' else: row['batch_job_id'] = "'%s'" %(row['batch_job_id']) query = "update mutations set response = '{0}', executed_at=now(), is_success={1}, is_processing={2}, batch_job_id={3} where id = '{4}'".format( message, is_success, row["is_processing"],row['batch_job_id'] , row["id"], ) Database().executeQuery(query)
def getAdvertsDataframe(self): """Pulls advert, advert_performance and adgroup data Returns chunks of 2000""" query = """ SELECT adverts.google_id, adverts.final_urls, adverts.headline_1, adverts.status, adverts.description, adverts.path_2, adverts.path_1, adverts.headline_2, adverts.id, adverts.adgroup_id, adverts.domain, adverts.ctr_significance, adverts.conversion_rate_significance, adverts.account_id, adverts.loser, adverts.potential_savings, adverts.headline_3, adverts.description_2, advert_performance.impressions, advert_performance.clicks, advert_performance.conversions, advert_performance.cost, advert_performance.conversion_value, advert_performance.date_range, advert_performance.id, advert_performance.cpa, advert_performance.conversion_rate, advert_performance.average_cpc, advert_performance.ctr, advert_performance.roas, advert_performance.ctr_significance, advert_performance.conversion_rate_significance, advert_performance.ctr_message, advert_performance.conversion_rate_message, advert_performance.advert_id, advert_performance.average_position, advert_performance.impression_share, adgroups.name as adgroup_name, adgroups.campaign_id from adverts join advert_performance on adverts.id = advert_performance.advert_id join adgroups on adgroups.id = adverts.adgroup_id where adverts.account_id = '%s' and date_range = '%s' """ % (self.account_id, self.date_range) return pd.read_sql(query, Database().createEngine(), chunksize=2000)
def getUsername(self, account_id): query = """ SELECT u.name FROM users as u join accounts as a on a.user_id = u.id where a.id = "%s" """ % (account_id) result = (Database()).createEngine().execute(query) for row in result: return row[0]
def isActiveUser(self, user_id): """Returns true if user exists and has a refresh token""" query = """ SELECT id from users where refresh_token is not null and id = '%s' """ % (user_id) results = Database().executeQuery(query) if (results.rowcount > 0): return True return False
def __init__(self, ip='0.0.0.0', port=9000, log_level=logging.DEBUG): self.ip = ip self.port = port self.author = __author__ self.version = __version__ self.file_path = os.path.realpath(__file__) self.dir_path = os.path.dirname(self.file_path) # mark system start time self.system_initialized = datetime.now() # weixin about self.appid = 'wxb1efccbbb5bafcbb' self.appsecret = '9d64356f48062e46159b4d179dea5c44' self.token = 'shenhailuanma' self.access_token = '' self.signature = None self.echostr = None self.timestamp = None self.nonce = None self.wechat = WechatBasic(token=self.token, appid=self.appid, appsecret=self.appsecret) self.weather = weather() # database self.database = Database() # set the logger self.log_level = logging.DEBUG self.log_path = 'myWeixinServer.log' self.logger = logging.getLogger('myWeixinServer') self.logger.setLevel(self.log_level) # create a handler for write the log to file. fh = logging.FileHandler(self.log_path) fh.setLevel(self.log_level) # create a handler for print the log info on console. ch = logging.StreamHandler() ch.setLevel(self.log_level) # set the log format formatter = logging.Formatter('%(asctime)s - %(name)s - %(levelname)s - %(message)s') fh.setFormatter(formatter) ch.setFormatter(formatter) # add the handlers to logger self.logger.addHandler(fh) self.logger.addHandler(ch) self.logger.info('init over.') ####### web test ###### @bottle.route('/') def index_get(): try: # get the post data self.logger.debug('handle a GET request: /, ') # e.g : /?signature=04d39d841082682dc7623945528d8086cc9ece97&echostr=8242236714827861439×tamp=1440564411&nonce=2061393952 # get the data self.logger.debug('handle the request data: %s' %(bottle.request.query_string)) #self.logger.debug('handle the request signature:%s' %(bottle.request.query.signature)) #self.logger.debug('handle the request echostr:%s' %(bottle.request.query.echostr)) #self.logger.debug('handle the request timestamp:%s' %(bottle.request.query.timestamp)) #self.logger.debug('handle the request nonce:%s' %(bottle.request.query.nonce)) return bottle.request.query.echostr except Exception,ex: return "%s" %(ex) return "Hello, this is myWeixinServer."
class update_access_token: def __init__(self, appid, appsecret): self.appid = appid self.appsecret = appsecret # set the logger self.log_level = logging.DEBUG self.log_path = '/var/log/update_access_token.log' self.logger = logging.getLogger('Token') self.logger.setLevel(self.log_level) # create a handler for write the log to file. fh = logging.FileHandler(self.log_path) fh.setLevel(self.log_level) # create a handler for print the log info on console. ch = logging.StreamHandler() ch.setLevel(self.log_level) # set the log format formatter = logging.Formatter('%(asctime)s - %(name)s - %(levelname)s - %(message)s') fh.setFormatter(formatter) ch.setFormatter(formatter) # add the handlers to logger self.logger.addHandler(fh) self.logger.addHandler(ch) self.database = Database() self.logger.debug('update_access_token init over.') def update(self): # get access_token from weixin access_token = self.get_access_token() # check the access_token if exist, if not to insert it. db_access_token = self.database.get_access_token(self.appid, self.appsecret) self.logger.debug('get_access_token from database:%s.' %(db_access_token)) if db_access_token != None: # update access_token self.database.update_access_token(self.appid, self.appsecret, access_token) else: self.database.insert_access_token(self.appid, self.appsecret, access_token) def get_access_token(self): try: url = "https://api.weixin.qq.com/cgi-bin/token?grant_type=client_credential&appid={0}&secret={1}".format(self.appid, self.appsecret) self.logger.debug('get_access_token request url:%s.' %(url)) response = self.http_get(url, 5) if response != None: response_json = json.loads(response) if response_json.has_key('access_token') and len(response_json['access_token']) > 0: return response_json['access_token'] return None except Exception,ex: self.logger.error('get_access_token error:{0}'.format(str(ex))) return None
class Server: def __init__(self, ip='0.0.0.0', port=9000, log_level=logging.DEBUG): self.ip = ip self.port = port self.author = __author__ self.version = __version__ self.file_path = os.path.realpath(__file__) self.dir_path = os.path.dirname(self.file_path) # mark system start time self.system_initialized = datetime.now() # weixin about self.appid = 'wxb1efccbbb5bafcbb' self.appsecret = '9d64356f48062e46159b4d179dea5c44' self.token = 'shenhailuanma' self.access_token = '' self.signature = None self.echostr = None self.timestamp = None self.nonce = None self.wechat = WechatBasic(token=self.token, appid=self.appid, appsecret=self.appsecret) self.weather = weather() # database self.database = Database() # set the logger self.log_level = logging.DEBUG self.log_path = 'myWeixinServer.log' self.logger = logging.getLogger('myWeixinServer') self.logger.setLevel(self.log_level) # create a handler for write the log to file. fh = logging.FileHandler(self.log_path) fh.setLevel(self.log_level) # create a handler for print the log info on console. ch = logging.StreamHandler() ch.setLevel(self.log_level) # set the log format formatter = logging.Formatter('%(asctime)s - %(name)s - %(levelname)s - %(message)s') fh.setFormatter(formatter) ch.setFormatter(formatter) # add the handlers to logger self.logger.addHandler(fh) self.logger.addHandler(ch) self.logger.info('init over.') ####### web test ###### @bottle.route('/') def index_get(): try: # get the post data self.logger.debug('handle a GET request: /, ') # e.g : /?signature=04d39d841082682dc7623945528d8086cc9ece97&echostr=8242236714827861439×tamp=1440564411&nonce=2061393952 # get the data self.logger.debug('handle the request data: %s' %(bottle.request.query_string)) #self.logger.debug('handle the request signature:%s' %(bottle.request.query.signature)) #self.logger.debug('handle the request echostr:%s' %(bottle.request.query.echostr)) #self.logger.debug('handle the request timestamp:%s' %(bottle.request.query.timestamp)) #self.logger.debug('handle the request nonce:%s' %(bottle.request.query.nonce)) return bottle.request.query.echostr except Exception,ex: return "%s" %(ex) return "Hello, this is myWeixinServer." @bottle.route('/', method="POST") def index_post(): try: response = '' self.logger.debug('handle a POST request: /, ') self.logger.debug('handle the request data: %s' %(bottle.request.query_string)) post_data = bottle.request.body.getvalue() self.logger.debug('handle the request post data: %s' %(post_data)) echostr = bottle.request.query.echostr signature = bottle.request.query.signature timestamp = bottle.request.query.timestamp nonce = bottle.request.query.nonce if self.wechat.check_signature(signature=signature, timestamp=timestamp, nonce=nonce): self.logger.debug('check_signature ok.') self.wechat.parse_data(post_data) message = self.wechat.get_message() if message.type == 'text': if message.content == 'wechat': response = self.wechat.response_text(u'^_^') elif u'天气' in message.content: city = u'北京' data = self.database.get_weather_data(city) self.logger.debug('get the weather response:{0}'.format(data)) response = self.wechat.response_text(data) else: response = self.wechat.response_text(u'文字') elif message.type == 'image': response = self.wechat.response_text(u'图片') elif message.type == 'video': self.logger.debug('message.media_id:%s' %(message.media_id)) response = self.wechat.download_media(message.media_id) self.logger.debug('message.media_id:%s over' %(message.media_id)) #response = self.wechat.response_text(u'视频') elif message.type == 'voice': response = self.wechat.response_text(u'音频') elif message.type == 'location': response = self.wechat.response_text(u'位置') else: response = self.wechat.response_text(u'未知') return response except Exception,ex: self.logger.debug('error:%s' %(ex)) return "%s" %(ex)
class DouyinDownloader(object): """docstring for DouyinDownloader""" def __init__(self, download_dir = '/tmp/download/'): super(DouyinDownloader, self).__init__() # self.arg = arg self.downloadCount = 0 self.uploadCount = 0 if download_dir.endswith('/'): self.downloadDir = download_dir else: self.downloadDir = download_dir + '/' # redis connect self.redisClient = redis.StrictRedis(host='115.159.157.98', port=17379, db=0, password='******') # mysql self.mysqlDB = Database(host='localhost', user='******', passwd='zx#Video2018', database='video') # nextcloud self.oc = owncloud.Client('http://127.0.0.1:18080') self.oc.login('zhangxu', 'zx@12346') self.oc.logout() self.oc2 = owncloud.Client('http://115.29.34.236:18181') self.oc2.login('zhangxu', 'zx@12346') self.oc2.logout() def downloadAll(self): while True: try: task = self.getTask() if task != None: print("get one task, to download") print(task) # 判断是否已经存在 exist = self.videoExist(task) if exist == True: print("video is exist, return") else: self.downloadOne(task) except Exception as e: print("downloadAll error:") print(e) time.sleep(1) def getTask(self): # 从任务队列中取任务 task = self.redisClient.rpop("douyinTask") return task def downloadOne(self, task): nowtime = time.time() filename = str(nowtime) + ".mp4" # dirname = time.strftime("%Y-%m-%d", time.localtime()) filepath = self.downloadDir + filename downloadDirNow = self.downloadDir print("to download video:" + filepath) # prepare dir if os.path.exists(downloadDirNow) == False: os.makedirs(downloadDirNow, 666) # stream=True作用是推迟下载响应体直到访问Response.content属性 res = requests.get(task, stream=True) # 将视频写入文件夹 with open(filepath, 'ab') as f: f.write(res.content) f.flush() self.downloadCount = self.downloadCount + 1 print(filename + '下载完成' + ', count:' + str(self.downloadCount)) # 计算文件md5 file_md5 = self.get_file_md5(filepath) print("file md5:" + file_md5) # 使用md5值作为新文件名 new_file_path = downloadDirNow + file_md5 + ".mp4" videoInfo = self.mysqlDB.get_video_info_by_md5(file_md5) if videoInfo != None: # 文件已经存在 print("video same md5 is exist, return") # 删除文件 os.remove(filepath) else: # 重命名 os.rename(filepath, new_file_path) # upload print("upload file to local cloud.") cloudFilePath = self.uploadFileToCloud(new_file_path, file_md5 + ".mp4", self.oc) # 暂时不上传到远程云了, 网速太慢了。。。 # print("upload file to remote cloud.") # self.uploadFileToCloud(new_file_path, file_md5 + ".mp4", self.oc2) # 创建异步任务copy视频到远程云 # if len(cloudFilePath) > 0: # self.redisClient.lpush('copyTask', cloudFilePath) # 更新上传计数 self.uploadCount = self.uploadCount + 1 print("upload file count:" + str(self.uploadCount)) # 信息插入数据库 urlmd5 = hashlib.md5(task).hexdigest() result,msg = self.mysqlDB.insert_video_info(platform=1, status=0, title='', url=str(task, encoding = "utf-8") , md5=file_md5, urlmd5=urlmd5, storepath=new_file_path) print("insert_video_info:" + result) # 删除文件 os.remove(new_file_path) def videoExist(self, task): # 计算url的md5值,之后根据该值在数据库中查询, 从而判断该视频是否已经存在 urlmd5 = hashlib.md5(task).hexdigest() # 根据urlMd5 查询视频信息 videoInfo = self.mysqlDB.get_video_info(urlmd5) if videoInfo != None: return True return False def get_file_md5(self, file_path): f = open(file_path,'rb') md5_obj = hashlib.md5() while True: d = f.read(8096) if not d: break md5_obj.update(d) hash_code = md5_obj.hexdigest() f.close() md5 = str(hash_code).lower() return md5 def uploadFileToCloud(self, file_path, file_name, oc): try: oc.login('zhangxu', 'zx@12346') dirname = time.strftime("%Y-%m-%d", time.localtime()) dirname = dirname + '-%d' %(self.uploadCount/200) try: oc.mkdir(dirname) except Exception as e: print("mkdir failed:" + str(e)) # upload cloudFilePath = dirname + '/' + file_name oc.put_file(cloudFilePath, file_path) # logout oc.logout() print("uploadFileToCloud success, file:" + cloudFilePath) return cloudFilePath except Exception as e: print("uploadFileToCloud error:") print(e) return ""