def start(self):
loop = asyncio.get_event_loop()
try:
client = VKAPIClient(
loop, [TaskToUpdateCommunities, TaskToUpdateAudience])
loop.run_until_complete(client.run())
finally:
Database.close()
loop.close()
def load_ordered_by_update_time(cls, min_members):
sql = ('SELECT "vkid", "members" '
'FROM "community" '
'WHERE "deactivated" = FALSE '
'AND "members" >= {0:d} '
'ORDER BY "audience_updated" ASC').format(min_members)
return Database().execute(sql, handler=lambda row: cls(row[0], row[1]))
def save(self):
if not self.profile2count:
return
with Database() as db:
audience_params_list = []
sql = (
'INSERT INTO "profile" ("sex_vkid", "age_range_id", "country_vkid", "app_id") '
'VALUES (%s, %s, %s, %s) '
'RETURNING "id"')
for profile, count in self.profile2count.items():
profile_id = self._profile2id.get(profile)
if profile_id is None:
params = profile.sex_vkid, profile.age_range_id, profile.country_vkid, profile.app_id
profile_id = db.execute(sql, params, lambda row: row[0])[0]
self._profile2id[profile] = profile_id
audience_params_list.append((self.vkid, profile_id, count))
sql = ('DELETE FROM "audience" ' 'WHERE "community_vkid" = %s')
db.execute(sql, (self.vkid, ))
sql = (
'INSERT INTO "audience" ("community_vkid", "profile_id", "count") '
'VALUES (%s, %s, %s)')
db.executemany(sql, audience_params_list)
logging.info('audience of community %s was saved' % self.vkid)
def _load_profiles(cls):
sql = (
'SELECT "id", "sex_vkid", "age_range_id", "country_vkid", "app_id" '
'FROM "profile"')
kvpairs = Database().execute(
sql,
handler=lambda row:
(Profile(row[1], row[2], row[3], row[4]), row[0]))
cls._profile2id = dict(kvpairs)
def _init_applications(cls):
sql = ('SELECT "id", "name" ' 'FROM "application"')
kvpairs = Database().execute(sql, handler=lambda row: (row[1], row[0]))
name2id = dict(kvpairs)
cls.APP_UNKNOWN = name2id['UNKNOWN']
cls.APP_IOS = name2id['IOS']
cls.APP_ANDROID = name2id['ANDROID']
cls.APP_MOBILE = name2id['MOBILE']
cls.APP_BROWSER = name2id['BROWSER']
def _init_types(cls):
sql = ('SELECT "id", "name" '
'FROM "community_type"')
kvpairs = Database().execute(sql, handler=lambda row: (row[1], row[0]))
name2id = dict(kvpairs)
cls.PUBLIC_PAGE = name2id['PUBLIC_PAGE']
cls.OPEN_GROUP = name2id['OPEN_GROUP']
cls.CLOSED_GROUP = name2id['CLOSED_GROUP']
cls.PRIVATE_GROUP = name2id['PRIVATE_GROUP']
def save(self):
sql = ('UPDATE "community" '
'SET "deactivated"=%s, "type"=%s, "name"=%s, "description"=%s, '
'"members"=%s, "status"=%s, "verified"=%s, "site"=%s, "age_limit"=%s '
'WHERE "vkid"=%s')
params = (self.deactivated, self.type, self.name, self.description,
self.members, self.status, self.verified, self.site, self.age_limit,
self.vkid)
Database().execute(sql, params)
logging.info('community(id%s) was saved' % self.vkid)
def _init_age_ranges(cls):
sql = ('SELECT "id", "name" ' 'FROM "age_range"')
kvpairs = Database().execute(sql, handler=lambda row: (row[1], row[0]))
name2id = dict(kvpairs)
cls.AGE_UNKNOWN = name2id['UNKNOWN']
cls.AGE_14_AND_YOUNGER = name2id['-14']
cls.AGE_15_17 = name2id['15-17']
cls.AGE_18_21 = name2id['18-21']
cls.AGE_22_25 = name2id['22-25']
cls.AGE_26_29 = name2id['26-29']
cls.AGE_30_34 = name2id['30-34']
cls.AGE_35_39 = name2id['35-39']
cls.AGE_40_44 = name2id['40-44']
cls.AGE_45_49 = name2id['45-49']
cls.AGE_50_59 = name2id['50-59']
cls.AGE_60_AND_OLDER = name2id['60+']
def create(cls, vkid, deactivated, type_, name, description, members, status, verified, site, age_limit):
comm = cls(vkid)
comm.deactivated = deactivated
comm.type = type_
comm.name = name
comm.description = description
comm.members = members
comm.status = status
comm.verified = verified
comm.site = site
comm.age_limit = age_limit
sql = ('INSERT INTO "community" '
'("vkid", "deactivated", "type", "name", "description", '
'"members", "status", "verified", "site", "age_limit") '
'VALUES (%s, %s, %s, %s, %s, %s, %s, %s, %s, %s)')
params = (comm.vkid, comm.deactivated, comm.type, comm.name, comm.description,
comm.members, comm.status, comm.verified, comm.site, comm.age_limit)
Database().execute(sql, params)
return comm
def _load(self):
sql = ('SELECT "token" ' 'FROM "account" ' 'WHERE "active" = TRUE')
tokens = Database().execute(sql, handler=lambda t: t[0])
self.tokens = deque((t, DateTime(1970, 1, 1)) for t in tokens)
self.sem = asyncio.BoundedSemaphore(len(tokens), loop=self.loop)
def _init_countries_ids(cls):
sql = ('SELECT "vkid", "name" ' 'FROM "country"')
kvpairs = Database().execute(sql, handler=lambda row: (row[1], row[0]))
name2id = dict(kvpairs)
cls.UNKNOWN_COUNTRY = name2id.pop('UNKNOWN')
cls.countries_ids = frozenset(name2id.values())
def load_ordered_by_update_time(cls):
sql = ('SELECT "vkid" '
'FROM "community" '
'ORDER BY "updated" ASC')
return Database().execute(sql, handler=lambda row: cls(row[0]))