Frollo
Posts: 20
Joined: Thu Aug 27, 2015 8:44 pm

SQL request fail from python script launched by cron

Sun Jun 24, 2018 7:12 pm

I have some scripts in Python which are executed by crontab on Raspbian. I use the MySQLdb library for request in the local network.

All scripts work fine if i launch them directly from Python-IDLE, or in the console.
But if they are launched by cron, those who just execute "INSERT" requests work, but those who execute "SELECT" request don't work.

I haven't found a clear solution, but it seems that crontab doesn't execute the same configuration of the SQL client as the user of the console. Maybe i have to change the path before all request ? (looking for a "./my.cnf" ) ?
Maybe i have to "force" the user, but i thought that
"crontab -e" is for the user, and "sudo crontab -e" with root rights.

unsuccessfull tries :
tested with libraries MySQLdb and PyMySQL
tested with "sudo python /home/pi/Documents/..."
tested on Jessie '(Raspi 2), on Stretch (Raspi 3B+)
tested with the script as a service
tested with "sudo crontab -e"
:cry:

an example code below (on a local network) :

Code: Select all

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

# importations  
import os
import time
import sys
import pymysql as sql  # or MySQLdb as...
from os import path as os_path

#  constants
PATH_THERM = "/home/pi/Documents/" #path to this script
DB_SERVER ='192.168.0.59'       # MySQL : IP server 
DB_USER='user'                    # MySQL : user
DB_PWD='password'              # MySQL : password
DB_BASE='capteurs'              # MySQL : database name

def log(texte):
    datation = time.strftime('%d-%m-%Y %H:%M:%S')

    logue = open('log_test.txt','a')
    txt = "\n" + datation + "\t" + texte
    txt = txt.encode('utf-8')
    logue.write(txt)
    logue.close()

def query_temp():
    datebuff = time.strftime('%d-%m-%Y')

    db = sql.connect(DB_SERVER, DB_USER, DB_PWD, DB_BASE)
    cursor = db.cursor()
    cursor.execute("""SELECT sonde2,date FROM `PiTemp` ORDER BY date DESC LIMIT 0, 1""")
    rows = cursor.fetchall()
    print datebuff, u" : Dernière température de l'eau :", rows[0][0], u"°C"
    log(u"lecture température SQL - ok")

    a = rows[0][0]
    b =  rows[0][1]
    return (a, b)

#             principal code                              
#-------------------------------------
PATH=os_path.abspath(os_path.split(__file__)[0])
os.chdir(PATH)
log('start')
log(PATH)
txt = str(query_temp()[0])
log(txt)

and the corresponding crontab :

Code: Select all

*/1 * * * * python /home/pi/Documents/180623_test.py

Frollo
Posts: 20
Joined: Thu Aug 27, 2015 8:44 pm

Re: SQL request fail from python script launched by cron

Mon Jun 25, 2018 2:12 pm

NB : the script works launched from the console or from IDLE, but if i "sudo chmod +x test.py" and clic on it, then "execute", the SQL-request fail..
:?

Frollo
Posts: 20
Joined: Thu Aug 27, 2015 8:44 pm

Re: SQL request fail from python script launched by cron

Mon Jun 25, 2018 7:39 pm

... print (u"Alerte, tous à poil !")
UnicodeEncodeDecode Error !
Strange, looks like cron doesn't encode / decode the same than the user.... (!?)

Solved with no latin characters in print, or print passed by try/except...

Return to “Python”

Who is online

Users browsing this forum: scotty101 and 21 guests