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.

You might have multipel python installations. If you change from `python` to `python3` that you have in your header in the python script. Or wise verse. Or you could from the terminal run `$ python -m pip install mysql-connector` to install mysql-connector for your current python installation — Adam, Jun 01 '19 at 23:15
I think the issue must be with multiple versions.Running as Python 3 throws the error. After installling the module I now get a different error. `code Traceback (most recent call last): File "py2db.py", line 10, in ip = ipaddress.get_ipaddress() File "/home/pi/PyScripts/ipaddress.py", line 18, in get_ipaddress return self.get_interface_ipaddress(network) File "/home/pi/PyScripts/ipaddress.py", line 14, in get_interface_ipaddress struct.pack('256s', network[:15]) struct.error: argument for 's' must be a bytes object` — Sparky3295, Jun 02 '19 at 08:27

score 0 · Accepted Answer · answered Jun 02 '19 at 05:48

0

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()

answered Jun 02 '19 at 05:48

Masoud Rahimi

5,785
15
39
67

Thanks for the reply @M. R. The system path I got from using the above was : /home/pi/.local/lib/python2.7/site-packages/mysql I then added the path to the script like this : import sys sys.path.append('/home/pi/.local/lib/python2.7/site-packages/mysql') But still have the same issue where on reboot it doesn't know what the module is. – Sparky3295 Jun 02 '19 at 08:07
The path is for Python2 but your script is using **Python3**. That is the problem. Your path is wrong. Try running script with `python3 get_mysql_path.py` and get the path for Python3. – Masoud Rahimi Jun 02 '19 at 09:08
Adjusting this has changed the error to : `code Initalize IPAddress Traceback (most recent call last): File "py2db.py", line 10, in ip = ipaddress.get_ipaddress() File "/home/pi/PyScripts/ipaddress.py", line 18, in get_ipaddress return self.get_interface_ipaddress(network) File "/home/pi/PyScripts/ipaddress.py", line 14, in get_interface_ipaddress struct.pack('256s', network[:15]) struct.error: argument for 's' must be a bytes object ` The script still runs fine just running as python. – Sparky3295 Jun 02 '19 at 10:01
It seems that your problem with importing `mysql` has been fixed. The next error seems for `ipaddress ` module, I don't know this module. – Masoud Rahimi Jun 02 '19 at 10:28
1

Thanks for the help. It appears the ipaddress module I was using wasn't compatible with python3. Changed to a different module and all working now. – Sparky3295 Jun 03 '19 at 19:51

Write to MySQL databse on Pi startup using Python

1 Answers1