[PyQt] display the contents of the table in descending order of the date

[atlass218]

Hello;
I tried to display the contents of my "loc35R" table in a Widget table that was created by QtDesigner.
the display looks good, but with disordered dates.
I tried to display with a selection of dates in descending order, but it did not work

here is the code simplifies the functions used for this :

creation of the database and the table :

    
def create_database_and_table(self):

	monitor1=self.lineEdit_monitor1.text()         
	monitor2=self.lineEdit_monitor2.text()              
	date_releves = self.lineEdit_date.text()
	connexion = sqlite3.connect('ils35R.db')
	curseur = connexion.cursor()
	curseur.execute('''CREATE TABLE IF NOT EXISTS loc35R (date_releves_reading text text, monitor1_reading text, monitor2_reading text)  ''')        
	curseur.execute('''INSERT INTO rloc35R VALUES (?,?,?) ''',(date_releves, monitor1, monitor2))            
	connexion.commit()                 
	curseur.close()
	connexion.close()

read and display the contents of the table :

def display_table_loc35R(self):
         						
	conn = sqlite3.connect ('ils35R.db')
	curseur=conn.cursor()               
	c=curseur.execute("SELECT * FROM loc35R  ORDER BY date_releves_reading DESC")			  
	liste_table_loc35R=c.fetchall()
	self.tableWidget_loc35R.setRowCount(len(liste_table_loc35R))
	
	for lignes_loc35R in range(len(liste_table_loc35R)):
		for colonnes_loc35R in range(3):
			self.tableWidget_loc35R.setItem(lignes_loc35R,colonnes_loc35R, QTableWidgetItem(liste_table_loc35R[lignes_loc35R][colonnes_loc35R]))
										
	self.tableWidget_loc35R.resizeRowsToContents()
	self.tableWidget_loc35R.resizeColumnsToContents()
			 
	curseur.close()
	conn.close()
                
	QtCore.QTimer.singleShot(1000, self.display_table_loc35R)

in the picture above, we notice that only days that are sorted in descending order
thanks for help

[Denni]

Using that oh so wonderful but extremely lame Designer -- welcome to the pain that is the Designer ;)

That being said are you married to that designer and/or would you like to learn to code PyQt like it was meant to be coded -- which btw is just as quick once you understand it as using the designer but renders you something that not only do you understand what it is doing but you can easily modify going forward -- further it is no longer that dangerous black-box untouchable code.

If yes I can help you learn how to do this using your above example -- as they have stated this is an educational forum not just a questions and answer forum. Note: You will need to post the entire code base (designer garbage included) so that I can help you learn how to rewrite that into proper quality pyqt

[jan_pips]

That being said are you married to that designer and/or would you like to learn to code PyQt like it was meant to be coded -- which btw is just as quick once you understand it as using the designer but renders you something that not only do you understand what it is doing but you can easily modify going forward -- further it is no longer that dangerous black-box untouchable code.

I could not agree more. I used to be married also to QT Designer but since I have met that wonderful mistress know by name "Pure Code" I have dumped deceptively "easy" at first glance but extremally hard to "maintain" and "understand" wife know by name "QT Designer".

[atlass218]

