SQLAlchemy是python的一個資料庫ORM工具,提供了強大的物件模型間的轉換,可以滿足絕大多數資料庫操作的需求,並且支援多種資料庫引擎(sqlite,mysql,postgres, mongodb等),在這裡記錄基本用法和學習筆記
一、安裝
通過pip安裝
1 |
$ pip install SQLAlchemy |
二、使用
首先是連線到資料庫,SQLALchemy支援多個資料庫引擎,不同的資料庫引擎連線字串不一樣,常用的有
1 2 3 4 |
mysql://username:password@hostname/database postgresql://username:password@hostname/database sqlite:////absolute/path/to/database sqlite:///c:/absolute/path/to/database |
更多連線字串的介紹參見這裡
下面是連線和使用sqlite資料庫的例子
1. connection
使用傳統的connection的方式連線和運算元據庫
1 2 3 4 5 6 7 8 9 10 11 12 13 14 |
from sqlalchemy import create_engine # 資料庫連線字串 DB_CONNECT_STRING = 'sqlite:///:memory:' # 建立資料庫引擎,echo為True,會列印所有的sql語句 engine = create_engine(DB_CONNECT_STRING, echo=True) # 建立一個connection,這裡的使用方式與python自帶的sqlite的使用方式類似 with engine.connect() as con: # 執行sql語句,如果是增刪改,則直接生效,不需要commit rs = con.execute('SELECT 5') data = rs.fetchone()[0] print "Data: %s" % data |
與python自帶的sqlite不同,這裡不需要Cursor游標,執行sql語句不需要commit
2. connection事務
使用事務可以進行批量提交和回滾
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 |
from sqlalchemy import create_engine # 資料庫連線字串 DB_CONNECT_STRING = 'sqlite:////Users/zhengxiankai/Desktop/Document/db.sqlite' engine = create_engine(DB_CONNECT_STRING, echo=True) with engine.connect() as connection: trans = connection.begin() try: r1 = connection.execute("select * from User") r2 = connection.execute("insert into User(name, age) values(?, ?)", 'bomo', 24) trans.commit() except: trans.rollback() raise |
3. session
connection是一般使用資料庫的方式,sqlalchemy還提供了另一種運算元據庫的方式,通過session物件,session可以記錄和跟蹤資料的改變,在適當的時候提交,並且支援強大的ORM的功能,下面是基本使用
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 |
from sqlalchemy import create_engine from sqlalchemy.orm import sessionmaker # 資料庫連線字串 DB_CONNECT_STRING = 'sqlite:////Users/zhengxiankai/Desktop/Document/db.sqlite' # 建立資料庫引擎,echo為True,會列印所有的sql語句 engine = create_engine(DB_CONNECT_STRING, echo=True) # 建立會話類 DB_Session = sessionmaker(bind=engine) # 建立會話物件 session = DB_Session() # dosomething with session # 用完記得關閉,也可以用with session.close() |
上面建立了一個session物件,接下來可以運算元據庫了,session也支援通過sql語句運算元據庫
1 2 3 4 5 6 |
session.execute('select * from User') session.execute("insert into User(name, age) values('bomo', 13)") session.execute("insert into User(name, age) values(:name, :age)", {'name': 'bomo', 'age':12}) # 如果是增刪改,需要commit session.commit() |
注意引數使用dict,並在sql語句中使用
:key
佔位
4. ORM
上面簡單介紹了sql的簡單用法,既然是ORM框架,我們先定義兩個模型類User
和Role
,sqlalchemy的模型類繼承自一個由declarative_base()
方法生成的類,我們先定義一個模組Models.py
生成Base類
1 2 3 |
from sqlalchemy.ext.declarative import declarative_base Base = declarative_base() |
User.py
1 2 3 4 5 6 7 8 |
from sqlalchemy import Column, Integer, String from Models import Base class User(Base): __tablename__ = 'User' id = Column('id', Integer, primary_key=True, autoincrement=True) name = Column('name', String(50)) age = Column('age', Integer) |
Role.py
1 2 3 4 5 6 7 |
from sqlalchemy import Column, Integer, String from Models import Base class Role(Base): __tablename__ = 'Role' id = Column('id', Integer, primary_key=True, autoincrement=True) name = Column('name', String(50)) |
從上面很容易看出來,這裡的模型對應資料庫中的表,模型支援的型別有Integer
, String
, Boolean
, Date
, DateTime
, Float
,更多型別包括型別對應的Python的型別參見:這裡
Column建構函式相關設定
- name:名稱
- type_:列型別
- autoincrement:自增
- default:預設值
- index:索引
- nullable:可空
- primary_key:外來鍵
更多介紹參見這裡
接下來通過session進行增刪改查
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 25 26 27 28 29 30 31 32 33 34 35 36 37 38 39 40 41 42 43 44 45 46 47 48 49 50 51 52 53 54 55 56 57 58 59 60 61 62 63 64 65 66 67 68 69 70 71 72 73 74 75 76 77 |
from sqlalchemy import create_engine from sqlalchemy.orm import sessionmaker from User import User from Role import Role from Models import Base DB_CONNECT_STRING = 'sqlite:////Users/zhengxiankai/Desktop/Document/db.sqlite' engine = create_engine(DB_CONNECT_STRING, echo=True) DB_Session = sessionmaker(bind=engine) session = DB_Session() # 1. 建立表(如果表已經存在,則不會建立) Base.metadata.create_all(engine) # 2. 插入資料 u = User(name = 'tobi', age = 200) r = Role(name = 'user') # 2.1 使用add,如果已經存在,會報錯 session.add(u) session.add(r) session.commit() print r.id # 3 修改資料 # 3.1 使用merge方法,如果存在則修改,如果不存在則插入 r.name = 'admin' session.merge(r) # 3.2 也可以通過這種方式修改 session.query(Role).filter(Role.id == 1).update({'name': 'admin'}) # 4. 刪除資料 session.query(Role).filter(Role.id == 1).delete() # 5. 查詢資料 # 5.1 返回結果集的第二項 user = session.query(User).get(2) # 5.2 返回結果集中的第2-3項 users = session.query(User)[1:3] # 5.3 查詢條件 user = session.query(User).filter(User.id < 6).first() # 5.4 排序 users = session.query(User).order_by(User.name) # 5.5 降序(需要匯入desc方法) from sqlalchemy import desc users = session.query(User).order_by(desc(User.name)) # 5.6 只查詢部分屬性 users = session.query(User.name).order_by(desc(User.name)) for user in users: print user.name # 5.7 給結果集的列取別名 users = session.query(User.name.label('user_name')).all() for user in users: print user.user_name # 5.8 去重查詢(需要匯入distinct方法) from sqlalchemy import distinct users = session.query(distinct(User.name).label('name')).all() # 5.9 統計查詢 user_count = session.query(User.name).order_by(User.name).count() age_avg = session.query(func.avg(User.age)).first() age_sum = session.query(func.sum(User.age)).first() # 5.10 分組查詢 users = session.query(func.count(User.name).label('count'), User.age).group_by(User.age) for user in users: print 'age:{0}, count:{1}'.format(user.age, user.count) session.close() |
5. 多表關係
上面的所有操作都是基於單個表的操作,下面是多表以及關係的使用,我們修改上面兩個表,新增外來鍵關聯(一對多和多對一)
User模型
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 |
from sqlalchemy import Column, Integer, String from sqlalchemy import ForeignKey from sqlalchemy.orm import relationship from Models import Base class User(Base): __tablename__ = 'users' id = Column('id', Integer, primary_key=True, autoincrement=True) name = Column('name', String(50)) age = Column('age', Integer) # 新增角色id外來鍵(關聯到Role表的id屬性) role_id = Column('role_id', Integer, ForeignKey('roles.id')) # 新增同表外來鍵 second_role_id = Column('second_role_id', Integer, ForeignKey('roles.id')) # 新增關係屬性,關聯到role_id外來鍵上 role = relationship('Role', foreign_keys='User.role_id', backref='User_role_id') # 新增關係屬性,關聯到second_role_id外來鍵上 second_role = relationship('Role', foreign_keys='User.second_role_id', backref='User_second_role_id') |
Role模型
1 2 3 4 5 6 7 8 9 10 11 12 13 |
from sqlalchemy import Column, Integer, String from sqlalchemy.orm import relationship from Models import Base class Role(Base): __tablename__ = 'roles' id = Column('id', Integer, primary_key=True, autoincrement=True) name = Column('name', String(50)) # 新增關係屬性,關聯到User.role_id屬性上 users = relationship("User", foreign_keys='User.role_id', backref="Role_users") # 新增關係屬性,關聯到User.second_role_id屬性上 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 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 |
u = User(name='tobi', age=200) r1 = Role(name='admin') r2 = Role(name='user') u.role = r1 u.second_role = r2 session.add(u) session.commit() # 查詢(對於外來鍵關聯的關係屬性可以直接訪問,在需要用到的時候session會到資料庫查詢) roles = session.query(Role).all() for role in roles: print 'role:{0} users' for user in role.users: print '\t{0}'.format(user.name) print 'role:{0} second_users' for user in role.second_users: print '\t{0}'.format(user.name) |
上面表示的是一對多(多對一)的關係,還有一對一,多對多,如果要表示一對一的關係,在定義relationship的時候設定uselist
為False(預設為True),如在Role中
1 2 3 |
class Role(Base): ... user = relationship("User", uselist=False, foreign_keys='User.role_id', backref="Role_user") |
6. 多表查詢
多表查詢通常使用join
進行表連線,第一個引數為表名,第二個引數為條件,例如
1 2 3 4 |
users = db.session.query(User).join(Role, Role.id == User.role_id) for u in users: print u.name |
join
為內連線,還有左連線outerjoin
,用法與join類似,右連線和全外連結在1.0
版本上不支援,通常來說有這兩個結合查詢的方法基本夠用了,1.1
版本貌似新增了右連線和全外連線的支援,但是目前只是預覽版
還可以直接查詢多個表,如下
1 2 3 4 |
result = db.session.query(User, Role).filter(User.role_id = Role.id) # 這裡選擇的是兩個表,使用元組獲取資料 for u, r in result: 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 |
$ alembic init YOUR_ALEMBIC_DIR |
alembic會在根目錄建立YOUR_ALEMBIC_DIR
目錄和alembic.ini
檔案,如下
1 2 3 4 5 6 7 8 9 10 |
yourproject/ alembic.ini YOUR_ALEMBIC_DIR/ env.py README script.py.mako versions/ 3512b954651e_add_account.py 2b1ae634e5cd_add_order_id.py 3adcc9a56557_rename_username_field.py |
其中
alembic.ini
提供了一些基本的配置env.py
每次執行Alembic都會載入這個模組,主要提供專案Sqlalchemy Model 的連線script.py.mako
遷移指令碼生成模版versions
存放生成的遷移指令碼目錄
預設情況下建立的是基於單個資料庫的,如果需要支援多個資料庫或其他,可以通過alembic list_templates
檢視支援的模板
1 2 3 4 5 6 7 8 9 10 |
$ alembic list_templates Available templates: generic - Generic single-database configuration. multidb - Rudimentary multi-database configuration. pylons - Configuration that reads from a Pylons project environment. Templates are used via the 'init' command, e.g.: 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 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 25 26 27 28 29 30 31 32 33 34 35 36 37 38 |
"""create table Revision ID: 4fd533a56b34 Revises: Create Date: 2016-09-18 17:20:27.667100 """ from alembic import op import sqlalchemy as sa # revision identifiers, used by Alembic. revision = '4fd533a56b34' down_revision = None branch_labels = None depends_on = None def upgrade(): # 新增表 op.create_table( 'account', sa.Column('id', sa.Integer, primary_key=True), sa.Column('name', sa.String(50), nullable=False), sa.Column('description', sa.Unicode(200)), ) # 新增列 # op.add_column('account', sa.Column('last_transaction_date', sa.DateTime)) def downgrade(): # 刪除表 op.drop_table('account') # 刪除列 # op.drop_column('account', 'last_transaction_date') |
這裡使用到了了op物件,關於op物件的更多API使用,參見這裡
5. 升級資料庫
剛剛實現了升級和降級的方法,通過下面命令升級資料庫到最新版本
1 |
$ alembic upgrade head |
這時候可以看到資料庫多了兩個表alembic_version
和account
,alembic_version
存放資料庫版本
關於升級和降級的其他命令還有下面這些
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 25 |
# 升到最高版本 $ alembic upgrade head # 降到最初版本 $ alembic downgrade base # 升兩級 $ alembic upgrade +2 # 降一級 $ alembic downgrade -1 # 升級到制定版本 $ alembic upgrade e93b8d488143 # 檢視當前版本 $ alembic current # 檢視歷史版本詳情 $ alembic history --verbose # 檢視歷史版本(-r引數)類似切片 $ alembic history -r1975ea:ae1027 $ alembic history -r-3:current $ alembic history -r1975ea: |
6. 通過後設資料升級資料庫
上面我們是通過API升級和降級,我們也可以直接通過後設資料更新資料庫,也就是自動生成升級程式碼,先定義兩個Model(User
, Role
),這裡我定義成三個檔案
1 2 3 4 5 6 |
yourproject/ YOUR_ALEMBIC_DIR/ tutorial/Db Models.py User.py Role.py |
程式碼就放在一起了
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 |
from sqlalchemy.ext.declarative import declarative_base from sqlalchemy import Column, Integer, String Base = declarative_base() class User(Base): __tablename__ = 'users' id = Column('id', Integer, primary_key=True, autoincrement=True) name = Column('name', String) class Role(Base): __tablename__ = 'roles' id = Column('id', Integer, primary_key=True, autoincrement=True) name = Column('name', String) |
在YOUR_ALEMBIC_DIR/env.py
配置後設資料
1 |
target_metadata = None |
改為
1 2 3 4 5 6 7 8 9 10 |
import os import sys # 這裡需要新增相對路徑到sys.path,否則會引用失敗,嘗試過使用相對路徑,但各種不好使,還是使用這種方法靠譜些 sys.path.append(os.path.abspath(os.path.join(os.getcwd(), "../yourproject/tutorial/Db"))) from User import User from Role import Role from Models import Base target_metadata = Base.metadata |
os.path.join(os.getcwd()
這個獲取到的地址不是env.py的路徑,而是根目錄
在建立資料庫版本的時候新增--autogenerate
引數,就會從Base.metadata後設資料中生成指令碼
1 |
$ alembic revision --autogenerate -m "add user table" |
這時候會在生成升級程式碼
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 25 26 27 28 29 30 31 32 33 34 35 36 37 38 39 40 41 42 43 44 45 |
"""add user table Revision ID: 97de1533584a Revises: 8678ab6d48c1 Create Date: 2016-09-19 21:58:00.758410 """ from alembic import op import sqlalchemy as sa # revision identifiers, used by Alembic. revision = '97de1533584a' down_revision = '8678ab6d48c1' branch_labels = None depends_on = None def upgrade(): ### commands auto generated by Alembic - please adjust! ### op.create_table('roles', sa.Column('id', sa.Integer(), nullable=False), sa.Column('name', sa.String(), nullable=True), sa.PrimaryKeyConstraint('id') ) op.create_table('users', sa.Column('id', sa.Integer(), nullable=False), sa.Column('name', sa.String(), nullable=True), sa.PrimaryKeyConstraint('id') ) op.drop_table('account') ### end Alembic commands ### def downgrade(): ### commands auto generated by Alembic - please adjust! ### op.create_table('account', sa.Column('id', sa.INTEGER(), nullable=False), sa.Column('name', sa.VARCHAR(length=50), nullable=False), sa.Column('description', sa.VARCHAR(length=200), nullable=True), sa.Column('last_transaction_date', sa.DATETIME(), nullable=True), sa.PrimaryKeyConstraint('id') ) op.drop_table('users') op.drop_table('roles') ### end Alembic commands ### |
由於我沒有定義account模型,會被識別為刪除,如果刪除了model的列的宣告,則會被識別為刪除列,自動生成的版本我們也可以自己修改,然後執行升級命令即可升級alembic upgrade head
需要注意的是
Base.metadata
宣告的類必須以資料庫中的一一對應,如果資料庫中有的表,而在後設資料中沒有,會識別成刪除表- revision建立版本之前執行之前需要升級到最新版本
- 配置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:如有其他問題歡迎留言