how to add data from ui (made using pyqt) to sqlite db?

Question

i have made a simple pyqt gui with QLineEdit and QPushButton. i like to add showurl(in con.py given below) to sqlite db. the code :
ui.py

from PyQt4 import QtCore, QtGui

try:
    _fromUtf8 = QtCore.QString.fromUtf8
except AttributeError:
    def _fromUtf8(s):
        return s

try:
    _encoding = QtGui.QApplication.UnicodeUTF8
    def _translate(context, text, disambig):
        return QtGui.QApplication.translate(context, text, disambig, _encoding)
except AttributeError:
    def _translate(context, text, disambig):
        return QtGui.QApplication.translate(context, text, disambig)

class Ui_Form(object):
    def setupUi(self, Form):
        Form.setObjectName(_fromUtf8("Form"))
        Form.resize(400, 300)
        self.verticalLayout_2 = QtGui.QVBoxLayout(Form)
        self.verticalLayout_2.setObjectName(_fromUtf8("verticalLayout_2"))
        self.verticalLayout = QtGui.QVBoxLayout()
        self.verticalLayout.setObjectName(_fromUtf8("verticalLayout"))
        self.inserturl = QtGui.QLineEdit(Form)
        self.inserturl.setObjectName(_fromUtf8("inserturl"))
        self.verticalLayout.addWidget(self.inserturl)
        spacerItem = QtGui.QSpacerItem(20, 40, QtGui.QSizePolicy.Minimum, QtGui.QSizePolicy.Expanding)
        self.verticalLayout.addItem(spacerItem)
        self.insertdb_btn = QtGui.QPushButton(Form)
        self.insertdb_btn.setObjectName(_fromUtf8("insertdb_btn"))
        self.verticalLayout.addWidget(self.insertdb_btn)
        self.verticalLayout_2.addLayout(self.verticalLayout)

        self.retranslateUi(Form)
        QtCore.QMetaObject.connectSlotsByName(Form)

    def retranslateUi(self, Form):
        Form.setWindowTitle(_translate("Form", "Form", None))
        self.insertdb_btn.setText(_translate("Form", "ok", None))  

con.py

import sys
from PyQt4 import QtCore, QtGui, QtSql
from insertdb2_ui import Ui_Form

def createConnection():
    db = QtSql.QSqlDatabase.addDatabase('QSQLITE')
    db.setDatabaseName('webscrap.db')
    if db.open():
        return True
    else:
        print db.lastError().text()
        return False

class Test(QtGui.QWidget, Ui_Form):
    def __init__(self):
        super(Test, self).__init__()
        self.setupUi(self)
        self.insertdb_btn.clicked.connect(self.onClick)


    def onClick(self):
        showurl = self.inserturl.text()

        print showurl
if __name__ == "__main__":

    app = QtGui.QApplication(sys.argv)
    if not createConnection():
        sys.exit(1)
    window = Test()
    window.show()
    sys.exit(app.exec_())  

i made the input data ie. showurl print on terminal. i want to add the showurl to sqlitedb webscrap.db which has fields id and url.

I am also confused about using model view(QSqlTableModel, QDataWidgetMapper).

Answer 1

You can use the QtSql.QSqlQuery() class to create query objects that interact with your database. To execute a query (in this case, an INSERT query), perform the following steps:

1. Instantiate a QSqlQuery() object.
2. Call the prepare() method on that object, passing to the prepare() method the SQL text that you want to execute.
3. Use the addBindValue() method to pass in variables to your query -- in this case, you want to pass in showurl.
4. Finally, call exec_() on the query object to execute the query. If there is an error, you can retrieve it by calling on the query object lastError().text().

So, in code, you would need to change your onClick() function to:

def onClick(self):
    showurl = self.inserturl.text()
    query_object = QtSql.QSqlQuery()
    query_object.prepare("INSERT INTO table (url) VALUES (?)")
    query_object.addBindValue(showurl)
    if not query_object.exec_():
        print query_object.lastError().text()

Please note that I did not specify a table name to insert into because you provided field names (id and url) but not a table name; therefore the query string uses a fake table name (table). You should replace this with a real table name. If you do not have a table in your database, you need to create one. Please reference https://www.sqlite.org/lang.html for documentation on SQLite commands (e.g. for creating tables). Please also refer to the PyQt4 documentation on QtSql.QSqlQuery(): http://pyqt.sourceforge.net/Docs/PyQt4/qsqlquery.html

For QSqlTableModel and QDataWidgetMapper, it is difficult to help without having a specific question to answer about them, but I highly recommend that you check out this excellent guide to both of those classes: http://doc.qt.digia.com/qq/qq21-datawidgetmapper.html. This guide does not use PyQt4 (instead it uses Qt alone, so the code is in C++), but the code is sparse and the concepts should resonate even if you are not familiar with C++.