async def update_db(data):
parsed = json.loads(data)
print(parsed['text'])
vote, coin = is_vote(parsed["text"])
if None in [vote, coin]:
return
print(f'New vote detected : {coin}')
async with create_engine(
user="******",
database="deviant",
host="127.0.0.1",
password="******",
port=5432,
) as engine:
mgr = SQLModelManager.instance()
mgr.database = engine
user, created = await User.objects.get_or_create(id=int(parsed['id']))
if created:
user.name = parsed["user"]["name"]
user.screen_name = parsed["user"]["screen_name"]
user.followers_count = parsed["user"]["followers_count"]
user.statuses_count = parsed["user"]["statuses_count"]
await user.save()
new_record = Tweet()
new_record.id = parsed["id"]
new_record.text = parsed["text"]
new_record.created_at = tdate_to_timestamp(parsed["created_at"])
new_record.user = user
new_record.coin = coin.upper()
await new_record.save(force_insert=True)
async def prepare(self):
async with create_engine(user="******",
database="deviant",
host="127.0.0.1",
password="******",
port=5432) as engine:
SQLModelManager.instance().database = engine
users = await User.objects.all()
for u in users:
tmp_dict = {}
tmp_dict["Name"] = u.screen_name
tmp_dict["Tweets"] = u.statuses_count
tmp_dict["Followers"] = u.followers_count
tmp_dict["id"] = str(u.id)
tmp_dict["Follow"] = u.follower
u.tweets = await Tweet.objects.filter(user=u.id)
if True in [i.retweet for i in u.tweets]:
tmp_dict["Retweet"] = True
tmp = [i.coin for i in u.tweets if i.coin != '']
log.debug(f'tmp : {len(tmp)}')
if len(tmp) == 1:
tmp_dict["Coin"] = tmp[0].upper()
self.datas["data"].append(tmp_dict)
async def db(event_loop):
if DATABASE_URL.startswith("sqlite"):
m = re.match(r"(.+)://(.+)", DATABASE_URL)
assert m, "DATABASE_URL is an invalid format"
schema, db = m.groups()
params = dict(database=db)
else:
m = re.match(r"(.+)://(.+):(.*)@(.+):(\d+)/(.+)", DATABASE_URL)
assert m, "DATABASE_URL is an invalid format"
schema, user, pwd, host, port, db = m.groups()
params = dict(host=host, port=int(port), user=user, password=pwd)
if schema == "mysql":
from aiomysql import connect
from aiomysql.sa import create_engine
elif schema == "postgres":
from aiopg import connect
from aiopg.sa import create_engine
elif schema == "sqlite":
from aiosqlite import connect
create_engine = connect
else:
raise ValueError("Unsupported database schema: %s" % schema)
if schema == "mysql":
params["autocommit"] = True
params["loop"] = event_loop
if schema == "sqlite":
if os.path.exists(db):
os.remove(db)
else:
if schema == "postgres":
params["database"] = "postgres"
async with connect(**params) as conn:
async with conn.cursor() as c:
# WARNING: Not safe
await c.execute("DROP DATABASE IF EXISTS %s;" % db)
await c.execute("CREATE DATABASE %s;" % db)
if schema == "mysql":
params["db"] = db
elif schema == "postgres":
params["database"] = db
async with create_engine(**params) as engine:
mgr = SQLModelManager.instance()
mgr.database = {"default": engine}
yield engine
async def create_db_tables():
async with create_engine(user='******',
database='deviant',
host='127.0.0.1',
password='******',
port=5432) as engine:
mgr = SQLModelManager.instance()
mgr.database = engine
mgr.create_tables()
await User.objects.create()
await Tweet.objects.create()
async def get_summary_report(self):
async with create_engine(user="******",
database="deviant",
host="127.0.0.1",
password="******",
port=5432) as engine:
SQLModelManager.instance().database = engine
followers = await User.objects.filter(follower__is=True)
followers_count = len(followers)
followers_count_users_id = [i.id for i in followers]
print(f"nb followers : {followers_count}")
coins = await Tweet.objects.filter(coin__gt='')
coins_count_users_id = {i.user.id for i in coins}
coins_count = len(coins_count_users_id)
print(f"coins count: {coins_count}")
retweets = await Tweet.objects.filter(retweet__is=True)
retweets_count_users_id = {i.user.id for i in retweets}
retweets_count = len(retweets_count_users_id)
print(f"retweets count: {retweets_count}")
listes = [
followers_count_users_id,
coins_count_users_id,
retweets_count_users_id,
]
total_liste = reduce(set.intersection, [set(l_) for l_ in listes])
log.debug(total_liste)
summary_report = [
{
"name": "Followers",
"nb": followers_count
},
{
"name": "Users have retweet",
"nb": retweets_count
},
{
"name": "Users haave coined",
"nb": coins_count
},
{
"name": "Users qualified",
"nb": len(total_liste)
},
]
self.viewer.summaryreport.summary_report_list = summary_report
async def update_db():
last_retweet_from_db = await get_last_retweet_from_db()
retweets_from_twitter = api.retweets(TWEET_ID, count=100)
if not last_retweet_from_db:
new_retweets = [
r
for r in retweets_from_twitter
if r.created_at > datetime(2019, 8, 31, 0, 0)
]
else:
new_retweets = [
r
for r in retweets_from_twitter
if r.created_at > last_retweet_from_db.created_at
]
my_logger.debug(f"{len(new_retweets)} retweets to insert in db")
async with create_engine(
user="******",
database="deviant",
host="127.0.0.1",
password="******",
port=5432,
) as engine:
mgr = SQLModelManager.instance()
mgr.database = engine
for retweet in new_retweets:
user, created = await User.objects.get_or_create(id=int(retweet.user.id))
if created:
my_logger.debug(f"New User created {retweet.user.screen_name}")
user.name = retweet.user.name
user.screen_name = retweet.user.screen_name
user.followers_count = retweet.user.followers_count
user.statuses_count = retweet.user.statuses_count
await user.save()
new_record = Tweet()
new_record.id = retweet.id
new_record.text = retweet.text
new_record.created_at = retweet.created_at
new_record.user = user
new_record.retweet = True
my_logger.debug(f"New retweet created {retweet.text}")
await new_record.save(force_insert=True)
async def get(self): # TODO Manage websockets when browser not refreshed
viewer = Viewer(request=self.request, response=self, datas=[])
tornado.log.gen_log.debug("I'm doing some stuff")
tmp_list = []
async with create_engine(user="******",
database="deviant",
host="127.0.0.1",
password="******",
port=5432) as engine:
SQLModelManager.instance().database = engine
coins = await Tweet.objects.all()
coins_list = Counter([c.coin for c in coins if c.coin != ''])
for k, v in coins_list.items():
tmp_list.append({'coin': k.upper(), 'nb': v})
viewer.coinsleaderboard.leaderboard_list = tmp_list
CACHE[viewer.ref] = viewer
self.write(viewer.render())
async def get_last_retweet_from_db():
async with create_engine(
user="******",
database="deviant",
host="127.0.0.1",
password="******",
port=5432,
) as engine:
mgr = SQLModelManager.instance()
mgr.database = engine
last_retweet = await Tweet.objects.filter(retweet__is=True)
ordered = sorted(last_retweet, key=operator.attrgetter("created_at"), reverse=True)
if not ordered:
my_logger.debug("DB is empty")
return None
my_logger.debug(f"Last retweet in DB {ordered[0].created_at}")
print()
return ordered[0]
async def get_user_tweets(self, userid):
tweetslist = []
async with create_engine(user="******",
database="deviant",
host="127.0.0.1",
password="******",
port=5432) as engine:
SQLModelManager.instance().database = engine
tweets = await Tweet.objects.filter(user=userid)
for t in tweets:
try:
username = str(t.user.screen_name)
except:
username = ''
tweetslist.append({
"text": t.text,
"coin": str(t.coin),
"retweet": str(t.retweet),
"date": str(t.created_at),
"id": str(t.id),
"user": username
})
self.viewer.tweetsdetails.tweets_list = tweetslist
def test_build_tables():
# Trigger table creation
SQLModelManager.instance().create_tables()
async def get_followers_and_update_db():
auth = OAuthHandler(consumer_key, consumer_secret)
auth.set_access_token(access_token, access_token_secret)
api = API(auth_handler=auth, retry_count=3)
twitter_followers = []
if mode_production is True:
for page in Cursor(api.followers_ids,
screen_name=USER_SCREEN_NAME).pages():
twitter_followers.extend(page)
#with open("test/followers.txt", "x") as f:
# for t in twitter_followers:
# f.write(f"{t}\n")
else:
with open("test/followers.txt", "r") as f:
for l in f:
twitter_followers.append(l[:-1])
print(f"Followers count: {len(twitter_followers)}")
async with create_engine(
user="******",
database="deviant",
host="127.0.0.1",
password="******",
port=5432,
) as engine:
mgr = SQLModelManager.instance()
mgr.database = engine
mgr.create_tables()
users_in_db = await User.objects.filter(follower__is=True)
ids_in_db = [i.id for i in users_in_db]
users_to_check = [i for i in twitter_followers if i not in ids_in_db]
print(f"Followers en twitter account: {len(twitter_followers)}")
print(f"Followers in DB before update: {len(ids_in_db)}")
print(f"{len(users_to_check)} to check")
a = 0
for i in users_to_check:
# print(type(i))
await gen.sleep(1)
a += 1
user, created = await User.objects.get_or_create(id=int(i))
if created:
# print(f'Create User {user.screen_name}')
u = api.get_user(i, include_entities=False)
user.name = u.name
user.screen_name = u.screen_name
user.followers_count = u.followers_count
user.statuses_count = u.statuses_count
user.follower = True
print(
f"{a} / {len(users_to_check)} : User {user.screen_name} created"
)
else:
# print('User already in db')
user.follower = True
print(
f"{a} / {len(users_to_check)} : User {user.screen_name} updated"
)
await user.save()