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
#21
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")
		
Reply
#22
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
Reply
#23
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
Reply
#24
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
Reply
#25
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
Reply
#26
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
Reply
#27
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
Reply
#28
I fully understand how that works -- and your Link gives me the following
Quote: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
Reply
#29
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
Reply
#30
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
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