script crashes on last day of month but works every other day

[neil black]

Hi
I have this python script using mysql.connector
I am using a raspberry pi 4 and the db is MariaDB version 10.5.15

I hope this the right forum to ask this question so here goes.

#!/usr/bin/python3

### imports ###
import mysql.connector
import datetime
import schedule
import time

### connect to mysql ###

def check_date():
db = mysql.connector.connect(
host="localhost",
user="user",
password="hello",
database="there"
)

print("connect")

cursor = db.cursor()
sql = "(select count(*) from meter where date = current_date() + 1)"
cursor.execute(sql)
result = cursor.fetchone()
cursor.close()
today = result[0]
print("check")
print(today)
if today == 1:
db.close()
print("------------")
return

else:
mycursor = db.cursor()
sql = "insert into meter (date, water_in_lts, rain_gauge) Values (%s, %s, %s)"
val = (datetime.datetime.now() + datetime.timedelta(days=1), 0, 0)
mycursor.execute(sql, val)
db.commit()
print("insert")
check_date()

check_date()

schedule.every().day.at("00:01").do(check_date)
#schedule.every().minutes.do(check_date)

while True:
schedule.run_pending()
time.sleep(1)


Its purpose is to insert tomorrows date into my database so other scrips can then update other fields for the current day

This works fine on every day except the last day of the month in which it just crashes and burns.

looking at the logs it seems that it a MYSQL problem rather than python
the logs say that I have a duplicate entry for the date column

The script runs as a service and spends all of the last day of the month crashing but as soon as the date turn to the first of the next month it runs fine.

can any one see why this would happen?


Regards Neil