前文使用了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的支持。
