def cleanUp(conn, root): texts = txtparser.getTextCollection(root) cursor = conn.cursor() getItems = """ select item_name from blog_items order by creation_date desc """ delete = "delete from blog_items where item_name = ?" cursor.execute(getItems) data = cursor.fetchall() # get names into a list names = [] for t in texts: names.append(t.name) logging.debug('getting name ' + t.name) for rec in data: name = rec[0] if any(name in s for s in names): logging.debug(name+' exists in list') else: logging.debug('deleting '+name) cursor.execute(delete, [name]) conn.commit()
def insertAllTexts(conn, root): cursor = conn.cursor() texts = txtparser.getTextCollection(root) insert = """ insert into blog_items (item_name, item_title, item_text, author, creation_date, edit_date, static) values (?,?,?,?,?,?,?) """ update = """ update blog_items set item_title = ?, item_text = ?, edit_date = ?, author = ?, static = ? where item_name = ? """ exists = """ select count(*) from blog_items where item_name = ? """ for item in texts: cursor.execute(exists, [item.name]) res = cursor.fetchone()[0] title = item.title if title != None: title = title.strip() dt = item.unixtime if res: logging.debug('Updating') cursor.execute(update, (title, item.html, dt, item.author, item.static, item.name)) else: logging.debug('Inserting') cursor.execute(insert, (item.name, title, item.html, item.author, dt, dt, item.static)) if item.categories: insertNewCategories(conn, item.categories) insertCategoriesForText(conn, item.categories, item.name) logging.debug('categories: '+','.join(item.categories)) conn.commit()