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
#11
excuse me, i didn't understand what you want with this code
Reply
#12
I am going to take it English is not your native language so here run this and see what it prints out.
if __name__ == '__main__':
    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)
As what the above prints out will be what you are sending to the database to execute as the query. In fact I always put my Queries into variable so that if need be I can print out what it is so that I can then copy/paste that query directly into my database engine interface and run that query directly if it is having any issues.
Reply
#13
when i said i didn't understand, it was about the usefulness of this code: since MySQL_1 and MySQL_2 have the form of a character string.
on the terminal, it is displayed:

SELECT * FROM correction_loc35R where date_correction between '01-12-2019' AND '28-12-2019' ORDER BY date_correction DESC

SELECT * FROM correction_loc35R where date_correction between start_date AND end_date ORDER BY date_correction DESC
Reply
#14
Yes and if you look at what was printed -- which by the way is what you send to the database as a query -- you can see that the first one (which works) sends a proper SQL Statement -- while the second one does not for the database would not have any way of interpretting what you mean by start_date and end_date especially when its comparing those to a Date field.

I was (1) trying to show you how to validate your SQL Statements and (2) hoping that you would understand the issue once you printed out what it is you are actually sending to the database.

Typically I create my SQL In Code Statements as follows (especially if I am using variables to supply the parameters):
strDbSQL = ''
strDbSQL += 'SELECT * '
strDbSQL += '  FROM correction_loc35R '
strDbSQL += ' WHERE date_correction between '
strDbSQL += start_date.strftime('%m-%d-%Y')
strDbSQL += '   AND '
strDbSQL += end_date.strftime('%m-%d-%Y')
strDbSQL += ' ORDER BY date_correction DESC'
This makes it easy to read and allows for easier insertion of more statements as needed
Reply
#15
for information, I use sqlite3 for database and the field for date_correction is 'TEXT'

I modified the function designated to display the selection by dates as follows:

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.strftime('%Y-%m-%d') AND end_date.strftime('%Y-%m-%d') ")
                     
         
        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 nothing happen except dispaly error
Reply
#16
Okay you are missing the issue. You are wrapping your variables (and now functions as well) inside your Quotes which turns that entire thing into a single string and that is not what you are wanting to do as string is a string not a variable nor a function. Now if you insist on doing this all in one line (a bad thing to do btw) then change the following:

c=curseur.execute("SELECT * FROM correction_loc35R WHERE date_correction BETWEEN start_date.strftime('%Y-%m-%d') AND end_date.strftime('%Y-%m-%d') ")

To This

c=curseur.execute("SELECT * FROM correction_loc35R WHERE date_correction BETWEEN "+start_date.strftime('%Y-%m-%d')+" AND "+end_date.strftime('%Y-%m-%d'))
Reply
#17
I modify the code like you say but does'nt work.

and for information the variables 'start_date' and 'end_date' are recovered respectively from functions

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("{:%Y-%m-%d}".format(start_date))
        print("OK")
	
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("{:%Y-%m-%d}".format(end_date))
        print("OK")
Reply
#18
Okay well when you can supply a MRE (Minimal Reproducible Example) rather than code snippets then perhaps I can help you troubleshoot what you are doing wrong but without that I am just spinning my wheels which does you or I no good. So give me the MUC (Minimal Usable Code) you are using to validate that you can get the specific thing you want done to work. If you using the entire program to troubleshoot this one issue you are doing that wrong -- for you need to learn to isolate what you are trying to make work until you understand that piece then and only then do you bring it into the program. To do otherwise can be an exercise in futility
Reply
#19
[Image: 0tev.jpg]

[Image: dkph.jpg]

The first step is to create a sqlite3 database and a table with the following function:

#function to create sqlite3 database and table :    
    def create_database_and_table_correction_loc35R(self):
 		
        monitor_before_correction=self.lineEdit_monitor_before_correction.text()          
        monitor_after_correction=self.lineEdit_monitor_after_correction.text()   
        	
        date_correction =str(time.strftime("%d-%m-%Y"))
        time_correction = str(time.strftime('%H:%M:%S'))    
                            		
        script_path_correction = os.path.dirname(os.path.realpath(__file__))
        new_path_correction=script_path_correction + '\\data_base_ils35R' 
		
        os.makedirs(new_path_correction, exist_ok=True)   

        connexion_correction = sqlite3.connect(new_path_correction+'\\correction.db')
        curseur_correction = connexion_correction.cursor()
        curseur_correction.execute('''CREATE TABLE IF NOT EXISTS correction_loc35R
           (reading_date_correction text, reading_time_correction text,reading_monitor_before_correction text, reading_monitor_after_correction text)  ''') 
              
        if (self.lineEdit_monitor_before_correction.text()=="") :
	        self.textEdit_display_correction.setText('<span style="color:#FF0000; font-weight: bold;font-size:15px;">invalid data entry for monitor data before correction.</span>')     

        elif (self.lineEdit_monitor_after_correction.text()==""):
            self.textEdit_display_correction.setText('<span style="color:#FF0000; font-weight: bold;font-size:15px;">invalid data entry for monitor data after correction.</span>')     
       	
        else:
            curseur_correction.execute('''INSERT INTO correction_loc35R VALUES (?,?,?,?) ''',
            (date_correction,time_correction, monitor_before_correction, monitor_after_correction))
            
            connexion_correction.commit()
            self.textEdit_display_correction.setText('<span style=" color:#00aa00;font-size:15px;font-weight: bold;">information saved with succes in database</span>')                                   
            curseur_correction.close()
            connexion_correction.close()
