def get_users_mail(is_debug=False):
conn = db.get_jokes_app_connection()
if not is_debug:
return db.execute_read(
conn, "select * from users_mail where deleted_at is null")
else:
return db.execute_read(
conn, "select * from users_mail where email = '*****@*****.**'")
def get_joke_not_sent_by_pfm_already(conn: Engine,
limit=1,
sent_from="mail") -> pd.DataFrame:
sql_author = __get_sql_jokes(limit, from_author=True, sent_from=sent_from)
df = db.execute_read(conn, sql_author)
if df.empty: # no more jokes from authors, get from scrapped sources
sql_author = __get_sql_jokes(limit,
from_author=False,
sent_from=sent_from)
df = db.execute_read(conn, sql_author)
return df
def check_user_exists(user_id: str):
sql_telegram = "select user_id from users_telegram where user_id='{user_id}'".format(
user_id=user_id)
sql_mail = "select id_hash from users_mail where id_hash='{user_id}'".format(
user_id=user_id)
conn = db.get_jokes_app_connection()
has_telegram_user = not db.execute_read(conn, sql_telegram).empty
has_mail_user = not db.execute_read(conn, sql_mail).empty
if has_mail_user or has_telegram_user:
return True # at least one of the users exists
else:
return False # the id does not correspond to telegram or mail users -> fake ID
def get_joke(joke_id: int):
conn = db.get_jokes_app_connection()
sql = f"select * from jokes_to_send where id = {joke_id}"
try:
df = db.execute_read(conn, sql)
except sqlalchemy.exc.SQLAlchemyError:
return pd.DataFrame()
return df
def put_validated_jokes_in_joke_db():
logger = logging.getLogger(__name__)
conn = db.get_jokes_app_connection()
logger.info("Select validated jokes")
sql_query = "select * from validate_jokes where deleted_at is null and is_joke is true"
df_validated_jokes = db.execute_read(conn, sql_query)
if not df_validated_jokes.empty:
# drop columns that we do not want and rename cols
col_exclude = [
"id",
"hash_id",
"user_str_id",
"user_name",
"is_joke",
"validated_by_user_id",
"updated_at",
"deleted_at",
]
df_jokes = df_validated_jokes.drop(columns=col_exclude)
# add all the records to Jokes DB
db.add_records(conn, "jokes_to_send", df_jokes)
# put soft-delete in validate_jokes
update_query = "update validate_jokes set deleted_at = NOW() where deleted_at is null and is_joke is true"
db.execute_update(conn, update_query)
logger.info("Updated '{}' jokes. New jokes in Jokes DB".format(
len(df_jokes.index)))
else:
logger.info("No new validated jokes to put into the DB")
return True
def check_joke_id_exists(joke_id):
sql = "select id from jokes_to_send where id={joke_id}".format(
joke_id=joke_id)
conn = db.get_jokes_app_connection()
joke_id_exists = not db.execute_read(conn, sql).empty
return joke_id_exists
def get_tags():
conn = db.get_jokes_app_connection()
sql = "select * from tags"
df_tags = db.execute_read(conn, sql)
return df_tags.to_dict(orient="index")
def has_db_telegram_user(conn: Engine, user_id: str) -> bool:
df = db.execute_read(
conn,
"select id from users_telegram where user_id = '{}'".format(user_id))
return not df.empty # returns true if the user is in the DB
def has_db_mail_user_deleted(conn: Engine, email: str) -> bool:
df = db.execute_read(
conn, "select email from users_mail where email='{}'".format(email))
return not df.empty
def get_admin_users():
conn = db.get_jokes_app_connection()
return db.execute_read(conn, "select * from users_admin")
def has_twitter_db_joke(conn: Engine, hash_id: str) -> bool:
df = db.execute_read(
conn, "select hash_id from validate_jokes where hash_id = '{}'".format(
hash_id))
return not df.empty