简单介绍:
说明: 此模块主要用于将关系型数据库表映射到PY的类,行映射到PY类的实例,列映射为PY实例的属性,由于其兼容众多DB-API及扩展,SO可以优先考虑数据模型,而忽略底层的DB-API切换,数据迁移更方便.
快速安装:
pip install --upgrade SQLAlchemy
定义结构:
#!/usr/bin/env python# -*- coding: utf-8 -*-"""## Authors: limanman# OsChina: http://xmdevops.blog.51cto.com/# Purpose:#"""# 说明: 导入公共模块from datetime import datetimefrom sqlalchemy.ext.declarative import declarative_basefrom sqlalchemy import (create_engine, MetaData, Table, Column, Integer, String, DateTime, Numeric, ForeignKey, PrimaryKeyConstraint, ForeignKeyConstraint, UniqueConstraint)# 说明: 导入其它模块from constants import USERNAME, PASSWORD, HOSTNAME, HOSTPORT, DATABASE, CHARSSET, ALLDBURIBase = declarative_base()class Cookies(Base): __tablename__ = 'cookies' id = Column(Integer(), primary_key=True, autoincrement=True) cookie_name = Column(String(50), index=True) cookie_recipe_url = Column(String(255)) cookie_sku = Column(String(55)) quantity = Column(Integer()) unit_cost = Column(Numeric(12, 2))
说明: SQLAlchemy ORM与SQLAlchemy Core有些许不同,用户只用关注上层数据对象定义,之前我们还需要定义metadata容器对象让表对象引用它,而在ORM中只需要定义的表类继承自declarative_base实例基类则会自动关联表对象和metadata对象,而无需关注底层的实现.
注意: 要定义一个ORM的表类,需要满足至少四个条件,必须继承自declarative_base实例,必须包含一个__tablename__类属性,值为对应数据库中的表名,必须至少包含一个或是更多的列属性,必须至少有一个或更多列作为主键
索引约束:
class Cookies(Base): __tablename__ = 'cookies' __table_args__ = (PrimaryKeyConstraint('id')) id = Column(Integer(), autoincrement=True) cookie_name = Column(String(50), index=True) cookie_recipe_url = Column(String(255)) cookie_sku = Column(String(55)) quantity = Column(Integer()) unit_cost = Column(Numeric(12, 2))
说明: SQLAlchemy Core中可直接在定义Table时定义表级约束/索引,在SQLAlchemy ORM中也可以很方便实现,可通过设置表类的__table_args__ 类属性来设置多个索引和约束,值可为序列对象,PrimaryKeyConstraint, ForeignKeyConstraint, UniqueConstraint的用法和SQLAlchemy Core是一样的~
关系映射:
说明: 如上图,一个用户可以产生多个订单,所以在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.orm import relationship, backreffrom sqlalchemy.ext.declarative import declarative_basefrom sqlalchemy import (create_engine, Column, Integer, String, DateTime, Numeric, ForeignKey)# 说明: 导入其它模块from constants import USERNAME, PASSWORD, HOSTNAME, HOSTPORT, DATABASE, CHARSSET, ALLDBURIBase = declarative_base()class Users(Base): __tablename__ = 'users' id = Column(Integer(), primary_key=True, autoincrement=True) customer_number = Column(Integer(), autoincrement=True) username = Column(String(15), unique=True, nullable=False) email_address = Column(String(255), nullable=False) phone = Column(String(20), nullable=False) password = Column(String(25), nullable=False) created_on = Column(DateTime(), default=datetime.now) updated_on = Column(DateTime(), default=datetime.now, onupdate=datetime.now)class Orders(Base): __tablename__ = 'orders' id = Column(Integer(), primary_key=True, autoincrement=True) user_id = Column(Integer(), ForeignKey('Users.id'), nullable=False) user = relationship('Users', backref=backref('orders', order_by=id))class LineItems(Base): __tablename__ = 'line_items' id = Column(Integer(), primary_key=True, autoincrement=True) quantity = Column(Integer()) extended_cost = Column(Numeric(12, 2)) order_id = Column(Integer(), ForeignKey('Orders.id'), nullable=False) cookie_id = Column(Integer(), ForeignKey('Cookies.id'), nullable=False) order = relationship('Orders', backref=backref('line_items', order_by=id)) cookies = relationship('Cookies', backref=backref('line_item', uselist=False))class Cookies(Base): __tablename__ = 'cookies' id = Column(Integer(), primary_key=True, autoincrement=True) cookie_name = Column(String(50), index=True) cookie_recipe_url = Column(String(255)) cookie_sku = Column(String(55)) quantity = Column(Integer()) unit_cost = Column(Numeric(12, 2))if __name__ == '__main__': # 第一步: 创建引擎对象 engine = create_engine(ALLDBURI, echo=True, pool_recycle=3600) # 第二步: 创建元数据对象 Base.metadata.create_all(bind=engine)
说明: 如上简单演示了SQLAlchemy ORM常见建表过程,和SQLAlchemy Core不同,它强制通过relationship和backref来为关联表类增加互相指向的类属性,relationship的第一个参数为要关联的表类名称,常用的还有backref,通过backref创建,第一个参数为支持另一个表反向访问的属性名,剩余的relationship和backref的参数一样的,如上的uselist=False表示反向引用时是one2one模式,更多参数可以参考源码实例Lib/site-packages/sqlalchemy/orm/relationships.py
本文出自 “满满李 - 运维开发之路” 博客，请务必保留此出处http://xmdevops.blog.51cto.com/11144840/1870170