深入SQLAlchemy之SQLAlchemy ORM重构表-白红宇

深入SQLAlchemy之SQLAlchemy ORM重构表

阅读量：7010 次

发布时间：2019-06-28

本文共 4713 字，大约阅读时间需要 15 分钟。

简单介绍:

说明: 此模块主要用于将关系型表映射到PY的类,行映射到PY类的实例,列映射为PY实例的属性,由于其兼容众多及扩展,SO可以优先考虑数据模型,而忽略底层的DB-API切换,数据迁移更方便.

快速安装:

1	`pip` `install` `--upgrade SQLAlchemy`

定义结构:

#!/usr/bin/env python

# -*- coding: utf-8 -*-

"""

#

# Authors: limanman

# OsChina: http://xmdevops.blog.51cto.com/

# Purpose:

#

"""

# 说明: 导入公共模块

from datetime import datetime

from sqlalchemy.ext.declarative import declarative_base

from sqlalchemy import (create_engine, MetaData, Table, Column, Integer, String, DateTime, Numeric,

ForeignKey, PrimaryKeyConstraint, ForeignKeyConstraint, UniqueConstraint)

# 说明: 导入其它模块

from constants import USERNAME, PASSWORD, HOSTNAME, HOSTPORT, DATABASE, CHARSSET, ALLDBURI

Base = 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 datetime

from sqlalchemy.orm import relationship, backref

from sqlalchemy.ext.declarative import declarative_base

from sqlalchemy import (create_engine, Column, Integer, String, DateTime, Numeric, ForeignKey)

# 说明: 导入其它模块

from constants import USERNAME, PASSWORD, HOSTNAME, HOSTPORT, DATABASE, CHARSSET, ALLDBURI

Base = 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

登录乐搏学院官网

或关注我们的官方微博，还有更多惊喜哦~