简单介绍:
说明: 此模块主要用于将关系型数据库表映射到PY的类,行映射到PY类的实例,列映射为PY实例的属性,由于其兼容众多DB-API及扩展,SO可以优先考虑数据模型,而忽略底层的DB-API切换,数据迁移更方便.
快速安装:
pip install --upgrade SQLAlchemy
创建引擎:
1. 连接数据库前需创建引擎,作为执行SQL的接口,其实底层通过Pool(连接池)和Dialect(翻译器)将映射后的PY语句转换为对应DB-API支持的原生SQL语句去执行,这样写PY时就不用关注后端的数据库
2. 只有在连接被依赖而被动调用时才会建立Pool,否则创建引擎默认只会创建Dialect翻译器,简单的说Engine与后端Database创建的连接总是惰性被动创建的
# 连接字符串
#!/usr/bin/env python
# -*- coding: utf-8 -*-
"""
#
# Authors: limanman
# OsChina: http://xmdevops.blog.51cto.com/
# Purpose:
#
"""
# 说明: 导入公共模块
from sqlalchemy import create_engine
# 说明: 导入其它模块
if __name__ == '__main__':
"""
sqlite:///data.db : 相对当前目录查找
sqlite:var/run/data.db : 绝对/var/run下查找
sqlite:///C:\\Users\\Administrator\data.db : 绝对C:\\Users\\Administrator下查找
"""
engine = create_engine('sqlite:///data.db', echo=True)
# 注意: 为了避免乱码charset=utf8强制设置
engine = create_engine('mysql://username:password@hostname:hostport/'
'database?charset=utf8', echo=True, pool_recycle=3600)
说明: 如上以两个常用的数据库为例,创建引擎时参数echo=True则表示打印处理过程,默认MySQL连接闲置超过8小时会会自动关闭,pool_recycle=3600缩短时间到1小时,连接池会自动清理闲置连接
追本溯源:
说明: 转到sqlalchemy包__init__.py文件,可以看到默认导入了虽有核心支持的.sql/.types/.schema,所以我们完全可以在代码中直接通过from sqlalchemy import ...,...,...导入,而且转到对应的定义处有非常详细的使用场景以及使用案例演示.
.sql | alias,all_,and_,any_,asc,between,bindparam,case,cast,collate,column,delete,desc,distinct,except_,except_all,exists,extract,false,func,funcfilter,insert,intersect,intersect_all,join,lateral,literal,literal_column,modifier,not_,null,or_,outerjoin,outparam,over,select,subquery,table,tablesample,text,true,tuple_,type_coerce,union,union_all,update,within_group, |
.types | ARRAY,BIGINT,BINARY,BLOB,BOOLEAN,BigInteger,Binary,Boolean,CHAR,CLOB,DATE,DATETIME,DECIMAL,Date,DateTime,Enum,FLOAT,Float,INT,INTEGER,Integer,Interval,JSON,LargeBinary,NCHAR,NVARCHAR,NUMERIC,Numeric,PickleType,REAL,SMALLINT,SmallInteger,String,TEXT,TIME,TIMESTAMP,Text,Time,TypeDecorator,Unicode,UnicodeText,VARBINARY,VARCHAR, |
.schema | CheckConstraint,Column,ColumnDefault,Constraint,DefaultClause,FetchedValue,ForeignKey,ForeignKeyConstraint,Index,MetaData,PassiveDefault,PrimaryKeyConstraint,Sequence,Table,ThreadLocalMetaData,UniqueConstraint,DDL,BLANK_SCHEMA |
# 元数据存储
说明: 元数据主要用于存储描述表相关数据各种属性的数据,以便提供快速访问数据库结构,所以在定义表结构之前首先需要初始化一个metadata对象
#!/usr/bin/env python
# -*- coding: utf-8 -*-
"""
#
# Authors: limanman
# OsChina: http://xmdevops.blog.51cto.com/
# Purpose:
#
"""
# 说明: 导入公共模块
from sqlalchemy import MetaData
# 说明: 导入其它模块
if __name__ == '__main__':
metadata = MetaData()
# 定义表对象
说明: 通过调用.schema下的Table实例化一个表对象,第一个参数将作为数据库中对应的表名,第二个参数作为记录表相关属性的metadata对象,其它参数作为表结构中的列对象,列对象也是调用.schema下的Column实例化一个表对象,第一个参数将作为表中对应的列名,第二个参数作为列类型,其它参数作为特殊列标志
#!/usr/bin/env python
# -*- coding: utf-8 -*-
"""
#
# Authors: limanman
# OsChina: http://xmdevops.blog.51cto.com/
# Purpose:
#
"""
# 说明: 导入公共模块
from datetime import datetime
from sqlalchemy import create_engine, MetaData, Table, Column, Integer, String, DateTime, Numeric
# 说明: 导入其它模块
from constants import USERNAME, PASSWORD, HOSTNAME, HOSTPORT, DATABASE, CHARSSET, ALLDBURI
if __name__ == '__main__':
# 第一步: 创建引擎对象
engine = create_engine(ALLDBURI, echo=True, pool_recycle=3600)
# 第二步: 创建元数据对象
metadata = MetaData()
# 第三步: 创建一个表对象
users = Table(
'users', metadata,
Column('id', Integer(), primary_key=True, autoincrement=True),
Column('name', String(32), nullable=True, unique=False, default='anonymous'),
Column('mail', String(64), nullable=False, unique=True, index=True),
Column('salary', Numeric(12, 2), nullable=True, unique=False),
Column('update', DateTime(), default=datetime.now, onupdate=datetime.now, index=True)
)
# 第四步: 利用引擎创建表
metadata.create_all(engine)
说明: 如上是常规的建表过程,针对于不同的Column列类型,可以添加不同的扩展参数,primary_key=True表示为是否为主键,autoincrement=True表示主键ID是否自动增长,nullable=True表示是否为空,unique=False表示是否唯一,index=True表示是否建立索引加快查询速度,default=datetime.now表示默认值,onupdate=datetime.now表示任何一个列值更新都会触发重置此列
注意: 如上是常规的建表过程,Column初始化通过primary_key=True/unique=True等创建列级别的约束,只能约束单列,并不会因为你对两个列设置了相同约束而同时被约束,每列约束只会约束自身的值
# 索引键约束
说明: 键和约束来确保我们的数据在存储到数据库之前满足一定的要求,常用的有Index索引/PrimaryKeyConstraint主键约束/UniqueConstraint唯一约束/ForeignKeyConstraint外键约束
说明: 如上图,一个用户可以产生多个订单,所以在orders表中创建外键user_id关联users表,而每个订单又可以包含多个商品,且可以包含多个相同的商品最终还得有一个最终价格,所以在line_items中创建外键order_id关联orders,又因为每个商品有具体详情所以在line_items中创建外键cookie_id关联cookies
#!/usr/bin/env python
# -*- coding: utf-8 -*-
"""
#
# Authors: limanman
# OsChina: http://xmdevops.blog.51cto.com/
# Purpose:
#
"""
# 说明: 导入公共模块
from datetime import datetime
from sqlalchemy import (create_engine, MetaData, Table, Column, Integer, String, DateTime, Numeric, column,
Index, PrimaryKeyConstraint, UniqueConstraint, ForeignKeyConstraint)
# 说明: 导入其它模块
from constants import USERNAME, PASSWORD, HOSTNAME, HOSTPORT, DATABASE, CHARSSET, ALLDBURI
if __name__ == '__main__':
# 第一步: 创建引擎对象
engine = create_engine(ALLDBURI, echo=True, pool_recycle=3600)
# 第二步: 创建元数据对象
metadata = MetaData()
# 第三步: 创建一个表对象
users = Table(
'users', metadata,
Column('id', Integer(), autoincrement=True),
Column('customer_number', Integer(), autoincrement=True),
Column('username', String(15), nullable=False),
Column('email_address', String(255), nullable=False),
Column('phone', String(20), nullable=False),
Column('password', String(25), nullable=False),
Column('created_on', DateTime(), default=datetime.now),
Column('updated_on', DateTime(), default=datetime.now, onupdate=datetime.now),
PrimaryKeyConstraint('id'),
UniqueConstraint('username'),
)
orders = Table(
'orders', metadata,
Column('id', Integer(), autoincrement=True),
Column('user_id', Integer(), nullable=False),
PrimaryKeyConstraint('id'),
ForeignKeyConstraint(
['user_id'],
['users.id'],
)
)
line_items = Table(
'line_items', metadata,
Column('id', Integer(), autoincrement=True),
Column('quantity', Integer()),
Column('extended_cost', Numeric(12, 2)),
Column('order_id', Integer(), nullable=False),
Column('cookie_id', Integer(), nullable=False),
PrimaryKeyConstraint('id'),
ForeignKeyConstraint(
['order_id'],
['orders.id'],
),
ForeignKeyConstraint(
['cookie_id'],
['cookies.id'],
),
)
cookies = Table(
'cookies', metadata,
Column('id', Integer(), autoincrement=True),
Column('cookie_name', String(50)),
Column('cookie_recipe_url', String(255)),
Column('cookie_sku', String(55)),
Column('quantity', Integer()),
Column('unit_cost', Numeric(12, 2)),
Index(None, 'cookie_name'),
PrimaryKeyConstraint('id'),
)
# 第四步: 利用引擎创建表
metadata.create_all(engine)
说明: 如上是常规的建表过程, 索引键以及约束不仅可以在建表时通过对应的Column类初始化参数来添加,也可以通过对应的Table类初始化参数来添加,还可以在创建表后通过表实例的append_constraint()方法来动态添加,非常灵活
扩展: Index的第一个参数是普通索引名,而PrimaryKeyConstraint/UniqueConstraint/ForeignKeyConstraint的索引名是通过name来指定的,创建的约束都是基于表级别的,都支持同时多个约束
注意: ForeignKeyConstraint的前两个参数都必须是一个序列,且第一个序列内元素必须是已存在列名,如果要创建基于表级的约束(同时对多列创建约束),默认第二个参数内部元素表名必须是本表名,否则如上单独创建和列级别的约束没啥区别
转载于:https://blog.51cto.com/xmdevops/1869167