将I2C传感器数据发送到本地SQL数据库的Python代码

Question

我正在开发一个I2C传感器系统，这些传感器连接在一起，并与树莓pi4B通信。使用下面的代码，我可以将测量结果保存在excel文件中。 我想将它们存储在我在笔记本电脑上本地创建的sql数据库中的一个表中。我应该在代码中更改什么

import time
import datetime
import bme680
from as7262 import AS7262
from datetime import date
from openpyxl import load_workbook

as7262 = AS7262()

as7262.set_gain(1) # 1, 3.7, 16, 64
as7262.set_integration_time(10) #1 to 255 x 2.8ms exposure
#mode 0 - bank 1 only continuous, mode 1 - bank 2 only continuous, mode 2 - both banks continuous, mode 3 - both banks single read 
as7262.set_measurement_mode(2) #2 all colours continuous
as7262.set_illumination_led_current(12.5) #12.5mA 25mA 50mA 100mA
as7262.set_illumination_led(0)

sensor_bme680 = bme680.BME680()

# Load the workbook and select the sheet
wb = load_workbook('/mypath/data.xlsx')
sheet = wb['data_log']

try:
    while True:
        values = as7262.get_calibrated_values() #get values from scan
        spec = [float(i) for i in list(values)] #convert results from string to float
        temperature = round(sensor_bme680.data.temperature, 2)
        pressure = round (sensor_bme680.data.pressure, 2)
        humidity = round(sensor_bme680.data.humidity, 2)
        gas_resistance = round(sensor_bme680.data.gas_resistance, 2)
        red_light = round(spec[0], 4)
        orange_light = round(spec[1], 4)
        yellow_light = round(spec[2], 4)
        green_light = round(spec[3], 4)
        blue_light = round(spec[4], 4)
        violet_light = round(spec[5], 4)
        today = date.today()
        now = datetime.datetime.now().time()
        
        # Inform the user!
        print('Adding this data to the spreadsheet:')
        print(today)
        print(now)
        print('{}*C {}hPa {}% {}res microM microM microM microM microM microM'.format(temperature, pressure, humidity, gas_resistance, red_light,orange_light,yellow_light,green_light,blue_light,violet_light))


        # Append data to the spreadsheet
        row = (today, now, temperature, pressure, humidity, gas_resistance, red_light,orange_light,yellow_light,green_light,blue_light,violet_light)
        sheet.append(row)
        
        #Save the workbook
        wb.save('/home/pi/Documents/sensors/data.xlsx')
        # Wait for 10 minutes seconds (600 seconds)
        time.sleep(10)

finally:
    # Make sure the workbook is saved!
    wb.save('/mypath/data.xlsx')
    
    print('Goodbye!')

Answer 1

我个人喜欢在大多数情况下与python数据库交互时使用sqlalchemy。它将表定义表示为类，要将行添加到数据库中，只需创建类的对象并通过sqlalchemy命令将其添加到数据库中。因此，必须用python定义数据库，以便代码知道其结构

例如，我假设您的数据库中只有一个表，与您的excel工作表具有相同的列。表的定义和db的创建（在该脚本所在的同一文件夹中创建的本地sqlite db）将如下所示作为脚本（让我们将此脚本称为db.py）：

from sqlalchemy.ext.declarative import declarative_base
from sqlalchemy import Column, Float, DateTime, Date
from sqlalchemy import create_engine

engine = create_engine('sqlite:///foo.db')
Base = declarative_base()

class Example(Base):
    id = Column(Integer, primary_key=True)
    temperature = Column(Float)
    humidity = Column(Float)
    .
    .
    # your other variables
    .
    .
    today = Column(Date)
    now = Column(DateTime)
    
if __name__ == '__main__':
    Base.metadata.create_all(engine)

运行上述脚本后，在脚本（您发布的脚本）中，必须导入Example类，并将向excel添加行的行替换为向数据库添加Example对象（创建后）的行

import time
import datetime
import bme680
from as7262 import AS7262
from datetime import date
from openpyxl import load_workbook
from db import Example
from sqlalchemy import create_engine
from sqlalchemy.orm import sessionmaker

# create a session for writing to your db
engine = create_engine('sqlite:///foo.db')
Session = sessionmaker(bind=engine)
session = Session()

as7262 = AS7262()

as7262.set_gain(1) # 1, 3.7, 16, 64
as7262.set_integration_time(10) #1 to 255 x 2.8ms exposure
#mode 0 - bank 1 only continuous, mode 1 - bank 2 only continuous, mode 2 - both banks continuous, mode 3 - both banks single read 
as7262.set_measurement_mode(2) #2 all colours continuous
as7262.set_illumination_led_current(12.5) #12.5mA 25mA 50mA 100mA
as7262.set_illumination_led(0)

sensor_bme680 = bme680.BME680()



try:
    while True:

        example_object = Example(
                temperature = round(sensor_bme680.data.temperature, 2),
                humidity = round(sensor_bme680.data.humidity, 2),
                .
                .
                # you other attributes
                .
                .
                today = date.today(),
                now = datetime.datetime.now().time())
        
        # Inform the user!
        print('Adding this data to the spreadsheet:')
        print(today)
        print(now)
        print('{}*C {}hPa {}% {}res microM microM microM microM microM microM'.format(example_object.temperature, example_object.pressure, example_object.humidity, example_object.gas_resistance, example_object.red_light,example_object.orange_light,example_object.yellow_light,example_object.green_light,example_object.blue_light,example_object.violet_light))


        # Add object to database
        session.add(example_object)
        session.commit()

        
        

finally:
    
    print('Goodbye!')

Answer 2

为了直接在数据库中而不是Excel工作表中发布数据，可以在python中使用mariadb

首先在RaspberryPi中下载mariadb，并使用所需的表设置数据库。然后，您可以添加下面提到的代码以连接到您的程序

for example:

mariadb_connection = mariadb.connect(user='username', password='password', database='databasename')
cursor= mariadb_connection.cursor()
Query1="Your query that you want to run"
cursor.execute(Query1,"anyvalue that will be passed")
mariadb_connection.commit();