代码之家 › 专栏 › 技术社区 › royatirek

无法使用sqlite和pyqt将数据插入数据库

qtsql pyqt4 pyqt python-3.x python

royatirek · 技术社区 · 7 年前

我正在使用Pycharm 2017.3。我的python版本是3.4。
我试图使用sqlite4和pyqt将数据插入表中。
我已成功创建了一个表,但无法插入任何数据。

以下是我要介绍的内容

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

下面是我如何创建表的

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

然后有一个函数,其目的是将数据插入到不工作的表中。

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

正如您所看到的,我已经将所有内容都转换为字符串,因为我的内容不是字符串。传递给getWordsAndInsert方法的参数类型为

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

分别地

下面是调用函数getWordsAndInsert的示例

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']}])

虽然我可以在用实际字符串替换变量时插入,但显然这并不能完成工作。

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

1 回复 | 直到 7 年前

eyllanesc Yonghwan Shin 7 年前

要在查询中建立值,我们可以使用 prepare() ,其中我们为数据建立占位符,这些占位符我们通过 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())