理解sqlalchemy与ORM

SQLAlchemy是Python编程语言下的一款ORM框架，该框架建立在数据库API之上，使用关系对象映射进行数据库操作，简言之便是：将对象转换成SQL，然后使用数据API执行SQL并获取执行结果。

使用 ORM/Schema Type/SQL Expression Language/Engine/ConnectionPooling/Dialect 所有组件对数据进行操作。根据类创建对象，对象转换成SQL，执行SQL。

__author__ = 'lizheng'
# !/usr/bin/env python
#-*- coding:utf-8 -*-


from sqlalchemy import create_engine
from sqlalchemy.ext.declarative import declarative_base
from sqlalchemy import Column, Integer, String, ForeignKey, and_, or_ , func
from sqlalchemy.orm import sessionmaker, relationship

Base = declarative_base()   # 生成一个sqlORM 基类

engine = create_engine('mysql+pymysql://root:root@localhost:3306/test?charset=utf8', echo=False)  # echo显示执行过程

class Host(Base):
    __tablename__ = 'hosts'
    id = Column(Integer, primary_key=True, autoincrement=True)
    hostname = Column(String(64), unique=True, nullable=False)
    ip_addr = Column(String(128), unique=True, nullable=False)
    port = Column(Integer, default=22)
    group_id = Column(Integer, ForeignKey('group.id'))
#backref为Group关联Host时用的对象名
    group = relationship('Group', backref='host_list')

class Group(Base):
    __tablename__ = 'group'
    id = Column(Integer, primary_key=True)
    name = Column(String(64), unique=True, nullable=False)

Base.metadata.create_all(engine)    # 创建所有表结构
if __name__ == '__main__':
    # 创建与数据库的会话session class，注意：这里返回给session的是个class，不是实例
    SessionCls = sessionmaker(bind=engine)
    session = SessionCls()      # 创建实例
    h = session.query(Host).filter(Host.hostname=='server').first()   # first 结果为class属性
    print('host group name -->', h.group.name)
    g = session.query(Group).filter(Group.name=='g2').first()
#g.host_list为Host对象
    for i in g.host_list:
        print('group 2 hostname -->', g.host_list.hostname)
    # h1 = Host(hostname='localhost', ip_addr='127.0.0.1')
    # h2 = Host(hostname='ubuntu', ip_addr='192.168.1.1', port=8888)
    # h3 = Host(hostname='server2', ip_addr='192.168.2.22', port=1234,group_id = 2)
    # session.add_all([h3,])
    # session.commit()
    # g1 = Group(name='g1')
    # g2 = Group(name='g2')
    # g3 = Group(name='g3')
    # session.add_all([g1,g2,g3])
    # session.commit()
    # res = session.query(Host).filter(Host.hostname=='server').all()   # all 结果为列表
    # res = session.query(Host).filter(Host.hostname=='server').first()   # first 结果为class属性
    # print('host group name -->', res.group.name)
    # print('res type-->', type(res))
    # g = session.query(Group).filter(Group.name=='g2').first()
    # print('group 2 hostname -->', g.host_list)
    # print('group name to hostname -->', g.host_list.hostname)
    # print('group-->', g)
    # obj = session.query(Host, func.count()).group_by(Host.group_id).all()
    # print(obj)
    # session.commit()