Search/add in db

Question

So, I have a db with fields (word TEXT, par1 INTEGER). So, I parse some txt file with some text there and get words list([word1,word2,word3]). And I need to check if the word from list in db, if word in db I change par1, if NOT in db - add this words to db. How can I do it?

we can't help you unless you post some code that you wrote to attempt your problem. — Seekheart, May 12 '16 at 12:56

score 1 · Answer 1 · edited May 23 '17 at 11:59

See the top answer here for an excellent introduction to connecting to a database with Python, if you happen to be using MySQL.

Depending on what database you are accessing, you will have to change the first two lines:

import MySQLdb
#Make a connection to the database where your fields are stored
db = MySQLdb.connect("host machine", "dbuser", "password", "dbname")
cur = db.cursor() #Create a cursor object

#Replace this with a line parsing your text file
words = ['word1','word2','word3']

#Loop through each word found in the file
for word in words:
    #If the word was found in the 'word' column, we arbitrarily change its par1
    if cur.execute("SELECT COUNT(word) FROM YourTable WHERE par1 = '{}'".format(word)):
        cur.execute("UPDATE YourTable SET par1 = par1 + 1000 WHERE word = '{}'".format(word))
    else: #But, if it did not exist, add it
        cur.execute("SELECT MAX(par1) FROM YourTable") #Execute a query
        maxIndex = db.cursor.fetchall() #Get a result
        cur.execute("INSERT INTO YourTable ('{}',{})".format(word,maxIndex+1))

#Be sure to close the connection when done
db.close

You'll replace the explicit definition of words with parsing of the text file; also replace "YourTable" with the actual name of your table in your database.

Also, I am arbitrarily changing par1, as you mentioned; maybe you can explain what you want to change it to, as well as what you want to set it to when inserting a new record?

Search/add in db

1 Answers1