Python工具箱系列(三十五)

前文使用了SQLAlchemy的Core层来实现数据库、表与数据的CRUD。初步体现出了SQLAlchemy的优势。但ORM的特点没有充分地表现出来。下面的代码则从Python的类出现，生成表结构，并且进行数据的CRUD操作。

from sqlalchemy import (Column, DateTime, Float, ForeignKey, Integer, MetaData,
                        String, Table, create_engine, text)
from sqlalchemy.orm import Session, registry, relationship
from sqlalchemy_utils.functions import create_database, database_exists
import random
import datetime
from sqlalchemy import select
configure_pg = {"user": "postgres",
                'password': '88488848',
                'dns': '192.168.0.155',
                "port": 5432,
                'prefix': 'postgresql+psycopg2',
                'postfix': ''
                }

configure_mssql = {"user": "sa",
                   'password': '88488848',
                   'dns': '192.168.0.155',
                   "port": 1433,
                   'prefix': 'mssql+pymssql',
                   'postfix': '?charset=utf8'
                   }

configure_mysql = {"user": "root",
                   'password': '88488848',
                   'dns': '192.168.0.155',
                   "port": 3306,
                   'prefix': 'mysql+mysqlconnector',
                   'postfix': ''
                   }

config = {'mssql': configure_mssql,
          'mysql': configure_mysql, 'postgresql': configure_pg}

database_name = 'sensordb'
table_sensor_location = "sensor_location_orm"
table_sensor_data = "sensor_data_orm"

mapper_registry = registry()
Base = mapper_registry.generate_base()

class Sensor(Base):
    __tablename__ = table_sensor_location
    id = Column(Integer, primary_key=True, autoincrement=False)
    location = Column(String(30))

    def __repr__(self):
        return f"Sensor(id={self.id!r}, location={self.location!r}"


class SensorData(Base):
    __tablename__ = table_sensor_data
    id = Column(Integer, primary_key=True, autoincrement=False)
    sensor_id = Column(Integer, ForeignKey(f'{table_sensor_location}.id'))
    sensor = relationship("Sensor")
    area = Column(String(30))
    pm25 = Column(Float)
    timestamp = Column(DateTime)

    def __repr__(self):
        return f"SensorData(id={self.id!r}, pm25={self.pm25!r})"


def linkdb(targetstr):
    """
    连接不同的数据库

    Args:
        targetstr (string): 数据库名称

    Returns:
        engine: 用于后续的数据库连接
    """
    if targetstr in config.keys():
        item = config[targetstr]
        connectstring = f"{item['prefix']}://{item['user']}:{item['password']}@{item['dns']}:{item['port']}/{database_name}{item['postfix']}"
        engine = create_engine(connectstring, echo=True, future=True)

    # 如果数据库不存在，则创建之
    if not database_exists(engine.url):
        create_database(engine.url)

    return engine

def gen_sensorinfo(connector):
    # 向sensor_location_orm插入传感器记录
    sensor1 = Sensor(id=1, location="floor")
    sensor2 = Sensor(id=2, location='wall')
    session = Session(connector)
    session.add(sensor1)
    session.add(sensor2)
    session.commit()


def gen_sensordata(connector):
    # 向sensor_data_orm插入测量数据
    session = Session(connector)
    currenttime = datetime.datetime.now()

    # 向sensor_data_orm插入多条记录
    for index in range(10):
        currenttime = currenttime+datetime.timedelta(seconds=1)

        sensordata = SensorData(id=index, sensor_id=index % 2 + 1,
                                area="xian", pm25=random.uniform(0, 300), timestamp=currenttime)
        session.add(sensordata)
        session.flush()
    session.commit()

