专栏名称: 编程派
Python程序员都在看的公众号，跟着编程派一起学习Python，看最新国外教程和资源！
目录
相关文章推荐
Python中文社区  ·  免费金融数据 + Python ... ·  5 天前  
Python爱好者社区  ·  王者归来！《大模型最佳实践》开源了。。。 ·  6 天前  
Python爱好者社区  ·  写个爬虫，一单一个W ·  5 天前  
Python爱好者社区  ·  真的建议赶紧搞个软考证书！（红利期） ·  1 周前  
Python爱好者社区  ·  GPT-5被曝不及预期，OpenAI员工：没 ... ·  6 天前  
51好读  ›  专栏  ›  编程派

数据库操作必读：SQLAlchemy入门

编程派  · 公众号  · Python  · 2017-04-13 11:57

正文

原文：https://segmentfault.com/a/1190000004288061

全文约 7991 字，读完可能需要 12 分钟。

环境：Ubuntu 15.10 64-bit

SQLAlchemy是Python的ORM框架，它的理念是：数据库的量级和性能重要于对象集合，而对象集合的抽象又重要于表和行。

安装

直接通过pip安装：

  1. $ pip install sqlalchemy

打开Python，测试是否安装成功：

  1. >>> import sqlalchemy
  2. >>> sqlalchemy.__version__
  3. '1.0.9'

创建引擎

SQLite

首先以SQLite为例，因为它比较简单。

  1. from sqlalchemy import create_engine, MetaData
  3. engine = create_engine('sqlite:///foo.db', echo=True)
  4. metadata = MetaData(engine)

参数 sqlite:///foo.db 解释为：

  1. sqlite://<nohostname>/<path>

其中foo.db是相对路径。也可写成：

  1. sqlite:///./foo.db

SQLAlchemy缺省使用Python内建的sqlite3模块来连接或创建SQLite数据库。执行完create_engine后，可以发现当前目录多了foo.db文件，不妨用sqlite打开看看。

  1. $ sqlite3 foo.db
  2. SQLite version 3.8.11.1 2015-07-29 20:00:57
  3. Enter ".help" for usage hints.
  4. sqlite> .tables

注意这里用的是sqlite3而非sqlite，因为foo.db是经由Python内建的sqlite3模块创建的。

MySQL

再来看看连接MySQL时怎么创建引擎。 本文后续示例全部基于MySQL，这是与官方文档不同的地方。 先在MySQL里创建一个测试数据库：sa_test，后续示例都将基于这个数据库。

  1. mysql> CREATE DATABASE sa_test DEFAULT CHARACTER SET UTF8;




    

  1. from sqlalchemy import create_engine, MetaData
  3. engine = create_engine('mysql+mysqldb://root:******@localhost/sa_test', echo=True)
  4. metadata = MetaData(engine)

这里的参数看上去就比较复杂了，完整的格式为：

  1. dialect+driver://username:password@host:port/database

这里driver用了mysqldb，详见：MySQLdb：Python操作MySQL数据库

引擎配置的详细信息可参考官方文档：Engine Configuration

MetaData

前面在创建MetaData时绑定了引擎：

  1. metadata = MetaData(engine)

当然也可以不绑定。绑定的好处是，后续很多调用 （比如 MetaData.create_all()，Table.create()，等等）就不用指定引擎了。

创建表

创建两张表，user和address，address表里有一个user id的外键。 注意：表名没有像官方文档及很多人推荐的那样使用复数形式，个人偏好而已，详细讨论请见StackOverflow的这个问题：Table NamingDilemma: Singular vs. Plural Names

  1. from sqlalchemy import create_engine, MetaData,
  2.         Table, Column, Integer, String, ForeignKey
  4. engine = create_engine('mysql+mysqldb://root:******@localhost/sa_test', echo=True)
  5. metadata = MetaData(engine)
  1. user_table = Table('user', metadata,
  2.         Column('id', Integer, primary_key=True),
  3.         Column('name', String(50)),




    

  4.         Column('fullname', String(100))
  5.         )
  7. address_table = Table('address', metadata,
  8.         Column('id', Integer, primary_key=True),
  9.         Column('user_id', None, ForeignKey('user.id')),
  10.         Column('email', String(128), nullable=False)
  11.         )
  13. metadata.create_all()

