先安装mysql数据库,并完成创建名为easy_test的数据库,通过以下命令在mysql命令行创建名为rf_ice_data的空表
CREATE TABLE rf_ice_data ( id INT AUTO_INCREMENT PRIMARY KEY, devId VARCHAR(255), time DATETIME, temperature DOUBLE, latitude DOUBLE, longitude DOUBLE, createTime DATETIME DEFAULT CURRENT_TIMESTAMP, updateTime DATETIME DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP, isDelete INT, projectId INT);
Query OK, 0 rows affected (0.02 sec)
安装python模块pip install sqlalchemy,pip install mysql
from sqlalchemy import create_engine, Column, Integer, String, DateTime, Float, func
from sqlalchemy.ext.declarative import declarative_base
from sqlalchemy.orm import sessionmaker
from datetime import datetime
# 创建基类
Base = declarative_base()
class RfIceData(Base):
__tablename__ = 'rf_ice_data' # 假设数据库表名为 rf_ice_data
# 定义字段,与 Java 类定义相似
id = Column(Integer, primary_key=True, autoincrement=True)
devId = Column(String)
time = Column(DateTime)
temperature = Column(Float)
latitude = Column(Float)
longitude = Column(Float)
createTime = Column(DateTime, default=func.now())
updateTime = Column(DateTime, onupdate=func.now())
isDelete = Column(Integer)
projectId = Column(Integer)
# 创建数据库引擎,这里以 SQL 为例
engine = create_engine('mysql+pymysql://root:123@localhost/easy_test')
#engine = create_engine('mysql+pymysql://your_username:your_password@localhost/your_database_name')
# 创建所有定义的表
Base.metadata.create_all(engine)
# 创建 Session 类
Session = sessionmaker(bind=engine)
# 使用 Session
session = Session()
# 示例:添加一条记录
new_record = RfIceData(
devId="device123",
time=datetime.now(),
temperature=23.5,
latitude=34.05,
longitude=-118.25,
isDelete=0,
projectId=1
)
session.add(new_record)
session.commit()
# 示例:查询记录
records = session.query(RfIceData).all()
for record in records:
print(record.id, record.devId, record.time, record.temperature)
# 关闭 Session
session.close()
运行结果:
MovedIn20Warning: The ``declarative_base()`` function is now available as sqlalchemy.orm.declarative_base(). (deprecated since: 2.0) (Background on SQLAlchemy 2.0 at: https://sqlalche.me/e/b8d9)
Base = declarative_base()
1 device123 2024-05-21 09:57:58 23.5Process finished with exit code 0
前往mysql数据库查看对应表格已经创建,并且有内容了