Python 3.6 and Sqlite3.

I have a database with something like 300K entries, each entry has several fields (date, username, etc) and a "text" one. I have a list of 200 words and for each word i have to return the number of entry in the database with the word in the "text" field. Currently, it takes around 1 second per word to do that. It's fine for 200 words but the list and the db will grow up in the future so i would like to optimize the speed. Here is how i proceed for now (I won't paste the entire code which is a bit too long but important part but if you need more i'll do it !).

I extract the data from the db with this line and i obtain a list of tuple, each tuple is a row in the db :

db = cursor.execute("select * from mydata").fetchall()

Then I loop on the list of word and i'm looking for the word in the db list like this (row[6] is my "text" field):

for word in wordList:
    for row in db:
        hasFound = re.compile(r"\b"+word.lower()+"\\b")
        if hasFound.search(row[6]):
            compteur += 1
    dicoVolume[word] = compteur

Is there a way to search faster ? Thanks !

Python 3.6 and Sqlite3.

I have a database with something like 300K entries, each entry has several fields (date, username, etc) and a "text" one. I have a list of 200 words and for each word i have to return the number of entry in the database with the word in the "text" field. Currently, it takes around 1 second per word to do that. It's fine for 200 words but the list and the db will grow up in the future so i would like to optimize the speed. Here is how i proceed for now (I won't paste the entire code which is a bit too long but important part but if you need more i'll do it !).

I extract the data from the db with this line and i obtain a list of tuple, each tuple is a row in the db :

db = cursor.execute("select * from mydata").fetchall()

Then I loop on the list of word and i'm looking for the word in the db list like this (row[6] is my "text" field):

for word in wordList:
    for row in db:
        hasFound = re.compile(r"\b"+word.lower()+"\\b")
        if hasFound.search(row[6]):
            compteur += 1
    dicoVolume[word] = compteur

Is there a way to search faster ?

Python 3.6 and Sqlite3.

I have a database with something like 300K entries, each entry has several fields (date, username, etc) and a "text" one. I have a list of 200 words and for each word i have to return the number of entry in the database with the word in the "text" field. Currently, it takes around 1 second per word to do that. It's fine for 200 words but the list and the db will grow up in the future so i would like to optimize the speed. Here is how i proceed for now (I won't paste the entire code which is a bit too long but important part but if you need more i'll do it !).

I extract the data from the db with this line and i obtain a list of tuple, each tuple is a row in the db :

db = cursor.execute("select * from tweet").fetchall()

Then I loop on the list of word and i'm looking for the word in the db list like this (row[6] is my "text" field):

for word in wordList:
    for row in db:
        hasFound = re.compile(r"\b"+word.lower()+"\\b")
        if hasFound.search(row[6]):
            compteur += 1
    dicoVolume[word] = compteur

Is there a way to search faster ? Thanks !

Python 3.6 and Sqlite3.

I have a database with something like 300K entries, each entry has several fields (date, username, etc) and a "text" one. I have a list of 200 words and for each word i have to return the number of entry in the database with the word in the "text" field. Currently, it takes around 1 second per word to do that. It's fine for 200 words but the list and the db will grow up in the future so i would like to optimize the speed. Here is how i proceed for now (I won't paste the entire code which is a bit too long but important part but if you need more i'll do it !).

I extract the data from the db with this line and i obtain a list of tuple, each tuple is a row in the db :

db = cursor.execute("select * from mydata").fetchall()

Then I loop on the list of word and i'm looking for the word in the db list like this (row[6] is my "text" field):

for word in wordList:
    for row in db:
        hasFound = re.compile(r"\b"+word.lower()+"\\b")
        if hasFound.search(row[6]):
            compteur += 1
    dicoVolume[word] = compteur

Is there a way to search faster ? Thanks !