SQLAlchemy 是 Python 的 SQL 工具包和对象关系映射器( Object Relational Mapper,ORM)，为应用程序开发人员提供了 SQL 的全部能力和灵活性。它提供了一整套著名的企业级持久化模式，旨在实现高效和高性能的数据库访问，并将其转化为简单且符合 Python 风格的领域语言。

SQLAlchemy 主要由对象关系映射器（ORM）和Core组成。

Core包含了 SQLAlchemy 的 SQL 和数据库集成以及描述服务的功能，其中最突出的部分是 SQL 表达式语言。SQL 表达式语言是一个独立的工具包，用于构建 SQL 表达式的系统，这些表达式可以在特定事务的范围内执行并返回结果集。

ORM 提供了一种将领域对象模型映射到数据库模式的方式，通过一种称为工作单元的模式自动化 DML 操作，使得对数据库的操作更加面向对象化。

ORM的好处:

简洁性:ORM允许用户使用自己选择的编程语言，比使用原始SQL查询更简洁、更简单的语法，从而减少所需的代码量。最优化:ORM还使用户能够利用面向对象编程的优化，如继承、封装和抽象，通过将数据库记录表示为对象。灵活性:使用ORM，用户可以轻松地在不同的数据库管理系统（MySQL、Sqlite3、PostgreSQL等等）之间进行切换，而无需对这些系统以及SQL语言具有扎实的理解。

SQLAlchemy 是 Python 应用开发中的核心组件，是应用程序与数据库交互的关键。掌握 SQLAlchemy 是 Python 工程师必备的技能。我们将分为两部分介绍 SQLAlchemy。第一部分将结合原生 SQL 和 SQLAlchemy 的操作方式进行对比，以加深理解。第二部分将结合我们在 SmartNotebook 应用开发上的实践经验，分享一些最佳实践和我们在开发过程中遇到的一些问题及解决方法。

接下来，我们将通过模拟两张简单的表格，演示原生 SQL 和 SQLAlchemy 两种操作方式的对比。我们将列举有限的几个示例，更详细的内容可以参考 SQLAlchemy 的文档手册。

为了方便介绍原生 SQL 操作，我们将使用 SmartNotebook 中的 SQL 单元格。而对于 SQLAlchemy 的操作，我们将借用 SmartNotebook 中的数据源和引擎（通过数据源标记 UID 获得 engine）。当然，您也可以创建自己的 engine，但这里不详细展开。

创建的测试数据库

我们将使用MySQL创建两张示例表,分别是humans和dogs。一个human可以拥有零只或多只dog。

create table humans(    id INT AUTO_INCREMENT PRIMARY KEY,    name varchar(64),    age int,    gender varchar(8),    job varchar(64));insert into humans(name, age, gender, job) values('tom', 25, 'male', 'teacher'),('jerry', 35, 'male', 'astronaut'),('vivi', 28, 'female', 'scientist'),('lili', 46, 'female', 'florist'),('bob', 68, 'male', 'retired');create table dogs(    id INT AUTO_INCREMENT PRIMARY KEY,    name varchar(64),    age int,    gender varchar(8),    breed varchar(64),    weight varchar(64),    owner int,    CONSTRAINT fk_humans FOREIGN KEY (owner) REFERENCES humans(id));insert into dogs(name, age, gender, breed, weight, owner) values('rocky', 7, 'male', 'german shepherd', 20, 1),('remy', 3, 'female', 'mongrel', 8, 1),('fifi', 13, 'female', 'german shepherd', 25, 2),('baaron', 14, 'male', 'german shepherd', 24, 3),('lucky', 5, 'male', 'mongrel', 12, null),('ducky', 5, 'female', 'mongrel', 13, null);

简单查询示例:

原生SQL:

SELECT * FROM dogs

用SQLAlchemy,这里包含初始准备工作: 对象类的定义和绑定,engine 和session 的创建。

from sqlalchemy import create_engine, Column, Integer, String, selectfrom sqlalchemy.orm import Sessionfrom sqlalchemy.ext.declarative import declarative_basefrom sqlalchemy.orm import sessionmakerfrom snb_plugin.sql.execute_sql import __smartnotebook_getengine_by_conn_id as snb_conn  engine=snb_conn("0242ac110002-11ede30f-a22ca266-92a2", context=globals())DbSession = sessionmaker(bind=engine)# 生成所有模型类的父类SnbBaseModel = declarative_base(bind=engine)class Human(SnbBaseModel):    __tablename__ = 'humans'    id: int = Column(Integer, primary_key=True)    name: str = Column(String)    age: int = Column(Integer)    gender: str = Column(String)    job: str = Column(String)    def __repr__(self):      return f"id={self.id} , name={self.name} , age={self.age}, gender={self.gender} , job={self.job}"class Dog(SnbBaseModel):    __tablename__ = 'dogs'    id: int = Column(Integer, primary_key=True)    name: str = Column(String)    age: int = Column(Integer)    gender: str = Column(String)    breed: str = Column(String)    weight: int = Column(Integer)    owner: int = Column(Integer)        def __repr__(self):      return f"id={self.id} , name={self.name} , age={self.age}, gender={self.gender} , breed={self.breed} , weight={self.weight} , owner={self.owner}"session=DbSession()statement = select(Dog) # SQLAlchemy statement goes hereres = session.execute(statement)for dog in res:  print(dog)

