display the contents of a sqlite3 database according to the dates

display the contents of a sqlite3 database according to the dates - Printable Version

+- Python Forum (https://python-forum.io)
+-- Forum: Python Coding (https://python-forum.io/forum-7.html)
+--- Forum: General Coding Help (https://python-forum.io/forum-8.html)
+--- Thread: display the contents of a sqlite3 database according to the dates (/thread-16508.html)

display the contents of a sqlite3 database according to the dates - atlass218 - Mar-02-2019

Hi;
the code I'm going to mention below works properly by inserting defined dates :

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

from tkinter import *
from datetime import *
import time
import calendar
import sqlite3
import datetime

fen = Tk()
fen.title('ILS system ')
fen.resizable(0,0)
largeur=920
hauteur=660
fen.geometry('{}x{}+0+0'.format(largeur,hauteur))

def monitoring () :

	fen_monitoring=Toplevel(fen)
	fen_monitoring.title('ddm axe')
	fen_monitoring.geometry('450x400+300+150')

	s= Scrollbar(fen_monitoring)
	T= Text(fen_monitoring,bg='powder blue',width=450,height=400)
	s.pack(side=RIGHT, fill=Y)
	T.pack(side=LEFT, fill=Y)
	s.config(command=T.yview)
	T.config(yscrollcommand=s.set)

	def update_releves():
 		
		T.delete('1.0', END)
		T.update()
	 
		conn = sqlite3.connect('bdd/test.db')
		curseur = conn.cursor()     
		for resultats in curseur.execute('SELECT * FROM releves  where  time_releves_axe_loc35R between "2019-03-01" AND "2019-03-31" ORDER BY time_releves_axe_loc35R DESC'): 
		   
			contenu_table_releves= str(resultats[1])+"\t\t"+ str(resultats[2])+"\t\t"+ str(resultats[3])+"\n"

			T.insert(END,contenu_table_releves)  
		T.after(1000,update_releves)
		curseur.close()

	update_releves()

bt=Button(fen,text='click on\nthe button', command=monitoring)
bt.pack()

this code display to me correctly the content of the database,
I insert this second code (to calculate first and last day of the month) in the initial code:

#to calculate first and last day of the month:
now = datetime.datetime.now()
start_month = datetime.datetime(now.year, now.month, 1)
date_on_next_month = start_month + datetime.timedelta(35)
start_next_month = datetime.datetime(date_on_next_month.year, date_on_next_month.month, 1)
last_day_month = start_next_month - datetime.timedelta(1)
last_day=last_day_month.strftime('%Y-%m-%d')
first_day=start_month = datetime.datetime(now.year, now.month, 1).strftime('%Y-%m-%d')

and I change at the initial code at the function update_releves:
* the first data of date ("2019-03-01") by first_day
* the second data of date ("2019-03-31") by the last_day

the new code becomes like that:

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

from tkinter import *
from datetime import *
import time
import calendar
import sqlite3
import datetime

fen = Tk()
fen.title('ILS system ')
fen.resizable(0,0)
largeur=920
hauteur=660
fen.geometry('{}x{}+0+0'.format(largeur,hauteur))

#to calculate first and last day of the month:
now = datetime.datetime.now()
start_month = datetime.datetime(now.year, now.month, 1)
date_on_next_month = start_month + datetime.timedelta(35)
start_next_month = datetime.datetime(date_on_next_month.year, date_on_next_month.month, 1)
last_day_month = start_next_month - datetime.timedelta(1)
last_day=last_day_month.strftime('%Y-%m-%d')
first_day=start_month = datetime.datetime(now.year, now.month, 1).strftime('%Y-%m-%d')

def monitoring () :

	fen_monitoring=Toplevel(fen)
	fen_monitoring.title('ddm axe')
	fen_monitoring.geometry('450x400+300+150')

	s= Scrollbar(fen_monitoring)
	T= Text(fen_monitoring,bg='powder blue',width=450,height=400)
	s.pack(side=RIGHT, fill=Y)
	T.pack(side=LEFT, fill=Y)
	s.config(command=T.yview)
	T.config(yscrollcommand=s.set)

	def update_releves():
 		
		T.delete('1.0', END)
		T.update()
		global first_day
		global last_day
		conn = sqlite3.connect('bdd/test.db')
		curseur = conn.cursor()     
		for resultats in curseur.execute('SELECT * FROM releves  where  time_releves_axe_loc35R between first_day AND last_day ORDER BY time_releves_axe_loc35R DESC'): 
		   
			contenu_table_releves= str(resultats[1])+"\t\t"+ str(resultats[2])+"\t\t"+ str(resultats[3])+"\n"

			T.insert(END,contenu_table_releves)  
		T.after(1000,update_releves)
		curseur.close()

	update_releves()

bt=Button(fen,text='click on\nthe button', command=monitoring)
bt.pack()

when I execute this script I have this error: no such column: first_day

someone can explain to me why this code doesn't work

thanks

RE: display the contents of a sqlite3 database according to the dates - Larz60+ - Mar-02-2019

Quote:when I execute this script I have this error: no such column: first_day

Please post the entire unmodified error traceback, Thank you

RE: display the contents of a sqlite3 database according to the dates - atlass218 - Mar-02-2019

the complete error traceback is :

Exception in Tkinter callback
Traceback (most recent call last):
File "C:\Users\AHMED\AppData\Local\Programs\Python\Python36\lib\tkinter\__init__.py", line 1702, in __call__
return self.func(*args)
File "C:\Users\AHMED\Desktop\mon_cev_exe\ddm_test.py", line 55, in monitoring
update_releves()
File "C:\Users\AHMED\Desktop\mon_cev_exe\ddm_test.py", line 47, in update_releves
for resultats in curseur.execute('SELECT * FROM releves where time_releves_axe_loc35R between first_day AND last_day ORDER BY time_releves_axe_loc35R DESC'):
sqlite3.OperationalError: no such column: first_day

at the end for information; there is the sample of the code to create table releves in the database 'test.db':

def CreateTable_releves():
	
	conn1_releves = sqlite3.connect('bdd/test.db')
	curseur1_releves = conn1_releves.cursor()
	curseur1_releves.execute('''CREATE TABLE IF NOT EXISTS releves (id INTEGER PRIMARY KEY,time_releves TEXT NOT NULL, monitor1_releves TEXT NOT NULL,monitor2_releves TEXT NOT NULL)''')
	curseur1_releves.close()


def Add_To_Table_releves():

	time_releves_get = str(date_choisie_ils35R)

	monitor1_releves_get = str(ent_releves_mesure1.get())
	monitor2_releves_get = str(ent_releves_mesure2.get())

	conn2_releves = sqlite3.connect('bdd/test.db')
	curseur2_releves = conn2_releves.cursor()
	curseur2_releves.execute('''INSERT INTO releves (time_releves,monitor1_releves,monitor2_releves) VALUES (?,?,?)''',(time_releves_get,monitor1_releves_get,monitor2_releves_get))
	conn2_releves.commit()
	curseur2_releves.close()

RE: display the contents of a sqlite3 database according to the dates - Larz60+ - Mar-03-2019

curseur.execute('SELECT * FROM releves where time_releves_axe_loc35R between first_day AND last_day ORDER BY time_releves_axe_loc35R DESC')

it doesn't like the syntax for the between statemant as it relates to time_releves_axe_loc35R
I don't see that column in your create statement above.
I'd open sqlite3 from command line and ay '>' prompt type .schema releves;
make sure you table contains the column

RE: display the contents of a sqlite3 database according to the dates - atlass218 - Mar-03-2019

to minimize my code for this forum, I made this typing error:
I just correct it

curseur.execute('SELECT * FROM releves where time_releves between first_day AND last_day ORDER BY time_releves DESC')

as additional information:

time_releves_get = str(date_choisie_ils35R)
monitor1_releves_get = str(ent_releves_mesure1.get())
monitor2_releves_get = str(ent_releves_mesure2.get())

are data retrieved from widget Entry :
*entry_time_releve,
*entry_monitor1_releves,
*entry_monitor2_releves.