Display records in Sqlite Pyqt4

[viandante]

Hi all,

I'm learning Pyqt4. What I would like to do is to display some records I have in an sqlite database in a TableView. I can't manage to do that although.

Those are the codes I'm using:

# -*- coding: utf-8 -*-
 
# Form implementation generated from reading ui file 'gui.ui'
#
# Created: Fri Sep 24 11:15:38 2010
#      by: PyQt4 UI code generator 4.7.2
#
# WARNING! All changes made in this file will be lost!
 
from PyQt4 import QtCore, QtGui
 
class Ui_MainWindow(object):
    def setupUi(self, MainWindow):
        MainWindow.setObjectName("MainWindow")
        MainWindow.resize(800, 600)
        self.centralwidget = QtGui.QWidget(MainWindow)
        self.centralwidget.setObjectName("centralwidget")
        self.lineEdit = QtGui.QLineEdit(self.centralwidget)
        self.lineEdit.setGeometry(QtCore.QRect(352, 16, 441, 91))
        self.lineEdit.setObjectName("lineEdit")
        self.pushButton = QtGui.QPushButton(self.centralwidget)
        self.pushButton.setGeometry(QtCore.QRect(260, 78, 80, 27))
        self.pushButton.setObjectName("pushButton")
        self.tableView = QtGui.QTableView(self.centralwidget)
        self.tableView.setGeometry(QtCore.QRect(2, 120, 791, 411))
        self.tableView.setObjectName("tableView")
        MainWindow.setCentralWidget(self.centralwidget)
        self.menubar = QtGui.QMenuBar(MainWindow)
        self.menubar.setGeometry(QtCore.QRect(0, 0, 800, 25))
        self.menubar.setObjectName("menubar")
        MainWindow.setMenuBar(self.menubar)
        self.statusbar = QtGui.QStatusBar(MainWindow)
        self.statusbar.setObjectName("statusbar")
        MainWindow.setStatusBar(self.statusbar)
        self.toolBar = QtGui.QToolBar(MainWindow)
        self.toolBar.setObjectName("toolBar")
        MainWindow.addToolBar(QtCore.Qt.ToolBarArea(QtCore.Qt.TopToolBarArea), self.toolBar)
 
        self.retranslateUi(MainWindow)
        QtCore.QMetaObject.connectSlotsByName(MainWindow)
 
    def retranslateUi(self, MainWindow):
        MainWindow.setWindowTitle(QtGui.QApplication.translate("MainWindow", "MainWindow", None, QtGui.QApplication.UnicodeUTF8))
        self.pushButton.setText(QtGui.QApplication.translate("MainWindow", "Run", None, QtGui.QApplication.UnicodeUTF8))
        self.toolBar.setWindowTitle(QtGui.QApplication.translate("MainWindow", "toolBar", None, QtGui.QApplication.UnicodeUTF8))

# -*- coding: utf-8 -*-

# Form implementation generated from reading ui file 'gui.ui'
#
# Created: Fri Sep 24 11:15:38 2010
#      by: PyQt4 UI code generator 4.7.2
#
# WARNING! All changes made in this file will be lost!

from PyQt4 import QtCore, QtGui

class Ui_MainWindow(object):
    def setupUi(self, MainWindow):
        MainWindow.setObjectName("MainWindow")
        MainWindow.resize(800, 600)
        self.centralwidget = QtGui.QWidget(MainWindow)
        self.centralwidget.setObjectName("centralwidget")
        self.lineEdit = QtGui.QLineEdit(self.centralwidget)
        self.lineEdit.setGeometry(QtCore.QRect(352, 16, 441, 91))
        self.lineEdit.setObjectName("lineEdit")
        self.pushButton = QtGui.QPushButton(self.centralwidget)
        self.pushButton.setGeometry(QtCore.QRect(260, 78, 80, 27))
        self.pushButton.setObjectName("pushButton")
        self.tableView = QtGui.QTableView(self.centralwidget)
        self.tableView.setGeometry(QtCore.QRect(2, 120, 791, 411))
        self.tableView.setObjectName("tableView")
        MainWindow.setCentralWidget(self.centralwidget)
        self.menubar = QtGui.QMenuBar(MainWindow)
        self.menubar.setGeometry(QtCore.QRect(0, 0, 800, 25))
        self.menubar.setObjectName("menubar")
        MainWindow.setMenuBar(self.menubar)
        self.statusbar = QtGui.QStatusBar(MainWindow)
        self.statusbar.setObjectName("statusbar")
        MainWindow.setStatusBar(self.statusbar)
        self.toolBar = QtGui.QToolBar(MainWindow)
        self.toolBar.setObjectName("toolBar")
        MainWindow.addToolBar(QtCore.Qt.ToolBarArea(QtCore.Qt.TopToolBarArea), self.toolBar)

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

    def retranslateUi(self, MainWindow):
        MainWindow.setWindowTitle(QtGui.QApplication.translate("MainWindow", "MainWindow", None, QtGui.QApplication.UnicodeUTF8))
        self.pushButton.setText(QtGui.QApplication.translate("MainWindow", "Run", None, QtGui.QApplication.UnicodeUTF8))
        self.toolBar.setWindowTitle(QtGui.QApplication.translate("MainWindow", "toolBar", None, QtGui.QApplication.UnicodeUTF8))

