Python Forum
display the contents of a sqlite3 database according to the dates
Thread Rating:
  • 0 Vote(s) - 0 Average
  • 1
  • 2
  • 3
  • 4
  • 5
display the contents of a sqlite3 database according to the dates
#1
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
Reply
#2
Quote:when I execute this script I have this error: no such column: first_day
Please post the entire unmodified error traceback, Thank you
Reply
#3
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()
Reply
#4
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
Reply
#5
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.
Reply


Possibly Related Threads…
Thread Author Replies Views Last Post
  Create SQLite3 database with peewee Jim53_1980 2 600 Dec-20-2023, 02:38 PM
Last Post: buran
  Regex replace in SQLite3 database WJSwan 1 749 Dec-04-2023, 05:55 PM
Last Post: Larz60+
  Rows not adding to sqlite3 database using SQLAlchemy Calab 11 1,597 Jun-02-2023, 05:53 PM
Last Post: bowlofred
  How to display <IPython.core.display.HTML object>? pythopen 3 45,704 May-06-2023, 08:14 AM
Last Post: pramod08728
  Basic SQL query using Py: Inserting or querying sqlite3 database not returning data marlonbown 3 1,304 Nov-08-2022, 07:16 PM
Last Post: marlonbown
  Python Variables and Sqlite3 Database Staples200 1 3,092 May-25-2021, 02:40 AM
Last Post: Staples200
  how to suppress not to display the package contents from pydoc! maiya 3 2,435 Mar-19-2021, 03:41 AM
Last Post: bowlofred
Information Unable to display joystick's value from Python onto display box MelfoyGray 2 2,172 Nov-11-2020, 02:23 AM
Last Post: MelfoyGray
  sqlite3 database problem Maryan 2 2,427 Oct-05-2020, 05:21 AM
Last Post: buran
  sqlite3 database does not save data across restarting the program SheeppOSU 1 3,405 Jul-24-2020, 05:53 AM
Last Post: SheeppOSU

Forum Jump:

User Panel Messages

Announcements
Announcement #1 8/1/2020
Announcement #2 8/2/2020
Announcement #3 8/6/2020