SQLAlchemy 简要

import urllib.parse

import sqlalchemy

password = ...
# escaping special characters such as @ signs
password = urllib.parse.quote_plus(password)
url = f'dialect+driver://username:{password}@host:port/database'
engine = sqlalchemy.create_engine(url)  # Engine object

with engine.connect() as conn:
    results = conn.execute('SELECT * FROM students')
    for row in results:
        print(row)

根据官方文档, The Engine is not synonymous to the DBAPI connect function, which represents just one connection resource - the Engine is most efficient when created just once at the module level of an application, not per-object or per-function call.

The Engine is a factory for connections as well as a pool of connections, not the connection itself. When you say conn.close(), the connection is returned to the connection pool within the Engine, not actually closed.

engine.dispose() 参考官方文档.

engine.connect() 返回一个 Connection 对象.

# engine/base.py

class Connection(Connectable):
    def __exit__(self, type_, value, traceback):
        self.close()

关于 engine.execute(...), engine.connect(), Session 的区别, 参考这里.

engine.execute(...) 其实内部先创建 engine.connect(), 执行完后再关闭. 不过 pandas 的 read_sql 内部不会关闭, 参考这里.
Session 用来做 Object Relationship Management (ORM).

ORM 是用面向对象的方式操作数据库, 使得对所有支持的数据库类型都能用相同的代码. 教程参考官方文档 (但总体而言官方文档写得太长了, 见为什么很多人都喜欢 Django 的 ORM 而不是 SQLAlchemy), 稳定版是 1.4, 最近已经有 2.0 beta 了, 只保留了核心操作. (我没看过)

如果写 raw sql 需要模板, 可以用 jinjasql 防止 SQL 注入.

查询很大的时候可以参考 Loading SQL data into Pandas without running out of memory.

参考