def get_number_users_by_feature(feature: str, feature_type: str) -> int:
"""
Get number of users that have same smartphone, camera, lens or that
have been to the same country
:param feature: string which is name of a particular feature e.g.
camera name or country name
:param feature_type: string which is name of the column in database
:return: string which is a message to user
"""
log.debug('Check how many users also have this feature: %s...',
feature)
query = ("SELECT DISTINCT chat_id "
"FROM photo_queries_table2 "
f"WHERE {feature_type}=%s")
parameters = feature,
try:
cursor = db.execute_query(query, parameters)
except DatabaseConnectionError:
log.error("Cannot check how many users also have this feature: %s...",
feature)
raise
if not cursor.rowcount:
log.debug('There were no users with %s...', feature)
return 0
log.debug('There is %d users with %s', cursor.rowcount, feature)
return cursor.rowcount - 1
def compare_and_update(user, message: Message) -> None:
"""
Updates user's info if needed
This method compare a user object from the bot and his info from
the Telegram message to check whether a user has changed his bio
or not. If yes, the user object that represents him in the bot will
be updated accordingly. Now this function is called only when a user
asks the bot for showing the most popular cams
:param user: user object that represents a Telegram user in this bot
:param message: object from Telegram that contains info about user's
message and about himself
:return: None
"""
log.info('Checking whether user have changed his info or not...')
msg = message.from_user
usr_from_message = User(message.chat.id, msg.first_name, msg.username,
msg.last_name)
if user.chat_id != usr_from_message.chat_id:
log.error("Wrong user to compare!")
return
if user.first_name != usr_from_message.first_name:
user.first_name = usr_from_message.first_name
elif user.nickname != usr_from_message.nickname:
user.nickname = usr_from_message.nickname
elif user.last_name != usr_from_message.last_name:
user.last_name = usr_from_message.last_name
else:
log.debug("User's info hasn't changed")
return
log.info("User has changed his info")
log.debug("Updating user's info in the database...")
query = (f"UPDATE users "
f"SET first_name=%s, "
f"nickname=%s, "
f"last_name=%s "
f"WHERE chat_id=%s")
parameters = (user.first_name, user.nickname, user.last_name,
user.chat_id)
try:
db.add(query, parameters)
except DatabaseError:
log.error("Could not update info about %s in the database", user)
else:
log.debug("User's info has been updated")
def get_total_number() -> int:
"""
Count the total number of users in the database
:return: integer which is the total number of users
"""
query = "SELECT COUNT(*) FROM users"
try:
cursor = db.execute_query(query)
except DatabaseConnectionError:
log.error("Can't count the total number of users!")
raise
return cursor.fetchone()[0]
def add(self, query: str, parameters: tuple = None):
"""
Shortcut to add something to a database
:param query: query to execute
:param parameters: parameters for query
:return: boolean - True if the method succeeded and False otherwise
"""
try:
self.execute_query(query, parameters)
self.conn.commit()
except Exception as e:
log.error(e)
raise DatabaseError("Cannot add your data to the database!")
def clean_cache(self, limit: int) -> None:
"""
Method that remove several User objects from cache - the least
active users
:param limit: number of the users that the method should remove
from cache
:return: None
"""
log.info('Figuring out the least active users...')
# Select users that the least active recently
user_ids = tuple(self.users.keys())
query = ('SELECT chat_id '
'FROM photo_queries_table2 '
f'WHERE chat_id in {user_ids} '
'GROUP BY chat_id '
'ORDER BY MAX(time) '
f'LIMIT %s')
parameters = limit,
try:
cursor = db.execute_query(query, parameters)
except DatabaseConnectionError:
log.error("Can't figure out the least active users...")
return
if not cursor.rowcount:
log.warning("There are no users in the db")
return
# Make list out of tuple of tuples that is returned by MySQL
least_active_users = [chat_id[0] for chat_id in cursor.fetchall()]
log.info('Removing %d least active users from cache...', limit)
num_deleted_entries = 0
for entry in least_active_users:
log.debug('Deleting %s...', entry)
deleted_entry = self.users.pop(entry, None)
if deleted_entry:
num_deleted_entries += 1
log.debug("%d users were removed from cache.", num_deleted_entries)
def _add_to_db(user: User) -> None:
"""
Adds the User object to the database
:param user: User object with info about user
:return: None
"""
query = ("INSERT INTO users (chat_id, first_name, nickname, "
"last_name, language) "
f"VALUES (%s, %s, %s, %s, %s)")
parameters = (user.chat_id, user.first_name, user.nickname,
user.last_name, user.language)
try:
db.add(query, parameters)
except DatabaseError:
log.error("Cannot add user to the database")
else:
log.info(f"User {user} was successfully added to the users db")
def start_bot(self) -> None:
"""
Method to get the bot started
Wrapper around self._run just for the sake of making it more reliable
and reconnect in case of errors. And to store time when bot started to
work
:return: None
"""
try:
self.start_time = datetime.now()
self._run()
except requests.exceptions.ReadTimeout as e:
log.error(e)
self.stop_polling()
log.warning('Pausing bot for 30 seconds...')
time.sleep(30)
log.warning('Try to start the bot again...')
self._run()
def set_language(self, lang: str) -> None:
"""
Update language of user in the User object and in the database
:param lang: string with language tag like "en-US"
:return: None
"""
log.debug(
'Updating info about user %s language '
'in memory & database...', self)
self.language = lang
query = ("UPDATE users " f"SET language=%s " f"WHERE chat_id=%s")
parameters = self.language, self.chat_id
try:
db.add(query, parameters)
except DatabaseError:
log.error("Can't add new language of %s to the database", self)
else:
log.debug('Language updated.')
def admin_menu(call: CallbackQuery) -> None:
"""
Respond to commands from the admin menu
:param call: object that contains info about user's reaction to an
interactive keyboard
:return: None
"""
# Remove progress bar from pressed button
bot.answer_callback_query(callback_query_id=call.id, show_alert=False)
if call.data == 'off':
if db.disconnect():
bot.turn_off()
else:
log.error('Cannot stop bot.')
bot.send_message(chat_id=config.MY_TELEGRAM,
text='Cannot stop bot.')
elif call.data == 'last active':
bot.send_message(config.MY_TELEGRAM,
text=get_admin_stat('last active users'))
elif call.data == 'total number photos sent':
bot.send_message(config.MY_TELEGRAM,
text=get_admin_stat('total number photos sent'))
elif call.data == 'photos today':
bot.send_message(config.MY_TELEGRAM,
text=get_admin_stat('photos today'))
elif call.data == 'number of users':
bot.send_message(config.MY_TELEGRAM,
text=get_admin_stat('number of users'))
elif call.data == 'number of gadgets':
bot.send_message(config.MY_TELEGRAM,
text=get_admin_stat('number of gadgets'))
elif call.data == 'uptime':
bot.send_message(config.MY_TELEGRAM,
text=get_admin_stat('uptime'))
def _get_address(self, latitude: float, longitude: float) \
-> Tuple[Dict[str, str], Dict[str, str]]:
"""
# Get address as a string by coordinates from photo that user sent
to bot
:param latitude: latitude from a photo as a float
:param longitude: longitude rom a photo as a float
:return: address as a string where photo was taken; name of
country in English and Russian to keep statistics
of the most popular countries among users of the bot
"""
address = {}
country = {}
coordinates = f"{latitude}, {longitude}"
log.debug('Getting address from coordinates %s...', coordinates)
geolocator = Nominatim()
lang = self.user.language
try:
# Get name of the country in English and Russian language
location_en = geolocator.reverse(coordinates, language='en')
address['en-US'] = location_en.address
country['en-US'] = location_en.raw['address']['country']
location_ru = geolocator.reverse(coordinates, language='ru')
address['ru-RU'] = location_ru.address
country['ru-RU'] = location_ru.raw['address']['country']
address = address[lang]
return address, country
except Exception as e:
log.error(e)
log.error('Getting address has failed!')
raise
def cache(self, limit: int) -> None:
"""
Caches last active users from database to a dictionary inside object of
this class
:param limit: limit of entries to be cached
:return: None
"""
log.debug("Start caching last active users from the DB...")
try:
last_active_users = self.get_last_active_users(limit)
except DatabaseConnectionError:
log.error("Cannot cache users!")
return
for items in last_active_users:
# if chat_id of a user is not known to the bot
if items[0] not in self.users:
# adding a user from the database to the "cache"
self.users[items[0]] = User(*items)
log.debug("Caching user: %s", self.users[items[0]])
log.info('Users have been cached.')
def get_last_active_users(
limit: int) -> Tuple[Tuple[int, str, str, str, str]]:
"""
Get from the database a tuple of users who have been recently using
the bot
:param limit: integer that specifies how much users to get
:return: tuple of tuples with user's info
"""
log.info('Evaluating last active users with date of '
'last time when they used bot...')
# From photo_queries_table2 we take chat_id of the last
# active users and from 'users' table we take info about these
# users by chat_id which is a foreign key
query = ('SELECT p.chat_id, u.first_name, u.nickname, u.last_name, '
'u.language '
'FROM photo_queries_table2 p '
'INNER JOIN users u '
'ON p.chat_id = u.chat_id '
'GROUP BY u.chat_id, u.first_name, u.nickname, u.last_name, '
'u.language '
'ORDER BY MAX(time)'
f'DESC LIMIT %s')
parameters = limit,
try:
cursor = db.execute_query(query, parameters)
except DatabaseConnectionError:
log.error("Cannot get the last active users because of some "
"problems with the database")
raise
last_active_users = cursor.fetchall()
return last_active_users
def _check_camera_tags(*tags: str) -> List[str]:
"""
Converts camera and lens name to proper ones
Function that convert stupid code name of a smartphone or a camera
from EXIF to a meaningful one by looking a collation in a special MySQL
table For example instead of just Nikon there can be
NIKON CORPORATION in EXIF
:param tags: a tuple with a name of a camera and lens from EXIF
:return: list with one or two strings which are name of
camera and/or lens. If there is not better name for the gadget
in database, function just returns name how it is
"""
checked_tags = []
for tag in tags:
if tag: # If there was this information inside EXIF of the photo
tag = str(tag).strip()
log.info('Looking up collation for %s', tag)
query = ('SELECT right_tag '
'FROM tag_table '
'WHERE wrong_tag=%s')
parameters = tag,
cursor = db.execute_query(query, parameters)
if not cursor:
log.error("Can't check the tag because of the db error")
log.warning("Tag will stay as is.")
continue
if cursor.rowcount:
# Get appropriate tag from the table
tag = cursor.fetchone()[0]
log.info('Tag after looking up in tag_tables - %s.', tag)
checked_tags.append(tag)
return checked_tags
def execute_query(self,
query: str,
parameters: tuple = None,
trials: int = 0):
"""
Executes a given query
:param query: query to execute
:param parameters: parameters for query
:param trials: integer that denotes number of trials to execute
a query in case of known errors
:return: cursor object
"""
if not self.conn or not self.conn.open:
self.connect()
try:
cursor = self.conn.cursor()
cursor.execute(query, parameters)
# try to reconnect if MySQL server has gone away
except MySQLdb.OperationalError as e:
# (2013, Lost connection to MySQL server during query)
# (2006, Server has gone away)
if e.args[0] in [2006, 2013]:
log.info(e)
self.connect()
if trials <= 3:
# trying to execute query one more time
trials += 1
log.warning(e)
log.info("Trying execute the query again...")
return self.execute_query(query, parameters, trials)
if trials > 3:
log.error(e)
log.warning("Ran out of limit of trials...")
raise DatabaseConnectionError("Cannot connect to the "
"database")
else:
log.error(e)
raise
except Exception as e:
log.error(e)
raise
else:
return cursor
def find_one(self, message: Message) -> User:
"""
Look up a user by a message which we get together with request
from Telegram
:param message: object from Telegram that contains info about user's
message and about himself
:return: user object that represents a Telegram user in this bot
"""
# look up user in the cache of the bot
user = self.users.get(message.chat.id, None)
if user:
return user
# otherwise look up the user in the database
log.debug("Looking up the user in the database as it doesn't "
"appear in cache")
query = (f'SELECT first_name, nickname, last_name, language '
f'FROM users '
f'WHERE chat_id=%s')
parameters = message.chat.id,
try:
cursor = db.execute_query(query, parameters)
except DatabaseConnectionError:
# Even if the database in unreachable add user to dictionary
# with users otherwise the bot will crash requesting this
# user's info
log.error('Cannot lookup the user with chat_id %d in database',
message.chat.id)
msg = message.from_user
user = self.add_new_one(message.chat.id,
msg.first_name,
msg.last_name,
msg.username,
language='en-US',
add_to_db=False)
return user
if not cursor.rowcount:
# This user uses our photoGPSbot for the first time as we
# can't find him in the database
log.info('Adding totally new user to the system...')
msg = message.from_user
user = self.add_new_one(message.chat.id,
msg.first_name,
msg.last_name,
msg.username,
language='en-US')
bot.send_message(config.MY_TELEGRAM,
text=f'You have a new user! {user}')
log.info('You have a new user! Welcome %s', user)
# finally if the user wasn't found in the cache of the bot, but was
# found in the database
else:
log.debug('User %d has been found in the database',
message.chat.id)
user_data = cursor.fetchall()[0]
user = self.add_new_one(message.chat.id,
*user_data,
add_to_db=False)
return user
def get_most_popular_items(item_type: str, message: Message) -> str:
"""
Get the most common cameras/lenses/countries from database and
make list of them
:param item_type: string with column name to choose between cameras,
lenses and countries
:param message: telebot object with info about user and his message
:return: string which is either list of most common
cameras/lenses/countries or message which states that list is
empty
"""
user = users.find_one(message)
def tuple_to_ordered_str_list(list_of_gadgets: Tuple[Tuple[str]]) -> str:
"""
Converts Python list to ordered list as a string
Example:
1. Canon 80D
2. iPhone 4S
:param list_of_gadgets: tuple of tuples with string where every string
is a name of a camera or lens or a country
:return: ordered list as a string
"""
string_roaster = ''
index = 1
for item in list_of_gadgets:
if not item[0]:
continue
string_roaster += '{}. {}\n'.format(index, item[0])
index += 1
return string_roaster
log.debug('Evaluating most popular things...')
# This query returns item types in order where the first one item
# has the highest number of occurrences
# in a given column
query = (f'SELECT {item_type} FROM photo_queries_table2 '
f'GROUP BY {item_type} '
f'ORDER BY count({item_type}) '
'DESC')
try:
cursor = db.execute_query(query)
except DatabaseConnectionError:
log.error("Can't evaluate a list of the most popular items")
return messages[user.language]['doesnt work']
# Almost impossible case but still
if not cursor.rowcount:
log.warning('There is nothing in the main database table')
bot.send_message(chat_id=config.MY_TELEGRAM,
text='There is nothing in the main database table')
return messages[user.language]['no_top']
popular_items = cursor.fetchall()
log.info('Finish evaluating the most popular items')
return tuple_to_ordered_str_list(popular_items[:30])