理解sqlalchemy與ORM

標籤：

SQLAlchemy是Python程式設計語言下的一款ORM架構，該架構建立在資料庫API之上，使用關聯性物件映射進行資料庫操作，簡言之便是：將對象轉換成SQL，然後使用資料API執行SQL並擷取執行結果。

使用 ORM/Schema Type/SQL Expression Language/Engine/ConnectionPooling/Dialect 所有組件對資料進行操作。根據類建立對象，對象轉換成SQL，執行SQL。

 1 __author__ = ‘lizheng‘ 2 # !/usr/bin/env python 3 #-*- coding:utf-8 -*- 4  5  6 from sqlalchemy import create_engine 7 from sqlalchemy.ext.declarative import declarative_base 8 from sqlalchemy import Column, Integer, String, ForeignKey, and_, or_ , func 9 from sqlalchemy.orm import sessionmaker, relationship10 11 Base = declarative_base()   # 產生一個sqlORM 基類12 13 engine = create_engine(‘mysql+pymysql://root:[email protected]:3306/test?charset=utf8‘, echo=False)  # echo顯示執行過程14 15 class Host(Base):16     __tablename__ = ‘hosts‘17     id = Column(Integer, primary_key=True, autoincrement=True)18     hostname = Column(String(64), unique=True, nullable=False)19     ip_addr = Column(String(128), unique=True, nullable=False)20     port = Column(Integer, default=22)21     group_id = Column(Integer, ForeignKey(‘group.id‘))22 #backref為Group關聯Host時用的對象名23     group = relationship(‘Group‘, backref=‘host_list‘)24 25 class Group(Base):26     __tablename__ = ‘group‘27     id = Column(Integer, primary_key=True)28     name = Column(String(64), unique=True, nullable=False)29 30 Base.metadata.create_all(engine)    # 建立所有表結構31 if __name__ == ‘__main__‘:32     # 建立與資料庫的會話session class，注意：這裡返回給session的是個class，不是執行個體33     SessionCls = sessionmaker(bind=engine)34     session = SessionCls()      # 建立執行個體35     h = session.query(Host).filter(Host.hostname==‘server‘).first()   # first 結果為class屬性36     print(‘host group name -->‘, h.group.name)37     g = session.query(Group).filter(Group.name==‘g2‘).first()38 #g.host_list為Host對象39     for i in g.host_list:40         print(‘group 2 hostname -->‘, g.host_list.hostname)41     # h1 = Host(hostname=‘localhost‘, ip_addr=‘127.0.0.1‘)42     # h2 = Host(hostname=‘ubuntu‘, ip_addr=‘192.168.1.1‘, port=8888)43     # h3 = Host(hostname=‘server2‘, ip_addr=‘192.168.2.22‘, port=1234,group_id = 2)44     # session.add_all([h3,])45     # session.commit()46     # g1 = Group(name=‘g1‘)47     # g2 = Group(name=‘g2‘)48     # g3 = Group(name=‘g3‘)49     # session.add_all([g1,g2,g3])50     # session.commit()51     # res = session.query(Host).filter(Host.hostname==‘server‘).all()   # all 結果為列表52     # res = session.query(Host).filter(Host.hostname==‘server‘).first()   # first 結果為class屬性53     # print(‘host group name -->‘, res.group.name)54     # print(‘res type-->‘, type(res))55     # g = session.query(Group).filter(Group.name==‘g2‘).first()56     # print(‘group 2 hostname -->‘, g.host_list)57     # print(‘group name to hostname -->‘, g.host_list.hostname)58     # print(‘group-->‘, g)59     # obj = session.query(Host, func.count()).group_by(Host.group_id).all()60     # print(obj)61     # session.commit()

View Code

 

理解sqlalchemy與ORM