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')"))

If you want us to help you, you must provide a minimal reproducible example, currently your code is not. — eyllanesc
– eyllanesc, Commented Jan 4, 2018 at 1:10

eyllanesc · Accepted Answer · 2018-01-04 01:38:27Z

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

Collectives™ on Stack Overflow

Can't INSERT data into database using sqlite and pyqt

1 Answer 1

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

Comments

Your Answer

Sign up or log in

Post as a guest

Related