def downloaded_tweets(limit=2000, include_command=False, include_text=False):
db = DB.database()
rows = db.query(statement="SELECT TWEET_AUTHOR, TWEET_ID, DATE_DOWNLOADED, COMMAND, TWEET_TEXT FROM DOWNLOADED_TWEETS ORDER BY DATE_DOWNLOADED DESC LIMIT 2000", fetchall=True)
db.close()
if not include_text:
header = '#'.center(7)
header += 'TWITTER USERNAME'.center(22)
header += 'TWEET ID'.center(26)
header += 'DATE DOWNLOADED'.center(22)
if include_command: header += 'COMMAND'.center(106)
print(header)
print('-'*len(header))
for i, row in enumerate(rows):
position = str(i+1).center(7)
tweet_author = str(row[0]).center(22)
tweet_id = str(row[1]).center(26)
date_downloaded = str(row[2]).center(22)
if include_command:
print('{}{}{}{}{}'.format(position, tweet_author, tweet_id, date_downloaded, str(row[3]).center(106)))
else:
print('{}{}{}{}'.format(position, tweet_author, tweet_id, date_downloaded))
def users(sort_field):
if sort_field.lower().startswith('x'): statement = "SELECT LOWER(TWEET_AUTHOR), STATUS, TWEET_ID, LAST_LOOKUP FROM TWITTER_USERS"
elif sort_field.lower().startswith('s'): statement = "SELECT LOWER(TWEET_AUTHOR), STATUS, TWEET_ID, LAST_LOOKUP FROM TWITTER_USERS ORDER BY STATUS"
elif sort_field.lower().startswith('i'): statement = "SELECT LOWER(TWEET_AUTHOR), STATUS, TWEET_ID, LAST_LOOKUP FROM TWITTER_USERS ORDER BY TWEET_ID"
elif sort_field.lower().startswith('d'): statement = "SELECT LOWER(TWEET_AUTHOR), STATUS, TWEET_ID, LAST_LOOKUP FROM TWITTER_USERS ORDER BY LAST_LOOKUP"
elif sort_field.lower().startswith('n'): statement = "SELECT LOWER(TWEET_AUTHOR), STATUS, TWEET_ID, LAST_LOOKUP FROM TWITTER_USERS ORDER BY LOWER(TWEET_AUTHOR)"
db = DB.database()
rows = db.query(statement, fetchall=True)
db.close()
header = '#'.center(7)
header += 'TWITTER USERNAME'.center(22)
header += 'STATUS'.center(20)
header += 'LAST MEDIA TWEET'.center(26)
header += 'LAST UPDATE'.center(24)
print(header)
print('-'*len(header))
for i, row in enumerate(rows):
position = str(i+1).center(7)
tweet_author = str(row[0]).center(22)
status = str(row[1]).center(20)
tweet_id = str(row[2]).center(26)
last_lookup = str(row[3]).center(22)
print('{}{}{}{}{}'.format(position, tweet_author, status, tweet_id, last_lookup))
def remove_like(tweet_id, like_owner):
db = DB.database()
db.query(
statement=
"DELETE FROM LIKED_TWEETS WHERE TWEET_ID = ? AND lower(LIKE_OWNER) = ?",
values=(tweet_id, like_owner.lower()),
commit=True)
db.close()
def already_downloaded(tweet_id):
db = DB.database()
row = db.query("SELECT * FROM DOWNLOADED_TWEETS WHERE TWEET_ID = ?",
values=(tweet_id, ),
fetchone=True)
db.close()
return row != None
def get_user(user):
db = DB.database()
row = db.query(
"SELECT LOWER(TWEET_AUTHOR), STATUS, TWEET_ID, LAST_LOOKUP FROM TWITTER_USERS WHERE LOWER(TWEET_AUTHOR) = ?",
values=(user.lower(), ),
fetchone=True)
db.close()
return row
def get_last_tweet_from_user(user):
db = DB.database()
row = db.query(
"SELECT TWEET_ID, DATE_DOWNLOADED FROM DOWNLOADED_TWEETS WHERE LOWER(TWEET_AUTHOR) = ? ORDER BY TWEET_ID DESC LIMIT 1",
values=(user.lower(), ),
fetchone=True)
db.close()
return row
def get_ids_from_user(user):
db = DB.database()
rows = db.query(
"SELECT TWEET_ID FROM DOWNLOADED_TWEETS WHERE LOWER(TWEET_AUTHOR) = ? ORDER BY RANDOM()",
values=(user.lower(), ),
fetchall=True)
db.close()
return rows if len(rows) != 0 else None
def get_random_tweet(user):
db = DB.database()
row = db.query(
"SELECT * FROM DOWNLOADED_TWEETS WHERE LOWER(TWEET_AUTHOR) = ? ORDER BY RANDOM() LIMIT 1",
values=(user.lower(), ),
fetchone=True)
db.close()
return row
def like(Tweet, like_owner): values = [Tweet.author.screen_name] values.append(Tweet.id) values.append(like_owner.lower()) values.append(Tweet.created_at) values.append(Tweet.text) db = DB.database() db.query(statement="INSERT INTO LIKED_TWEETS VALUES(?,?,?,?,?)", values=tuple(values), commit=True) db.close()
def download(tweet, command):
values = [tweet.author.screen_name]
values.append(int(tweet.id))
values.append(tweet.created_at.strftime('%Y-%m-%d %X'))
values.append(time.strftime('%Y-%m-%d %X', time.localtime()))
values.append(command)
values.append(tweet.full_text)
db = DB.database()
db.query(statement="INSERT INTO DOWNLOADED_TWEETS VALUES(?,?,?,?,?,?)", values=tuple(values), commit=True)
db.close()
def user(user, last_id=None, last_lookup=None):
if SQ.get_user(user):
Queries.Exceptions.print_error("### ERROR ### -- User [ {} ] already exists!".format(user))
return
values = [user.lower()]
values.append('Active' if last_lookup and last_id else 'Not Accessible')
values.append(last_id if last_id else 0)
values.append(last_lookup if last_lookup else time.strftime('%Y-%m-%d %X', time.localtime()))
db = DB.database()
db.query(statement="INSERT INTO TWITTER_USERS VALUES(?,?,?,?)", values=tuple(values), commit=True)
db.close()
Queries.Exceptions.print_info('Added user [ {} ] to the database'.format(user))
def deleted_users():
header = '#'.center(7)
header += 'TWITTER USERNAME'.center(22)
header += 'DATE OF DELETION'.center(24)
print(header)
print('-'*len(header))
db = DB.database()
rows = db.query("SELECT TWEET_AUTHOR, DATE_DELETION FROM DELETED_USERS_HISTORY", fetchall=True)
db.close()
for i, row in enumerate(rows):
position + str(i+1).center(7)
tweet_author = str(row[0]).center(22)
date_deletion = str(row[1]).center(24)
print('{}{}{}'.format(position, tweet_author, date_deletion))
def update_user(user, status=None):
db_user = SQ.get_user(user)
if db_user is None:
raise Queries.Exceptions.UnknownUser(
"We couldn't find any user with the name [ {} ] in the database!".
format(user))
db = DB.database()
if status:
if status.lower().startswith('a'): status = 'Active'
elif status.lower().startswith('n'): status = 'Not Accessible'
elif status.lower().startswith('p'): status = 'Protected'
elif status.lower().startswith('s'): status = 'Suspended'
elif status.lower().startswith('d'): status = 'Deleted'
else:
raise Queries.Exceptions.UnknownStatus(
"Status not recognized: {}".format(status))
if db_user[1] != status:
db.query(
statement=
"UPDATE TWITTER_USERS SET STATUS = ? WHERE lower(TWEET_AUTHOR) = ?",
values=(status, user.lower()),
commit=True)
Queries.Exceptions.print_info(
"=====> Changed status of [ {} ] to '{}'\n".format(
user, status))
try:
tweet_id, tweet_date = SQ.get_last_tweet_from_user(user)
db.query(
statement=
"UPDATE TWITTER_USERS SET TWEET_ID = ?, LAST_LOOKUP = ? WHERE lower(TWEET_AUTHOR) = ?",
values=(tweet_id, tweet_date, user.lower()),
commit=True)
except TypeError as err:
logging.warning(
"Could not find any tweets whose author is the user [ {} ] in the DOWNLOADED_TWEETS table"
)
db.close()
def get_user_list(status):
if status.lower().startswith('a'):
statement = "SELECT LOWER(TWEET_AUTHOR) FROM TWITTER_USERS WHERE STATUS = 'Active' ORDER BY lower(TWEET_AUTHOR)"
elif status.lower().startswith('s'):
statement = "SELECT LOWER(TWEET_AUTHOR) FROM TWITTER_USERS WHERE STATUS = 'Suspended' ORDER BY lower(TWEET_AUTHOR)"
elif status.lower().startswith('p'):
statement = "SELECT LOWER(TWEET_AUTHOR) FROM TWITTER_USERS WHERE STATUS = 'Active' ORDER BY lower(TWEET_AUTHOR)"
elif status.lower().startswith('d'):
statement = "SELECT LOWER(TWEET_AUTHOR) FROM TWITTER_USERS WHERE STATUS = 'Protected' ORDER BY lower(TWEET_AUTHOR)"
elif status.lower().startswith('n'):
statement = "SELECT LOWER(TWEET_AUTHOR) FROM TWITTER_USERS WHERE STATUS = 'Not Accessible' ORDER BY lower(TWEET_AUTHOR)"
else:
statement = "SELECT LOWER(TWEET_AUTHOR) FROM TWITTER_USERS WHERE STATUS != 'Active' ORDER BY lower(TWEET_AUTHOR)"
db = DB.database()
rows = db.query(statement, fetchall=True)
db.close()
return [row[0] for row in rows]
def fuse_users(user_one, user_two):
if SQ.get_user(user_one) is None or SQ.get_user(user_two) is None:
raise Queries.Exceptions.UnknownUser(
"Both or one of the users provided are not present in the database!"
.format(user))
user_one_folder = os.path.join(os.getcwd(), user_one)
user_two_folder = os.path.join(os.getcwd(), user_two)
for _, _, files in os.walk(user_one_folder):
for file in files:
if user_one.lower() in file.lower():
old_file = os.path.join(user_one_folder, file)
try:
new_file = os.path.join(
user_two_folder,
file.lower().replace(user_one.lower(), user_two))
os.rename(old_file, new_file)
except FileExistsError:
new_file = "[Duplicate] " + file.lower().replace(
user_one.lower(), user_two)
os.rename(old_file, new_file)
Queries.Exceptions.print_info(
"Changed name of file {} to {}".format(old_file, new_file))
shutil.rmtree(user_one_folder)
Queries.Exceptions.print_info(
"Deleted folder [ {} ]".format(user_one_folder))
db = DB.database()
db.query("DELETE FROM TWITTER_USERS WHERE lower(TWEET_AUTHOR) = ?",
values=(user_one.lower(), ),
commit=True)
db.close()
def likes(like_owner):
header + '#'.center(7)
header += 'TWITTER USERNAME'.center(22)
header += 'TWEET ID'.center(26)
header += 'WHO LIKED THE TWEET?'.center(22)
header += 'DATE OF TWEET'.center(24)
print(header)
print('-'*len(header))
db = DB.database()
rows = db.query("SELECT TWEET_ID, TWEET_OWNER, LIKE_OWNER, DATE_POSTED FROM LIKED_TWEETS WHERE LOWER(LIKE_OWNER) = ? ORDER BY TWEET_ID DESC LIMIT 2000", values=(like_owner.lower(),), fetchall=True)
db.close()
for i, row in enumerate(rows):
position + str(i+1).center(7)
tweet_id += str(row[0]).center(22)
tweet_author += str(row[1]).center(26)
like_owner += str(row[2]).center(22)
date_posted += str(row[3]).center(24)
print('{}{}{}{}{}'.format(position, tweet_author, status, tweet_id, last_lookup))
def errors():
header = '#'.center(7)
header += 'TWITTER USERNAME'.center(22)
header += 'STATUS'.center(20)
header += 'CODE #'.center(8)
header += 'DATE OF ERROR'.center(24)
print(header)
print('-'*len(header))
db = DB.database()
rows = db.query("SELECT TWEET_AUTHOR, STATUS, ERROR_CODE, DATE_ERROR FROM ERRORS_HISTORY", fetchall=True)
db.close()
for i, row in enumerate(rows):
position + str(i+1).center(7)
tweet_author = str(row[0]).center(22)
status = str(row[1]).center(20)
error_code = str(row[2]).center(8)
date_error = str(row[3]).center(24)
print('{}{}{}{}{}'.format(position, tweet_author, status, error_code, date_error))
def delete_user(user):
if SQ.get_user(user) is None:
raise Queries.Exceptions.UnknownUser(
"We couldn't find any user with the name [ {} ] in the database!".
format(user))
values = [user.lower()]
values.append(time.strftime('%Y-%m-%d %X', time.localtime()))
db = DB.database()
db.query("DELETE FROM TWITTER_USERS WHERE lower(TWEET_AUTHOR) = ?",
values=(user.lower(), ),
commit=True)
db.query("INSERT INTO DELETED_USERS_HISTORY VALUES(?,?)",
values=tuple(values),
commit=True)
db.close()
shutil.rmtree(os.path.join(os.getcwd(), user), ignore_errors=True)
Queries.Exceptions.print_info(
"Database updated! User [ {} ] has been deleted!".format(user))
Queries.Exceptions.print_info("Folder deleted!")
def error(tweet_author, status, error_code):
db = DB.database()
values = []
if db.query("SELECT * FROM ERRORS_HISTORY WHERE lower(TWEET_AUTHOR) = ?", values=(tweet_author.lower(),) , fetchone=True) is None:
values = [tweet_author.lower()]
values.append(status)
values.append(error_code)
values.append(time.strftime('%Y-%m-%d %X', time.localtime()))
db.query(statement="INSERT INTO ERRORS_HISTORY VALUES(?,?,?,?)", values=tuple(values), commit=True)
else:
values = [status]
values.append(error_code)
values.append(time.strftime('%Y-%m-%d %X', time.localtime()))
values.append(tweet_author.lower())
db.query(statement="UPDATE ERRORS_HISTORY SET STATUS = ?, ERROR_CODE = ?, DATE_ERROR = ? WHERE lower(TWEET_AUTHOR) = ?", values=tuple(values), commit=True)
logging.critical("User [ {} ] is not accessible anymore: The account is now {}".format(tweet_author, status))
db.close()
def renamed_users(include_command=False):
header = '#'.center(7)
header += 'OLD NAME'.center(22)
header += 'NEW NAME'.center(22)
header += 'DATE OF RENAME'.center(24)
if include_command: header += 'COMMAND'.center(106)
print(header)
print('-'*len(header))
db = DB.database()
rows = db.query("SELECT TWEET_AUTHOR_OLD, TWEET_AUTHOR_NEW, DATE_RENAME, COMMAND FROM RENAMED_USERS_HISTORY", fetchall=True)
db.close()
for i, row in enumerate(rows):
position + str(i+1).center(7)
tweet_author_old = str(row[0]).center(22)
tweet_author_new = str(row[1]).center(22)
date_rename = str(row[2]).center(24)
if include_command:
print('{}{}{}{}{}'.format(position, tweet_author_old, tweet_author_new, date_rename, str(row[3]).center(106)))
else:
print('{}{}{}{}'.format(position, tweet_author_old, tweet_author_new, date_rename))
def rename_user(old_user_name, new_user_name, command):
db = DB.database()
if SQ.get_user(old_user_name) is None:
raise Queries.Exceptions.UnknownUser(
"We couldn't find any user with the name [ {} ] in the database!".
format(old_user_name))
if new_user_name != sanitize_filename(new_user_name):
raise Queries.Exceptions.InvalidName(
"User [ {} ] already exists in the database!".format(
new_user_name))
if SQ.get_user(new_user_name):
raise Queries.Exceptions.UserExists(
"User [ {} ] already exists in the database!".format(
new_user_name))
if os.path.isdir(os.path.join(os.getcwd(), new_user_name)):
raise Queries.Exceptions.FolderExists(
"A folder named [ {} ] already exists!".format(new_user_name))
db.query(
"UPDATE TWITTER_USERS SET TWEET_AUTHOR = ? WHERE lower(TWEET_AUTHOR) = ?",
values=(new_user_name, old_user_name.lower()))
folder_to_rename = os.path.join(os.getcwd(), old_user_name)
for _, _, files in os.walk(folder_to_rename):
for file in files:
try:
if old_user_name.lower() in file.lower():
old_file = os.path.join(folder_to_rename, file)
renamed_file = file.lower().replace(
old_user_name.lower(), new_user_name)
new_file = os.path.join(folder_to_rename, renamed_file)
os.rename(old_file, new_file)
except FileExistsError:
renamed_file = "[Duplicate] " + file.lower().replace(
old_user_name.lower(), new_user_name)
new_file = os.path.join(folder_to_rename, renamed_file)
os.rename(old_file, new_file)
break
os.rename(folder_to_rename, os.path.join(os.getcwd(), new_user_name))
Queries.Exceptions.print_info(
"====> User [ {} ] changed its name to [ {} ]".format(
old_user_name, new_user_name))
values = [old_user_name.lower()]
values.append(new_user_name.lower())
values.append(time.strftime('%Y-%m-%d %X', time.localtime()))
values.append(command)
db.query(statement="INSERT INTO RENAMED_USERS_HISTORY VALUES(?,?,?,?)",
values=tuple(values),
commit=True)
db.close()