[PyQt] to display cotent of database between two dates - Printable Version +- Python Forum (https://python-forum.io) +-- Forum: Python Coding (https://python-forum.io/forum-7.html) +--- Forum: GUI (https://python-forum.io/forum-10.html) +--- Thread: [PyQt] to display cotent of database between two dates (/thread-23360.html) |
RE: to display cotent of database between two dates - atlass218 - Jan-10-2020 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") RE: to display cotent of database between two dates - Denni - Jan-10-2020 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 RE: to display cotent of database between two dates - atlass218 - Jan-10-2020 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 sqlite3thank you for accompanying me all this long time to find the solution to my problem RE: to display cotent of database between two dates - atlass218 - Jan-10-2020 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 RE: to display cotent of database between two dates - atlass218 - Jan-12-2020 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 RE: to display cotent of database between two dates - Denni - Jan-13-2020 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_MainWindowWhich you did not include RE: to display cotent of database between two dates - atlass218 - Jan-13-2020 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 RE: to display cotent of database between two dates - Denni - Jan-13-2020 I fully understand how that works -- and your Link gives me the following Quote:This page is not allowed in the USSo 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 RE: to display cotent of database between two dates - atlass218 - Jan-17-2020 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 RE: to display cotent of database between two dates - Denni - Jan-17-2020 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 |