Created
August 15, 2016 09:56
-
-
Save mydreambei-ai/5d32a4c8242dade94a83858f07c84405 to your computer and use it in GitHub Desktop.
sqlalchemy subqueryload, eagerload, joinedload, contains_eager, lazyload differences
This file contains hidden or bidirectional Unicode text that may be interpreted or compiled differently than what appears below. To review, open the file in an editor that reveals hidden Unicode characters.
Learn more about bidirectional Unicode characters
| import re | |
| import time | |
| from sqlalchemy import Column, Integer, TIMESTAMP, func, String, ForeignKey | |
| from sqlalchemy import create_engine | |
| from sqlalchemy import event | |
| from sqlalchemy.ext.declarative import as_declarative, declared_attr, AbstractConcreteBase | |
| from sqlalchemy.orm import scoped_session, sessionmaker, relationship | |
| engine = create_engine('sqlite://', convert_unicode=True, echo=False) | |
| Session = sessionmaker(autocommit=False, | |
| autoflush=False, | |
| bind=engine) | |
| session = scoped_session(Session) | |
| @event.listens_for(session.bind, "before_cursor_execute") | |
| def before_cursor_execute(conn, cursor, statement, | |
| parameters, context, executemany): | |
| conn.info.setdefault('query_start_time', []).append(time.time()) | |
| print("Start Query: %s" % statement) | |
| @event.listens_for(session.bind, "after_cursor_execute") | |
| def after_cursor_execute(conn, cursor, statement, | |
| parameters, context, executemany): | |
| total = time.time() - conn.info['query_start_time'].pop(-1) | |
| print("Query Complete!") | |
| print("Total Time: %f" % total) | |
| @as_declarative() | |
| class Base(object): | |
| @declared_attr | |
| def __tablename__(cls): | |
| cls_name = cls.__name__.lower() | |
| if cls_name.endswith("event"): | |
| return '{}_events'.format(cls_name[:len(cls_name) - 5]) | |
| else: | |
| return "_".join(map(lambda x: x.lower(), re.findall('[A-Z][^A-Z]*', cls.__name__))) + 's' | |
| id = Column(Integer, primary_key=True) | |
| created_date = Column(TIMESTAMP, nullable=False, server_default=func.current_timestamp()) | |
| updated_date = Column(TIMESTAMP, nullable=False, server_default=func.current_timestamp(), | |
| onupdate=func.current_timestamp()) | |
| class _Event(AbstractConcreteBase): | |
| @declared_attr | |
| def hackinfo_id(self): | |
| return Column(ForeignKey("hackinfos.id", ondelete='CASCADE')) | |
| @declared_attr | |
| def hackinfo(self): | |
| return relationship("HackInfo") | |
| class HackInfo(Base): | |
| __tablename__ = 'hackinfos' | |
| url = Column(String(255), nullable=False) | |
| class KeywordEvent(Base, _Event): | |
| keyword = Column(String(20), nullable=False) | |
| if __name__ == '__main__': | |
| from sqlalchemy.orm import subqueryload, eagerload, joinedload, contains_eager, lazyload | |
| Base.metadata.create_all(bind=engine) | |
| for i in range(1000): | |
| session.add(KeywordEvent(hackinfo=HackInfo(url="xxxxxx"), keyword="fsf")) | |
| session.commit() | |
| # session.query(KeywordEvent).options(joinedload(KeywordEvent.hackinfo)).all() | |
| ''' | |
| SELECT keyword_events.id AS keyword_events_id, keyword_events.created_date AS keyword_events_created_date, keyword_events.updated_date AS keyword_events_updated_date, keyword_events.keyword AS keyword_events_keyword, keyword_events.hackinfo_id AS keyword_events_hackinfo_id, hackinfos_1.id AS hackinfos_1_id, hackinfos_1.created_date AS hackinfos_1_created_date, hackinfos_1.updated_date AS hackinfos_1_updated_date, hackinfos_1.url AS hackinfos_1_url | |
| FROM keyword_events LEFT OUTER JOIN hackinfos AS hackinfos_1 ON hackinfos_1.id = keyword_events.hackinfo_id | |
| Total Time: 0.000127 | |
| ''' | |
| # session.query(KeywordEvent).options(eagerload(KeywordEvent.hackinfo)).all() | |
| ''' | |
| SELECT keyword_events.id AS keyword_events_id, keyword_events.created_date AS keyword_events_created_date, keyword_events.updated_date AS keyword_events_updated_date, keyword_events.keyword AS keyword_events_keyword, keyword_events.hackinfo_id AS keyword_events_hackinfo_id, hackinfos_1.id AS hackinfos_1_id, hackinfos_1.created_date AS hackinfos_1_created_date, hackinfos_1.updated_date AS hackinfos_1_updated_date, hackinfos_1.url AS hackinfos_1_url | |
| FROM keyword_events LEFT OUTER JOIN hackinfos AS hackinfos_1 ON hackinfos_1.id = keyword_events.hackinfo_id | |
| Total Time: 0.000927 | |
| ''' | |
| # session.query(KeywordEvent).options(subqueryload(KeywordEvent.hackinfo)).all() | |
| ''' | |
| SELECT keyword_events.id AS keyword_events_id, keyword_events.created_date AS keyword_events_created_date, keyword_events.updated_date AS keyword_events_updated_date, keyword_events.keyword AS keyword_events_keyword, keyword_events.hackinfo_id AS keyword_events_hackinfo_id | |
| FROM keyword_events | |
| Query Complete! | |
| Total Time: 0.000113 | |
| Start Query: SELECT hackinfos.id AS hackinfos_id, hackinfos.created_date AS hackinfos_created_date, hackinfos.updated_date AS hackinfos_updated_date, hackinfos.url AS hackinfos_url, anon_1.keyword_events_hackinfo_id AS anon_1_keyword_events_hackinfo_id | |
| FROM (SELECT DISTINCT keyword_events.hackinfo_id AS keyword_events_hackinfo_id | |
| FROM keyword_events) AS anon_1 JOIN hackinfos ON hackinfos.id = anon_1.keyword_events_hackinfo_id ORDER BY anon_1.keyword_events_hackinfo_id | |
| Query Complete! | |
| Total Time: 0.002664 | |
| ''' | |
| # session.query(KeywordEvent).options(contains_eager(KeywordEvent.hackinfo)).all() | |
| ''' | |
| SELECT hackinfos.id AS hackinfos_id, hackinfos.created_date AS hackinfos_created_date, hackinfos.updated_date AS hackinfos_updated_date, hackinfos.url AS hackinfos_url, keyword_events.id AS keyword_events_id, keyword_events.created_date AS keyword_events_created_date, keyword_events.updated_date AS keyword_events_updated_date, keyword_events.keyword AS keyword_events_keyword, keyword_events.hackinfo_id AS keyword_events_hackinfo_id | |
| FROM hackinfos, keyword_events | |
| Query Complete! | |
| Total Time: 0.000726 | |
| ''' | |
| session.query(KeywordEvent).join(HackInfo).all() | |
| ''' | |
| SELECT keyword_events.id AS keyword_events_id, keyword_events.created_date AS keyword_events_created_date, keyword_events.updated_date AS keyword_events_updated_date, keyword_events.keyword AS keyword_events_keyword, keyword_events.hackinfo_id AS keyword_events_hackinfo_id | |
| FROM keyword_events JOIN hackinfos ON hackinfos.id = keyword_events.hackinfo_id | |
| Query Complete! | |
| Total Time: 0.000521 | |
| ''' | |
Sign up for free
to join this conversation on GitHub.
Already have an account?
Sign in to comment