Pagination in QtableWidget

khakhil

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

mrjj

@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.

khakhil

@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 :)

mrjj

@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.

raven-worx

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

use QSoftFilterProxyModel and add custom properties. One for the count of items to display "per page" and one for the current page index.
reimplement filterAcceptsRow() and check if the source row fulfills the following requirement:

(currentPage * pageSize) <= source_row && source_row < (currentPage * pageSize) + pageSize

hide the vertical scrollbar of the item view by setting the scrollbarpolicy to Qt::ScrollBarAlwaysOff
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.
set the range of the custom scrollbar from 0 to (rowCount / pageSize)
connect the scrollbar's valueChanged() signal to the filter-model's current-page property and invalidate the filter-model (QSortFilterProxyModel::invalidate())

khakhil

@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.

mrjj

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. :)

khakhil

@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 .

mrjj

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

raven-worx

@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...

khakhil

@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.

mrjj

@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

@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????

mrjj

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

khakhil

@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: