Pagination in QtableWidget



  • Hi I am using python and have a QTableWidget that showing the data fetching from database.
    Now I want pagination , suppose i have 20 rows which are displayed in table correctly but i want paging so that 6 rows are displayed at a time and next 6 rows are displayed on next page.

    I tried the following:

    def listshow(self):
    db = MySQLdb.connect("localhost","root","","kailash" )
    cursor = db.cursor()
    try:
    a="SELECT * FROM customer_list"
    cursor.execute(a)

            self.tableWidget.setRowCount(cursor.rowcount) 
            self.tableWidget.setColumnCount(8)
    
            row_count = 0
            while True:        
                row = cursor.fetchone()
                if row == None:
                    break
    
                btn_name = 'print_btn_'+ str(row[0])
                
                button = QtGui.QPushButton(btn_name, self)
                button.setText('Print')
                button.setObjectName(btn_name)      
                self.tableWidget.setCellWidget(row_count, 7, button)
                button.clicked.connect( self.print_command )
                for col in range(0,7):
                    self.tableWidget.setItem(row_count, col , QtGui.QTableWidgetItem( str(row[col]) ))
    
    
                row_count = row_count+1
        except:
            db.close()

  • Qt Champions 2016

    @khakhil said:

    hi and welcome
    QTableWidget dont have pages, but an endless scrolling list.
    So what do you mean on next page?

    You can of cause, store the result list.
    add 6 elements to the Table
    add a button
    when button pressed, clear list and add 6 new.
    also button to go the other way.



  • @mrjj
    I tried the following for you suggested:

    self.pushButton_5.clicked.connect(self.next)  
    
    def next(self):
        self.pushButton_4.setEnabled(True)
        db = MySQLdb.connect("localhost","root","","kailash" )
    
        cursor = db.cursor()
        x=3
        try:
            cursor = db.cursor()
            
            b="SELECT * FROM customer_list  limit 3 offset %s" % (x) 
            cursor.execute(b)
            self.tableWidget.setRowCount(cursor.rowcount) 
            self.tableWidget.setColumnCount(8)
    
            row_count = 0
            while True:        
                row = cursor.fetchone()
                if row == None:
                    break
    
                btn_name = 'print_btn_'+ str(row[0])  
    
                button = QtGui.QPushButton(btn_name, self)
                button.setText('Print')
                button.setObjectName(btn_name)      
                self.tableWidget.setCellWidget(row_count, 7, button)
                button.clicked.connect( self.print_command )
                for col in range(0,7):
                    self.tableWidget.setItem(row_count, col , QtGui.QTableWidgetItem( str(row[col]) ))
    
                row_count = row_count+1
                
        except:
            db.close()
    

    but i am not getting that how offset value is changes when button is is pressed continuously.
    please help !!!
    thank you :)


  • Qt Champions 2016

    @khakhil said:

    b="SELECT * FROM customer_list limit 3 offset %s" % (x)

    oh that can return list and offset the result ?
    ( i never used SQL offset before)

    My idea was slightly more lame :)
    put result in list and have currentpos to that list
    then add from currentpos to +6 to list.


  • Moderators

    @khakhil
    just an idea (inspired by @mrjj 's answer)

    1. use QSoftFilterProxyModel and add custom properties. One for the count of items to display "per page" and one for the current page index.
    2. reimplement filterAcceptsRow() and check if the source row fulfills the following requirement:
    (currentPage * pageSize) <= source_row && source_row < (currentPage * pageSize) + pageSize
    
    1. hide the vertical scrollbar of the item view by setting the scrollbarpolicy to Qt::ScrollBarAlwaysOff
    2. place a custom scrollbar beside the table view. This can either be done by setting the viewport-margins (QAbstractScrollArea) and do the positioning of the scrollbar beside the viewport on resize events yourself or simply place it beside the whole table if thats enough for you.
    3. set the range of the custom scrollbar from 0 to (rowCount / pageSize)
    4. connect the scrollbar's valueChanged() signal to the filter-model's current-page property and invalidate the filter-model (QSortFilterProxyModel::invalidate())


  • @mrjj said:

    (x)

    yup... it is working very well . i am confused that how increase the value of x when next button is pressed again and again.


  • Qt Champions 2016

    well im pyt noob but
    if you set x=0 in constructor of mainwidnow
    then in button
    x=x+3; // or what ever

    Currently u set x=3 on each button click so u need to init it somewhere else
    and then in button increase / decrease.

    However, you should check out what @raven-worx writes about.
    its much smarter way. :)



  • @mrjj
    yes i tried the increasing and decreasing way but its not working.

    @raven-worx
    sorry, i tried the way u have suggested but unable to implement .


  • Qt Champions 2016

    @khakhil
    but
    if u change
    x=12
    it does change what it lists?


  • Moderators

    @khakhil said:

    sorry, i tried the way u have suggest but unable to implement .

    well i would be surprised if you could implement it in 30 mins being a Qt beginner.
    Maybe you can show what u got so far...



  • @mrjj said:

    @khakhil
    but
    if u change
    x=12
    it does change what it lists?

    when i change x=12.. i got 3 rows which are on the position 13,14,15 in database table.
    and next button only works once.


  • Qt Champions 2016

    @khakhil
    ok so offset works nice. good.
    then why did
    x=x+3
    or
    x=x-3

    not work?
    u should move
    x=3 to constructor
    also the creation and connect of the button should not be in
    "next" as it will create new button on all next() ?
    i assume this
    button = QtGui.QPushButton(btn_name, self)
    creates a new button ?



  • @khakhil
    hii thanks @mrjj and @raven-worx for helping.

    for this problem i have tried following and it doing very well

    def search(self, offset = 0):
        name_get = self.lineEdit.text()
        db = MySQLdb.connect("localhost","root","","kailash" )
    
        try:
            cursor = db.cursor()
            
            limit = 6
            
            if name_get == None or name_get == '':
                count_q = "SELECT  COUNT(1) AS num_rows FROM customer_list"                
            else:
                count_q = "SELECT COUNT(1) AS num_rows FROM customer_list WHERE name= '%s'" % (name_get) 
            cursor.execute(count_q)
            row = cursor.fetchone() 
            num_rows = row[0]
            
               
            if name_get == None or name_get == '':
                b = "SELECT * FROM customer_list"
                b += " limit "+ str(limit) + " offset " + str(offset)
            else:
                b = "SELECT * FROM customer_list WHERE name= '%s'" % (name_get)
             
            cursor.execute(b)
            self.tableWidget.setRowCount(cursor.rowcount + 2)
            self.tableWidget.setColumnCount(8)
            
            print cursor.rowcount
    
            row_count = 0
            while True:
                row = cursor.fetchone()
                if row == None:
                    break
    
                btn_name = 'print_btn_'+ str(row[0])
    
                button = QtGui.QPushButton(btn_name, self)
                button.setText('Print')
                button.setObjectName(btn_name)
                self.tableWidget.setCellWidget(row_count, 7, button)
                button.clicked.connect( self.print_command )
                for col in range(0,7):
                    self.tableWidget.setItem(row_count, col , QtGui.QTableWidgetItem( str(row[col]) ))
    
                row_count = row_count+1                
                
         
                
            if limit + offset < num_rows:
                next_offset = limit + offset
                self.nextbutton = QtGui.QPushButton("Next", self)
                self.tableWidget.setCellWidget(row_count+1, 4, self.nextbutton)
                self.nextbutton.setObjectName('next_btn_' + str(next_offset))
                self.nextbutton.clicked.connect( lambda: self.search(next_offset) )    
            
            if offset >= limit :
                prev_offset = offset - limit
                self.prebutton = QtGui.QPushButton("Previous", self)
                self.tableWidget.setCellWidget(row_count+1, 3, self.prebutton)
                self.prebutton.setObjectName('prev_btn_' + str(prev_offset))
                self.prebutton.clicked.connect( lambda: self.search(prev_offset) 
    

    but the problem is that whenever next button is clicked newly added row override the previous displayed rows. so if i have 10 rows in database and limit is set to 6 then if i clicked next button starting 4 rows are overrides but last two rows are same as previous last row.

    Is there any other approach to display database table data in the form of bunches????


  • Qt Champions 2016

    @khakhil said:
    Hi
    maybe you can just call
    self.tableWidget.clear()
    to start fresh each time you add items?



  • @mrjj said:

    self.tableWidget.clear()

    Thanks a lot @mrjj :) :) :)
    it works for me.
    thank you so much

    def search(self, offset = 0):
        self.tableWidget.clear()
        name_get = self.lineEdit.text()
        db = MySQLdb.connect("localhost","root","","kailash" )
    
        try:
            cursor = db.cursor()
            
            limit = 6
            
            if name_get == None or name_get == '':
                count_q = "SELECT  COUNT(1) AS num_rows FROM customer_list"                
            else:
                count_q = "SELECT COUNT(1) AS num_rows FROM customer_list WHERE name= '%s'" % (name_get) 
            cursor.execute(count_q)
            row = cursor.fetchone() 
            num_rows = row[0]
            
               
            if name_get == None or name_get == '':
                b = "SELECT * FROM customer_list"
                b += " limit "+ str(limit) + " offset " + str(offset)
            else:
                b = "SELECT * FROM customer_list WHERE name= '%s'" % (name_get)
             
            cursor.execute(b)
            self.tableWidget.setRowCount(cursor.rowcount + 2)
            self.tableWidget.setColumnCount(8)
            
            print cursor.rowcount
    
            row_count = 0
            while True:
                row = cursor.fetchone()
                if row == None:
                    break
    
                btn_name = 'print_btn_'+ str(row[0])
    
                button = QtGui.QPushButton(btn_name, self)
                button.setText('Print')
                button.setObjectName(btn_name)
                self.tableWidget.setCellWidget(row_count, 7, button)
                button.clicked.connect( self.print_command )
                for col in range(0,7):
                    self.tableWidget.setItem(row_count, col , QtGui.QTableWidgetItem( str(row[col]) ))
    
                row_count = row_count+1                
                
         
                
            if limit + offset < num_rows:
                next_offset = limit + offset
                self.nextbutton = QtGui.QPushButton("Next", self)
                self.tableWidget.setCellWidget(row_count+1, 4, self.nextbutton)
                self.nextbutton.setObjectName('next_btn_' + str(next_offset))
                self.nextbutton.clicked.connect( lambda: self.search(next_offset) )    
            
            if offset >= limit :
                prev_offset = offset - limit
                self.prebutton = QtGui.QPushButton("Previous", self)
                self.tableWidget.setCellWidget(row_count+1, 3, self.prebutton)
                self.prebutton.setObjectName('prev_btn_' + str(prev_offset))
                self.prebutton.clicked.connect( lambda: self.search(prev_offset) )                
                
                
        except Exception, e:

Log in to reply
 

Looks like your connection to Qt Forum was lost, please wait while we try to reconnect.