简单介绍:
说明: 此模块主要用于将关系型数据库表映射到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 datetimefrom sqlalchemy import create_engine, MetaData, Table, Column, Integer, String, DateTime, Numeric# 说明: 导入其它模块from constants import USERNAME, PASSWORD, HOSTNAME, HOSTPORT, DATABASE, CHARSSET, ALLDBURIif __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 datetimefrom 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, ALLDBURIif __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的前两个参数都必须是一个序列,且第一个序列内元素必须是已存在列名,如果要创建基于表级的约束(同时对多列创建约束),默认第二个参数内部元素表名必须是本表名,否则如上单独创建和列级别的约束没啥区别
本文出自 “满满李 - 运维开发之路” 博客，请务必保留此出处http://xmdevops.blog.51cto.com/11144840/1869167