执行完metadata.create_all()这一句，两张表就创建好了，可以在MySQL里立即查看。

MetaData.createall()可以多次调用，不会报错，它在内部会检查表是否已经创建。 因为MetaData创建时已经绑定了引擎，所以此处createall()就不必再指定了，否则得写成：

  1. metadata.create_all(engine)

创建引擎时，echo参数为True，程序运行时便有很多调试信息打印出来。在这些调试信息中，可以看到如下两条MySQL的CREATE TABLE语句：

  1. CREATE TABLE user (
  2.     id INTEGER NOT NULL AUTO_INCREMENT,
  3.     name VARCHAR(50),
  4.     fullname VARCHAR(100),
  5.     PRIMARY KEY (id)
  6. )
  8. CREATE TABLE address (
  9.     id INTEGER NOT NULL AUTO_INCREMENT,
  10.     user_id INTEGER,
  11.     email VARCHAR(128) NOT NULL,
  12.     PRIMARY KEY (id),




    

  13.     FOREIGN KEY(user_id) REFERENCES user (id)
  14. )

除了metadata.create_all()，Table自己也有create方法：

  1. create(bind=None, checkfirst=False)

参数bind一般就是指引擎。 参数checkfirst表示是否检查表已经存在。为True时，若表已经存在，不报错，只是什么也不做；为False时，若表已经存在，则将引发异常。 使用这个方法来创建这两张表：

  1. user_table.create(checkfirst=True)
  2. address_table.create(checkfirst=True)

这里忽略了bind参数，因为创建MetaData对象时已经绑定了引擎，而创建表对象时又传入了metadata，所以顺藤摸瓜，表自己是知道引擎的。 如果调整一下表的创建顺序，就会报错，因为address表里有一个user表的外键，而这时候user表还没创建呢。所以，还是建议使用MetaData.create_all()吧，毕竟它也会检查表是否已经存在。

表的反射 Table Reflection

表创建好了，一般也就不动了。所以实际应用时，往往表都已经存在，并不需要创建，只需把它们"导入"进来即可，这时就得使用autoload参数。

  1. from sqlalchemy import create_engine, MetaData, Table
  3. engine = create_engine('mysql+mysqldb://root:******@localhost/sa_test', echo=False)
  4. metadata = MetaData(engine)
  6. user_table = Table('user', metadata, autoload=True)
  8. print 'user' in metadata.tables
  9. print [c.name for c in user_table.columns]
  11. address_table = Table('address', metadata, autoload=True)
  12. print 'address' in metadata.tables

输出：

  1. True




    

  2. ['id', 'name', 'fullname']
  3. True

如果MetaData没有绑定引擎，则另需指定autoload_with参数：

  1. user_table = Table('user', metadata, autoload=True, autoload_with=engine)

如果被反射的表外键引用了另一个表，那么被引用的表也会一并被反射。比如只反射address表，user表也一并被反射了。

  1. from sqlalchemy import create_engine, MetaData, Table
  3. engine = create_engine('mysql+mysqldb://root:******@localhost/sa_test', echo=False)
  4. metadata = MetaData(engine)
  6. address_table = Table('address', metadata, autoload=True)
  8. print 'user' in metadata.tables
  9. print 'address' in metadata.tables

输出：

  1. True
  2. True

插入数据

插入数据之前，必须要有表对象，不管是新创建的，还是通过反射导入的。

Insert对象

要往表里插数据，先创建一个Insert对象：

  1. ins = user_table.insert()
  2. print ins

打印这个Insert对象，可以看到它所对应的SQL语句：

  1. INSERT INTO user (id, name, fullname) VALUES (%s, %s, %s)

如果连接的数据库不是MySQL而是SQLite，那输出可能就是下面这样：

  1. INSERT INTO user (id, name, fullname) VALUES (?, ?, ?)

可见SQLAlchemy帮我们封装了不同数据库之间语法的差异。 如果MetaData创建时没有绑定引擎，那么输出会略有不同：

  1. INSERT INTO "user" (id, name, fullname) VALUES (:id, :name, :fullname)

