I am writing a search indexer for a site I built. It counts the number of times a word is used in a given story allowing one use of the word per chapter. I have also attempt to weight words in the titles and the descriptions more heavily by including them in every chapter that is indexed.I would like to know if I am handling transactions properly for this, and preventing dead-locks while the update is performed on the database. I would also like to know if I can clarify the sql statements any or if there is a better way of concatenating the columns.#!/usr/bin/python# Generates the search index for the writersiteimport mysql.connectorimport refrom sets import Setfrom collections import CounterMIN_WORD_LENGTH = 3SQL_GET_REINDEXABLE = SELECT id,titleFROM StoriesWHERE Stories.needsReindex = TRUESQL_GET_CHAPTER_BY_STORY_ID = SELECT id FROM Chapters WHERE StoryId = %s;SQL_GET_CHAPTER_SAMPLE_BY_ID = SELECT  Chapters.id,  CONCAT(Stories.title, ' ', IFNULL(Chapters.title, ' '), ' ',    IFNULL(Stories.description, ' '), ' ', text) as textFROM Chapters, StoriesWHERE Chapters.StoryId = Stories.id AND Chapters.id = %s;SQL_FREQ_UPSERT = REPLACE INTO FreqIdx(word, StoryId, weight) VALUES    (%s, %s, %s)SQL_DELETE_OLD_INDEX = DELETE FROM FreqIdx WHERE StoryId = %s;SQL_UPDATE_STORY_INDEX_STATUS = UPDATE Stories SET needsReindex = FALSE WHERE id = %sdef deleteStoryIndexByStoryId(db, StoryId):    # delete the old index    print(Deleting Index of Story {}.format(StoryId))    cursor = db.cursor()    cursor.execute(SQL_DELETE_OLD_INDEX, (StoryId,))    cursor.close()    db.commit()def indexText(wordDict, text):    text = re.sub(r[^A-Za-z' ], ' ', text).lower()    words = Set()    for word in text.split():        words.add(word)    for word in words:        wordDict[word] = wordDict[word] + 1    return wordDictdef processChapterById(db, chapterId, wordDict):    cur = db.cursor()    cur.execute(SQL_GET_CHAPTER_SAMPLE_BY_ID, (chapterId,))    for (chid, text) in cur:        wordDict = indexText(wordDict, text)        print(Chapter: {}, length {}, wordDict {}.format(            chid, len(text.split()), len(wordDict)))    cur.close()    return wordDictdef markStoryAsIndexedById(db, StoryId):    cur = db.cursor()    cur.execute(SQL_UPDATE_STORY_INDEX_STATUS, (StoryId,))    cur.close()    db.commit()def processStoryById(db, StoryId):    deleteStoryIndexByStoryId(db, StoryId)    # word dict to store the new index temporary    wordDict = Counter()    # get the chapters    cur = db.cursor(buffered=True)    cur.execute(SQL_GET_CHAPTER_BY_STORY_ID, (StoryId,))    for (ChapterId,) in cur:        wordDict = processChapterById(db, ChapterId, wordDict)    cur.close()    # replace the index    for word in wordDict:        cur = db.cursor()        cur.execute(SQL_FREQ_UPSERT, (word, StoryId, wordDict[word],))        cur.close()    db.commit()    markStoryAsIndexedById(db, StoryId)def main():    # Open Db connection    db = mysql.connector.connect(            host=localhost,            user=writersite,            passwd=password,            db=writersite)    cur = db.cursor(buffered=True)    cur.execute(SQL_GET_REINDEXABLE)    for (StoryId, Title) in cur:        print(processing Story {} - {}.format(StoryId, Title))        processStoryById(db, StoryId)     db.close()# main sentinelif __name__ == __main__:    main()The table containing the index looks like the following:CREATE TABLE FreqIdx (    word VARCHAR(255) NOT NULL,    weight INT(11) DEFAULT '0',    StoryId INT(11),    CONSTRAINT FreqIdx_ibfk_1 FOREIGN KEY (StoryId) REFERENCES Stories (id));CREATE UNIQUE INDEX freq_idx_word__story_id ON FreqIdx (word, StoryId);CREATE INDEX StoryId ON FreqIdx (StoryId);The relevant relations are shown below:
Search Indexer Transaction Safety
python;mysql