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 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 save_info_to_db(self, image_data: ImageData) -> None:
"""
Insert info about user's query to the database
When user sends photo as a file to get information, bot also stores
information about this query to the database to keep statistics that
can be shown to a user in different ways. It stores time of query,
Telegram id of a user, his camera and lens which were used for
taking photo, his first and last name, nickname and country where
the photo was taken. The bot does not save photos or their
coordinates.
:image_data: an instance of ImageData dataclass with info about
the image
:return: None
"""
camera_name, lens_name = image_data.camera, image_data.lens
if not image_data.country:
country_en = country_ru = None
else:
country_en = image_data.country["en-US"]
country_ru = image_data.country["ru-RU"]
log.info('Adding user query to photo_queries_table...')
query = ('INSERT INTO photo_queries_table2 '
'(chat_id, camera_name, lens_name, country_en, country_ru) '
'VALUES (%s, %s, %s, %s, %s)')
parameters = (self.user.chat_id, camera_name, lens_name, country_en,
country_ru)
db.execute_query(query, parameters)
db.conn.commit()
log.info('User query was successfully added 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 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 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])
def get_admin_stat(command: str) -> str:
"""
Function that returns statistics to admin by command
:param command: string with a command what kind of statistics to prepare
:return: a string with either answer with statistics or an error message
"""
error_answer = "Can't execute your command. Check logs"
answer = 'There is some statistics for you: \n'
# Set to a beginning of the day
today = (datetime
.today()
.replace(hour=0, minute=0, second=0, microsecond=0)
.strftime('%Y-%m-%d %H:%M:%S'))
# Last users with date of last time when they used bot
if command == 'last active users':
try:
last_active_users = users.get_last_active_users(100)
except DatabaseConnectionError:
return error_answer
bot_users = ''
# Makes a human readable list of last active users
for usr, index in zip(last_active_users,
range(len(last_active_users))):
user = User(*usr)
bot_users += f'{index + 1}. {user}\n'
answer = ('Up to 100 last active users by the time when they sent '
'picture last time:\n')
answer += bot_users
log.info('Done.')
return answer
elif command == 'total number photos sent':
log.info('Evaluating total number of photo queries in database...')
query = ('SELECT COUNT(chat_id) '
'FROM photo_queries_table2')
try:
cursor = db.execute_query(query)
except DatabaseConnectionError:
return error_answer
answer += f'{cursor.fetchone()[0]} times users sent photos.'
query = ('SELECT COUNT(chat_id) '
'FROM photo_queries_table2 '
'WHERE chat_id !=%s')
parameters = config.MY_TELEGRAM,
try:
cursor = db.execute_query(query, parameters)
except DatabaseConnectionError:
answer += ("\nCannot calculate number of photos that were send "
"excluding your photos. Check logs")
return answer
answer += f'\nExcept you: {cursor.fetchone()[0]} times.'
log.info('Done.')
return answer
elif command == 'photos today':
# Show how many photos have been sent since 00:00:00 of today
log.info('Evaluating number of photos which were sent today.')
query = ("SELECT COUNT(chat_id) "
"FROM photo_queries_table2 "
"WHERE time > %s")
parameters = today,
try:
cursor = db.execute_query(query, parameters)
except DatabaseConnectionError:
return error_answer
answer += f'{cursor.fetchone()[0]} times users sent photos today.'
query = ("SELECT COUNT(chat_id) "
"FROM photo_queries_table2 "
"WHERE time > %s "
"AND chat_id !=%s")
parameters = today, config.MY_TELEGRAM
try:
cursor = db.execute_query(query, parameters)
except DatabaseConnectionError:
return error_answer
answer += '\nExcept you: {} times.'.format(cursor.fetchone()[0])
log.info('Done.')
return answer
elif command == 'number of users':
# Show number of users who has used bot at leas"
# once or more (first for the whole time, then today)
log.info('Evaluating number of users that use bot '
'since the first day and today...')
try:
num_of_users = users.get_total_number()
except DatabaseConnectionError:
return error_answer
answer += f'There are totally {num_of_users} users.'
query = ("SELECT COUNT(DISTINCT chat_id) "
"FROM photo_queries_table2 "
"WHERE time > %s")
parameters = today,
try:
cursor = db.execute_query(query, parameters)
except DatabaseConnectionError:
answer += ("\nCannot calculate how many user have sent their "
"photos today")
return answer
answer += f'\n{cursor.fetchone()[0]} users have sent photos today.'
log.info('Done.')
return answer
elif command == 'number of gadgets':
# To show you number smartphones + cameras in database
log.info('Evaluating number of cameras and smartphones in database...')
query = ('SELECT COUNT(DISTINCT camera_name) '
'FROM photo_queries_table2')
try:
cursor = db.execute_query(query)
except DatabaseConnectionError:
return error_answer
answer += (f'There are totally {cursor.fetchone()[0]} '
f'cameras/smartphones.')
query = ("SELECT COUNT(DISTINCT camera_name) "
"FROM photo_queries_table2 "
"WHERE time > %s")
parameters = today,
try:
cursor = db.execute_query(query, parameters)
except DatabaseConnectionError:
answer += ("Cannot calculate the number of gadgets that have been "
"used today so far")
return answer
answer += (f'\n{cursor.fetchone()[0]} cameras/smartphones '
'were used today.')
log.info('Done.')
return answer
elif command == 'uptime':
fmt = 'Uptime: {} days, {} hours, {} minutes and {} seconds.'
td = datetime.now() - bot.start_time
# datetime.timedelta.seconds returns you total number of seconds
# since given time, so you need to perform
# a little bit of math to make whole hours, minutes and seconds from it
# And there isn't any normal way to do it in Python unfortunately
uptime = fmt.format(td.days, td.seconds // 3600, td.seconds % 3600 //
60, td.seconds % 60)
log.info(uptime)
return uptime
else:
return 'There is no such a command'