树莓派systemd启动含SQLite的Python采集服务失败求助

开发树莓派开机自动向SQLite数据库追加数据的代码，原本用txt文件时systemd服务运行正常，改用SQLite后开机服务无法工作。手动运行代码时两种方式都正常，尝试修改systemd配置延迟启动（设置Type=idle或After=graphical.target）仍未解决。

服务文件VoltCurr.service #

[Unit]
After=multi-user.target
[Service]
Type=idle
ExecStart=/usr/bin/python /home/wrhsc1310/Python/VoltCurrWrite.py
[Install]
WantedBy=multi-user.target

Python代码 #

import time
import sqlite3
import Adafruit_ADS1x15
import board
from adafruit_ina219 import ADCResolution, BusVoltageRange, INA219
i2c_bus = board.I2C()  # uses board.SCL and board.SDA
# i2c_bus = board.STEMMA_I2C()  # For using the built-in STEMMA QT connector on a microcontroller

ina219 = INA219(i2c_bus)

# optional : change configuration to use 32 samples averaging for both bus voltage and shunt voltage
ina219.bus_adc_resolution = ADCResolution.ADCRES_12BIT_32S
ina219.shunt_adc_resolution = ADCResolution.ADCRES_12BIT_32S
# optional : change voltage range to 16V
ina219.bus_voltage_range = BusVoltageRange.RANGE_16V

# Create an ADS1115 ADC (16-bit) instance.
adc = Adafruit_ADS1x15.ADS1115()

# Choose a gain of 1 for reading voltages from 0 to 4.09V.
# Or pick a different gain to change the range of voltages that are read:
#  - 2/3 = +/-6.144V
#  -   1 = +/-4.096V
#  -   2 = +/-2.048V
#  -   4 = +/-1.024V
#  -   8 = +/-0.512V
#  -  16 = +/-0.256V
# See table 3 in the ADS1015/ADS1115 datasheet for more info on gain.
GAIN = 2

Time_Const=0
elapsed_time=0
i=0

while True:
    Time_Const=Time_Const+elapsed_time
    start_time=time.time()
    Time = (Time_Const) #Time in seconds
    Current = (ina219.current) #Current in mA
    Voltage = (adc.read_adc(0, gain=GAIN))*(0.0000625) #Voltage in Volts
    connection = sqlite3.connect('PowerData.db')
    cursor = connection.cursor()
    cursor.execute("INSERT INTO Data (Time, Voltage, Current) VALUES (?, ?, ?)",[Time,Voltage,Current])
    connection.commit()
    cursor.close()
    connection.close()
    i=i+1
    time.sleep(0.01)
    end_time=time.time()
    elapsed_time=end_time-start_time

服务错误信息 #

● VoltCurr.service
     Loaded: loaded (/lib/systemd/system/VoltCurr.service; enabled; vendor preset: enabled)
     Active: failed (Result: exit-code) since Tue 2023-05-30 19:04:48 BST; 2min 10s ago
    Process: 435 ExecStart=/usr/bin/python /home/wrhsc1310/Python/VoltCurrWrite.py (code=exited, status=1/FAILURE)
   Main PID: 435 (code=exited, status=1/FAILURE)
        CPU: 1.951s

May 30 19:04:36 raspberrypi systemd[1]: Started VoltCurr.service.
May 30 19:04:47 raspberrypi python[435]: Traceback (most recent call last):
May 30 19:04:47 raspberrypi python[435]:   File "/home/wrhsc1310/Python/VoltCurrWrite.py", line 43, in <module>
May 30 19:04:47 raspberrypi python[435]:     cursor.execute("INSERT INTO Data (Time, Voltage, Current) VALUES (?, ?, ?)",[Time,Voltage,Current])
May 30 19:04:47 raspberrypi python[435]: sqlite3.OperationalError: no such table: Data
May 30 19:04:48 raspberrypi systemd[1]: VoltCurr.service: Main process exited, code=exited, status=1/FAILURE
May 30 19:04:48 raspberrypi systemd[1]: VoltCurr.service: Failed with result 'exit-code'.
May 30 19:04:48 raspberrypi systemd[1]: VoltCurr.service: Consumed 1.951s CPU time.

核心问题是systemd服务运行时的工作目录与手动运行不一致，导致脚本在根目录创建了新的空SQLite数据库，而不是使用你手动运行时创建的带Data表的数据库。

1. 使用数据库绝对路径（最直接） #

修改Python脚本中连接数据库的代码，把相对路径改成绝对路径：

# 原代码
# connection = sqlite3.connect('PowerData.db')
# 修改为
connection = sqlite3.connect('/home/wrhsc1310/Python/PowerData.db')

这样不管脚本在哪个目录运行，都会指向正确的数据库文件。

2. 配置systemd服务的工作目录（更规范） #

在VoltCurr.service的[Service]段添加WorkingDirectory，指定脚本所在的目录：

[Service]
Type=idle
WorkingDirectory=/home/wrhsc1310/Python
ExecStart=/usr/bin/python /home/wrhsc1310/Python/VoltCurrWrite.py

同时建议添加User指定运行用户，避免权限问题：

User=wrhsc1310

修改后重新加载systemd配置并重启服务：

sudo systemctl daemon-reload
sudo systemctl restart VoltCurr.service

3. 确保Data表存在（兜底措施） #

在脚本中添加表初始化逻辑，确保数据库连接后自动创建Data表（如果不存在）：

connection = sqlite3.connect('/home/wrhsc1310/Python/PowerData.db')
cursor = connection.cursor()
# 初始化Data表
cursor.execute('''CREATE TABLE IF NOT EXISTS Data
                  (Time REAL, Voltage REAL, Current REAL)''')
connection.commit()
# 然后执行插入操作
cursor.execute("INSERT INTO Data (Time, Voltage, Current) VALUES (?, ?, ?)",[Time,Voltage,Current])

内容的提问来源于stack exchange，提问作者wrhsc1310