Can't INSERT data into database using sqlite and pyqt

Question

I am using Pycharm 2017.3. My python version is 3.4.
I am trying to insert data into table using sqlite4 and pyqt.
I have successfully created a table but could not insert even a single data.

Here is what I am importing

from PyQt4.QtGui import *
site_pack_path = "C:\\Python34\\Lib\\site-packages"
QApplication.addLibraryPath('{0}\\PyQt4\\plugins'.format(site_pack_path))
from PyQt4.QtSql import *
from PyQt4.QtCore import *
import sys
from urllib.request import Request, urlopen

Here is how I have created a table

query.exec_("CREATE TABLE dictin(ID INTEGER PRIMARY KEY AUTOINCREMENT, "
            "word varchar(100), searchShortDefn varchar(300),mnemonics varchar(500), "
            "defArr varchar(500), defDict varchar(500))")

Then there is a function whose purpose is to insert data into table which is not working.

def getWordsAndInsert(word, searchShortDefn, mnemonics, defArr, defDict):

    word=str(word)
    searchShortDefn=str(searchShortDefn)
    mnemonics=str(mnemonics)
    defArr=str(defArr)
    defDict=str(defDict)
    print(query.exec_("INSERT INTO dictin(word, searchShortDefn, mnemonics, defArr, defDict) "
                      "VALUES(word, searchShortDefn, mnemonics, defArr, defDict)"))

As you can see that I have converted everything to string as my contents are not string. Type of the argument being passed to the getWordsAndInsert method is

 class 'str'
 class 'str' 
 class 'str' 
 class 'list'
 class 'list'

respectively.

Here is the example of the function getWordsAndInsert being called

getWordsAndInsert("abbreviate",
              "shorten",
              "abbreviate consists of brevity(means brief)",
              ['(verb) reduce in scope while retaining essential elements', '(verb) shorten'],
              [{'syn': ['renounce'], 'sent': ['The King abdicated when he married a divorcee']}]) 

Though I can insert when I replace variables with actual string but obviously it does not get the work done.

 print(query.exec_("INSERT INTO dictin(word, searchShortDefn, mnemonics, defArr, defDict) "
                  "VALUES('word', 'searchShortDefn', 'mnemonics', 'defArr', 'defDict')"))

Answer 1

To establish the values in the query we can use prepare(), where we establish placeholders for the data, these placeholders we fill in through bindValue():

def getWordsAndInsert(word, searchShortDefn, mnemonics, defArr, defDict):
    word = str(word)
    searchShortDefn = str(searchShortDefn)
    mnemonics = str(mnemonics)
    defArr = str(defArr)
    defDict = str(defDict)

    query = QSqlQuery()
    query.prepare("""INSERT INTO dictin (word, searchShortDefn, mnemonics, defArr, defDict)
        VALUES (:word, :searchShortDefn, :mnemonics, :defArr, :defDict)""")

    query.bindValue(":word", word)
    query.bindValue(":searchShortDefn", searchShortDefn)
    query.bindValue(":mnemonics", mnemonics)
    query.bindValue(":defArr", defArr)
    query.bindValue(":defDict", defDict)

    if query.exec_():
        print("Successful")
    else:
        print("Error: ", query.lastError().text())