how to display data from database in table view in python

Question

I am new to python and been stuck with an issue, any one please help me to solve this issue. Requirement is I have created a sqlite database and created a table and also inserted values to it but the problem is I am not getting how to display that data from database in table view in python so please help me out from this.....advance thanks..

db_con = sqlite3.Connection
db_name = "./patientData.db"
createDb = sqlite3.connect(db_name)
queryCurs = createDb.cursor()
queryCurs.execute('''CREATE TABLE IF NOT EXISTS PATIENT
    (NAME TEXT NOT NULL, ID INTEGER PRIMARY KEY, AGE INTEGER NOT NULL, GENDER TEXT NOT NULL , EYE_TYPE TEXT NOT NULL)''')

pName = self.patientEdit.text()
pId =self.patientidEdit.text()
#pId1 = int(pId)
pAge = self.ageEdit.text()
#pAge1 = int(pAge)
pGender = self.patientgend.text()
pEye_type = self.eyeTypeEdit.text()
queryCurs.execute('''INSERT INTO PATIENT(NAME,ID,AGE, GENDER,EYE_TYPE) VALUES(?, ?, ?, ?, ?)''',(pName, pId, pAge, pGender, pEye_type))
print ('Inserted row')
createDb.commit()

now how can I dispaly data in a tableview /listview any example code is also helpful

Answer 1

This is a short, albeit complete example on how to achieve the expected result. The trick is to define a QSqlQueryModel and pass it to a QTableView; in this way you use the PyQt4 SQL modules instead of sqlite3 module, and the table can loop the query result automatically.

from PyQt4.QtSql import QSqlQueryModel,QSqlDatabase,QSqlQuery
from PyQt4.QtGui import QTableView,QApplication
import sys

app = QApplication(sys.argv)

db = QSqlDatabase.addDatabase("QSQLITE")
db.setDatabaseName("patientData.db")
db.open()

projectModel = QSqlQueryModel()
projectModel.setQuery("select * from patient",db)

projectView = QTableView()
projectView.setModel(projectModel)

projectView.show()
app.exec_()

Answer 2

I know this question has been a long time ago and the accepted one is for PyQt4. Because the API has been changed a bit on PyQt5, hope my answer can help someone using PyQt5.

from PyQt5 import QtWidgets, QtSql
# connect to postgresql
db = QtSql.QSqlDatabase.addDatabase("QPSQL")
db.setHostName(**)
db.setDatabaseName(**)
db.setPort(**) # int
db.setUserName(**)
db.setPassword(**)

# create tableview
tableView = QtWidgets.QTableView()

# create sqlquery
query = QtSql.QSqlQuery()
result = query.exec_("""select * from "table" """)
if result:
    model = QtSql.QSqlTableModel(db=db)
    model.setQuery(query)
    tableView.setModel(model)
    tableView.show()