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