这时SQLAlchemy还不知道具体的数据库语法，表名加了引号（"user"），列名也改用为:id之类一般性的格式。 此外，这条INSERT语句列出了user表里的每一列，而id在插入时一般是不需要指定的，可以通过Insert.values()方法加以限制：

  1. ins = ins.values(name='adam', fullname='Adam Gu')
  2. print ins

限制后，id列已经没有了：

  1. INSERT INTO user (name, fullname) VALUES (%s, %s)

可见values()方法限制了INSERT语句所包含的列。但是我们指定的name和fullname的值并没有打印出来，这两个值保存在Insert对象里，只有等到执行时才会用到。

执行

我们一直在说的引擎，可以理解成一个数据库连接对象的仓库，通过连接对象可以往数据库发送具体的SQL语句。调用引擎的connect()方法可以获取一个连接：

  1. conn = engine.connect()

现在把前面的Insert对象丢给它来执行：

  1. result = conn.execute(ins)

由调试信息可见具体的INSERT语句：

  1. INSERT INTO user (name, fullname) VALUES (%s, %s)
  2. ('adam', 'Adam Gu')
  3. COMMIT

返回值result是一个ResultProxy对象，ResultProxy是对DB- API中cursor的封装。插入语句的结果并不常用，但是查询语句肯定是要用到它的。 不妨在MySQL里看一下刚插入的数据。 

  1. mysql> select * from user;
  2. +----+------+----------+
  3. | id | name | fullname |
  4. +----+------+----------+
  5. |  1 | adam | Adam Gu  |
  6. +----+------+----------+
  7. 1 row in set (0.00 sec)

执行多条语句

还记得前面的Insert对象使用values()方法来限制列吗？

  1. ins = ins.values(name='adam', fullname='Adam Gu')

这种方式其实不利于Insert对象的复用，更好的做法是把参数通过execute()方法传进去：

  1. ins = user_table.insert()
  2. conn.execute(ins, name='adam', fullname='Adam Gu')

Insert对象本身还是会包含所有列，最终INSERT语句里的列由execute()的参数决定。由调试信息可见具体的INSERT语句：

  1. INSERT INTO user (name, fullname) VALUES (%s, %s)
  2. ('adam', 'Adam Gu')
  3. COMMIT

一次插入多条记录也很简单，只要传一个字典列表（每个字典的键必须一致）给execute()即可。

  1. conn.execute(address_table.insert(), [
  2.     { 'user_id': 1, 'email': '[email protected]' },
  3.     { 'user_id': 1, 'email': '[email protected]' },
  4.     ])

调试信息里具体的INSERT语句：

  1. INSERT INTO address (user_id, email) VALUES (%s, %s)
  2. ((1, '[email protected]'), (1, '[email protected]'))
  3. COMMIT

在MySQL里看一下插入的地址：

  1. mysql> select * from address;
  2. +----+---------+-----------------------+
  3. | id | user_id | email                 |
  4. +----+---------+-----------------------+
  5. |  1 |       1 | sprinfall@gmail.com   |
  6. |  2 |       1 | sprinfall@hotmail.com |
  7. +----+---------+-----------------------+
  8. 2 rows in set (0.00 sec)

题图：pexels，CC0 授权。

点击阅读原文，查看更多 Python 教程和资源。

推荐文章
Python中文社区  ·  免费金融数据 + Python 自动化采集：机器学习股票预测神器
5 天前
Python爱好者社区  ·  王者归来！《大模型最佳实践》开源了。。。
6 天前
Python爱好者社区  ·  写个爬虫，一单一个W
5 天前
Python爱好者社区  ·  真的建议赶紧搞个软考证书！（红利期）
1 周前
Python爱好者社区  ·  GPT-5被曝不及预期，OpenAI员工：没什么科学突破了，接下来只需要工程
6 天前
猎云网  ·  独家 | 星云数据服务平台完成千万元天使轮融资，以游戏切入深耕大数据服务
8 年前
狗与爱的世界  ·  揪心！两只狗狗卧轨长达两天却奇迹生还，画面让人震惊！
7 年前
占豪  ·  2017年度最佳微小说《最大的炫耀》
7 年前
西子湖畔  ·  惠州第一美女找工作，简历震惊全国！
7 年前
网易科技  ·  比iPad Air 2便宜900元 新iPad开箱图赏
7 年前
Sov5搜索   ·   小百科   ·   移动版
51好读 - 好文章就要读起来!