def get_last_log_info(parser_name):
result_dict = dict(
log_name="Parser not started yet",
new_items=0,
total_channels=0,
total_programs=0,
log_cr_tm="Parser not started yet",
log_status="Parser not started yet",
execution_time=0,
)
db.execute(
""" SELECT * FROM log WHERE parser_name='%(parser_name)s'
ORDER BY cr_tm DESC; """
% {"parser_name": parser_name}
)
result = db.fetchone()
if not result:
return result_dict
return dict(
log_name=result.get("parser_name"),
new_items=result.get("count_new_items"),
total_channels=result.get("total_channels"),
total_programs=result.get("total_programs"),
log_cr_tm=result.get("cr_tm"),
log_status=result.get("success"),
execution_time=hours_minutes_seconds_from_seconds(result.get("execution_time")),
)
def save(self, table_name):
items = {key: value for key, value in self.__dict__.items() if (key in getattr(self, "db_fields")) and value}
fields_name = ",".join(items.keys())
fields_value = "','".join(items.values())
db.execute(
""" INSERT INTO %(table_name)s(%(keys)s) VALUES ('%(values)s');"""
% {"table_name": table_name, "keys": fields_name, "values": fields_value}
)
sql_connection.commit()
def update_table(table_name, item_id, key, value):
print(table_name)
print(item_id)
print(key)
print(value)
db.execute(
""" UPDATE %(table_name)s SET %(key)s='%(value)s' WHERE id=%(item_id)s;"""
% {"table_name": table_name, "key": key, "value": value, "item_id": item_id}
)
sql_connection.commit()
def insert_icons_into_files(list_of_links):
db.execute(
""" SELECT file_link FROM files WHERE file_link IN %(list_of_links)s; """
% {"list_of_links": tuple(list_of_links)}
)
links = [link["file_link"] for link in db.fetchall()]
db.executemany(
""" INSERT INTO files(file_link) VALUES(%(link)s); """,
[{"link": elem} for elem in list_of_links if elem not in links],
)
sql_connection.commit()
def insert_log_info(parser_name="channels", new_items=0, execution_time=0, success=True):
db.execute(
""" INSERT INTO log(parser_name, count_new_items,
execution_time, success)
VALUES ('%(parser_name)s', '%(new_items)s', '%(execution_time)s',
'%(success)s'); """
% dict(
parser_name=parser_name, new_items=int(new_items), execution_time=int(execution_time), success=success
)
)
sql_connection.commit()
def get_full_channels_info():
db.execute(
""" SELECT c.name, c.cr_tm, c.link, c.web_site, c.description, f.file_link as icon_link
FROM channels c LEFT JOIN files f ON c.icon_id=f.id; """
)
db_elements = []
for elem in db.fetchall():
db_elements.append(
{
"name": elem["name"].decode("utf-8"),
"cr_tm": elem["cr_tm"],
"link": elem["link"],
"web_site": elem["web_site"],
"description": elem["description"].decode("utf-8") if elem["description"] else None,
"icon_link": elem["icon_link"],
}
)
return db_elements
def save_channels_to_db(self, dict_of_elements):
new_elements_count = 0
self.insert_icons_into_files([link["icon"] for link in dict_of_elements.values() if link["icon"]])
for element in dict_of_elements.keys():
db.execute(
""" SELECT COUNT(id) FROM channels WHERE link='{link}'
OR name='{name}' """.format(
link=element, name=dict_of_elements[element]["name"]
)
)
if db.fetchone()[0] == 0:
db.execute(
""" INSERT INTO channels(name, link, icon_id)
VALUES ('{name}', '{link}',
(SELECT id FROM files WHERE file_link='{file_link}')); """.format(
name=dict_of_elements[element]["name"],
link=element,
file_link=dict_of_elements[element]["icon"],
)
)
new_elements_count += 1
sql_connection.commit()
return new_elements_count
def check_channel_field_exists(self, key):
db.execute(
""" SELECT COUNT(%(key)s) FROM channels WHERE id='%(channel_id)s' """
% {"key": key, "channel_id": self.channel_id}
)
def check_channel_exists(self):
db.execute(""" SELECT COUNT(id) FROM channels WHERE name='{name}';""".format(name=self.name))
return db.fetchone()[0]
def get_channel(channel_id):
db.execute(""" SELECT * FROM channels WHERE id='{channel_id}'; """.format(channel_id=channel_id))
return db.fetchone()
def get_channels_id_and_link():
db.execute(""" SELECT id, link FROM channels; """)
return [{"id": channel_id, "link": link} for channel_id, link in db.fetchall()]
