Write to MySQL databse on Pi startup using Python

Question

My Raspberry Pi hosts a MySQL database. When my Pi starts it runs a python script which writes to the database. The script writes the date, time and IP address to the database.

The python script to do this runs perfectly when running it from the terminal. However when I try and run the python script on startup nothing happens. Creating a cronjob to run the python script showed me an error was being produced saying : "ImportError: No module named mysql.connector"

I'm just not sure why the script isn't working properly from startup when it works If I manually run it.

I have tried runnning the script directly from : /etc/rc.local running as :

sudo python /home/pi/PyScripts/py2db.py&

I have also created a sh script called launcher. I then call the python script in the sh script and call launcher.sh from a crontab. The idea behind the launcher script was that I could just add exra lines to this script If I added more things to my Pi's startup.

The python script launches fine when running the launcher.sh file or py2db.py file.

I created an output for the contab to see what happens when it trys to run.

Crontab code :

@reboot /home/pi/Scripts/launcher.sh >/home/pi/Logs/cronlog 2>&1

---

launcher.sh

#!/bin/sh
launcher.sh
python /home/pi/PyScripts/py2db.py

---

Crontab Log

Traceback (most recent call last):
  File "/home/pi/PyScripts/py2db.py", line 3, in <module>
    import mysql.connector
ImportError: No module named mysql.connector

---

py2db.py

#!/usr/bin/python3

import mysql.connector
import datetime
from ipaddress import IPAddress
import sys

ipaddress = IPAddress()
ip = ipaddress.get_ipaddress()

now = datetime.datetime.now()
time = now.isoformat()
date = now.strftime("%Y-%m-%d")

mydb = mysql.connector.connect(
  host="localhost",
  user="mark",
  passwd="password",
  database="mydb"
)

mycursor = mydb.cursor()

sql = "INSERT INTO piLog (date, time, ip) VALUES (%s, %s, %s)"
val = (date, time, ip)
mycursor.execute(sql, val)

mydb.commit()

print(mycursor.rowcount, "record inserted.")
print("[py2db] - Done.")

sys.exit()

I expect the python script to run on startup and write an entry to the database. The entry adds the time, date and ip address to a specified database table.

Currently nothing is happening on startup. The python script will only write to the database when called manually.

Answer 1

It seems that when you run your script as a cronjob it can't find the correct PYTHONPATH. First, find your module path with the below script:

import os
import mysql
mysql_path = os.path.dirname(mysql.__file__)
print(mysql_path)

Next add the mysql_path to sys.path before importing mysql:

#!/usr/bin/python3

from ipaddress import IPAddress
import datetime
import mysql.connector
import sys
sys.path.append('<mysql_path>')


ipaddress = IPAddress()
ip = ipaddress.get_ipaddress()

now = datetime.datetime.now()
time = now.isoformat()
date = now.strftime("%Y-%m-%d")

mydb = mysql.connector.connect(
    host="localhost",
    user="mark",
    passwd="password",
    database="mydb"
)

mycursor = mydb.cursor()

sql = "INSERT INTO piLog (date, time, ip) VALUES (%s, %s, %s)"
val = (date, time, ip)
mycursor.execute(sql, val)

mydb.commit()

print(mycursor.rowcount, "record inserted.")
print("[py2db] - Done.")

sys.exit()