<p>我个人喜欢在大多数情况下与python数据库交互时使用sqlalchemy。它将表定义表示为类,要将行添加到数据库中,只需创建类的对象并通过sqlalchemy命令将其添加到数据库中。因此,必须用python定义数据库,以便代码知道其结构</p>
<p>例如,我假设您的数据库中只有一个表,与您的excel工作表具有相同的列。表的定义和db的创建(在该脚本所在的同一文件夹中创建的本地sqlite db)将如下所示作为脚本(让我们将此脚本称为db.py):</p>
<pre><code>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)
</code></pre>
<p>运行上述脚本后,在脚本(您发布的脚本)中,必须导入<code>Example</code>类,并将向excel添加行的行替换为向数据库添加<code>Example</code>对象(创建后)的行</p>
<pre><code>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!')
</code></pre>