Python Forum
[PyQt] to display cotent of database between two dates
Thread Rating:
  • 0 Vote(s) - 0 Average
  • 1
  • 2
  • 3
  • 4
  • 5
[PyQt] to display cotent of database between two dates
#1
hi; I try to display the cotent of my database between two dates but I have somme problems.
there is the code of the function with which I want to display database content between two dates.

def select_by_dates (self):
              
	start_date =self.lineEdit_start_date.text()             
	end_date = self.lineEdit_end_date.text()
			
	try :
        
		conn = sqlite3.connect ('correction.db')
		curseur=conn.cursor()
		c=curseur.execute("SELECT * FROM correction_loc35R WHERE date_correction BETWEEN start_date AND end_date ")
					
		
		liste_select_by_dates_correction=c.fetchall()
		nbr_rows=len(liste_select_by_dates_correction)
				  
		if nbr_rows != 0 :
            
			# set row count
			self.tableWidget_select_by_dates_correction.setRowCount(nbr_rows)
                    
			for nb in range(nbr_rows):
				for nombre in range(10):
				self.tableWidget_select_by_dates_correction.setItem(nb,nombre, QTableWidgetItem(liste_select_by_dates_correction[nb][nombre]))
                
			self.tableWidget_select_by_dates_correction.resizeRowsToContents()
			self.tableWidget_select_by_dates_correction.resizeColumnsToContents()        
                        
		conn.commit()
		curseur.close()
		conn.close()
		else :
			print("OK")

		except :
			print("error")
for information, the type of 'start_date' and 'end_date' is string and like this format (day-month-year)
I associate a pushbutton to this function like this:
self.pushbutton_select.clicked.connect(self.select_by_dates )

the result is error display at the terminal.
but if I modify the code at the line 10 ,by changing for example :
start_date by ‘01-12-2019’ and end_date by ‘20-12-2019’

the code work well and show me what I want

I would love to have help and explanation of this

thanks
Reply
#2
to check if date between two others:
>>> from datetime import date

>>> def isbetween(startdate, enddate, mydate):
...    return startdate < mydate < enddate
...
>>>
use like:
>>> startdate = date(2019, 2, 1)
>>> enddate = date(2019, 11, 1)
>>>
>>> if isbetween(startdate, enddate, date(2019, 1, 15)):
...     print("The date is in range")
... else:
...     print("The date is NOT in range")
... 
The date is NOT in range
>>> if isbetween(startdate, enddate, date(2019, 4, 15)):
...     print("The date is in range")
... else:
...     print("The date is NOT in range")
... 
The date is in range
>>>
Reply
#3
thanks for your answer, but I don't understanf very well th code.
to explain my problem with picture of sample of project :

when I use in my code the string variable start_date and end_date , like in the picture below
[Image: ftlr.jpg]
I have error,
but if I use the date in string format like '1-12-2019' and '28-12-2019' in the source code at line 10, I have a display of the content of database, and at this case the date block is imperatively not used.
the display is correct for me :
[Image: taiy.jpg]

and at the bottom there is the pushbutton to activate the function select_by_dates
[Image: rwph.jpg]
Reply
#4
Hi; someone can explain to me the difference when I put string variable(star_date,end_date) in my code and the fact to write date in format date like '01-12-2019' for the first date, '28-12-2019' for the last date;
because in the first case my code doesn't work; but in the second case works.
why ??
I think there is the same thing they have a same format
Reply
#5
@atlass218 I know I am weighing in on this a bit late but you do not want to reference you database backend from your Gui frontend (lookup MVC methodology). Further when building your database interface in pure python it is best to build it as a self-contained autonomous class that handles everything more generically and then all your frontend does is call preconfigured Methods passing in any parameters in as a dictionary that the database might need to use in the query. If you would like to see a templated version of this I have one that I share with my students that I can share with you as well

As far as dates go if they are stored as a DateTime object then they are without formatting and are stored as the number of seconds from a base date when you ask it to format that and give it a format it converts it. So unless you need to display the data you should never format it just leave it in its native state. And never work with the formatted version unless you are getting one from the User in which case its best to force the formatting you want or break the fields up in 3 separate entities and then behind the scenes do the conversion you self into a native DateTime object. I hope that helps you.
Reply
#6
for information; the column "date_correction" of the table "correction_loc35R" is programmed like TEXT
to recover the start date entered by the user; we created the following function :

def join_start_date(self):
    try:
        start_day = int(self.lineEdit_start_day.text())
        start_month = int(self.lineEdit_start_month.text())
        start_year = int(self.lineEdit_start_year.text())
        start_date = dt.date(start_year, start_month, start_day)
        
    except ValueError:    
		print("error")
        
    else:
        self.lineEdit_start_date_writted.setText("{:%d-%m-%Y}".format(start_date))
        print("OK")
to recover the end date entered by the user; we created the following function :

def join_end_date(self):
    try:
        end_day = int(self.lineEdit_end_day.text())
        end_month = int(self.lineEdit_end_month.text())
        end_year = int(self.lineEdit_end_year.text())
        end_date = dt.date(end_year, end_month, end_day)
        
    except ValueError:    
		print("error")
        
    else:
        self.lineEdit_end_date_writted.setText("{:%d-%m-%Y}".format(end_date))
        print("OK")
