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