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