Building the App

In this talk we'll illustrate a simple web application using SQLAlchemy, showing off the style of development that SQLAlchemy is most geared towards, the so-called "foundational" style which provides for development and maintainance of custom conventions which then service the needs of the model declarations and use case implementations.

Transcript

1. Mike Bayer Pycon 2014 W Wr ri it ti in ng g t th he e A Ap pp p Mike Bayer mike_mp@zzzcomputing.com 1 of 42
2. W Wh ha at t''s s t th hi is s t ta al lk k a ab bo ou ut t? ? Writing larger, longer lived applications on top of SQLAlchemy The code that we write when we first plug in our frameworks and libraries - the "foundation" Having a healthy amount of this "up front" code is a good thing, rather than a failure of libraries/ frameworks to do everything possible SQLAlchemy is designed with "foundations" in mind 2 of 42
3. D De ef fi in ni in ng g " "F Fo ou un nd da at ti io on n" " Technologies to use, libraries, frameworks Directory structures, file naming schemes Architectural patterns Build / Configuration / Test approaches The decisions we make here will determine: what new developers need to grok the specific steps to go from “Use case” into “Executed code” 3 of 42
4. T Th he e F Fo ou un nd da at ti io on n I Is s. .. .. . Source files at the base of our application The basis for patterns and idioms that are repeated throughout use-case code Possibly reusable in other apps The adaptation of the frameworks and libraries we’ve chosen to the task at hand 4 of 42
5. T Th he e F Fo ou un nd da at ti io on n I Is s N No ot t. .. .. . A replacement for existing, well-documented and solid frameworks or libraries The start of “our company’s new app platform!” A reinvention of the wheel …unless we’re talking about some very mediocre wheels 5 of 42
6. L Le et t''s s S Se ee e a a F Fo ou un nd da at ti io on n! ! Web framework: Pyramid! Things I like about Pyramid: view / renderer model - separates response data from templates opinionated in the right places great emphasis on testability huge attention to detail, mega-mature components curmudgeonly like me! Use whatever framework! 6 of 42
7. S Sa am mp pl le e A Ap pp pl li ic ca at ti io on n https://bitbucket.org/zzzeek/pycon2014_atmcraft/ 7 of 42
8. O Ov ve er rv vi ie ew w the "meta" folder conventions / recipes session scoping constraint naming migrations testing 8 of 42
9. T Th he e M Me et ta a F Fo ol ld de er r atmcraft/ model/ meta/ __init__.py base.py orm.py schema.py types.py 9 of 42
10. T Th he e M Me et ta a F Fo ol ld de er r __init__.py - provides names as from atmcraft.model.meta import <name> base.py - fundamental units like declarative_base, Session orm.py - object-relational mapping helpers schema.py - database schema generation/definition helpers types.py - types and SQL constructs specific to our application 10 of 42
11. C Co on nv ve en nt ti io on ns s / / R Re ec ci ip pe es s - - O Ov ve er rv vi ie ew w Goal is to reduce as much repetition as possible without taking it too far Key strategies: plain Python functions, mixins, events, @compiles, custom types SQLAlchemy is continuously trying to add more and more capability and predictability Some conventions/recipes eventually become SQLAlchemy features 11 of 42
12. W Wh hy y N No ot t P Pa ar rt t o of f S SQ QL LA Al lc ch he em my y? ? Naming - once it needs names, SQLAlchemy usually wants you to do it Some conventions d do o become features, or at least partial features Composable components, rather than pre-packaged defaults and patterns, provides more long-term value and use case potential "Give someone a fish and feed them for a day..." ... "give someone an @event.listens_for('fish_available') hook and..." ... you get it 12 of 42
13. C Co on nv ve en nt ti io on ns s - - M Mi ix xi in ns s a an nd d F Fu un nc ct ti io on ns s Add an integer primary key to classes, refer via foreign key: c cl la as ss s S Su ur rr ro og ga at te eP PK K(object): id = Column(Integer, primary_key=True) d de ef f ReferenceCol(tablename, nullable=False, **kw): r re et tu ur rn n Column(ForeignKey("%s.id" % tablename), nullable=nullable, **kw) 13 of 42
14. C Co on nv ve en nt ti io on ns s - - M Mi ix xi in ns s a an nd d F Fu un nc ct ti io on ns s Usage: c cl la as ss s A Ac cc co ou un nt tB Ba al la an nc ce e(SurrogatePK, Base): __tablename__ = 'account_balance' balance_type_id = ReferenceCol('balance_type') balance_type = relationship("BalanceType") 14 of 42
15. C Co on nv ve en nt ti io on ns s - - M Mi ix xi in ns s a an nd d F Fu un nc ct ti io on ns s Slicker version that's in the example app: c cl la as ss s A Ac cc co ou un nt tB Ba al la an nc ce e(SurrogatePK, Base): __tablename__ = 'account_balance' balance_type = many_to_one("BalanceType") 15 of 42
16. C Co on nv ve en nt ti io on ns s - - U Us si in ng g @ @c co om mp pi il le es s Define SQL idioms that vary across backends: f fr ro om m s sq ql la al lc ch he em my y. .e ex xt t. .c co om mp pi il le er r i im mp po or rt t compiles f fr ro om m s sq ql la al lc ch he em my y. .s sq ql l i im mp po or rt t functions f fr ro om m s sq ql la al lc ch he em my y. .t ty yp pe es s i im mp po or rt t DateTime c cl la as ss s u ut tc cn no ow w(functions.FunctionElement): key = 'utcnow' type = DateTime(timezone=True) @ @c co om mp pi il le es s(utcnow, 'postgresql') d de ef f _pg_utcnow(element, compiler, **kw): r re et tu ur rn n "(CURRENT_TIMESTAMP AT TIME ZONE 'utc')"\ "::TIMESTAMP WITH TIME ZONE" @ @c co om mp pi il le es s(utcnow, 'mssql') d de ef f _ms_utcnow(element, compiler, **kw): r re et tu ur rn n "GETUTCDATE()" 16 of 42
17. C Co on nv ve en nt ti io on ns s - - U Us si in ng g @ @c co om mp pi il le es s Use cases: # in SQL statements result = connection.execute(select([utcnow()])) # in table defs Table('orders', metadata, Column('created_at', DateTime(timezone=True), default=utcnow()) ) 17 of 42
18. R Re ec ci ip pe es s - - U Us si in ng g e ev ve en nt ts s Adds auto-generating "created_at" / "updated_at" columns to all tables @ @e ev ve en nt t. .l li is st te en ns s_ _f fo or r(Table, "after_parent_attach") d de ef f timestamp_cols(table, metadata): i if f metadata i is s Base.metadata: table.append_column( Column('created_at', DateTime(timezone=True), nullable=False, default=utcnow()) ) table.append_column( Column('updated_at', DateTime(timezone=True), nullable=False, default=utcnow(), onupdate=utcnow()) ) 18 of 42
19. R Re ec ci ip pe es s - - C Cu us st to om m T Ty yp pe es s Persist a password transparently using bcrypt: c cl la as ss s P Pa as ss sw wo or rd d(str): d de ef f __eq__(self, other): i if f n no ot t isinstance(other, Password): other = bcrypt.hashpw(self, self) r re et tu ur rn n str.__eq__(self, other) d de ef f __ne__(self, other): r re et tu ur rn n n no ot t self.__eq__(other) c cl la as ss s B Bc cr ry yp pt tT Ty yp pe e(TypeDecorator): impl = String(128) d de ef f process_bind_param(self, value, dialect): r re et tu ur rn n bcrypt.hashpw(value, bcrypt.gensalt(4)) d de ef f process_result_value(self, value, dialect): r re et tu ur rn n Password(value) 19 of 42
20. R Re ec ci ip pe es s - - C Cu us st to om m T Ty yp pe es s Use Case: # runs "ImASecret" through bcrypt before INSERT session.add(Client(identifier='snowflake', secret='ImASecret')) session.commit() c1 = session.query(Client).\ filter_by(identifier == 'snowflake').first() # runs "ImASecret" through bcrypt w/ correct salt # before comparison operation r re et tu ur rn n c1.secret == 'ImASecret' 20 of 42
21. S Se es ss si io on n S Sc co op pi in ng g The SQLAlchemy ORM defines database access through a central object known as the S Se es ss si io on n. When writing a web app, we need to set up two things: Where is the session during a request? 1. How does it get there and how does it leave? 2. 21 of 42
22. S Se es ss si io on n S Sc co op pi in ng g - - W Wh he er re e i is s i it t? ? Method One - scoped to the current thread f fr ro om m s sq ql la al lc ch he em my y. .o or rm m i im mp po or rt t sessionmaker, scoped_session Session = scoped_session(sessionmaker()) # usage Session.query(Foo).all() 22 of 42
23. S Se es ss si io on n S Sc co op pi in ng g - - W Wh he er re e i is s i it t? ? Method Two - scoped to the current request f fr ro om m s sq ql la al lc ch he em my y. .o or rm m i im mp po or rt t sessionmaker, scoped_session f fr ro om m p py yr ra am mi id d. .t th hr re ea ad dl lo oc ca al l i im mp po or rt t get_current_request Session = scoped_session(sessionmaker(), scopefunc=get_current_request) # usage Session.query(Foo).all() 23 of 42
24. S Se es ss si io on n S Sc co op pi in ng g - - W Wh he er re e i is s i it t? ? Method Three - attached to the current request f fr ro om m s sq ql la al lc ch he em my y. .o or rm m i im mp po or rt t sessionmaker f fr ro om m p py yr ra am mi id d. .e ev ve en nt ts s i im mp po or rt t NewRequest, subscriber Session = sessionmaker() @ @s su ub bs sc cr ri ib be er r(NewRequest) d de ef f new_session(event): event.request.db_session = Session() # usage request.db_session.query(Foo).all() 24 of 42
25. S Se es ss si io on n S Sc co op pi in ng g - - H Ho ow w d do oe es s i it t l le ea av ve e? ? Use the web framework's system for ending a request f fr ro om m p py yr ra am mi id d. .e ev ve en nt ts s i im mp po or rt t NewRequest, subscriber @ @s su ub bs sc cr ri ib be er r(NewRequest) d de ef f cleanup_session(event): d de ef f remove(request): Session.remove() event.request.add_finished_callback(remove) 25 of 42
26. C Co on ns st tr ra ai in nt t N Na am mi in ng g Database constraints like FOREIGN KEY, UNIQUE, INDEX, etc. all have names, so they can be targeted for CREATE / DROP Controlling these names is a good idea, though tedious SQLAlchemy generally provides no default names We can make names automatically using schema events The new naming_convention feature provides this for us 26 of 42
27. C Co on ns st tr ra ai in nt t N Na am mi in ng g S Se et tt ti in ng g New in SQLAlchemy 0.9.2 - 0.9.4 f fr ro om m s sq ql la al lc ch he em my y. .e ex xt t. .d de ec cl la ar ra at ti iv ve e i im mp po or rt t declarative_base Base = declarative_base() Base.metadata = MetaData(naming_convention={ "pk": "pk_%(table_name)s", "fk": "fk_%(table_name)s_%(column_0_name)s_%(referred_table_name)s", "uq": "uq_%(table_name)s_%(column_0_name)s", "ix": "ix_%(table_name)s_%(column_0_name)s", "ck": "ck_%(table_name)s_%(constraint_name)s" }) # ... later ... Base.metadata.create_all(some_engine) 27 of 42
28. C Co on ns st tr ra ai in nt t N Na am mi in ng g SQL Output: C CR RE EA AT TE E T TA AB BL LE E account_balance ( id SERIAL N NO OT T N NU UL LL L, balance NUMERIC(8, 2), last_trans_id UUID, created_at T TI IM ME ES ST TA AM MP P W WI IT TH H TIME Z ZO ON NE E N NO OT T N NU UL LL L, updated_at T TI IM ME ES ST TA AM MP P W WI IT TH H TIME Z ZO ON NE E N NO OT T N NU UL LL L, account_id INTEGER, balance_type_id INTEGER, C CO ON NS ST TR RA AI IN NT T pk_account_balance P PR RI IM MA AR RY Y K KE EY Y (id), C CO ON NS ST TR RA AI IN NT T fk_account_balance_account_id_account F FO OR RE EI IG GN N K KE EY Y(account_id) R RE EF FE ER RE EN NC CE ES S account (id), C CO ON NS ST TR RA AI IN NT T fk_account_balance_balance_type_id_balance_type F FO OR RE EI IG GN N K KE EY Y(balance_type_id) R RE EF FE ER RE EN NC CE ES S balance_type (id) ); 28 of 42
29. G Ge en ne er ra at ti in ng g a an n A Al le em mb bi ic c M Mi ig gr ra at ti io on n Generating a migration: classic$ .venv/bin/alembic -c development.ini \ revision -m "initial rev" --autogenerate [alembic.migration] Context impl PostgresqlImpl. [alembic.migration] Will assume transactional DDL. [alembic.autogenerate.compare] Detected added table 'balance_type' [alembic.autogenerate.compare] Detected added table 'client' [alembic.autogenerate.compare] Detected added table 'account' [alembic.autogenerate.compare] Detected added table 'auth_session' [alembic.autogenerate.compare] Detected added table 'account_balance' [alembic.autogenerate.compare] Detected added table 'transaction' Generating ./migrations/versions/52caef8c4043_initial_rev.py ... done 29 of 42
30. C Co on ns st tr ra ai in nt t N Na am mi in ng g i in n M Mi ig gr ra at ti io on ns s Autogenerate will place our naming conventions into the file: revision = '52caef8c4043' down_revision = None d de ef f upgrade(): op.create_table('account_balance', sa.Column('id', sa.Integer(), nullable=False), sa.Column('balance', sa.Numeric(precision=8, scale=2), nullable=True), # ... sa.ForeignKeyConstraint(['account_id'], [u'account.id'], name=op.f('fk_account_balance_account_id_account')), sa.ForeignKeyConstraint(['balance_type_id'], [u'balance_type.id'], name=op.f('fk_account_balance_balance_type_id_balance_type')), sa.PrimaryKeyConstraint('id', name=op.f('pk_account_balance')) ) # ... d de ef f downgrade(): op.drop_table('account_balance') # ... 30 of 42
31. U Up pc co om mi in ng g A Al le em mb bi ic c F Fe ea at tu ur re es s Multiple version directories Automatic branch resolution Versions as a directed graph, rather than linear 31 of 42
32. T Te es st ti in ng g For testing database-enabled models, two approaches are: Transactional Mock 32 of 42
33. T Tr ra an ns sa ac ct ti io on na al l T Te es st ti in ng g Simplified structure c cl la as ss s S So om me eT Te es st t(TestCase): d de ef f setUp(self): self.connection = engine.connect() self.trans = connection.begin() self.session = Session(bind=self.connection) d de ef f _fixture(self): self.session.add(Foo()) d de ef f test_something(self): self._fixture() foo = self.session.query(Foo).first() a as ss se er rt t foo d de ef f tearDown(self): self.session.close() self.trans.rollback() self.connection.close() 33 of 42
34. T Tr ra an ns sa ac ct ti io on na al l T Te es st ti in ng g In practice f fr ro om m . .. .m mo od de el l i im mp po or rt t Client, AuthSession f fr ro om m . .. .v vi ie ew ws s i im mp po or rt t auth_on_token f fr ro om m p py yr ra am mi id d i im mp po or rt t testing d de ef f _auth_fixture(self, created_at=None): auth_session = AuthSession(Client(identifier="ident"), created_at=created_at) Session.add(auth_session) r re et tu ur rn n auth_session d de ef f test_auth_timeout(self): auth_session = self._auth_fixture( created_at=datetime.datetime.utcnow() - datetime.timedelta(seconds=800)) request = testing.DummyRequest() request.params={"auth_token": auth_session.token} self.assertRaises(exc.HTTPForbidden, auth_on_token(l la am mb bd da a req: "hi"), request) 34 of 42
35. T Tr ra an ns sa ac ct ti io on na al l T Te es st ti in ng g Advantages: Easy Well suited towards integration tests Disadvantages: Slow Tests are too generalized Need a database 35 of 42
36. M Mo oc ck k T Te es st ti in ng g Using MockSession in atmcraft/tests/_mock_session.py > >> >> > f fr ro om m a at tm mc cr ra af ft t. .t te es st ts s. ._ _m mo oc ck k_ _s se es ss si io on n i im mp po or rt t MockSession > >> >> > session = MockSession() > >> >> > session.query(Foo).filter_by(id=5).first.return_value = 5 > >> >> > session.query(Foo).filter(Foo.id == 7).first.return_value = "blah" > >> >> > session.query(Foo).filter_by(id=12).one.side_effect = NoResultFound > >> >> > session.query(Foo).filter_by(id=5).first() 5 > >> >> > session.query(Foo).filter(Foo.id == 7).first() "blah" > >> >> > session.query(Foo).filter_by(id=12).one() Traceback ... sqlalchemy.orm.exc.NoResultFound 36 of 42
37. M Mo oc ck k T Te es st ti in ng g Simplified structure f fr ro om m . ._ _m mo oc ck k_ _s se es ss si io on n i im mp po or rt t MockSession c cl la as ss s S So om me eT Te es st t(TestCase): d de ef f setUp(self): self.session = MockSession() d de ef f _fixture(self): self.session.query(Foo).first = Foo() d de ef f test_something(self): self._fixture() foo = self.session.query(Foo).first() a as ss se er rt t foo 37 of 42
38. M Mo oc ck k T Te es st ti in ng g In practice (part 1 of 2) - the fixture is much more complicated... f fr ro om m . .. .m mo od de el l i im mp po or rt t Client, AuthSession f fr ro om m . .. .v vi ie ew ws s i im mp po or rt t auth_on_token f fr ro om m p py yr ra am mi id d i im mp po or rt t testing d de ef f _auth_fixture(self, created_at): auth_session = AuthSession(Client(identifier="ident"), created_at=created_at) validate_session_q = Session.query(AuthSession).\ filter_by(token=auth_session.token).\ filter(AuthSession.created_at > utcnow() - datetime.timedelta(seconds=360)) i if f created_at i is s n no ot t None a an nd d datetime.datetime.utcnow() - \ created_at > datetime.timedelta(seconds=360): validate_session_q.one.side_effect = NoResultFound e el ls se e: validate_session_q.one.return_value = auth_session r re et tu ur rn n auth_session 38 of 42
39. M Mo oc ck k T Te es st ti in ng g In practice (part 2 of 2) - ... but the test itself is exactly the same d de ef f test_auth_timeout(self): auth_session = self._auth_fixture( created_at=datetime.datetime.utcnow() - datetime.timedelta(seconds=800)) request = testing.DummyRequest() request.params={"auth_token": auth_session.token} self.assertRaises(exc.HTTPForbidden, auth_on_token(l la am mb bd da a req: "hi"), request) 39 of 42
40. M Mo oc ck k T Te es st ti in ng g Advantages: No database needed Tests can be focused to small units Encourages code to be modular Fast Disadvantages fixtures can be very tedious to create won't catch ORM configurational/SQL/query issues can turn into "mirrors" of tested code 40 of 42
41. A An n e ex xa am mp pl le e a ap pp p! ! run the pyramid app run the "client" 41 of 42
42. T Th ha an nk ks s! !! ! 42 of 42