[PyQt] to display cotent of database between two dates

[atlass218]

I rectify the indentation and the code : I had written the function (select_by_dates) in the old version, I apologize for the error I just corrected it. and yet the problem still remains without displaying the content of the database between the two dates of lineedit.

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 :

#function for star day :
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>') 

#function for end day :
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()
        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)
                        
        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")
		

[Denni]

Okay well I am going to put the work back on you -- if you can make each one of those actually executable (aka include the necessary imports and such -- slap them into a self contained class so all I have to do is instantiate them -- then I will run them and see where you issue resides -- ideally you would create a simple Window with 3 buttons that I could use to call your functions one after the other and all I would have to do is copy/paste the whole thing and run it

Once you have made them such that I can use them easily then I will take the time necessary to help YOU with YOUR problem... I stress the you and yours because you are not doing me the favor I am doing you the favor or at least I am trying to anyway -- so please make easier for me so that I optimize my time in order to help you better

[atlass218]

The main imports in my source code are as follows:

#!/usr/bin/python3
# -*- coding: utf-8 -*-

# import os :
import os, errno
from os import path
import pathlib
import sys
import logging

# PyQT5 :  
from PyQt5.QtWidgets import *
from PyQt5.QtCore import* 
from PyQt5.QtGui import *

#date / time :
import time
import datetime
import datetime as dt
from datetime import datetime

# import   sqlite3 for database :
import sqlite3

thank you for accompanying me all this long time to find the solution to my problem

[atlass218]

my little project is not created with the object oriented programming process,
I am minimizing the code; in the near future i will post the code that would be useful to you

[atlass218]

code of index.py :

#!/usr/bin/python3
# -*- coding: utf-8 -*-

###############################################################
# import os :
import os, errno
from os import path
import pathlib
import sys
import logging

###############################################################
# Fix qt import error : Include this file before import PyQt5 

def _append_run_path():
    if getattr(sys, 'frozen', False):
        pathlist = []
	
# If the application is run as a bundle, the pyInstaller bootloader
# extends the sys module by a flag frozen=True and sets the app
# path into variable _MEIPASS'.
        pathlist.append(sys._MEIPASS)

        # the application exe path
        _main_app_path = os.path.dirname(sys.executable)
        pathlist.append(_main_app_path)

# append to system path enviroment
        os.environ["PATH"] += os.pathsep + os.pathsep.join(pathlist)

    logging.error("current PATH: %s", os.environ['PATH'])

_append_run_path()

###############################################################
# PyQT5 :  
from PyQt5.QtWidgets import *
from PyQt5.QtCore import *
from PyQt5.QtGui import *
from PyQt5.QtGui import *

#import date and time :
import time
import datetime
import datetime as dt
from datetime import datetime

###############################################################
# import of the math module to work with the log10 method:
import math
###############################################################
# import of the sqlite3 module to work with table and database:
import sqlite3
###############################################################
from main import Ui_MainWindow

#####################################################à
#Initiate UI_File :
class MainApp(QMainWindow,Ui_MainWindow) :
    def __init__(self,parent=None) :
        super(MainApp,self).__init__(parent)
        QMainWindow.__init__(self)
        self.setupUi(self)
        self.win_UI() 
        
###########################################################################################################
#Function to configure the display dimensions of the application :
    def win_UI(self):
        
        self.setWindowTitle("ddm corrector")
        self.setGeometry(20,40,700,480) 
        self.actions_on_buttons()              

##########################################################################################################
# function of the execution correction ddm :
    def calcul_of_correction (self):
        
        #function to convert somme characters to point :   
        def convert(number) :

            character=[',',';',':','.','-','_']
            for loop in character :
                number= str(number).replace(loop,'.')           
            return number
         
        try:  

            ground_ddm=self.lineEdit_ground_ddm.text()
            ground_ddm=convert(ground_ddm)
            ground_ddm=float(ground_ddm)

            flight_ddm=self.lineEdit_flight_ddm.text()
            flight_ddm=convert(flight_ddm)
            flight_ddm=float(flight_ddm)
 
            correction= 20*(math.log10(flight_ddm/ground_ddm)) 
            correction=round(correction,3)
            correction =str(correction)

            self.lineEdit_ddm_correction_value.setText(correction) 
            self.textEdit_display_info.setText("")

        except: 
            self.textEdit_display_info.setText('<span style="font-weight:bold;font-size:14px;color:#FF0000">invalid entry.</span>')     
#########################################################################################################
    #function for star day :
    def function_for_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('one of the tree champs of bloc start date is invalid') 
        else:
            self.lineEdit_start_date.setText("{:%Y-%m-%d}".format(start_date))
            print('<date for start date is created with ("year/month/day") form')

############################################################################################################ 
    #function for end day :
    def function_for_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('one of the tree champs of bloc end date is invalid') 
        else:
            self.lineEdit_end_date.setText("{:%Y-%m-%d}".format(end_date))
            print('<date for end date is created with ("year/month/day") form') 

##########################################################################################################
##Function to create database and store into it info :   
    def create_database_correction(self):

		#fonction convertir point virgules :   
        def convert(number) :

            character=[',',';',':','.','-','_']
            for loop in character :
                number= str(number).replace(loop,'.')           
            return number
        
        input_date =str(time.strftime("%Y-%m-%d"))
        input_time = str(time.strftime('%H:%M:%S'))

        ddm_mon1=self.lineEdit_reading_monitor1.text()   
        input_ddm_mon1=convert(ddm_mon1)+" μA" 

        ddm_mon2=self.lineEdit_reading_monitor2.text()   
        input_ddm_mon2=convert(ddm_mon2) +" μA"
        
        ground_ddm=self.lineEdit_ground_ddm.text()   
        input_ground_ddm=convert(ground_ddm) +" μA"
    		                                      
        flight_ddm = self.lineEdit_flight_ddm.text()
        input_flight_ddm=convert(flight_ddm) +" μA"
        
        input_ddm_correction_value = self.lineEdit_ddm_correction_value.text() +' dB'
               		
        script_path_correction = os.path.dirname(os.path.realpath(__file__))#récupérer le répertoire courant  
        new_path_correction=script_path_correction + '\\data_base' #concaténer au path ci-dessus  le path du dossier data_base		
        os.makedirs(new_path_correction, exist_ok=True)   #creer le dossier data_base au nouveau path

        #create database
        connexion_correction = sqlite3.connect(new_path_correction+'\\correction.db')
        curseur_correction = connexion_correction.cursor()

        # Check if table loc35R does not exist and create it
        curseur_correction.execute('''CREATE TABLE IF NOT EXISTS correction_loc35R
                      (reading_date_correction text, time_correction text,reading_ddm_mon1_correction text, reading_ddm_mon2_correction text,
          reading_ground_ddm_correction text,reading_flight_ddm_correction text, reading_ddm_correction_value text )  ''')
                        
        if (self.lineEdit_reading_monitor1.text()=="") :
	        self.textEdit_display_info.setText('<span style="color:#FF0000; font-weight: bold;font-size:15px;">invalid entry for monitor 1.</span>')     

        elif (self.lineEdit_reading_monitor2.text()==""):
            self.textEdit_display_info.setText('<span style="color:#FF0000; font-weight: bold;font-size:15px;">invalid entry for monitor 2.</span>')     

        
        elif (self.lineEdit_ddm_correction_value.text()=="")  :
            self.textEdit_display_info.setText('<span style="color:#FF0000; font-weight: bold;font-size:15px;">invalid entry for value of correction ddm.</span>')     

        elif (self.lineEdit_flight_ddm.text()=="") :
            self.textEdit_display_info.setText('<span style="color:#FF0000; font-weight: bold;font-size:15px;">invalid entry for flight ddm</span>')       
        
        else:

            curseur_correction.execute('''INSERT INTO correction_loc35R VALUES (?,?,?,?,?,?,?) ''',
            (input_date,input_time, input_ddm_mon1, input_ddm_mon2, input_ground_ddm, input_flight_ddm, input_ddm_correction_value))
            
            self.textEdit_display_info.setText('<span style=" color:#00aa00;font-size:15px;font-weight: bold;">storage of info into database with success.</span>')     

            connexion_correction.commit()                                
            curseur_correction.close()
            connexion_correction.close()      
###########################################################################################################
#Fuction to dispaly content of database :
    def Display_flight_control_operations(self):

        script_path_dispaly = os.path.dirname(os.path.realpath(__file__))#récupérer le répertoire courant  
        new_path_dispaly=script_path_dispaly + '\\data_base' #concaténer au path ci-dessus  le path du dossier data_base		
        os.makedirs(new_path_dispaly, exist_ok=True)   #creer le dossier data_base au nouveau path
                             						
        try:
            conn_dispaly = sqlite3.connect (new_path_dispaly +'\\correction.db')
            curseur_dispaly =conn_dispaly .cursor()
                
            c_dispaly =curseur_dispaly .execute("SELECT * FROM correction_loc35R  ORDER BY reading_date_correction DESC ")
				  
            liste_dispaly=c_dispaly.fetchall()
# set row count
            self.tableWidget_display.setRowCount(len(liste_dispaly))
#insertion des donnees de la bdd dans la Qtablewidget:   pushButton_rafraichir_contenu_des_listes_releves_mesure_gp35L
            for nb_lignes_display in range(len(liste_dispaly)):
                for nombre_colonnes_display in range(7):
                    self.tableWidget_display.setItem(nb_lignes_display,nombre_colonnes_display, QTableWidgetItem(liste_dispaly[nb_lignes_display][nombre_colonnes_display]))
#mettre la couleur verte pour la ligne contenant la confirmation 'OK'
                    if (self.tableWidget_display.item(nb_lignes_display,nombre_colonnes_display).text())=='OK' :
                        for j in range(nombre_colonnes_display +1):      
                            self.tableWidget_display.item(nb_lignes_display,j).setBackground(QtGui.QColor(0, 204, 0))
										
            self.tableWidget_display.resizeRowsToContents()
            self.tableWidget_display.resizeColumnsToContents()        
			  
#fermeture du cursor
            curseur_dispaly.close()
    
#fermeture de la connexion à la bdd
            conn_dispaly.close()

        except :
            print("eror")    
##########################################################################################################
# function to display selection of flight operations by dates :
    def select_by_dates (self):

        script_path_selection = os.path.dirname(os.path.realpath(__file__))
        new_path_selection=script_path_selection + '\\data_base' 
        os.makedirs(new_path_selection, exist_ok=True)   
                                             
        try :
            start_date =self.lineEdit_start_date.text()           
            end_date = self.lineEdit_end_date.text()
                       
            conn = sqlite3.connect (new_path_selection+'\\correction.db')
            curseur=conn.cursor()
            strSQL = ''
            strSQL += "SELECT * "
            strSQL += "FROM correction_loc35R "
            strSQL += "WHERE reading_date_correction"
            strSQL += "BETWEEN " + start_date.strftime('%Y-%m-%d')
            strSQL += "AND " + end_date.strftime('%Y-%m-%d')
            c=curseur.execute(strSQL)
                                  
            liste_selection=c.fetchall()
            nbr_rows_loc35R=len(liste_selection)
          
            if nbr_rows_loc35R != 0 :
            
# set row count
                self.tableWidget_selection.setRowCount(nbr_rows_loc35R)
                    
                for nb in range(nbr_rows_loc35R):
                    for nombre in range(7):
                        self.tableWidget_selection.setItem(nb,nombre, QTableWidgetItem(liste_selection[nb][nombre]))
                
                self.tableWidget_selection.resizeRowsToContents()
                self.tableWidget_selection.resizeColumnsToContents()        
                        
                conn.commit()
                curseur.close()
                conn.close()

            else :
                print("OK")

        except :
           print("error")
			
#########################################################################################################
#Function for commands carried out by buttons:
    def actions_on_buttons(self):
        self.pushButton_execute_correction.clicked.connect(self.calcul_of_correction)
        self.pushButton_store_in_database.clicked.connect(self.create_database_correction)
        self.pushButton_update_display_correction.clicked.connect(self.Display_flight_control_operations)
        self.pushButton_activate_start_date.clicked.connect(self.function_for_start_date)
        self.pushButton_activate_end_date.clicked.connect(self.function_for_end_date)
        self.pushButton_search_selection.clicked.connect(self.select_by_dates)

##########################################################################################################
def main():
    app=QApplication(sys.argv)
    win=MainApp()
    win.show()
    app.exec_()# infinite loop

if __name__=='__main__' :
    main()

The main.py file linked to the index.py file, is obtained from the main.ui file (by QtDesigner) :
here is the download link for the main.py file :
fichier main.py attached to index.py

[Denni]

Great so we have most of this placed into a self running executable however what you provided will not execute without this

from main import Ui_MainWindow

Which you did not include

[atlass218]

with this line I import Ui_MainWindow from main.py file, that you will find it in the link below :
main.py file obtained from main.ui (QtDesigner)

this main.py file must be downloaded and put with the index.py file in the same folder for the index.py file to run correctly
I hope you find this information useful
thank you

[Denni]

I fully understand how that works -- and your Link gives me the following

This page is not allowed in the US
We're sorry but it appears your IP comes from the US so you're not allowed to download or stream.

If you have a premium account, please login to remove the limitation.

So copy/paste the contents of you UI file that you are importing -- if at all possible -- of course using PyQt as it was meant to be (aka not created via the Qt Designer which produces crappy non-proper python-qt code) used would be much better but I can fix that given at least the basics

[atlass218]

Hi;
I found the fault in my code which was causing the error by modifying the command line into "select_by_dates" function as follows:

c = curseur.execute("SELECT * FROM correction_loc35R  where  date_correction_loc35R between ? AND ? ORDER BY date_correction_loc35R DESC", (start_date, end_date))

thanks for help

[Denni]

Ah okay so the issue I kept trying to tell you about -- glad to see you finally figured it out -- do not forget to mark this as Solved