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