To copy to clipboard, switch view to plain text mode 

"""The user interface for our app"""
 
import os,sys
 
# Import Qt modules
from PyQt4 import QtCore,QtGui
from PyQt4 import QtSql
 
# Import the compiled UI module
from windowUI import Ui_MainWindow
#import PySqliteGUI
 
# Create a class for our main window
class Main(QtGui.QMainWindow):
	 def __init__(self):
		 QtGui.QMainWindow.__init__(self)
 
		 # This is always the same
		 self.ui=Ui_MainWindow()
		 self.ui.setupUi(self)
 
 
		 #My Code (Alessio)	
 
 
		 # table model
		 # ------------------------------------------------
		 db = QtSql.QSqlDatabase.addDatabase("QSQLITE")
		 db.setDatabaseName("/home/alessio/Dropbox/08_Trials/PySqliteQt/grundfos.sqlite")
		 ok=db.open()  
		 self.model = QtSql.QSqlTableModel()
		 self.model.setTable("GDK2009")
		 self.model.setEditStrategy(QtSql.QSqlTableModel.OnManualSubmit)
		 self.model.select()
		 print self.model.select()
 
 
		 # column headers
		 self.model.setHeaderData(0, QtCore.Qt.Horizontal, QtCore.QVariant("COLOR_COLOR"))
		 self.model.setHeaderData(1, QtCore.Qt.Horizontal, QtCore.QVariant("ANIMAL_COLOR"))
		 self.model.setHeaderData(2, QtCore.Qt.Horizontal, QtCore.QVariant("ANIMAL_NAME"))
		 self.model.setHeaderData(3, QtCore.Qt.Horizontal, QtCore.QVariant("ANIMAL_DESCRIPTION"))
 
 
		 # table view
		 # ------------------------------------------------
		 self.tableView = QtGui.QTableView()
		 self.tableView.setModel(self.model)
		 self.tableView.setItemDelegate(QtSql.QSqlRelationalDelegate(self.tableView))
		 self.tableView.setSelectionMode(QtGui.QTableView.SingleSelection)
		 self.tableView.setSelectionBehavior(QtGui.QTableView.SelectRows)
		 self.tableView.resizeColumnsToContents()
		 self.tableView.horizontalHeader().setStretchLastSection(True)
 
 
 
		 db.close()
 
 
 
def main():
 
 # Again, this is boilerplate, it's going to be the same on
 # almost every app you write
 app = QtGui.QApplication(sys.argv)
 window=Main()
 window.show()
 # It's exec_ because exec is a reserved word in Python
 sys.exit(app.exec_())
 
 #windowUI.lineEdit.SetText('prova')
 
if __name__ == "__main__":
 main()

"""The user interface for our app"""

import os,sys

# Import Qt modules
from PyQt4 import QtCore,QtGui
from PyQt4 import QtSql

# Import the compiled UI module
from windowUI import Ui_MainWindow
#import PySqliteGUI

