Python库之SQLAlchemy

一、SQLAlchemy简介 1.1、SQLAlchemy是什么？

sqlalchemy是一个python语言实现的的针对关系型数据库的orm库。可用于连接大多数常见的数据库，比如Postges、MySQL、SQLite、Oracle等。

1.2、为什么要使用SQLAlchemy？

它将你的代码从底层数据库及其相关的SQL特性中抽象出来。

1.3、SQLAlchemy提供了两种主要的使用模式

SQL表达式语言（SQLAlchemy Core）

ORM

1.4、应该选择哪种模式？

虽然你使用的框架中已经内置了ORM，但是希望添加更强大的报表功能，请选用Core。

如果你想在一个一模式为中心的视图中查看数据（用户类似于SQL），请使用Core。

如果你的数据不需要业务对象，请使用Core。

如果你要把数据看作业务对象，请使用ORM。

如果你想快速创建原型，请使用ORM。

如果你需要同事使用业务对象和其他与问题域无关的数据，请组合使用Core和ORM。

1.5、连接数据库

要连接到数据库，需要先创建一个SQLAlchemy引擎。SQLAlchemy引擎为数据库创建一个公共接口来执行SQL语句。这是通过包装数据库连接池和方言（不同数据库客户端）来实现的。

SQLAlchemy提供了一个函数来创建引擎。在这个函数中，你可以指定连接字符串，以及其他一些可选的关键字参数。

from sqlalchemy import create_engine engine = create_engine('sqlite:///cookies.db') engine1 = create_engine('sqlite:///:memory:') engine2 = create_engine('sqlite://///home/cookiemonster/cookies.db') engine3 = create_engine('sqlite:///c:\\Users\\cookiemonster\\cookies.db') engine_mysql = create_engine('mysql+pymysql://cookiemonster:chocolatechip', '@mysql01.monster.internal/cookies', pool_recycle=3600) 1.6、模式和类型

为了访问底层数据库，SQLAlchemy需要用某种东西来代表数据库中的表。为此，可以使用下面三种方法总的一种：

使用用户定义的Table对象

使用代表数据表的声明式类

从数据库中推断

二、SQLAlchemy core

SQLAlchemy core定义表结构使用的是1.5中说的第1种方式。table对象包含一系列带有类型的列和属性，它们与一个常见的元数据容器相关联。

元数据可以看作是一种Table对象目录。这些表可以通过MetaData.tables来访问。

2.1、定义表结构

在SQLAlchemy Core中，我们通过Table构造函数来初始化Table对象。我们要在构造函数中提供MetaData对象（元数据）和表名，任何其他参数都被认为是列对象。列是通过Column()函数创建的。

from sqlalchemy import create_engine from sqlalchemy import Column from sqlalchemy import Integer from sqlalchemy import String from sqlalchemy import MedaData metadata = MetaData() user = table('user', metadata, Column(id, Integer(), primary_key=True), Column(name, String(255)), ) engine = create_engine('sqlite:///:memory:') metadata.create_all(engine) # 表的持久化 2.2、插入数据

首先创造一条insert语句，用来把小明放入user表中。为此，先调用user表的insert()方法，然后再使用values()语句，关键字参数为各个列及相应值：

ins = user.insert().values( id=1, ) print(str(ins))

到此仅仅只是创建了一个inset语句，还没有真正执行呢，接下来执行插入操作：

connection = engine.connect() result = connection.execute(ins) print(result.inserted_primary_key) 2.3、查询数据

构建查询时，要用到select函数，它类似于标准SQL SELECT语句。

from sqlalchemy.sql import select s = select([user]) # 可以使用str(s)查看数据库看到的语句 print(str(s)) rp = connection.execute(s) results = rp.fetchall() 2.3.1、ResultProxy

execute()函数的返回值是一热ResultProxy对象，它允许使用索引、名称或Column对象进行访问。

使用ResultProxy处理行

first_row = results[0] first_row[1] first_row.name first_row[user.c.name]

迭代ResultProxy

rp = connection.execute(s) for record in rp: print(record.user_name)

使用方法访问结果

rp.first() # 若有记录，则返回第一个记录并关闭连接 rp.fetchone() # 返回一行，并保持光标为打开状态，以便你做更多获取调用 rp.scalar() # 入股查询结果是包含一个列的单条记录，则返回单个值 2.3.2、控制查询中的列数 s = select([user.c.name]) rp = connection.execute(s) print(rp.keys()) result = rp.first() 2.3.3、排序 s = select([user.c.name]) s = s.order_by(user.c.name) rp = connection.execute(s) for user in rp: print(user.name) 2.3.4、限制返回结果集的条数 s = select([user.c.name]) s = s.order_by(user.c.name) s = s.limit(2) rp = connection.execute(s) for user in rp: print(user.name) 2.3.5、内置SQL函数和标签 from sqlalchemy.sql import func s = select([func.sum(user.c.score)]) rp = connection.execute(s) print(rp.scalar()) 2.3.6、过滤

对查询过滤是通过添加where()语句来完成的。

s = select([user]).where(user.c.name == '小明') rp = connection.execute(s) record = rp.first() print(record.items())

这里只是介绍了常用的查询方法，更多复杂的查询请查阅官方文档。

2.3、更新数据