Notifications
Clear all
Topic starter
2022-12-30 1:41 am
Right now I have python file that automatically runs on startup through crontab. Originally, button click has a callback to pull a txt file, read the last line and either edit it or enter a new line. It has worked for a few years now. I am now attempting to change it over to a SQLite3 database. I am using the same function that has worked and replacing the file read and write to the SQLite3. Database was made and I did confirm that everything is proper there. I can even use the same commands(except for file location) on my windows computer and I have no problem reading and editing it. The PI 0W is headless. After confirming what I could on my windows machine, I transferred it to the pi and I have had issues. At first I was rebooting the Pi and checking, then I decided to let the original crontab run and run the new code through Putty. The original does not use SQLite3 at all so it shouldn't be a problem and shouldn't be causing the error. The callbacks are triggering properly.
After a few tries I finally got an error log set up(you can see the remnants of the failed try). It would not log to file in the first attempt.
# Buttons for Tyon screen
import sqlite3
import RPi.GPIO as GPIO
import time
import datetime
import os
from datetime import date
from datetime import datetime
import logging
import subprocess
GPIO.setmode(GPIO.BCM)
#Error Logging
#logging.basicConfig(filename='TysonButtonErrorLog.log', encoding='utf-8', level=logging.DEBUG)
#database Connection
con = sqlite3.connect('/home/pi/tyson101/python/examples/TysonDB.db')
cur = con.cursor()
dataDump = [] #A place to dump data recieved for DB
thisdate = [] #A place to store current date for comparison
# Set sleep time
sleepTm = 2
# Set Pins for buttons in GPIO
fb = 12
mb = 16
check = False
#counter
ctd = 120
tmo = 120
pto = 200
def getWeek():
global dataDump
global thisdate
global cur #not sure if these need to be here but tried anyways
global con
dataDump.clear() #Clears list for fresh info
#get last day of data
try:
thisdate = [int(datetime.today().year), int(datetime.today().month), int(datetime.today().day)]#Set todays date to compare. first try:(datetime.strptime('%Y')), datetime.strptime('%m'), datetime.strptime("%d")]
#cur=con.cursor() #This might be a mistake, commented out to check
cur.execute("SELECT Year, Month, Date, Food, AM, PM FROM Food WHERE Year = ? AND Month = ? ORDER BY -Date", (thisdate[0], thisdate[1]))
row = cur.fetchone() #Get one record, where condition above makes it the most recent
for entry in row:
dataDump.append(entry) #Append results in my list
print(str(dataDump)) #**********************************************DEBUG************************************************
except Exception as Argument:
f= open("TysonButtonErrorLog.txt", "a")
f.write(str(Argument))
f.close()
logging.error(Argument, exc_info=True)
# Callback for buttons
def fb_callback(fb): # Food Button
global ctd
global tmo
global dataDump
global thisdate
global cur
global con
getWeek()
if ctd == 0:
try:
#If date is found, compares date in fields of DB and compares to current date
if dataDump[0] == thisdate[0] and dataDump[1] == thisdate[1] and dataDump[2] == thisdate[2]:
if dataDump[3] < 6: #5 is the max, do not add if over.
tempDump = [(dataDump[3] + 1), dataDump[0], dataDump[1], dataDump[2]] #list for the update with the updated field first
print(str(tempDump)) #*************************************************DEBUG***************************************
#dataDump.insert(0, (dataDump[3] + 1)) #Alternate method
sqlUpdate = """UPDATE Food SET Food = ? WHERE Year = ? AND Month = ? AND Date = ?"""
cur.execute(sqlUpdate, tempDump)
con.commit()
else:
f = open("TysonButtonErrorLog.txt", "a")
f.write(str('Date Missmatch'))
f.close()
con.close()
ctd = tmo
except Exception as Argument:
f = open("TysonButtonErrorLog.txt", "a")
f.write(str(Argument))
f.close()
logging.error(Argument, exc_info=True)
This is the error message I get. It does actually repeat like that every time. I unfortunately do not know how to solve this. This would be the first time calling the database so I do not know what other thread would be running unless the PI OS happens to be using it and it would not let me create a new thread for my query. I made my cursor and connection global just to be sure it wasn't creating a new object inside the function.
SQLite objects created in a thread can only be used in that same thread. The object was created in thread id 3069925600 and this is thread id 3058426976.list index out of rangeSQLite objects created in a thread can only be used in that same thread. The object was created in thread id 3069925600 and this is thread id 3058426976.list index out of rangeSQLite objects created in a thread can only be used in that same thread. The object was created in thread id 3069479136 and this is thread id 3057980512.SQLite objects created in a thread can only be used in that same thread. The object was created in thread id 3069479136 and this is thread id 3057980512.SQLite objects created in a thread can only be used in that same thread. The object was created in thread id 3069479136 and this is thread id 3057980512.SQLite objects created in a thread can only be used in that same thread. The object was created in thread id 3069479136 and this is thread id 3057980512.SQLite objects created in a thread can only be used in that same thread. The object was created in thread id 3069458656 and this is thread id 3057960032.SQLite objects created in a thread can only be used in that same thread. The object was created in thread id 3069458656 and this is thread id 3057960032.SQLite objects created in a thread can only be used in that same thread. The object was created in thread id 3069458656 and this is thread id 3057960032.SQLite objects created in a thread can only be used in that same thread. The object was created in thread id 3069458656 and this is thread id 3057960032.SQLite objects created in a thread can only be used in that same thread. The object was created in thread id 3069458656 and this is thread id 3057960032.SQLite objects created in a thread can only be used in that same thread. The object was created in thread id 3069458656 and this is thread id 3057960032.list index out of rangeSQLite objects created in a thread can only be used in that same thread. The object was created in thread id 3069458656 and this is thread id 3057960032.list index out of range
Any help would be appreciated.
2022-12-30 4:28 am
I am NOT a python expert, but I looked at a program I wrote years ago that used the SQLitye3 DB. Each function/thread that needed to connect to the database had all the code necessary within the function/thread. For example, the following code was from a load(self) function in a thread. There was also a function called table_cell_clicked(self, row, col): that used the exact same code except for the execution command.
BASE_DIR = os.path.dirname(os.path.abspath(__file__))
db_path = os.path.join(BASE_DIR, "RPT_DB")
conn = sqlite3.connect(db_path)
c = conn.cursor()
c.execute("SELECT * FROM TherapyTypes")
.
.
.
.
.
conn.commit()
conn.close()
Topic starter
2022-12-30 5:16 am
I did just get it to work. I removed the connection and cursor object at the beginning and I included it in my functions, every time I needed it. I'm assuming that it was because it considered the functions separate threads(or it uses separate threads but I didn't think that Python would do that since it reads top down and does one thing at a time). Apparently, me declaring's it at the top opened it and the other calls were not allowed to use that thread. I'm not sure if this is the right way of doing it, but it worked. I would have thought that making an object at the top and calling would have been the correct way.
# Buttons for Tyon screen
import sqlite3
import RPi.GPIO as GPIO
import time
import datetime
import os
from datetime import date
from datetime import datetime, timedelta
GPIO.setmode(GPIO.BCM)
#Lists for use
dataDump = [] #A place to dump data recieved for DB
thisdate = [] #A place to store current date for comparison
newRecord = [0, 0, 0, 0, 0, 0, 0] #Holder for the new record
# Set sleep time
sleepTm = 2
# Set Pins for buttons in GPIO
fb = 12
mb = 16
check = False
#counter
ctd = 120
tmo = 120
pto = 200
def getWeek():
global dataDump
global thisdate
global cur #not sure if these need to be here but tried anyways
global con
dataDump.clear() #Clears list for fresh info
#Get last day of data
try:
thisdate = [int(datetime.today().year), int(datetime.today().month), int(datetime.today().day)]#Set todays date to compare.
con = sqlite3.connect('/home/pi/tyson101/python/examples/TysonDB.db')
cur = con.cursor()
cur.execute("SELECT Year, Month, Date, Food, AM, PM FROM Food WHERE Year = ? AND Month = ? ORDER BY -Date", (thisdate[0], thisdate[1]))
row = cur.fetchone() #Get one record, where condition above makes it the most recent
for entry in row:
dataDump.append(entry) #Append results in my list
con.close()
except Exception as Argument:
f= open("TysonButtonErrorLog.txt", "a")
f.write(str(Argument))
f.close()
# Callback for buttons
def fb_callback(fb): # Food Button
global ctd
global tmo
global dataDump
global thisdate
global cur
global con
if ctd == 0: #If timer had reached 0, this is because the built in debounce did not work. Timer is in loop section.
try:
getWeek()
#If date is found, compares date in fields of DB and compares to current date
if dataDump[0] == thisdate[0] and dataDump[1] == thisdate[1] and dataDump[2] == thisdate[2]:
if dataDump[3] < 6: #5 is the max, do not add if over.
tempDump = [(dataDump[3] + 1), dataDump[0], dataDump[1], dataDump[2]] #list for the update with the updated field first
con = sqlite3.connect('/home/pi/tyson101/python/examples/TysonDB.db')
cur = con.cursor()
sqlUpdate = """UPDATE Food SET Food = ? WHERE Year = ? AND Month = ? AND Date = ?"""
cur.execute(sqlUpdate, tempDump)
con.commit()
else:
today = date.today()
newRecordtemp = [thisdate[1], thisdate[2], thisdate[0], int(today.weekday()), 1, 0, 0]
con = sqlite3.connect('/home/pi/tyson101/python/examples/TysonDB.db')
cur = con.cursor()
sqlInsert = """INSERT INTO Food VALUES (?, ?, ?, ?, ?, ?, ?)"""
cur.execute(sqlInsert, newRecordtemp)
con.commit()
con.close()
os.system('python3 /home/pi/tyson101/python/examples/Tyson.py')
ctd = tmo
except Exception as Argument:
f = open("TysonButtonErrorLog.txt", "a")
f.write(str(Argument))
f.close()
This is so much cleaner than my original. It is also far easier to fix mistakes later.
huckOhio reacted