# Create a class for our main window
class Main(QtGui.QMainWindow):
	 def __init__(self):
		 QtGui.QMainWindow.__init__(self)

		 # This is always the same
		 self.ui=Ui_MainWindow()
		 self.ui.setupUi(self)
		 
		 
		 #My Code (Alessio)	
		 
		
		 # table model
		 # ------------------------------------------------
		 db = QtSql.QSqlDatabase.addDatabase("QSQLITE")
		 db.setDatabaseName("/home/alessio/Dropbox/08_Trials/PySqliteQt/grundfos.sqlite")
		 ok=db.open()  
		 self.model = QtSql.QSqlTableModel()
		 self.model.setTable("GDK2009")
		 self.model.setEditStrategy(QtSql.QSqlTableModel.OnManualSubmit)
		 self.model.select()
		 print self.model.select()
		 
       
		 # column headers
		 self.model.setHeaderData(0, QtCore.Qt.Horizontal, QtCore.QVariant("COLOR_COLOR"))
		 self.model.setHeaderData(1, QtCore.Qt.Horizontal, QtCore.QVariant("ANIMAL_COLOR"))
		 self.model.setHeaderData(2, QtCore.Qt.Horizontal, QtCore.QVariant("ANIMAL_NAME"))
		 self.model.setHeaderData(3, QtCore.Qt.Horizontal, QtCore.QVariant("ANIMAL_DESCRIPTION"))

		 		 
		 # table view
		 # ------------------------------------------------
		 self.tableView = QtGui.QTableView()
		 self.tableView.setModel(self.model)
		 self.tableView.setItemDelegate(QtSql.QSqlRelationalDelegate(self.tableView))
		 self.tableView.setSelectionMode(QtGui.QTableView.SingleSelection)
		 self.tableView.setSelectionBehavior(QtGui.QTableView.SelectRows)
		 self.tableView.resizeColumnsToContents()
		 self.tableView.horizontalHeader().setStretchLastSection(True)
		 

		 
		 db.close()

	 

def main():
 
 # Again, this is boilerplate, it's going to be the same on
 # almost every app you write
 app = QtGui.QApplication(sys.argv)
 window=Main()
 window.show()
 # It's exec_ because exec is a reserved word in Python
 sys.exit(app.exec_())
 
 #windowUI.lineEdit.SetText('prova')

if __name__ == "__main__":
 main()

To copy to clipboard, switch view to plain text mode 

[tbscope]

I think you need to set the database in the model before using it?

Edit: Hmm, no, it's automatically set.
Can you see if the database could be opened?

[viandante]

If I print ok it says true. I don't know other ways to check.

[viandante]

Ok, I've been able to solve the problem.

Now it does a really weird thing, it works for tables with a small amount of records, but it doesn't with bigger data sets (50000 records).

Ideas why this is happening?

By the way, the code is:

import sys
from PyQt4 import QtCore, QtGui, QtSql
from tr01_form import Ui_MainWindow
 
class Database:
    def __init__(self, parent = None):
        self.data = QtSql.QSqlDatabase.addDatabase("QSQLITE")
        self.data.setDatabaseName("grundfos.sqlite")
        self.data.open()
 
class Model(QtSql.QSqlTableModel):
    def __init__(self, parent = None):
        super(Model, self).__init__(parent)
        self.setEditStrategy(QtSql.QSqlTableModel.OnRowChange)
        self.setTable("GDK2009")
        self.select()
 
class Main(QtGui.QMainWindow):
    def __init__(self):
        QtGui.QMainWindow.__init__(self)
 
        self.db = Database()
        self.model = Model(self)
        self.ui = Ui_MainWindow()
        self.ui.setupUi(self)
        self.ui.tableView.setModel(self.model)
        self.ui.tableView.showColumn(1)
 
        for n in range(45764):
			n=n+10
			self.ui.tableView.setRowHidden(n, True)
			#self.ui.tableView.setColumnHidden(3, True)
			#self.ui.tableView.rowAt(2)
 
 
 
def main():
    app = QtGui.QApplication(sys.argv)
    window = Main()
    window.show()
    sys.exit(app.exec_())
 
if __name__ == "__main__":
    main()

import sys
from PyQt4 import QtCore, QtGui, QtSql
from tr01_form import Ui_MainWindow

class Database:
    def __init__(self, parent = None):
        self.data = QtSql.QSqlDatabase.addDatabase("QSQLITE")
        self.data.setDatabaseName("grundfos.sqlite")
        self.data.open()

class Model(QtSql.QSqlTableModel):
    def __init__(self, parent = None):
        super(Model, self).__init__(parent)
        self.setEditStrategy(QtSql.QSqlTableModel.OnRowChange)
        self.setTable("GDK2009")
        self.select()

class Main(QtGui.QMainWindow):
    def __init__(self):
        QtGui.QMainWindow.__init__(self)
        
        self.db = Database()
        self.model = Model(self)
        self.ui = Ui_MainWindow()
        self.ui.setupUi(self)
        self.ui.tableView.setModel(self.model)
        self.ui.tableView.showColumn(1)

        for n in range(45764):
			n=n+10
			self.ui.tableView.setRowHidden(n, True)
			#self.ui.tableView.setColumnHidden(3, True)
			#self.ui.tableView.rowAt(2)
		


def main():
    app = QtGui.QApplication(sys.argv)
    window = Main()
    window.show()
    sys.exit(app.exec_())

if __name__ == "__main__":
    main()

To copy to clipboard, switch view to plain text mode