and here is a descriptive picture of the input blocks of the start date and the end date

[Image: dkph.jpg]
Reply
#7
I modify the line 11 of the code of the function (select_by_dates ):

def select_by_dates (self):
               
    start_date =self.lineEdit_start_date.text()             
    end_date = self.lineEdit_end_date.text()
             
    try :
         
        conn = sqlite3.connect ('correction.db')
        curseur=conn.cursor()
      
		curseur.execute('SELECT * FROM correction_loc35R  where  date_correction between ? AND ? ORDER BY date_correction DESC', (start_date, end_date))
                     
         
        liste_select_by_dates_correction=c.fetchall()
        nbr_rows=len(liste_select_by_dates_correction)
                   
        if nbr_rows != 0 :
             
            # set row count
            self.tableWidget_select_by_dates_correction.setRowCount(nbr_rows)
                     
            for nb in range(nbr_rows):
                for nombre in range(10):
                self.tableWidget_select_by_dates_correction.setItem(nb,nombre, QTableWidgetItem(liste_select_by_dates_correction[nb][nombre]))
                 
            self.tableWidget_select_by_dates_correction.resizeRowsToContents()
            self.tableWidget_select_by_dates_correction.resizeColumnsToContents()        
                         
        conn.commit()
        curseur.close()
        conn.close()
        else :
            print("OK")
 
        except :
            print("error")
but always the same probleme like there is no data in database
Reply
#8
Okay your start-date and end-date functionality -- other than what you use those dates for -- is exactly the same to have 2 separate means of handling these is redundant coding -- you should create a class that encapsulates that whole thing and handles all the data manipulation -- then you would simple drop that class object in place and call its methods as needed. If that does not make sense I can try to clarify it better.
Reply
#9
I don't understand what you want me to do ; but I want you if it possible to clarify to me the differnce between this two codes :
first code that works :

def select_by_dates (self):
                

              
    try :
          
        conn = sqlite3.connect ('correction.db')
        curseur=conn.cursor()
       
        curseur.execute("SELECT * FROM correction_loc35R  where  date_correction between '01-12-2019' AND '28-12-2019' ORDER BY date_correction DESC")
                      
          
        liste_select_by_dates_correction=c.fetchall()
        nbr_rows=len(liste_select_by_dates_correction)
                    
        if nbr_rows != 0 :
              
            # set row count
            self.tableWidget_select_by_dates_correction.setRowCount(nbr_rows)
                      
            for nb in range(nbr_rows):
                for nombre in range(10):
                self.tableWidget_select_by_dates_correction.setItem(nb,nombre, QTableWidgetItem(liste_select_by_dates_correction[nb][nombre]))
                  
            self.tableWidget_select_by_dates_correction.resizeRowsToContents()
            self.tableWidget_select_by_dates_correction.resizeColumnsToContents()        
                          
        conn.commit()
        curseur.close()
        conn.close()
        else :
            print("OK")
  
        except :
            print("error")
second code that doesn't work :

def select_by_dates (self):
                
    start_date ='01-12-2019'             
    end_date = '28-12-2019'
              
    try :
          
        conn = sqlite3.connect ('correction.db')
        curseur=conn.cursor()
       
        curseur.execute("SELECT * FROM correction_loc35R  where  date_correction between start_date AND end_date ORDER BY date_correction DESC")
                      
          
        liste_select_by_dates_correction=c.fetchall()
        nbr_rows=len(liste_select_by_dates_correction)
                    
        if nbr_rows != 0 :
              
            # set row count
            self.tableWidget_select_by_dates_correction.setRowCount(nbr_rows)
                      
            for nb in range(nbr_rows):
                for nombre in range(10):
                self.tableWidget_select_by_dates_correction.setItem(nb,nombre, QTableWidgetItem(liste_select_by_dates_correction[nb][nombre]))
                  
            self.tableWidget_select_by_dates_correction.resizeRowsToContents()
            self.tableWidget_select_by_dates_correction.resizeColumnsToContents()        
                          
        conn.commit()
        curseur.close()
        conn.close()
        else :
            print("OK")
  
        except :
            print("error")
and yet the two codes are pretty much the same
Reply
#10
Put this in a little MUC (Minimal Usable Code) and run it and then you tell me why the 2nd one does not work ;-)
start_date ='01-12-2019'             
end_date = '28-12-2019'

MySQL_1 = "SELECT * FROM correction_loc35R  where  date_correction between '01-12-2019' AND '28-12-2019' ORDER BY date_correction DESC"
MySQL_2 = "SELECT * FROM correction_loc35R  where  date_correction between start_date AND end_date ORDER BY date_correction DESC"

print(MySQL_1)
print(MySQL_2)
Reply


Possibly Related Threads…
Thread Author Replies Views Last Post
  Display and update the label text which display the serial value jenkins43 5 3,586 Feb-04-2019, 04:36 AM
Last Post: Larz60+
  Display more than one button in GUI to display MPU6000 Sensor readings barry76 4 1,166 Jan-05-2019, 01:48 PM
Last Post: wuf

Forum Jump:

User Panel Messages

Announcements
Announcement #1 8/1/2020
Announcement #2 8/2/2020
Announcement #3 8/6/2020