概述
SQLAlchemy 库用于与各种数据库交互,你可使用一种类似于 Python 类和语句的方式创建数据模型和查询,SQLAlchemy 可用于连接大多数常见的数据库,比如 Postgres、MySQL、SQLite、Oracle 等:
SQLAlchemy 提供了两种主要的使用模式——SQLAlchemy Core和SQLAlchemy ORM,这为我们使用SQLAlchemy提供了很大的灵活性,两种方式可以单独使用也可以组合使用。
SQLAlchemy Core 和 SQLAlchemy ORM
SQLAlchemy Core 提供了连接数据库,操作数据的相关组件,其中SQL Expression Language是最为核心的组件,该组件允许我们使用sql语句和表达式实现对数据库进行操作:
engine = create_engine("[db]+[driver]://[username]:[password]@[host]:[port]/[database]")
with engine.connect() as con:
sql = "SELECT * FROM {table_name};"
con.execute(sql)
SQLAlchemy ORM类似于对象关系映射(ORM)。通过使用python中的类与数据库中的表相关关联,并在此基础使用类似于python方法的方式来实现对于数据的操作:
class User(db.Model):
__tableName__ = 'dc_user'
id = db.Column(db.Integer, autoincrement=True, primary_key=True)
name = db.Column(db.String(16))
password = db.Column(db.String(16))
User.query.get(id)
engine.session.add(User(id,name,password))
安装
SQLAlchemy 可以支持python2.7,python3.6+的python环境
直接从PyPI下载并安装
pip install SQLAlchemy
SQLAlchemy连接数据库需要和数据库对应的Python驱动程序的支持,SQLAlchemy默认直接支持SQLite3,不需要再安装额外的驱动程序,但是再连接其他数据库时候需要安装想要的数据库驱动程序,并且该程序需要支持DBAPI规范:
数据库 | 驱动 |
---|---|
Oracle | cs_Oracle |
Mysql | mysqlclient, pymysql, MySQL-Connector … |
PostgreSQL | psycopg2 |
SQLAlchemy 还支持连接 Drizzle、Firebird、Sybase 和 Microsoft SQL Server。
具体详情可以查看 SQLAlchemy DBAPI
SQLAlchmey Core 连接数据库
-
首先需要创建对应数据的Engine对象,Engine对象会根据数据库的连接参数为数据库连接池分配空间,并维持SQLAlchmey与数据库的连接,Engine对象通常是针对一种数据库而创建的全局对象。
from sqlalchemy import create_engine """ 需要指明连接数据库所用的驱动 例如连接mysql使用的是pymysql 则需要在creat_engine中的url的[db]+[driver]字段中指明mysql+pymysql echo 是否将执行信息打印 future 是否使用2.0版本中的 Engine 对象来创建 同时creat_engine方法中也提供了很多参数来用于设置连接数据库,具体可以查看相关源码 """ engine = create_engine("[db]+[driver]://[username]:[password]@[host]:[port]/[table/database]", echo=True, future=True)
当创建好engine时,实际上并没有真的去连接数据库,只是将连接的资源所需要进行分配,只有在第一次执行任务的时候才会真正连接到数据库中
-
再获得Engine对象之后我们就可以连接到数据库中
with engine.connect() as conn: result = conn.execute("{sql}") print(result.all()) # 使用with语句来管理Engine对象创建的Connection对象,保证在连接完成后可以自动释放资源,此时execute()执行后SQLAlchmey默认事务仍在执行中并不会自动的提交,当作用域结束释放连接之后会发生回滚,sql语句并不会修改数据库中的数据这时候就需要主动提交事务 conn.commit()
同时还可使用一下方式来连接数据库并执行:
with engine.begin() as conn: conn.execute( text("INSERT INTO some_table (x, y) VALUES (:x, :y)"), [{"x": 6, "y": 8}, {"x": 9, "y": 10}] ) #这种方式直接声明当前的连接块为事务块,会在资源回收后自动的commit
-
在执行sql语句时,一般先写好sql语句的statement之后再将相关的值注入sql语句中,SQLAlchemy Core中也提供相应的实现,我们可以使用text()方法来预先构建sql statement,对于需要注入值的变量,需要用
:
来标明,之后在execte()时传入对应的字典或者字典数组with engine.connect() as conn: sql = text("INSERT INTO some_table (x, y) VALUES (:x, :y)") # 插入单条数据 conn.execute( sql, {"x": 11, "y": 12} ) # 插入多条数据 conn.execute( sql, [{"x": 11, "y": 12}, {"x": 13, "y": 14}] ) conn.commit()
-
在连接并执行相关sql语句之后我们就需要对数据中的查询结果进行处理,SQLAlchemy Core中提供了一个Result的类,在一个Result的类对象中保存执行的结果,Result中提供了很多用于转换和读取的方法:
# 使用元组的方式访问result中的数据 result = conn.execute(text("select x, y from some_table")) for x, y in result: pass # 使用索引的方式 result = conn.execute(text("select x, y from some_table")) for row in result: x = row[0] # 使用访问其中属性的方式 result = conn.execute(text("select x, y from some_table")) for row in result: y = row.y print(f"Row: {row.x} {y}") # 同时Result中还提供将结果转换成字典的方法 Result.mappings() result = conn.execute(text("select x, y from some_table")) for dict_row in result.mappings(): x = dict_row['x'] y = dict_row['y']