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)
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
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 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