在SQLAlchemy中，我们必须为每个现有的表创建一个类，然后才能与数据库进行交互。虽然这一开始可能看起来有些麻烦，但当代码库变得庞大时，长远来看这会让事情变得更容易。

单条件查询:Select all dogs whose age ≤ 5

原生 SQL:

select * from dogs where age<=5

SQLAlchemy:

res = session.query(Dog).where(Dog.age<=5)for dog in res:  print(dog)

组合条件查询:Select all female dogs whose age ≤ 5

原生 SQL:

select * from dogs where age<=5 and gender='female'

SQLAlchemy:以下三种方式都可以

res = session.query(Dog).where(Dog.age<=5, Dog.gender=='female')for dog in res:  print(dog)print('*'*90)res = session.query(Dog).where(Dog.age<=5).where(Dog.gender=='female')for dog in res:  print(dog)print('*'*90)from sqlalchemy import  and_res = session.query(Dog).where(and_(Dog.age<=5, Dog.gender=='female'))for dog in res:  print(dog)

条件查询:Select all dogs that are EITHER female or whose age≤5

原生 SQL:

select * from dogs where age<=5 or gender='female'

SQLAlchemy:

from sqlalchemy import or_res = session.query(Dog).where(or_(Dog.age<=5, Dog.gender=='female'))for dog in res:  print(dog)

选择部分属性

原生 SQL:

select name, age, gender from dogs where age<= 5;

SQLAlchemy:

res = session.query(Dog.name, Dog.age, Dog.gender).where(Dog.age<=5)for dog in res:  print(dog)

汇总聚合:Get count grouped by gender

原生 SQL:

select gender, count(*) from dogs group by gender;

SQLAlchemy:

from sqlalchemy import funcres = session.query(Dog.gender,func.count(Dog.gender)).group_by(Dog.gender)for dog in res:  print(dog)

汇总聚合:Get average age grouped by gender

原生 SQL:

select gender, avg(age) from dogs group by gender;

SQLAlchemy:

from sqlalchemy import funcres = session.query(Dog.gender,func.avg(Dog.age)).group_by(Dog.gender)for dog in res:  print(dog)

汇总聚合:Get average age grouped by gender and breed

原生 SQL:

select gender, breed, avg(age) from dogs group by gender, breed;

SQLAlchemy:

from sqlalchemy import funcres = session.query(    Dog.gender, Dog.breed,    func.avg(Dog.age)).group_by(Dog.gender, Dog.breed)for dog in res:  print(dog)

关联查询:Select all dog names belonging to ‘tom’

原生 SQL:

select     humans.name as human_name,     dogs.name as dog_namefrom dogs inner join humans    on dogs.owner = humans.idwhere humans.name = 'tom';

SQLAlchemy:

res = session.query(Dog.name, Human.name)\        .select_from(Dog)\        .join(Human, Dog.owner==Human.id)\        .where(Human.name=='tom')for dog in res:  print(dog)

关联查询:Selecting all dog(name, age) + human(name, age, gender) where human is male

原生 SQL:

select     d.name as dog_name,    d.age as dog_age,    h.name as human_name,    h.age as human_age,    h.gender as human_genderfrom dogs as d inner join humans as h    on d.owner = h.idwhere h.gender='male';

SQLAlchemy:

res = session.query(Dog.name, Dog.age,     Human.name, Human.age, Human.gender)\.select_from(Dog)\.join(Human, Dog.owner==Human.id)\.where(Human.name=='tom')for dog in res:  print(dog)

查询: Selecting all dogs (name, age) without owners

原生 SQL:

select name, age from dogs where owner is null;

SQLAlchemy:

res = session.query(Dog.name, Dog.age).where(Dog.owner==None)for dog in res:  print(dog)

left join:Selecting all humans (name, age) without dogs

原生 SQL:

select     h.name, h.agefrom humans as h left join dogs as d    on h.id = d.ownerwhere d.id is null;

SQLAlchemy:

res = session.query(Human.name, Human.age)\    .select_from(Human)\    .outerjoin(Dog, Human.id==Dog.owner)\    .where(Dog.name==None)for dog in res:  print(dog)

inner join:Selecting all distinct human names who own at least one german shepherd

原生 SQL:

select     distinct(h.name)from humans as h inner join dogs as d    on h.id = d.ownerwhere d.breed = 'german shepherd';

SQLAlchemy:

from sqlalchemy import distinctres = session.query(distinct(Human.name))\    .select_from(Human)\    .join(Dog, Human.id==Dog.owner)\    .where(Dog.breed=='german shepherd')for dog in res:  print(dog)

SQLAlchemy是Python的SQL工具包和对象关系映射器（ORM），为开发人员提供了全面的SQL能力和灵活性。它提供了著名的持久化模式，以简单且符合Python风格的领域语言实现高效、高性能的数据库访问。ORM将领域对象模型映射到数据库模式，使数据库操作更面向对象化。ORM的优势在于简洁性、最优化和灵活性，让用户能够轻松地使用其选择的编程语言进行数据库操作，并利用面向对象编程的优势。