The second step is to create two functions which will set up the start date and the end date for the selection of the dates entered by the user :

def function_for_start_date(self):
	try:
	start_day = int(self.lineEdit_introduce_start_day.text())
	start_month = int(self.lineEdit_introduce_start_month.text())
	start_year = int(self.lineEdit_introduce_start_year.text())
	start_date = dt.date(start_year, start_month, start_day)
	except ValueError:
		self.textEdit_dispaly_info.setText('<span style="color:#FF0000; font-weight: bold;font-size:15px;">one of the tree champs is invalid</span>') 
	else:
		self.lineEdit_introduce_start_date.setText("{:%Y/%m/%d}".format(start_date))
		self.textEdit_dispaly_info.setText('<span style="color:green; font-weight: bold;font-size:15px;">date created with ("year/month/day") form.</span>') 


def function_for_end_date(self):
	try:
	end_day = int(self.lineEdit_introduce_end_day.text())
	end_month = int(self.lineEdit_introduce_end_month.text())
	end_year = int(self.lineEdit_introduce_end_year.text())
	end_date = dt.date(end_year, end_month, end_day)
	except ValueError:
		self.textEdit_dispaly_info.setText('<span style="color:#FF0000; font-weight: bold;font-size:15px;">one of the tree champs is invalid</span>') 
	else:
		self.lineEdit_introduce_end_date.setText("{:%Y/%m/%d}".format(end_date))
		self.textEdit_dispaly_info.setText('<span style="color:green; font-weight: bold;font-size:15px;">date created with ("year/month/day") form.</span>') 
The last step is to create a function to select the information from the database between the two dates previously entered by the user:

# function to select recorded data in database between two dates :
def select_by_dates (self):
                
    start_date =self.lineEdit_introduce_start_date.text() # retrieve from function_for_start_date function         
    end_date = self.lineEdit_introduce_end_date.text()  # retrieve from function_for_end_date function 
              
    try :
          
        conn = sqlite3.connect ('correction.db')
        curseur=conn.cursor()
        c=curseur.execute("SELECT * FROM correction_loc35R WHERE date_correction BETWEEN start_date.strftime('%Y-%m-%d') AND end_date.strftime('%Y-%m-%d') ")
                      
          
        liste_select_by_dates_correction=c.fetchall()
        nbr_rows=len(liste_select_by_dates_correction)
                    
        if nbr_rows != 0 :
              
            # set row count
            self.tableWidget_correction_by_dates.setRowCount(nbr_rows)
                      
            for nb in range(nbr_rows):
                for nombre in range(10):
                self.tableWidget_correction_by_dates.setItem(nb,nombre, QTableWidgetItem(liste_select_by_dates_correction[nb][nombre]))
                  
            self.tableWidget_correction_by_dates.resizeRowsToContents()
            self.tableWidget_correction_by_dates.resizeColumnsToContents()        
                          
        conn.commit()
        curseur.close()
        conn.close()
        else :
            print("OK")
  
        except :
            print("error")
Finally, and obviously all these functions are executed by a button specific to each of them.
In addition, the codes have been reduced to a minimum, but explanatory of the case of my needs.
thanks for the help
Reply
#20
Well it seems that some of your indentation is off but I think that I can adjust for that. Further assuming all that other disconnected stuff (first 3 functions/methods is working correctly. Here is all I think you need to adjust to make it work -- which is what I have been trying to get you to understand all along. Basically what you were doing was not passing in the variables' contents but a string of the variable name
        conn = sqlite3.connect ('correction.db')
        curseur=conn.cursor()
        strSQL = ''
        strSQL += "SELECT * "
        strSQL += "FROM correction_loc35R "
        strSQL += "WHERE date_correction "
        strSQL += "BETWEEN " + start_date.strftime('%Y-%m-%d')
        strSQL += "AND " + end_date.strftime('%Y-%m-%d')
        c=curseur.execute(strSQL)
Lastly let me state that you ought to have put your database interactions within a self-contained and autonomous Class where you simple call the API methods instead of what it appears you have perhaps done. What you have appears far more complex than it needs to be. Keep in mind if you keep it simple and smart it is far much easier to deal with now and later on.
Reply


Possibly Related Threads…
Thread Author Replies Views Last Post
  Display and update the label text which display the serial value jenkins43 5 8,995 Feb-04-2019, 04:36 AM
Last Post: Larz60+
  Display more than one button in GUI to display MPU6000 Sensor readings barry76 4 3,836 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