class TestFileHandler(unittest.TestCase):
def setUp(self):
self.fh = FileHandler("..")
def test_save_and_load_json(self):
sample_json = {}
sample_json["testbool"] = True
sample_json["teststring"] = "hello world"
self.fh.save_json(sample_json, "resources/sample.json")
data = self.fh.load_json("resources/sample.json")
self.assertEqual(data["testbool"], True)
self.assertEqual(data["teststring"], "hello world")
def test_save_and_load_file(self):
data = "text message"
self.fh.save_file(data=data, path="resources/sample.txt")
result = self.fh.load_file("resources/sample.txt")
self.assertEqual(result, "text message")
def test_file_exists(self):
data = "text message"
self.fh.save_file(data=data, path="resources/sample.txt")
self.assertTrue(self.fh.file_exists("resources/sample.txt"))
def tearDown(self):
base_path = os.path.abspath(os.path.dirname(__file__))
filepath_json = os.path.join(base_path, '..', "resources/sample.json")
filepath_text = os.path.join(base_path, '..', "resources/sample.txt")
if os.path.exists(filepath_json):
os.remove(filepath_json)
if os.path.exists(filepath_text):
os.remove(filepath_text)
def __init__(self, database_path):
self.database_path = database_path
self.filehandler = FileHandler(relative_root_path="..")
if not self.filehandler.file_exists(self.database_path):
sql_command = self.filehandler.load_file("resources/setup.sql")
conn = sqlite3.connect(self.database_path)
cursor = conn.cursor()
cursor.executescript(sql_command)
conn.commit()
conn.close()
def __init__(self, telegram_token, update_interval):
# Initialize bot internals
self.db = DatabaseHandler("resources/datastore.db")
self.fh = FileHandler("..")
# Register webhook to telegram bot
self.updater = Updater(telegram_token)
self.dispatcher = self.updater.dispatcher
# Add Commands to bot
self._addCommand(CommandHandler("start", self.start))
self._addCommand(CommandHandler("stop", self.stop))
self._addCommand(CommandHandler("help", self.help))
self._addCommand(CommandHandler("list", self.list))
self._addCommand(CommandHandler("add", self.add, pass_args=True))
self._addCommand(CommandHandler("get", self.get, pass_args=True))
self._addCommand(CommandHandler("remove", self.remove, pass_args=True))
# Start the Bot
self.processing = BatchProcess(database=self.db,
update_interval=update_interval,
bot=self.dispatcher.bot)
self.processing.start()
self.updater.start_polling()
self.updater.idle()
def __init__(self, telegram_token, update_interval):
# Initialize bot internals
self.db = DatabaseHandler("resources/userdata/datastore.db")
self.fh = FileHandler("..")
self.inventory = InventoryHandler()
# Register webhook to telegram bot
self.updater = Updater(telegram_token)
self.dispatcher = self.updater.dispatcher
# Add Commands to bot
self._addCommand(CommandHandler("start", self.start))
self._addCommand(CommandHandler("stop", self.stop))
self._addCommand(CommandHandler("help", self.help))
self._addCommand(CommandHandler("list", self.list))
self._addCommand(CommandHandler("about", self.about))
self._addCommand(CommandHandler("add", self.add, pass_args=True))
self._addCommand(CommandHandler("get", self.get, pass_args=True))
self._addCommand(CommandHandler("remove", self.remove, pass_args=True))
self._addCommand(
CommandHandler("addgroup", self.add_group, pass_args=True))
self._addCommand(
CommandHandler("search", self.inventory_search, pass_args=True))
self._addCommand(MessageHandler(Filters.text, self.vechten))
self._addCommand(MessageHandler(Filters.command, self.unknown))
self._addCommand(InlineQueryHandler(self.inlinequery))
# Start the Bot
self.processing = BatchProcess(database=self.db,
update_interval=update_interval,
bot=self.dispatcher.bot)
self.processing.start()
self.updater.start_polling()
self.updater.idle()
def __init__(self,
observer: Observer,
name: str,
foreground_color="#ffffff",
font_name=""):
super().__init__(observer, name, foreground_color, font_name)
self.foreground_color = foreground_color
self.name = "Weather"
self.font_name = font_name
self.main_layout = QVBoxLayout()
self.setLayout(self.main_layout)
self.start_widget = StartWidget(self, self.foreground_color,
self.font_name)
self.main_layout.addWidget(self.start_widget)
self.r = RestSpecial()
self.data = None
self.file_handler = FileHandler()
self.time_periode = 3600
self.load_start_config()
class DatabaseHandler(object):
def __init__(self, database_path):
self.database_path = database_path
self.filehandler = FileHandler(relative_root_path="..")
if not self.filehandler.file_exists(self.database_path):
sql_command = self.filehandler.load_file("resources/setup.sql")
conn = sqlite3.connect(self.database_path)
cursor = conn.cursor()
cursor.executescript(sql_command)
conn.commit()
conn.close()
def add_user(self, telegram_id, username, firstname, lastname,
language_code, is_bot, is_active):
"""Adds a user to sqlite database
Args:
param1 (int): The telegram_id of a user.
param2 (str): The username of a user.
param3 (str): The firstname of a user.
param4 (str): The lastname of a user.
param5 (str): The language_code of a user.
param6 (str): The is_bot flag of a user.
"""
conn = sqlite3.connect(self.database_path)
cursor = conn.cursor()
cursor.execute("INSERT OR IGNORE INTO user VALUES (?,?,?,?,?,?,?)",
(telegram_id, username, firstname, lastname,
language_code, is_bot, is_active))
conn.commit()
conn.close()
def remove_user(self, telegram_id):
"""Removes a user to sqlite database
Args:
param1 (int): The telegram_id of a user.
"""
conn = sqlite3.connect(self.database_path)
cursor = conn.cursor()
cursor.execute("DELETE FROM user WHERE telegram_id=" +
str(telegram_id))
conn.commit()
conn.close()
def update_user(self, telegram_id, **kwargs):
"""Updates a user to sqlite database
Args:
param1 (int): The telegram_id of a user.
param2 (kwargs): The attributes to be updated of a user.
"""
conn = sqlite3.connect(self.database_path)
cursor = conn.cursor()
sql_command = "UPDATE user SET "
for key in kwargs:
sql_command = sql_command + \
str(key) + "='" + str(kwargs[key]) + "', "
sql_command = sql_command[:-2] + \
" WHERE telegram_id=" + str(telegram_id)
cursor.execute(sql_command)
conn.commit()
conn.close()
def get_user(self, telegram_id):
"""Returns a user by its id
Args:
param1 (int): The telegram_id of a user.
Returns:
list: The return value. A list containing all attributes of a user.
"""
conn = sqlite3.connect(self.database_path)
cursor = conn.cursor()
cursor.execute("SELECT * FROM user WHERE telegram_id = " +
str(telegram_id))
result = cursor.fetchone()
conn.commit()
conn.close()
return result
def add_url(self, url):
conn = sqlite3.connect(self.database_path)
cursor = conn.cursor()
cursor.execute(
"INSERT OR IGNORE INTO web (url, last_updated) VALUES (?,?)",
(url, dh.get_datetime_now()))
conn.commit()
conn.close()
def remove_url(self, url):
conn = sqlite3.connect(self.database_path)
cursor = conn.cursor()
sql_command = "DELETE FROM web_user WHERE url='" + str(url) + "';"
cursor.execute(sql_command)
sql_command = "DELETE FROM web WHERE web.url NOT IN (SELECT web_user.url from web_user)"
cursor.execute(sql_command)
conn.commit()
conn.close()
def update_url(self, url, **kwargs):
conn = sqlite3.connect(self.database_path)
cursor = conn.cursor()
sql_command = "UPDATE web SET "
for key in kwargs:
sql_command = sql_command + \
str(key) + "='" + str(kwargs[key]) + "', "
if len(kwargs) == 0:
sql_command = sql_command + " WHERE url='" + str(url) + "';"
else:
sql_command = sql_command[:-2] + " WHERE url='" + str(url) + "';"
cursor.execute(sql_command)
conn.commit()
conn.close()
def get_url(self, url):
conn = sqlite3.connect(self.database_path)
cursor = conn.cursor()
sql_command = "SELECT * FROM web WHERE url='" + str(url) + "';"
cursor.execute(sql_command)
result = cursor.fetchone()
conn.commit()
conn.close()
return result
def get_all_urls(self):
conn = sqlite3.connect(self.database_path)
cursor = conn.cursor()
sql_command = "SELECT * FROM web;"
cursor.execute(sql_command)
result = cursor.fetchall()
conn.commit()
conn.close()
return result
def add_user_bookmark(self, telegram_id, url, alias):
conn = sqlite3.connect(self.database_path)
cursor = conn.cursor()
self.add_url(url) # add if not exists
cursor.execute("INSERT OR IGNORE INTO web_user VALUES (?,?,?)",
(url, telegram_id, alias))
conn.commit()
conn.close()
def remove_user_bookmark(self, telegram_id, url):
conn = sqlite3.connect(self.database_path)
cursor = conn.cursor()
cursor.execute(
"DELETE FROM web_user WHERE telegram_id=(?) AND url = (?)",
(telegram_id, url))
cursor.execute(
"DELETE FROM web WHERE web.url NOT IN (SELECT web_user.url from web_user)"
)
conn.commit()
conn.close()
def update_user_bookmark(self, telegram_id, url, alias):
conn = sqlite3.connect(self.database_path)
cursor = conn.cursor()
cursor.execute(
"UPDATE web_user SET alias=(?) WHERE telegram_id=(?) AND url=(?)",
(alias, telegram_id, url))
conn.commit()
conn.close()
def get_user_bookmark(self, telegram_id, alias):
conn = sqlite3.connect(self.database_path)
cursor = conn.cursor()
cursor.execute(
"SELECT web.url, web_user.alias, web.last_updated FROM web, web_user WHERE web_user.url = web.url AND web_user.telegram_id ="
+ str(telegram_id) + " AND web_user.alias ='" + str(alias) + "';")
result = cursor.fetchone()
conn.commit()
conn.close()
return result
def get_urls_for_user(self, telegram_id):
conn = sqlite3.connect(self.database_path)
cursor = conn.cursor()
cursor.execute(
"SELECT web.url, web_user.alias, web.last_updated FROM web, web_user WHERE web_user.url = web.url AND web_user.telegram_id ="
+ str(telegram_id) + ";")
result = cursor.fetchall()
conn.commit()
conn.close()
return result
def get_users_for_url(self, url):
conn = sqlite3.connect(self.database_path)
cursor = conn.cursor()
cursor.execute(
"SELECT user.*, web_user.alias FROM user, web_user WHERE web_user.telegram_id = user.telegram_id AND web_user.url ='"
+ str(url) + "';")
result = cursor.fetchall()
conn.commit()
conn.close()
return result
def get_url_for_user_from_alias(self, telegram_id, url_alias):
'''
:return: The url identified by url_alias for the given user or None if it does not exist
'''
conn = sqlite3.connect(self.database_path)
cursor = conn.cursor()
query = "SELECT url FROM web_user WHERE telegram_id = %d AND alias = '%s'" % (
telegram_id, url_alias)
cursor.execute(query)
url = cursor.fetchone()
conn.commit()
conn.close()
if url is None:
return None
else:
return url[0]
def add_filter(self, user, filter_alias, filter_regex, url_alias):
url = self.get_url_for_user_from_alias(user.id, url_alias)
if not url:
raise Exception("no url for filter and alias")
conn = sqlite3.connect(self.database_path)
cursor = conn.cursor()
cursor.execute("INSERT INTO filter VALUES (?,?,?, ?)",
(filter_regex, filter_alias, url, user.id))
conn.commit()
conn.close()
def get_filters(self, telegram_id, url):
'''
:return: All filters for the specified url of the given user or None if there are none
'''
conn = sqlite3.connect(self.database_path)
cursor = conn.cursor()
query = "SELECT regexp FROM filter WHERE filter.url = '%s' AND filter.telegram_id = %d;" % (
url, telegram_id)
cursor.execute(query)
result = cursor.fetchall()
conn.commit()
conn.close()
if len(result) > 0:
#unpack from list of tuples of strings into list of strings
return list(chain.from_iterable(result))
else:
return []
def get_filter(self, user, filter_alias, url_alias):
url = self.get_url_for_user_from_alias(user.id, url_alias)
if not url:
raise Exception("no url for filter and alias")
conn = sqlite3.connect(self.database_path)
cursor = conn.cursor()
query = "SELECT filter.* FROM filter WHERE filter.alias = '%s' AND filter.url = '%s' AND filter.telegram_id = %d;" % (
filter_alias, url, user.id)
cursor.execute(query)
result = cursor.fetchall()
conn.commit()
conn.close()
return result
def help(self, bot, update):
"""
Send a message when the command /help is issued.
"""
message = "If you need help with handling the commands, please have a look at my <a href='https://github.com/serguk89/telegram-rss'>Github</a> page. There I have summarized everything necessary for you!"
update.message.reply_text(message, parse_mode=ParseMode.HTML)
def stop(self, bot, update):
"""
Stops the bot from working
"""
telegram_user = update.message.from_user
self.db.update_user(telegram_id=telegram_user.id, is_active=0)
message = "Oh.. Okay, I will not send you any more news updates! If you change your mind and you want to receive messages from me again use /start command again!"
update.message.reply_text(message)
if __name__ == '__main__':
# Load Credentials
fh = FileHandler("..")
credentials = fh.load_json("resources/credentials.json")
# Pass Credentials to bot
token = credentials["telegram_token"]
update = credentials["update_interval"]
RobotRss(telegram_token=token, update_interval=update)
class DatabaseHandler(object):
def __init__(self, database_path):
self.database_path = database_path
self.filehandler = FileHandler(relative_root_path="..")
if not self.filehandler.file_exists(self.database_path):
sql_command = self.filehandler.load_file("resources/setup.sql")
conn = sqlite3.connect(self.database_path)
cursor = conn.cursor()
cursor.executescript(sql_command)
conn.commit()
conn.close()
def add_user(self, telegram_id, username, firstname, lastname,
language_code, is_bot, is_active):
"""Adds a user to sqlite database
Args:
param1 (int): The telegram_id of a user.
param2 (str): The username of a user.
param3 (str): The firstname of a user.
param4 (str): The lastname of a user.
param5 (str): The language_code of a user.
param6 (str): The is_bot flag of a user.
"""
conn = sqlite3.connect(self.database_path)
cursor = conn.cursor()
cursor.execute("INSERT OR IGNORE INTO user VALUES (?,?,?,?,?,?,?)",
(telegram_id, username, firstname, lastname,
language_code, is_bot, is_active))
conn.commit()
conn.close()
def remove_user(self, telegram_id):
"""Removes a user to sqlite database
Args:
param1 (int): The telegram_id of a user.
"""
conn = sqlite3.connect(self.database_path)
cursor = conn.cursor()
cursor.execute("DELETE FROM user WHERE telegram_id=" +
str(telegram_id))
conn.commit()
conn.close()
def update_user(self, telegram_id, **kwargs):
"""Updates a user to sqlite database
Args:
param1 (int): The telegram_id of a user.
param2 (kwargs): The attributes to be updated of a user.
"""
conn = sqlite3.connect(self.database_path)
cursor = conn.cursor()
sql_command = "UPDATE user SET "
for key in kwargs:
sql_command = sql_command + \
str(key) + "='" + str(kwargs[key]) + "', "
sql_command = sql_command[:-2] + \
" WHERE telegram_id=" + str(telegram_id)
cursor.execute(sql_command)
conn.commit()
conn.close()
def get_user(self, telegram_id):
"""Returns a user by its id
Args:
param1 (int): The telegram_id of a user.
Returns:
list: The return value. A list containing all attributes of a user.
"""
conn = sqlite3.connect(self.database_path)
cursor = conn.cursor()
cursor.execute("SELECT * FROM user WHERE telegram_id = " +
str(telegram_id))
result = cursor.fetchone()
conn.commit()
conn.close()
return result
def get_all_users(self):
"""Returns a user by its id
Args:
param1 (int): The telegram_id of a user.
Returns:
list: The return value. A list containing all attributes of a user.
"""
conn = sqlite3.connect(self.database_path)
cursor = conn.cursor()
result = cursor.execute("SELECT telegram_id FROM user;")
#result = cursor.fetchone()
conn.commit()
conn.close()
return result
def add_url(self, url, url_content):
conn = sqlite3.connect(self.database_path)
cursor = conn.cursor()
print("web ok")
#timestamp = datetime.datetime.fromtimestamp(ts).strftime('%Y-%m-%d %H:%M:%S')
timestamp = datetime.datetime.utcnow().strftime('%Y-%m-%d %H:%M:%S')
print("web ok, timestamp: " + timestamp)
cursor.execute(
"INSERT OR IGNORE INTO web (url, last_updated, url_content) VALUES (?,?,?)",
(url, timestamp, url_content))
conn.commit()
conn.close()
def remove_url(self, url):
conn = sqlite3.connect(self.database_path)
cursor = conn.cursor()
sql_command = "DELETE FROM web_user WHERE url='" + str(url) + "';"
cursor.execute(sql_command)
sql_command = "DELETE FROM web WHERE web.url NOT IN (SELECT web_user.url from web_user)"
cursor.execute(sql_command)
conn.commit()
conn.close()
def update_url(self, url, last_updated, url_content):
conn = sqlite3.connect(self.database_path)
cursor = conn.cursor()
sql_command = "UPDATE web SET last_updated = ?, url_content = ? where url = ?"
cursor.execute(sql_command, (last_updated, url_content, url))
conn.commit()
conn.close()
# def update_url(self, url, **kwargs):
# conn = sqlite3.connect(self.database_path)
# cursor = conn.cursor()
# sql_command = "UPDATE web SET "
# for key in kwargs:
# sql_command = sql_command + \
# str(key) + "='" + str(kwargs[key]) + "', "
# if len(kwargs) == 0:
# sql_command = sql_command + " WHERE url='" + str(url) + "';"
# else:
# sql_command = sql_command[:-2] + " WHERE url='" + str(url) + "';"
# cursor.execute(sql_command)
# conn.commit()
# conn.close()
def get_url(self, url):
conn = sqlite3.connect(self.database_path)
cursor = conn.cursor()
sql_command = "SELECT * FROM web WHERE url='" + str(url) + "';"
sql_command = "SELECT * FROM web WHERE url=?;"
cursor.execute(sql_command, (url))
result = cursor.fetchone()
conn.commit()
conn.close()
return result
def get_all_urls(self):
conn = sqlite3.connect(self.database_path)
cursor = conn.cursor()
sql_command = "SELECT * FROM web;"
cursor.execute(sql_command)
result = cursor.fetchall()
conn.commit()
conn.close()
return result
def add_user_bookmark(self, telegram_id, url, alias, url_content):
conn = sqlite3.connect(self.database_path)
cursor = conn.cursor()
print("adding bookmark")
self.add_url(url, url_content) # add if not exists
print("web completed")
cursor.execute("INSERT OR IGNORE INTO web_user VALUES (?,?,?)",
(url, telegram_id, alias))
conn.commit()
conn.close()
def remove_user_bookmark(self, telegram_id, url):
conn = sqlite3.connect(self.database_path)
cursor = conn.cursor()
cursor.execute(
"DELETE FROM web_user WHERE telegram_id=(?) AND url = (?)",
(telegram_id, url))
cursor.execute(
"DELETE FROM web WHERE web.url NOT IN (SELECT web_user.url from web_user)"
)
conn.commit()
conn.close()
def update_user_bookmark(self, telegram_id, url, alias):
conn = sqlite3.connect(self.database_path)
cursor = conn.cursor()
cursor.execute(
"UPDATE web_user SET alias=(?) WHERE telegram_id=(?) AND url=(?)",
(alias, telegram_id, url))
conn.commit()
conn.close()
def get_user_bookmark(self, telegram_id, alias):
conn = sqlite3.connect(self.database_path)
cursor = conn.cursor()
cursor.execute(
"SELECT web.url, web_user.alias, web.last_updated FROM web, web_user WHERE web_user.url = web.url AND web_user.telegram_id ="
+ str(telegram_id) + " AND web_user.alias ='" + str(alias) + "';")
result = cursor.fetchone()
conn.commit()
conn.close()
return result
def get_urls_for_user(self, telegram_id):
conn = sqlite3.connect(self.database_path)
cursor = conn.cursor()
cursor.execute(
"SELECT web.url, web_user.alias, web.last_updated FROM web, web_user WHERE web_user.url = web.url AND web_user.telegram_id ="
+ str(telegram_id) + ";")
result = cursor.fetchall()
conn.commit()
conn.close()
return result
def get_users_for_url(self, url):
conn = sqlite3.connect(self.database_path)
cursor = conn.cursor()
cursor.execute(
"SELECT user.*, web_user.alias FROM user, web_user WHERE web_user.telegram_id = user.telegram_id AND web_user.url ='"
+ str(url) + "';")
result = cursor.fetchall()
conn.commit()
conn.close()
return result
class RestSpecial():
api_key = None
rest_caller = None
def __init__(self):
self.rest_caller = Rest()
self.f = FileHandler()
# https://openweathermap.org/api/air-pollution
def call_server_pollution(self, call_params: dict) -> dict:
"""
params: call_params { "lat": "", "lon": "", "units": "", "language": "", "key": ""}
"""
data = None
lat = call_params['lat']
lon = call_params['lon']
units = call_params['units']
language = call_params['language']
key = call_params['key']
path = "http://api.openweathermap.org/data/2.5/air_pollution?lat={}&lon={}&units={}&lang={}&appid={}".format(lat, lon, units, language, key)
data = self.rest_caller.rest_call_get(path=path, headers={}, params={})
if data['code'] == 200:
logger.info("Weather API Call was successful.")
else:
logger.info("Weather API Call was not successful!")
return data
def call_server_weather(self, call_params: dict) -> dict:
"""
params: call_params { "city": "", "units": "", "language": "", "key": ""}
"""
data = None
key = call_params['key']
city = call_params['city']
units = call_params['units']
language = call_params['language']
path = "http://api.openweathermap.org/data/2.5/weather?q={}&units={}&lang={}&appid={}".format(city, units, language, key)
data = self.rest_caller.rest_call_get(path=path, headers={}, params={})
if data['code'] == 200:
logger.info("Weather API Call was successful.")
else:
logger.info("Weather API Call was not successful!")
return data
def save_data(self, data: dict, filename="./data/test.json") -> None:
try:
self.f.write_jsonfile(filename=filename, filedata=data)
except Exception as e:
logger.error("File saveing went wrong for File: {}\n With Exception: {}".format(filename, e))
def load_data(self, filename="./data/test.json") -> dict:
try:
loaded_data = self.f.read_jsonfile(filename=filename)
if "error" in loaded_data:
logger.error("File loading went wrong for File: {}".format(filename))
return {}
else:
return loaded_data
except Exception as e:
logger.error("File loading went wrong for File: {}\n With Exception: {}".format(filename, e))
def get_data_age_str(self, filename: str) -> str:
if self.f.is_file(filename):
last_mod_time_str = self.f.getLastModificationTimeString(filename)
return last_mod_time_str
else:
now = datetime.now()
date_time = now.strftime("%d.%m.%Y, %H:%M:%S")
return date_time
def get_data_age(self, filename: str) -> datetime:
if self.f.is_file(filename):
last_mod_time = self.f.getLastModificationTime(filename)
return last_mod_time
else:
return datetime.now()
def dataTimeDiff(self, filename: str, timediff=3600) -> int:
if self.f.is_file(filename):
date_obj = self.f.getLastModificationTime(filename)
diff = datetime.now() - date_obj
diff_in_hours = diff.total_seconds() // timediff
return int(diff_in_hours)
else:
return 0
def __init__(self):
self.rest_caller = Rest()
self.f = FileHandler()
def setUp(self):
self.fh = FileHandler("..")
class DatabaseHandler(object):
def __init__(self, database_path):
self.database_path = database_path
self.filehandler = FileHandler(relative_root_path="..")
if not self.filehandler.file_exists(self.database_path):
sql_command = self.filehandler.load_file("resources/setup.sql")
conn = sqlite3.connect(self.database_path)
cursor = conn.cursor()
cursor.executescript(sql_command)
conn.commit()
conn.close()
def add_user(self, telegram_id, username, firstname, lastname,
language_code, is_bot, is_active):
"""Adds a user to sqlite database
Args:
param1 (int): The telegram_id of a user.
param2 (str): The username of a user.
param3 (str): The firstname of a user.
param4 (str): The lastname of a user.
param5 (str): The language_code of a user.
param6 (str): The is_bot flag of a user.
"""
conn = sqlite3.connect(self.database_path)
cursor = conn.cursor()
cursor.execute("INSERT OR IGNORE INTO user VALUES (?,?,?,?,?,?,?)",
(telegram_id, username, firstname, lastname,
language_code, is_bot, is_active))
conn.commit()
conn.close()
def remove_user(self, telegram_id):
"""Removes a user to sqlite database
Args:
param1 (int): The telegram_id of a user.
"""
conn = sqlite3.connect(self.database_path)
cursor = conn.cursor()
cursor.execute("DELETE FROM user WHERE telegram_id=?", (telegram_id, ))
conn.commit()
conn.close()
def update_user(self, telegram_id, **kwargs):
"""Updates a user to sqlite database
Args:
param1 (int): The telegram_id of a user.
param2 (kwargs): The attributes to be updated of a user.
"""
conn = sqlite3.connect(self.database_path)
cursor = conn.cursor()
sql_command = "UPDATE user SET "
for key in kwargs:
sql_command += f"{key} ='{kwargs[key]}', "
sql_command = sql_command[:-2] + f" WHERE telegram_id={telegram_id}"
cursor.execute(sql_command)
conn.commit()
conn.close()
def get_user(self, telegram_id):
"""Returns a user by its id
Args:
param1 (int): The telegram_id of a user.
Returns:
list: The return value. A list containing all attributes of a user.
"""
conn = sqlite3.connect(self.database_path)
cursor = conn.cursor()
cursor.execute("SELECT * FROM user WHERE telegram_id = ?",
(telegram_id, ))
result = cursor.fetchone()
conn.commit()
conn.close()
return result
def add_url(self, url, items):
"""Add URL to database
Args:
url (string): URL to add
items (dict): A dictionary containing the items from the given feed.
Dictionary in the form: { 'Item_hash': {'active': True/False, 'last_date': Str}, ...}
"""
conn = sqlite3.connect(self.database_path)
cursor = conn.cursor()
cursor.execute(
"INSERT OR IGNORE INTO web (url, last_updated, items) VALUES (?,?,?)",
(url, dh.get_datetime_now(), json.dumps(items)))
conn.commit()
conn.close()
def remove_url(self, url):
"""Remove URL to database
Args:
url (string): URL to be removed
"""
conn = sqlite3.connect(self.database_path)
cursor = conn.cursor()
sql_command = "DELETE FROM web_user WHERE url=?;"
cursor.execute(sql_command, (url, ))
sql_command = "DELETE FROM web WHERE web.url NOT IN (SELECT web_user.url from web_user)"
cursor.execute(sql_command)
conn.commit()
conn.close()
def get_all_urls(self):
"""Return all URLs
Args:
None
Returns:
list: A list containing every URL
"""
conn = sqlite3.connect(self.database_path)
cursor = conn.cursor()
sql_command = "SELECT url FROM web;"
cursor.execute(sql_command)
result = cursor.fetchall()
conn.commit()
conn.close()
return result
def get_url_items(self, url):
"""Return saved items from a feed identified by URL
Args:
url (string): URL of the feed
Returns:
Dict: A dictionary containing the saved items from the given feed or empty dict if empty.
Dictionary in the form: { 'Item1_hash': {'active': True/False, 'last_date': Str}, ...}
"""
conn = sqlite3.connect(self.database_path)
cursor = conn.cursor()
cursor.execute("SELECT items FROM web WHERE url=?;", (url, ))
result = cursor.fetchone()
conn.commit()
conn.close()
if result:
return json.loads(result[0])
else:
return dict()
def update_url_items(self, url, items):
"""Update the saved items from a feed identified by URL
Args:
url (string): URL of the feed
items (dict): A dictionary containing the saved items from the given feed or empty dict if empty.
Dictionary in the form: { 'Item1_hash': {'active': True/False, 'last_date': Str}, ...}
Returns:
None
"""
conn = sqlite3.connect(self.database_path)
cursor = conn.cursor()
cursor.execute("UPDATE web SET items=? WHERE url=?;",
(json.dumps(items), url))
conn.commit()
conn.close()
def add_user_bookmark(self, telegram_id, url, alias):
"""Add a user bookmark
Args:
telegram_id (int): Telegram ID of the user
url (string): URL of the feed to add (URL must be already saved in web -table)
alias (string): Name/Alias of the feed for this user
Returns:
None
"""
conn = sqlite3.connect(self.database_path)
cursor = conn.cursor()
#self.add_url(url) # add if not exists
cursor.execute("INSERT OR IGNORE INTO web_user VALUES (?,?,?)",
(url, telegram_id, alias))
conn.commit()
conn.close()
def remove_user_bookmark(self, telegram_id, url):
"""Remove a user bookmark. Remove also from the URL table if there is no more bookmarks with this URL
Args:
telegram_id (int): Telegram ID of the user
url (string): URL of the bookmark to be removed from this user
Returns:
None
"""
conn = sqlite3.connect(self.database_path)
cursor = conn.cursor()
cursor.execute("DELETE FROM web_user WHERE telegram_id=? AND url = ?",
(telegram_id, url))
cursor.execute(
"DELETE FROM web WHERE web.url NOT IN (SELECT web_user.url from web_user)"
)
conn.commit()
conn.close()
def update_user_bookmark(self, telegram_id, url, alias):
"""Update a user bookmark.
Args:
telegram_id (int): Telegram ID of the user
url (string): URL of the bookmark to be updated from this user
alias (string): New name/alias of the feed for this user
Returns:
None
"""
conn = sqlite3.connect(self.database_path)
cursor = conn.cursor()
cursor.execute(
"UPDATE web_user SET alias='?' WHERE telegram_id=? AND url=?",
(alias, telegram_id, url))
conn.commit()
conn.close()
def get_user_bookmark(self, telegram_id, alias):
"""Get a user bookmark from the alias
Args:
telegram_id (int): Telegram ID of the user
alias (string): Name/alias of the feed to get for this user
Returns:
URL (String): URL of the feed identified with alias for this user
"""
conn = sqlite3.connect(self.database_path)
cursor = conn.cursor()
cursor.execute(
"SELECT url FROM web_user WHERE telegram_id =? AND alias =?;",
(telegram_id, alias))
result = cursor.fetchone()
conn.commit()
conn.close()
return result
def get_urls_for_user(self, telegram_id):
"""Get a user's URLs
Args:
telegram_id (int): Telegram ID of the user
Returns:
List: List of [url, alias] of this user.
"""
conn = sqlite3.connect(self.database_path)
cursor = conn.cursor()
cursor.execute("SELECT url, alias FROM web_user WHERE telegram_id =?;",
(telegram_id, ))
result = cursor.fetchall()
conn.commit()
conn.close()
return result
def get_users_for_url(self, url):
"""Get users and user's data for a given URL
Args:
URL (String): URL to search for
Returns:
List:
"""
conn = sqlite3.connect(self.database_path)
cursor = conn.cursor()
sql_command = "SELECT user.*, web_user.alias FROM user, web_user WHERE web_user.telegram_id = user.telegram_id " + \
"AND web_user.url =? AND user.is_active = 1;"
cursor.execute(sql_command, (url, ))
result = cursor.fetchall()
conn.commit()
conn.close()
return result
class DatabaseHandler(object):
def __init__(self, database_path):
self.database_path = database_path
self.filehandler = FileHandler(relative_root_path="..")
if not self.filehandler.file_exists(self.database_path):
sql_command = self.filehandler.load_file("resources/setup.sql")
conn = sqlite3.connect(self.database_path)
cursor = conn.cursor()
cursor.executescript(sql_command)
conn.commit()
conn.close()
def add_user(self, telegram_id, username, firstname, lastname,
language_code, is_bot, is_active):
"""Adds a user to sqlite database
Args:
param1 (int): The telegram_id of a user.
param2 (str): The username of a user.
param3 (str): The firstname of a user.
param4 (str): The lastname of a user.
param5 (str): The language_code of a user.
param6 (str): The is_bot flag of a user.
"""
conn = sqlite3.connect(self.database_path)
cursor = conn.cursor()
cursor.execute("INSERT OR IGNORE INTO user VALUES (?,?,?,?,?,?,?)",
(telegram_id, username, firstname, lastname,
language_code, is_bot, is_active))
conn.commit()
conn.close()
def remove_user(self, telegram_id):
"""Removes a user to sqlite database
Args:
param1 (int): The telegram_id of a user.
"""
conn = sqlite3.connect(self.database_path)
cursor = conn.cursor()
cursor.execute("DELETE FROM user WHERE telegram_id=?", (telegram_id, ))
conn.commit()
conn.close()
def update_user(self, telegram_id, **kwargs):
"""Updates a user to sqlite database
Args:
param1 (int): The telegram_id of a user.
param2 (kwargs): The attributes to be updated of a user.
"""
conn = sqlite3.connect(self.database_path)
cursor = conn.cursor()
sql_command = "UPDATE user SET "
for key in kwargs:
sql_command += str(key) + '=:' + str(key) + ','
sql_command = sql_command[:-1] + \
" WHERE telegram_id=:telegram_id"
kwargs['telegram_id'] = telegram_id
cursor.execute(sql_command, kwargs)
conn.commit()
conn.close()
def get_user(self, telegram_id):
"""Returns a user by its id
Args:
param1 (int): The telegram_id of a user.
Returns:
list: The return value. A list containing all attributes of a user.
"""
conn = sqlite3.connect(self.database_path)
cursor = conn.cursor()
cursor.execute("SELECT * FROM user WHERE telegram_id=?",
(telegram_id, ))
result = cursor.fetchone()
conn.commit()
conn.close()
return result
def add_url(self, url):
conn = sqlite3.connect(self.database_path)
cursor = conn.cursor()
cursor.execute(
"INSERT OR IGNORE INTO web (url, last_updated) VALUES (?,?)",
(url, dh.get_datetime_now()))
conn.commit()
conn.close()
def remove_url(self, url):
conn = sqlite3.connect(self.database_path)
cursor = conn.cursor()
sql_command = "DELETE FROM web_user WHERE url=?"
cursor.execute(sql_command, url)
sql_command = "DELETE FROM web " \
"WHERE web.url NOT IN (SELECT url from web_user) " \
"AND web.url NOT IN (SELECT url from web_channel) "
cursor.execute(sql_command)
conn.commit()
conn.close()
def update_url(self, url, **kwargs):
conn = sqlite3.connect(self.database_path)
cursor = conn.cursor()
sql_command = "UPDATE web SET "
for key in kwargs:
sql_command += str(key) + '=:' + str(key) + ','
sql_command = sql_command[:-1] + \
" WHERE url=:url"
kwargs['url'] = url
cursor.execute(sql_command, kwargs)
conn.commit()
conn.close()
def get_url(self, url):
conn = sqlite3.connect(self.database_path)
cursor = conn.cursor()
sql_command = "SELECT * FROM web WHERE url=?"
cursor.execute(sql_command, (url, ))
result = cursor.fetchone()
conn.commit()
conn.close()
return result
def get_all_urls(self):
conn = sqlite3.connect(self.database_path)
cursor = conn.cursor()
sql_command = "SELECT * FROM web;"
cursor.execute(sql_command)
result = cursor.fetchall()
conn.commit()
conn.close()
return result
def add_user_bookmark(self, telegram_id, url, alias):
conn = sqlite3.connect(self.database_path)
cursor = conn.cursor()
self.add_url(url) # add if not exists
cursor.execute("INSERT OR IGNORE INTO web_user VALUES (?,?,?)",
(url, telegram_id, alias))
conn.commit()
conn.close()
def remove_user_bookmark(self, telegram_id, url):
conn = sqlite3.connect(self.database_path)
cursor = conn.cursor()
cursor.execute(
"DELETE FROM web_user WHERE telegram_id=(?) AND url = (?)",
(telegram_id, url))
cursor.execute("DELETE FROM web "
"WHERE web.url NOT IN (SELECT url from web_user) "
"AND web.url NOT IN (SELECT url from web_channel)")
conn.commit()
conn.close()
def update_user_bookmark(self, telegram_id, url, alias):
conn = sqlite3.connect(self.database_path)
cursor = conn.cursor()
cursor.execute(
"UPDATE web_user SET alias=(?) WHERE telegram_id=(?) AND url=(?)",
(alias, telegram_id, url))
conn.commit()
conn.close()
def get_user_bookmark(self, telegram_id, alias):
conn = sqlite3.connect(self.database_path)
cursor = conn.cursor()
cursor.execute(
"SELECT web.url, web_user.alias, web.last_updated "
"FROM web, web_user "
"WHERE web_user.url = web.url "
"AND web_user.telegram_id=? "
"AND web_user.alias=?", (telegram_id, alias))
result = cursor.fetchone()
conn.commit()
conn.close()
return result
def get_urls_for_user(self, telegram_id):
conn = sqlite3.connect(self.database_path)
cursor = conn.cursor()
cursor.execute(
"SELECT web.url, web_user.alias, web.last_updated "
"FROM web, web_user "
"WHERE web_user.url = web.url "
"AND web_user.telegram_id=?", (telegram_id, ))
result = cursor.fetchall()
conn.commit()
conn.close()
return result
def get_users_for_url(self, url):
conn = sqlite3.connect(self.database_path)
cursor = conn.cursor()
cursor.execute(
"SELECT user.*, web_user.alias "
"FROM user, web_user "
"WHERE web_user.telegram_id = user.telegram_id "
"AND web_user.url=?", (url, ))
result = cursor.fetchall()
conn.commit()
conn.close()
return result
def add_channel(self, channel_name, url):
self.add_url(url)
conn = sqlite3.connect(self.database_path)
cursor = conn.cursor()
cursor.execute(
"INSERT INTO web_channel (url, channel_name, alias) VALUES (?,?,?)",
(url, channel_name, ''))
conn.commit()
conn.close()
def get_channels(self):
conn = sqlite3.connect(self.database_path)
cursor = conn.cursor()
cursor.execute("SELECT channel_name, url FROM web_channel")
result = cursor.fetchall()
conn.commit()
conn.close()
return result
def get_channels_for_url(self, url):
conn = sqlite3.connect(self.database_path)
cursor = conn.cursor()
cursor.execute(
"SELECT channel_name "
"FROM web_channel "
"WHERE url=? ", (url, ))
result = cursor.fetchall()
conn.commit()
conn.close()
return result
class Weather(Base):
def __init__(self,
observer: Observer,
name: str,
foreground_color="#ffffff",
font_name=""):
super().__init__(observer, name, foreground_color, font_name)
self.foreground_color = foreground_color
self.name = "Weather"
self.font_name = font_name
self.main_layout = QVBoxLayout()
self.setLayout(self.main_layout)
self.start_widget = StartWidget(self, self.foreground_color,
self.font_name)
self.main_layout.addWidget(self.start_widget)
self.r = RestSpecial()
self.data = None
self.file_handler = FileHandler()
self.time_periode = 3600
self.load_start_config()
def load_start_config(self):
config = self.r.load_data(
"./subscreens/weather_pkg/weather_app_config.json")
print("load_start_conifg", config)
if config.get("error") is None:
self.time_periode = float(config["time_periode"])
key = config["key"]
language = config["language"]
self.start_widget.set_key(key)
self.start_widget.set_language(language)
cities = config["cities"]
for city in cities:
if city["name"]:
self.start_widget.create_new_city(
city["name"], UnitSystem[city["unit_system"]])
else:
log.error("error: weather_app_config.json not loaded")
def save_config(self, config: dict):
config["time_periode"] = self.time_periode
self.r.save_data(config,
"./subscreens/weather_pkg/weather_app_config.json")
def get_data(self, config: dict) -> dict:
# TODO: read from config dictionary call param data
city = "Berlin"
filepath_str = "./data/weather_{}.json".format(city)
# check time age
log.debug("Last time file was modified: {}".format(
self.r.get_data_age_str(filepath_str)))
log.debug("Diff in hours: {}h".format(
self.r.dataTimeDiff(filepath_str)))
if self.r.dataTimeDiff(
filepath_str, self.time_periode
) >= 1 or not self.file_handler.is_file(filepath_str):
log.info("call server")
self.call_server(city=city, language="de")
self.save_data(filepath=filepath_str)
# keep old Data
# TODO: create a toggle/switch/config param for this threshold calling server for new data or not
elif self.r.dataTimeDiff(filepath_str, self.time_periode) < 1:
self.load_data(filepath=filepath_str)
return self.data
def save_data(self, filepath: str):
self.r.save_data(data=self.data, filename=filepath)
def load_data(self, filepath: str):
self.data = self.r.load_data(filename=filepath)
def showData(self):
if self.data is not None:
self.label.setText("{}°C".format(self.data['main']['temp']))
else:
self.label.setText("{}°C".format(
self.data["data"]['main']['temp']))
def call_server(self, city: str, language: str) -> dict:
"""
returns dictionary with response from Rest
if response HTTP Status != 200 returns None
"""
if not city:
city = "Berlin"
if not language:
language = "de" # en, fr, ... , https://openweathermap.org/current#multi
call_parameter = {
"city": city,
"units": "metric",
"language": language,
"key": "36dcf663b6964439a18574709e1d6eef"
}
new_data = self.r.call_server_weather(call_parameter)
if new_data['code'] == 200:
self.data = new_data['data']
else:
# check if self.data contains old data, if not set to empty dictionary,
# it will raise in exception in higher order class, for empty dictionary entrys
if not self.data:
self.data = {}
return self.data
