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