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 save_programs(channel_id, list_of_programs_classes):
db.executemany(
""" INSERT INTO tv_programs(name, genre, show_date, show_time,
channel_id) VALUES(%(name)s, %(genre)s, %(show_date)s, %(show_time)s,
%(channel_id)s); """,
[
{
"name": cls.name,
"genre": cls.genre,
"show_date": cls.show_date,
"show_time": cls.show_time,
"channel_id": channel_id,
}
for cls in list_of_programs_classes
],
)
sql_connection.commit()
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
