python连接mysql数据库

先安装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.5

Process finished with exit code 0

前往mysql数据库查看对应表格已经创建，并且有内容了