def query_demo(connector):
    """
    查询示例代码

    Args:
        connector (engine): 数据库连接
    """
    session = Session(connector)
    
    # 查询传感器信息
    stmt = select(Sensor).where(Sensor.id == 2)
    print(stmt)
    result = session.execute(stmt)
    for sensor_obj in result.scalars():
        print(sensor_obj.location)

    # 查询传感器数据
    stmt = select(SensorData).order_by(SensorData.sensor_id)
    print(stmt)
    result = session.execute(stmt)
    for sensordata_obj in result.scalars():
        print(sensordata_obj.pm25,sensordata_obj.timestamp)

    # join
    stmt = select(SensorData.id,SensorData.pm25, Sensor.location).join(SensorData.sensor).order_by(SensorData.pm25)
    for row in session.execute(stmt):
        print(row)

# 依次连接多个数据库。从而验证代码的一致性
for dbname in config.keys():
    con = linkdb(dbname)

    # 创建数据库
    mapper_registry.metadata.create_all(con)

    # 创建传感器信息
    gen_sensorinfo(con)

    # 创建传感器数据
    gen_sensordata(con)
    
    # 查询示例
    query_demo(con)

ORM编程的一个好处就是不用写SQL语句，而以面向对象的思维方式来看数据库。正如前文所说，ORM将类与数据库的表一一对应。我们定义两个类。

◆Sensor类。保存传感器本身的信息。

◆SensorData类-保存传感器测量出来的数据。

这两个类均继续SQLAlchemy提供的Base类。虽然也可以自行定义类与表的映射关系，但最方便的仍然是直接继承Base类。两个类的写法类似，均需要定义主键，同时在SensorData类定义了外键形成Sensor与SensorData的ONE-TO-MANY的关系如下图所示：

类Sensor中的repr是为了方便输出信息用。类SensorData中的relationship定义了与Sensor的关联，后续在ORM基于JOIN的查询中起作用。

定义好Sensor与SensorData类后，直接使用mapper_registry.metadata.create_all(con)就可以创建与两个类对应的数据库。代码分别在mysql/mssql/postgresql等数据库中同时建立sensordb数据库与对应的两个表。

随后gen_sensorinfo创建2个Sensor类的实例，然后当作记录插入到表中。gen_sensordata创建10个模拟数据，并且当作记录插入表中，如下图所示：

在整个创建数据库/表/记录的过程中，全程没有SQL语句出现。随后使用query_demo演示查询的能力。

stmt = select(Sensor).where(Sensor.id == 2)
print(stmt)

以上代码能够输出对应的SQL语句如下：

SELECT sensor_location_orm.id, sensor_location_orm.location
FROM sensor_location_orm
WHERE sensor_location_orm.id = %(id_1)s

这些代码就是SQLAlchemy在后台实际运行的SQL语句。更为复杂的基于表关联的查询如下所示：

stmt = select(SensorData.id,SensorData.pm25, Sensor.location).join(SensorData.sensor).order_by(SensorData.pm25)
for row in session.execute(stmt):
    print(row)

以上语句对表sensor_data_orm进行查询，同时基于sensor_id关联到sensor_location_orm表，将传感的位置信息输出。形成的查询结果如下所示：

(1, 52.030998649965355, 'wall')
(0, 52.33204928632623, 'floor')
(6, 59.39568057249285, 'floor')
(2, 73.41761802578553, 'floor')
(9, 77.12459935499986, 'wall')
(4, 119.62669535658156, 'floor')
(5, 154.50858896768042, 'wall')
(7, 165.03277721842784, 'wall')
(3, 186.1637895876464, 'wall')
(8, 288.3729395365834, 'floor')

由于语句中要求order_by(SensorData.pm25),所以id不是连续的，但pm列是从小到大排列。同时传感器的位置信息也显示出来。从示例代码来看，对于数据库的操作完成类似于对类的操作，查询方式也非常简单，不用关心SQL的实现，体现了ORM编程的强大威力。不过SQLAlchemy虽然好，但设计之初的目标是针对关系型数据库的，NOSQL数据库的支持不足。部分数据库(例如clickhouse等)自行提供了对SQLAlchemy的支持。