Python数据库ORM工具sqlalchemy的学习笔记

开发 前端
SQLAlchemy是python的一个数据库ORM工具,提供了强大的对象模型间的转换,可以满足绝大多数数据库操作的需求,并且支持多种数据库引擎(sqlite,mysql,postgres, mongodb等),在这里记录基本用法和学习笔记。

SQLAlchemy是python的一个数据库ORM工具,提供了强大的对象模型间的转换,可以满足绝大多数数据库操作的需求,并且支持多种数据库引擎(sqlite,mysql,postgres, mongodb等),在这里记录基本用法和学习笔记。

一、安装

通过pip安装

  1. $ pip install SQLAlchemy 

二、使用

首先是连接到数据库,SQLALchemy支持多个数据库引擎,不同的数据库引擎连接字符串不一样,常用的有

  1. mysql://username:password@hostname/database 
  2. postgresql://username:password@hostname/database 
  3. sqlite:////absolute/path/to/database 
  4. sqlite:///c:/absolute/path/to/database 

 更多连接字符串的介绍参见这里

下面是连接和使用sqlite数据库的例子

1. connection

使用传统的connection的方式连接和操作数据库

  1. from sqlalchemy import create_engine 
  2.  
  3. # 数据库连接字符串 
  4. DB_CONNECT_STRING = 'sqlite:///:memory:' 
  5.  
  6. # 创建数据库引擎,echo为True,会打印所有的sql语句 
  7. engine = create_engine(DB_CONNECT_STRING, echo=True
  8.  
  9. # 创建一个connection,这里的使用方式与python自带的sqlite的使用方式类似 
  10. with engine.connect() as con: 
  11.     # 执行sql语句,如果是增删改,则直接生效,不需要commit 
  12.     rs = con.execute('SELECT 5'
  13.     data = rs.fetchone()[0] 
  14.     print "Data: %s" % data 

 与python自带的sqlite不同,这里不需要Cursor光标,执行sql语句不需要commit

2. connection事务

使用事务可以进行批量提交和回滚 

  1. from sqlalchemy import create_engine 
  2.  
  3. # 数据库连接字符串 
  4. DB_CONNECT_STRING = 'sqlite:////Users/zhengxiankai/Desktop/Document/db.sqlite' 
  5. engine = create_engine(DB_CONNECT_STRING, echo=True
  6.  
  7. with engine.connect() as connection
  8.     trans = connection.begin() 
  9.     try: 
  10.         r1 = connection.execute("select * from User"
  11.         r2 = connection.execute("insert into User(name, age) values(?, ?)"'bomo', 24) 
  12.         trans.commit() 
  13.     except
  14.         trans.rollback() 
  15.         raise  

3. session

connection是一般使用数据库的方式,sqlalchemy还提供了另一种操作数据库的方式,通过session对象,session可以记录和跟踪数据的改变,在适当的时候提交,并且支持强大的ORM的功能,下面是基本使用 

  1. from sqlalchemy import create_engine 
  2. from sqlalchemy.orm import sessionmaker 
  3.  
  4. # 数据库连接字符串 
  5. DB_CONNECT_STRING = 'sqlite:////Users/zhengxiankai/Desktop/Document/db.sqlite' 
  6.  
  7. # 创建数据库引擎,echo为True,会打印所有的sql语句 
  8. engine = create_engine(DB_CONNECT_STRING, echo=True
  9.  
  10. # 创建会话类 
  11. DB_Session = sessionmaker(bind=engine) 
  12.  
  13. # 创建会话对象 
  14. session = DB_Session() 
  15.  
  16. # dosomething with session 
  17.  
  18. # 用完记得关闭,也可以用with 
  19. session.close()  

上面创建了一个session对象,接下来可以操作数据库了,session也支持通过sql语句操作数据库 

  1. session.execute('select * from User'
  2. session.execute("insert into User(name, age) values('bomo', 13)"
  3. session.execute("insert into User(name, age) values(:name, :age)", {'name''bomo''age':12}) 
  4.  
  5. # 如果是增删改,需要commit 
  6. session.commit()  

注意参数使用dict,并在sql语句中使用:key占位

4. ORM

上面简单介绍了sql的简单用法,既然是ORM框架,我们先定义两个模型类User和Role,sqlalchemy的模型类继承自一个由declarative_base()方法生成的类,我们先定义一个模块Models.py生成Base类 

  1. from sqlalchemy.ext.declarative import declarative_base 
  2.  
  3. Base = declarative_base()  

User.py 

  1. from sqlalchemy import ColumnInteger, String 
  2. from Models import Base 
  3.  
  4. class User(Base): 
  5.     __tablename__ = 'User' 
  6.     id = Column('id'Integer, primary_key=True, autoincrement=True
  7.     name = Column('name', String(50)) 
  8.     age = Column('age'Integer 

Role.py 

  1. from sqlalchemy import ColumnInteger, String 
  2. from Models import Base 
  3.  
  4. class Role(Base): 
  5.     __tablename__ = 'Role' 
  6.     id = Column('id'Integer, primary_key=True, autoincrement=True
  7.     name = Column('name', String(50))  

从上面很容易看出来,这里的模型对应数据库中的表,模型支持的类型有Integer, String, Boolean, Date, DateTime, Float,更多类型包括类型对应的Python的类型参见:这里

Column构造函数相关设置

  • name:名称
  • type_:列类型
  • autoincrement:自增
  • default:默认值
  • index:索引
  • nullable:可空
  • primary_key:外键

更多介绍参见这里

接下来通过session进行增删改查 

  1. from sqlalchemy import create_engine 
  2. from sqlalchemy.orm import sessionmaker 
  3. from User import User 
  4. from Role import Role 
  5. from Models import Base 
  6.  
  7. DB_CONNECT_STRING = 'sqlite:////Users/zhengxiankai/Desktop/Document/db.sqlite' 
  8. engine = create_engine(DB_CONNECT_STRING, echo=True
  9. DB_Session = sessionmaker(bind=engine) 
  10. session = DB_Session() 
  11.  
  12. # 1. 创建表(如果表已经存在,则不会创建) 
  13. Base.metadata.create_all(engine) 
  14.  
  15. # 2. 插入数据 
  16. u = User(name = 'tobi', age = 200) 
  17. r = Role(name = 'user'
  18.  
  19. # 2.1 使用add,如果已经存在,会报错 
  20. session.add(u) 
  21. session.add(r) 
  22. session.commit() 
  23. print r.id 
  24.  
  25. # 3 修改数据 
  26. # 3.1 使用merge方法,如果存在则修改,如果不存在则插入 
  27. r.name = 'admin' 
  28. session.merge(r) 
  29.  
  30. # 3.2 也可以通过这种方式修改 
  31. session.query(Role).filter(Role.id == 1).update({'name''admin'}) 
  32.  
  33. # 4. 删除数据 
  34. session.query(Role).filter(Role.id == 1).delete() 
  35.  
  36. # 5. 查询数据 
  37. # 5.1 返回结果集的第二项 
  38. user = session.query(User).get(2) 
  39.  
  40. # 5.2 返回结果集中的第2-3项 
  41. users = session.query(User)[1:3] 
  42.  
  43. # 5.3 查询条件 
  44. user = session.query(User).filter(User.id < 6).first() 
  45.  
  46. # 5.4 排序 
  47. users = session.query(User).order_by(User.name
  48.  
  49. # 5.5 降序(需要导入desc方法) 
  50. from sqlalchemy import desc 
  51. users = session.query(User).order_by(desc(User.name)) 
  52.  
  53. # 5.6 只查询部分属性 
  54. users = session.query(User.name).order_by(desc(User.name)) 
  55. for user in users: 
  56.     print user.name 
  57.  
  58. # 5.7 给结果集的列取别名 
  59. users = session.query(User.name.label('user_name')).all() 
  60. for user in users: 
  61.     print user.user_name 
  62.  
  63. # 5.8 去重查询(需要导入distinct方法) 
  64. from sqlalchemy import distinct 
  65. users = session.query(distinct(User.name).label('name')).all() 
  66.  
  67. # 5.9 统计查询 
  68. user_count = session.query(User.name).order_by(User.name).count() 
  69. age_avg = session.query(func.avg(User.age)).first() 
  70. age_sum = session.query(func.sum(User.age)).first() 
  71.  
  72. # 5.10 分组查询 
  73. users = session.query(func.count(User.name).label('count'), User.age).group_by(User.age) 
  74. for user in users: 
  75.     print 'age:{0}, count:{1}'.format(user.age, user.count
  76.  
  77. session.close() 

 5. 多表关系

上面的所有操作都是基于单个表的操作,下面是多表以及关系的使用,我们修改上面两个表,添加外键关联(一对多和多对一)

User模型 

  1. from sqlalchemy import ColumnInteger, String 
  2. from sqlalchemy import ForeignKey 
  3. from sqlalchemy.orm import relationship 
  4. from Models import Base 
  5.  
  6. class User(Base): 
  7.     __tablename__ = 'users' 
  8.     id = Column('id'Integer, primary_key=True, autoincrement=True
  9.     name = Column('name', String(50)) 
  10.     age = Column('age'Integer
  11.  
  12.     # 添加角色id外键(关联到Role表的id属性) 
  13.     role_id = Column('role_id'Integer, ForeignKey('roles.id')) 
  14.     # 添加同表外键 
  15.     second_role_id = Column('second_role_id'Integer, ForeignKey('roles.id')) 
  16.  
  17.     # 添加关系属性,关联到role_id外键上 
  18.     role = relationship('Role', foreign_keys='User.role_id', backref='User_role_id'
  19.     # 添加关系属性,关联到second_role_id外键上 
  20.     second_role = relationship('Role', foreign_keys='User.second_role_id', backref='User_second_role_id'

 Role模型 

  1. from sqlalchemy import ColumnInteger, String 
  2. from sqlalchemy.orm import relationship 
  3. from Models import Base 
  4.  
  5. class Role(Base): 
  6.     __tablename__ = 'roles' 
  7.     id = Column('id'Integer, primary_key=True, autoincrement=True
  8.     name = Column('name', String(50)) 
  9.  
  10.     # 添加关系属性,关联到User.role_id属性上 
  11.     users = relationship("User", foreign_keys='User.role_id', backref="Role_users"
  12.     # 添加关系属性,关联到User.second_role_id属性上 
  13.     second_users = relationship("User", foreign_keys='User.second_role_id', backref="Role_second_users"

 这里有一点需要注意的是,设置外键的时候ForeignKey('roles.id')这里面使用的是表名和表列,在设置关联属性的时候relationship('Role', foreign_keys='User.role_id', backref='User_role_id'),这里的foreign_keys使用的时候类名和属性名

接下来就可以使用了 

  1. u = User(name='tobi', age=200) 
  2.  
  3. r1 = Role(name='admin'
  4. r2 = Role(name='user'
  5.  
  6. u.role = r1 
  7. u.second_role = r2 
  8.  
  9. session.add(u) 
  10. session.commit() 
  11.  
  12. # 查询(对于外键关联的关系属性可以直接访问,在需要用到的时候session会到数据库查询) 
  13. roles = session.query(Role).all() 
  14. for role in roles: 
  15.     print 'role:{0} users' 
  16.     for user in role.users: 
  17.         print '\t{0}'.format(user.name
  18.     print 'role:{0} second_users' 
  19.     for user in role.second_users: 
  20.         print '\t{0}'.format(user.name

 上面表示的是一对多(多对一)的关系,还有一对一,多对多,如果要表示一对一的关系,在定义relationship的时候设置uselist为False(默认为True),如在Role中 

  1. class Role(Base): 
  2.     ... 
  3.     user = relationship("User", uselist=False, foreign_keys='User.role_id', backref="Role_user" 

6. 多表查询

多表查询通常使用join进行表连接,***个参数为表名,第二个参数为条件,例如 

  1. users = db.session.query(User).join(Role, Role.id == User.role_id) 
  2.  
  3. for u in users: 
  4.     print u.name  

join为内连接,还有左连接outerjoin,用法与join类似,右连接和全外链接在1.0版本上不支持,通常来说有这两个结合查询的方法基本够用了,1.1版本貌似添加了右连接和全外连接的支持,但是目前只是预览版

还可以直接查询多个表,如下 

  1. result = db.session.query(User, Role).filter(User.role_id = Role.id) 
  2. # 这里选择的是两个表,使用元组获取数据 
  3. for u, r in result: 
  4.       print u.name  

三、数据库迁移

sqlalchemy的数据库迁移/升级有两个库支持alembic和sqlalchemy-migrate

由于sqlalchemy-migrate在2011年发布了0.7.2版本后,就已经停止更新了,并且已经不维护了,也积累了很多bug,而alembic是较后来才出现,而且是sqlalchemy的作者开发的,有良好的社区支持,所以在这里只学习alembic这个库

alembic实现了类似git/svn的版本管理的控制,我们可以通过alembic维护每次升级数据库的版本

1. 安装

通过pip安装,pip会自动安装相关的依赖

  1. $ pip install alembic 

2. 初始化

安装完成后再项目根目录运行 

  1. yourproject/ 
  2.     alembic.ini 
  3.     YOUR_ALEMBIC_DIR/ 
  4.         env.py 
  5.         README 
  6.         script.py.mako 
  7.         versions/ 
  8.             3512b954651e_add_account.py 
  9.             2b1ae634e5cd_add_order_id.py 
  10.             3adcc9a56557_rename_username_field.py  

其中

  • alembic.ini 提供了一些基本的配置
  • env.py 每次执行Alembic都会加载这个模块,主要提供项目Sqlalchemy Model 的连接
  • script.py.mako 迁移脚本生成模版
  • versions 存放生成的迁移脚本目录

默认情况下创建的是基于单个数据库的,如果需要支持多个数据库或其他,可以通过alembic list_templates查看支持的模板 

  1. $ alembic list_templates 
  2. Available templates: 
  3.  
  4. generic - Generic single-database configuration. 
  5. multidb - Rudimentary multi-database configuration. 
  6. pylons - Configuration that reads from a Pylons project environment. 
  7.  
  8. Templates are used via the 'init' command, e.g.: 
  9.  
  10.   alembic init --template generic ./scripts  

3. 配置

使用之前,需要配置一下链接字符串,打开alembic.ini文件,设置sqlalchemy.url连接字符串,例如 

  1. sqlalchemy.url = sqlite:////Users/zhengxiankai/Desktop/database.db 

其他参数可以参见官网说明:http://alembic.zzzcomputing.com/en/latest/tutorial.html

4. 创建数据库版本

接下来我们创建一个数据库版本,并新建两个表 

  1. $ alembic revision -m 'create table' 

创建一个版本(会在yourproject/YOUR_ALEMBIC_DIR/versions/文件夹中创建一个python文件1a8a0d799b33_create_table.py)

该python模块包含upgrade和downgrade两个方法,在这里添加一些新增表的逻辑 

  1. """create table 
  2.  
  3. Revision ID: 4fd533a56b34 
  4. Revises: 
  5. Create Date: 2016-09-18 17:20:27.667100 
  6.  
  7. ""
  8. from alembic import op 
  9. import sqlalchemy as sa 
  10.  
  11.  
  12. # revision identifiers, used by Alembic. 
  13. revision = '4fd533a56b34' 
  14. down_revision = None 
  15. branch_labels = None 
  16. depends_on = None 
  17.  
  18. def upgrade(): 
  19.     # 添加表 
  20.     op.create_table( 
  21.         'account'
  22.         sa.Column('id', sa.Integer, primary_key=True), 
  23.         sa.Column('name', sa.String(50), nullable=False), 
  24.         sa.Column('description', sa.Unicode(200)), 
  25.     ) 
  26.  
  27.     # 添加列 
  28.     # op.add_column('account', sa.Column('last_transaction_date', sa.DateTime)) 
  29.  
  30.  
  31.  
  32. def downgrade(): 
  33.     # 删除表 
  34.     op.drop_table('account'
  35.  
  36.     # 删除列 
  37.     # op.drop_column('account''last_transaction_date' 

这里使用到了了op对象,关于op对象的更多API使用,参见这里

5. 升级数据库

刚刚实现了升级和降级的方法,通过下面命令升级数据库到***版本 

  1. $ alembic upgrade head 

这时候可以看到数据库多了两个表alembic_version和account,alembic_version存放数据库版本

关于升级和降级的其他命令还有下面这些 

  1. # 升到***版本 
  2. $ alembic upgrade head 
  3.  
  4. # 降到最初版本 
  5. $ alembic downgrade base 
  6.  
  7. # 升两级 
  8. $ alembic upgrade +2 
  9.  
  10. # 降一级 
  11. $ alembic downgrade -1 
  12.  
  13. # 升级到制定版本 
  14. $ alembic upgrade e93b8d488143 
  15.  
  16. # 查看当前版本 
  17. $ alembic current 
  18.  
  19. # 查看历史版本详情 
  20. $ alembic history --verbose 
  21.  
  22. # 查看历史版本(-r参数)类似切片 
  23. $ alembic history -r1975ea:ae1027 
  24. $ alembic history -r-3:current 
  25. $ alembic history -r1975ea:  

6. 通过元数据升级数据库

上面我们是通过API升级和降级,我们也可以直接通过元数据更新数据库,也就是自动生成升级代码,先定义两个Model(User,Role),这里我定义成三个文件 

  1. yourproject/ 
  2.     YOUR_ALEMBIC_DIR/ 
  3.     tutorial/Db 
  4.         Models.py 
  5.         User.py 
  6.         Role.py 

 代码就放在一起了 

  1. from sqlalchemy.ext.declarative import declarative_base 
  2. from sqlalchemy import ColumnInteger, String 
  3. Base = declarative_base() 
  4.  
  5. class User(Base): 
  6.     __tablename__ = 'users' 
  7.  
  8.     id = Column('id'Integer, primary_key=True, autoincrement=True
  9.     name = Column('name', String) 
  10.  
  11. class Role(Base): 
  12.     __tablename__ = 'roles' 
  13.  
  14.     id = Column('id'Integer, primary_key=True, autoincrement=True
  15.     name = Column('name', String) 

 在YOUR_ALEMBIC_DIR/env.py配置元数据 

  1. target_metadata = None 

改为 

  1. import os 
  2. import sys 
  3.  
  4. # 这里需要添加相对路径到sys.path,否则会引用失败,尝试过使用相对路径,但各种不好使,还是使用这种方法靠谱些 
  5. sys.path.append(os.path.abspath(os.path.join(os.getcwd(), "../yourproject/tutorial/Db"))) 
  6.  
  7. from User import User 
  8. from Role import Role 
  9. from Models import Base 
  10. target_metadata = Base.metadata  

os.path.join(os.getcwd()这个获取到的地址不是env.py的路径,而是根目录

在创建数据库版本的时候添加--autogenerate参数,就会从Base.metadata元数据中生成脚本 

  1. $ alembic revision --autogenerate -m "add user table" 

这时候会在生成升级代码 

  1. """add user table 
  2.  
  3. Revision ID: 97de1533584a 
  4. Revises: 8678ab6d48c1 
  5. Create Date: 2016-09-19 21:58:00.758410 
  6.  
  7. ""
  8. from alembic import op 
  9. import sqlalchemy as sa 
  10.  
  11.  
  12. # revision identifiers, used by Alembic. 
  13. revision = '97de1533584a' 
  14. down_revision = '8678ab6d48c1' 
  15. branch_labels = None 
  16. depends_on = None 
  17.  
  18. def upgrade(): 
  19.     ### commands auto generated by Alembic - please adjust! ### 
  20.     op.create_table('roles'
  21.     sa.Column('id', sa.Integer(), nullable=False), 
  22.     sa.Column('name', sa.String(), nullable=True), 
  23.     sa.PrimaryKeyConstraint('id'
  24.     ) 
  25.     op.create_table('users'
  26.     sa.Column('id', sa.Integer(), nullable=False), 
  27.     sa.Column('name', sa.String(), nullable=True), 
  28.     sa.PrimaryKeyConstraint('id'
  29.     ) 
  30.     op.drop_table('account'
  31.     ### end Alembic commands ### 
  32.  
  33.  
  34. def downgrade(): 
  35.     ### commands auto generated by Alembic - please adjust! ### 
  36.     op.create_table('account'
  37.     sa.Column('id', sa.INTEGER(), nullable=False), 
  38.     sa.Column('name', sa.VARCHAR(length=50), nullable=False), 
  39.     sa.Column('description', sa.VARCHAR(length=200), nullable=True), 
  40.     sa.Column('last_transaction_date', sa.DATETIME(), nullable=True), 
  41.     sa.PrimaryKeyConstraint('id'
  42.     ) 
  43.     op.drop_table('users'
  44.     op.drop_table('roles'
  45.     ### end Alembic commands ###  

由于我没有定义account模型,会被识别为删除,如果删除了model的列的声明,则会被识别为删除列,自动生成的版本我们也可以自己修改,然后执行升级命令即可升级alembic upgrade head

需要注意的是

  1. Base.metadata声明的类必须以数据库中的一一对应,如果数据库中有的表,而在元数据中没有,会识别成删除表
  2. revision创建版本之前执行之前需要升级到***版本
  3. 配置Base之前,需要保证所有的Model都已经执行(即导入)过一次了,否则无法读取到,也就是需要把所有Model都import进来

数据库升级有风险,升级前***先检查一遍upgrade函数,可以的话做好备份哈

四、常见问题

1. String长度问题

如果使用mysql数据库,String类型对应的是VARCHAR类型,需要指定长度,否则会报下面错误,而在sqlite不会出现 

  1. (in table 'user'column 'name'): VARCHAR requires a length on dialect mysql 

TODO:如有其他问题欢迎留言

五、参考链接

***安利一下自己的博客:http://blog.bomobox.org

责任编辑:庞桂玉 来源: segmentfault
相关推荐

2019-11-20 08:50:16

PythonORM工具包SQLAlchemy

2014-12-29 10:29:40

OpenStack数据库SQLAlchem

2023-05-17 10:16:04

ORM工具包SQLAlchemy

2013-11-26 09:47:47

ORM

2015-01-12 15:07:56

SQLAlchemy数据库开发OpenStac

2011-03-03 11:07:57

Spring数据库访问ORM

2011-08-11 14:23:57

SQL Server 索引分区

2011-08-25 11:08:21

Sybase数据库

2011-08-22 15:43:08

IOS开发数据库

2023-11-02 08:56:59

ORMGORM

2024-01-15 13:34:00

2021-08-19 07:25:02

数据库Flask插件

2011-08-29 18:17:12

Oracle联合查询

2011-08-30 13:33:29

Qt数据库

2023-06-06 08:25:31

SQLAlchemy数据库

2010-02-02 15:48:49

Python数据库

2009-05-14 09:20:41

关系数据库对象关系映射Terracotta

2011-03-23 15:34:57

数据库审计

2020-11-20 14:49:56

数据库

2011-03-28 15:44:45

惠普数据库Oracle数据库
点赞
收藏

51CTO技术栈公众号