I try to save time, working with the designer Qt Designer: I create the design of my application with this designer without any problem (for information I know what I'm doing with this designer).
currently, I have not been able to rank the data in descending order of the first column ("d m Y")

[Denni]

Okay I guess your idea and my idea of saving time are totally different. I look at the project as a whole -- if doing A saves me 5 minutes on the front end but causes me to lose 10 minutes in the middle and an additional 10 minutes towards the end that initial savings of time is really a total illusion and really is a waste of 15 minutes overall. That said you ARE going to lose time dealing with these black-box objects you are creating it is just the nature of that beast -- and frankly creating a GUI using PyQt versus the Designer saves you a lot of time over all but it is your choice.

Next as to your issue all I can do is point out how it is done via the proper coding technique you will have to figure out how to make that work with your Designer garbage

self.tableWidget_loc35R.sortItems(0, Qt.DescendingOrder)

I believe that is all you should need but I cannot test it to be sure

[atlass218]

I add this line:

self.tableWidget_loc35R.sortItems(0, Qt.DescendingOrder)

after :

self.tableWidget_loc35R.resizeRowsToContents()
self.tableWidget_loc35R.resizeColumnsToContents()

nothing done
but I intervened on the list "liste_table_loc35R" by modifying the code like this:

def display_table_loc35R(self):
                                 
    conn = sqlite3.connect ('ils35R.db')
    curseur=conn.cursor()               
    c=curseur.execute("SELECT * FROM loc35R  ORDER BY date_releves_reading DESC")             
    liste_table_loc35R=c.fetchall()
	##########################
	#lines added :
	liste_table_loc35R.sort(key=lambda tup: tup[0])
    liste_table_loc35R.sort(reverse = True)
	############################
    self.tableWidget_loc35R.setRowCount(len(liste_table_loc35R))
     
    for lignes_loc35R in range(len(liste_table_loc35R)):
        for colonnes_loc35R in range(3):
            self.tableWidget_loc35R.setItem(lignes_loc35R,colonnes_loc35R, QTableWidgetItem(liste_table_loc35R[lignes_loc35R][colonnes_loc35R]))
                                         
    self.tableWidget_loc35R.resizeRowsToContents()
    self.tableWidget_loc35R.resizeColumnsToContents()
              
    curseur.close()
    conn.close()           
    QtCore.QTimer.singleShot(1000, self.display_table_loc35R)

here is the result in image :

[Denni]

Okay glad you got it to work -- however I would suggest since you are sorting the query results list within your code because it appears the results are not sorted -- I would remove the now unnecessary ORDER BY within your SELECT query no need for that extra work since it is obviously not doing you any good -- which to me seems a bit weird. If it were me I would want to know why my query is not coming back as I would expect it to so at least I understand why it does what it does.

[atlass218]

Hi;
For information, to answer your remarks which are interesting and valuable: the old form under which the date was written is the French form ("% d% m% Y"). Apparently, that's why ORDER BY did not work. As a result I had to change the shape of the date to format ("%Y% / m% / %d").
Indeed, by removing the "ORDER BY" request in SELECT, the code works as well.
Now that the date is written in American or English format ("%Y / %m% / %d"), I have re-entered the sql "ORDER BY" request and have deleted the two previous lines added.

#lines added :
liste_table_loc35R.sort(key=lambda tup: tup[0])
liste_table_loc35R.sort(reverse = True)

the last code now works well :

def display_table_loc35R(self):
                                  
    conn = sqlite3.connect ('ils35R.db')
    curseur=conn.cursor()               
    c=curseur.execute("SELECT * FROM loc35R  ORDER BY date_releves_reading DESC")             
    liste_table_loc35R=c.fetchall()
    self.tableWidget_loc35R.setRowCount(len(liste_table_loc35R))
      
    for lignes_loc35R in range(len(liste_table_loc35R)):
        for colonnes_loc35R in range(3):
            self.tableWidget_loc35R.setItem(lignes_loc35R,colonnes_loc35R, QTableWidgetItem(liste_table_loc35R[lignes_loc35R][colonnes_loc35R]))
                                          
    self.tableWidget_loc35R.resizeRowsToContents()
    self.tableWidget_loc35R.resizeColumnsToContents()
               
    curseur.close()
    conn.close()           
    QtCore.QTimer.singleShot(1000, self.display_table_loc35R)

Now, the question is how to fix this problem if I want to display my information in the French format date ("%d / %m / %Y")

[Denni]

Well if you are using a database that supports Stored Procedures I would create those queries there -- personally I would make two separate queries one that returns a sorted english version and one that returns the sorted french version

If the database does not support Stored Procedures then I would to that via code -- (aka write two different "Stored Procedures" to handle return the correct format -- but in either case the correction takes place within the SELECT not within your code as the databases internal engine does it a lot more efficiently than the code base would.

SELECT FORMAT (date_releves_reading, 'dd-MM-yyyy') as FormtdDate FROM loc35R ORDER BY FDate DESC
--or--
SELECT FORMAT (date_releves_reading, 'yyyy-MM-dd') as FormtdDate FROM loc35R ORDER BY FDate DESC

Note as trick we sometimes used to get the proper sorting but not change the displayed formatting you do the above but simple bring the date in again into the query and use it for display while sorting by the FormtdDate which you then do not display

[atlass218]

I tried both your queries, but it did not work: it shows me by management of eception that my database is empty. which is not the case

here is the modified line :

c=curseur.execute("SELECT FORMAT (date_releves_reading, 'dd-MM-yyyy') as FormtdDate FROM loc35R ORDER BY FDate DESC")