} } }

    OpenStack Nova 之应用SQLAlchemy 操纵 Mysql 示例

    添加时间:2013-5-17 点击量:

    SQLAlchemy 简介 【链接】


    The SQLAlchemy Object Relational Mapper presents a method of associating user-defined Python classes with database tables, and instances of those classes (objects) with rows in their corresponding tables. It includes a system that transparently synchronizes all changes in state between objects and their related rows, called a unit of work, as well as a system for expressing database queries in terms of the user defined classes and their defined relationships between each other.


    我的懂得是,SQLAlchemy 是实体/关系映射的一种操纵数据的体式格式, 实体是由Python所编写的类, 这个类的每一个实体,就对应于数据库中的每一条元组(行数据)。此中对这个类的实体的操纵,直接映射(影响)到数据库中对应的元组, 这种体式格式叫做 工作单位(操纵单位,有点类似于一个原子操纵把)


    本身写的查询Flavor 的样例



     1  sqlalchemy.orm import sessionmaker
    
    2 sqlalchemy import create_engine
    3 sqlalchemy.ext.declarative import declarative_base
    4 sqlalchemy import Column, Integer, String, Float, Boolean
    5
    6 sql_connection = mysql://root:root@localhost/nova?charset=utf8
    7 engine = create_engine(sql_connection, echo=True) #echo=True 设置该项后,可以打印出sql的履行过程
    8 Session = sessionmaker(bind=engine)
    9 session = Session()
    10
    11 class NovaBase():
    12 pass
    13
    14 BASE = declarative_base() # 用户创建与DB映射的基类
    15
    16 class InstanceTypes(BASE, NovaBase):
    17 __tablename__ = instance_types # 数据库中表的名称
    18 id = Column(Integer, primary_key=True) #Column 用于定义数据表 instance_types”的字段, 其参数是该字段的具体的类型
    19 name = Column(String(255))
    20 memory_mb = Column(Integer)
    21 vcpus = Column(Integer)
    22 root_gb = Column(Integer)
    23 ephemeral_gb = Column(Integer)
    24 flavorid = Column(String(255))
    25 swap = Column(Integer, nullable=False, default=0)
    26 rxtx_factor = Column(Float, nullable=False, default=1
    27 vcpu_weight = Column(Integer, nullable=True)
    28 disabled = Column(Boolean, default=False)
    29 is_public = Column(Boolean, default=True)
    30
    31 flavors = session.query(InstanceTypes).all() #查询数据表
    32 for flavor in flavors:
    33 print flavor.id, flavor.name


    代码输出



    2013-05-15 18:41:10,928 INFO sqlalchemy.engine.base.Engine SELECT DATABASE()
    
    2013-05-15 18:41:10,929 INFO sqlalchemy.engine.base.Engine ()
    2013-05-15 18:41:10,933 INFO sqlalchemy.engine.base.Engine SHOW VARIABLES LIKE character_set%%
    2013-05-15 18:41:10,934 INFO sqlalchemy.engine.base.Engine ()
    2013-05-15 18:41:10,935 INFO sqlalchemy.engine.base.Engine SHOW VARIABLES LIKE lower_case_table_names
    2013-05-15 18:41:10,936 INFO sqlalchemy.engine.base.Engine ()
    2013-05-15 18:41:10,937 INFO sqlalchemy.engine.base.Engine SHOW COLLATION
    2013-05-15 18:41:10,937 INFO sqlalchemy.engine.base.Engine ()
    2013-05-15 18:41:10,946 INFO sqlalchemy.engine.base.Engine SHOW VARIABLES LIKE sql_mode
    2013-05-15 18:41:10,946 INFO sqlalchemy.engine.base.Engine ()
    2013-05-15 18:41:10,948 INFO sqlalchemy.engine.base.Engine BEGIN (implicit)
    2013-05-15 18:41:10,950 INFO sqlalchemy.engine.base.Engine SELECT instance_types.id AS instance_types_id, instance_types.name AS instance_types_name, instance_types.memory_mb AS instance_types_memory_mb, instance_types.vcpus AS instance_types_vcpus, instance_types.root_gb AS instance_types_root_gb, instance_types.ephemeral_gb AS instance_types_ephemeral_gb, instance_types.flavorid AS instance_types_flavorid, instance_types.swap AS instance_types_swap, instance_types.rxtx_factor AS instance_types_rxtx_factor, instance_types.vcpu_weight AS instance_types_vcpu_weight, instance_types.disabled AS instance_types_disabled, instance_types.is_public AS instance_types_is_public
    FROM instance_types
    2013-05-15 18:41:10,950 INFO sqlalchemy.engine.base.Engine ()

    1 m1.medium
    2 m1.tiny
    3 m1.large
    4 m1.xlarge
    5 m1.small
    6 m1.nano
    7 m1.micro


      


    Nova 中所对应的代码布局




      1. nova/openstack/common/db/sqlalchemy/models.py 定义了SQLAlchemy 的基类, 所有的对数据的操纵,经由过程该类中定义的办法来实现, 其子类,只须要调用相干的办法即可

      2. nova/db/sqlalchemy/models.py 定义了nova数据中的所有的表



    以InstanceTypes 为例




    BASE = declarative_base() # 与上一个类子中的办法一样
    

    class NovaBase(models.SoftDeleteMixin, models.ModelBase): #差别在这里, models 为1中定义的模块
    pass

    class InstanceTypes(BASE, NovaBase):
    
    __tablename__ = instance_types # 数据库中表的名称
    id = Column(Integer, primary_key=True) #Column 用于定义数据表 instance_types”的字段, 其参数是该字段的具体的类型
    name = Column(String(255))
    memory_mb = Column(Integer)
    vcpus = Column(Integer)
    root_gb = Column(Integer)
    ephemeral_gb = Column(Integer)
    flavorid = Column(String(255))
    swap = Column(Integer, nullable=False, default=0)
    rxtx_factor = Column(Float, nullable=False, default=1
    vcpu_weight = Column(Integer, nullable=True)
    disabled = Column(Boolean, default=False)
    is_public = Column(Boolean, default=True)


      





    容易发怒的意思就是: 别人做了蠢事, 然后我们代替他们, 表现出笨蛋的样子。—— 蔡康永
    分享到: