An Introduction to SQLAlchemy Core by Jason Myers

Transcript

1. SQLALCHEMY CORE AN INTRODUCTION / Jason Myers @jasonamyers Background by maul555
3. DIFFERENCES BETWEEN CORE AND ORM
4. ORM - DOMAIN MODEL c l a s s U s e r ( B a s e ) : _ _ t a b l e n a m e _ _ = ' u s e r s ' i d = C o l u m n ( I n t e g e r , p r i m a r y _ k e y = T r u e ) n a m e = C o l u m n ( S t r i n g ) f u l l n a m e = C o l u m n ( S t r i n g ) p a s s w o r d = C o l u m n ( S t r i n g )
5. CORE - SCHEMA-CENTRIC MODEL f r o m s q l a l c h e m y i m p o r t T a b l e , C o l u m n , I n t e g e r , S t r i n g , M e t a D a t a m e t a d a t a = M e t a D a t a ( ) u s e r s = T a b l e ( ' u s e r s ' , m e t a d a t a , C o l u m n ( ' i d ' , I n t e g e r , p r i m a r y _ k e y = T r u e ) , C o l u m n ( ' n a m e ' , S t r i n g ) , C o l u m n ( ' f u l l n a m e ' , S t r i n g ) , )
6. STRUCTURE Copyright © 2014 Mochimochi Land
7. STRUCTURE
9. INSTALLING pip install sqlalchemy pip install flask-sqlalchemy bin/paster create -t pyramid_alchemy tutorial
10. INITIALIZING i m p o r t s q l a l c h e m y f r o m s q l a l c h e m y i m p o r t c r e a t e _ e n g i n e e n g i n e = c r e a t e _ e n g i n e ( ' s q l i t e : / / / : m e m o r y : ' )
11. DEFINING A TABLE f r o m s q l a l c h e m y i m p o r t T a b l e , C o l u m n , I n t e g e r , S t r i n g , M e t a D a t a , F o r e i g n K e y m e t a d a t a = M e t a D a t a ( ) a c t o r s = T a b l e ( ' a c t o r s ' , m e t a d a t a , C o l u m n ( ' i d ' , I n t e g e r , p r i m a r y _ k e y = T r u e ) , C o l u m n ( ' n a m e ' , S t r i n g ) , C o l u m n ( ' f u l l n a m e ' , S t r i n g ) , C o l u m n ( ' b o d y _ c o u n t ' , I n t e g e r ) ) r o l e s = T a b l e ( ' r o l e s ' , m e t a d a t a , C o l u m n ( ' i d ' , I n t e g e r , p r i m a r y _ k e y = T r u e ) , C o l u m n ( ' a c t o r _ i d ' , N o n e , F o r e i g n K e y ( ' a c t o r s . i d ' ) ) , C o l u m n ( ' c h a r a c t e r _ n a m e ' , S t r i n g , n u l l a b l e = F a l s e ) )
12. CREATE THE TABLES m e t a d a t a . c r e a t e _ a l l ( e n g i n e )
13. TABLE OBJECTS a c t o r s . c o l u m n s . i t e m s ( ) [ ( ' i d ' , C o l u m n ( ' i d ' , I n t e g e r ( ) , t a b l e = a c t o r s , p r i m a r y _ k e y = T r u e . . . ) ) , ( ' n a m e ' , C o l u m n ( ' n a m e ' , S t r i n g ( ) , t a b l e = a c t o r s ) ) , ( ' f u l l n a m e ' , C o l u m n ( ' f u l l n a m e ' , S t r i n g ( ) , t a b l e = a c t o r s ) ) , ( ' b o d y _ c o u n t ' , C o l u m n ( ' b o d y _ c o u n t ' , I n t e g e r ( ) , t a b l e = a c t o r s ) ) ]
14. OPENING A CONNECTION c o n n = e n g i n e . c o n n e c t ( )
15. SINGLE INSERT i n s = a c t o r s . i n s e r t ( ) . v a l u e s ( n a m e = ' G r a h a m ' , f u l l n a m e = ' G r a h a m C h a p m a n ' , b o d y _ c o u n t = 3 ) r e s u l t = c o n n . e x e c u t e ( i n s ) r e s u l t . i n s e r t e d _ p r i m a r y _ k e y [ 1 ]
16. LOOKING AT WHAT WAS EXECUTED p r i n t s t r ( i n s ) i n s . c o m p i l e ( ) . p a r a m s I N S E R T I N T O a c t o r s ( n a m e , f u l l n a m e , b o d y _ c o u n t ) V A L U E S ( : n a m e , : f u l l n a m e , : b o d y _ c o u n t ) { ' b o d y _ c o u n t ' : 3 , ' f u l l n a m e ' : ' G r a h a m C h a p m a n ' , ' n a m e ' : ' G r a h a m ' }
17. MULTIPLE INSERT r e s u l t s = c o n n . e x e c u t e ( r o l e s . i n s e r t ( ) , [ { ' a c t o r _ i d ' : 1 , ' c h a r a c t e r _ n a m e ' : ' K i n g A r t h u r ' } , { ' a c t o r _ i d ' : 1 , ' c h a r a c t e r _ n a m e ' : ' V o i c e o f G o d ' } , { ' a c t o r _ i d ' : 2 , ' c h a r a c t e r _ n a m e ' : ' S i r L a n c e l o t ' } , { ' a c t o r _ i d ' : 2 , ' c h a r a c t e r _ n a m e ' : ' B l a c k K n i g h t ' } , { ' a c t o r _ i d ' : 3 , ' c h a r a c t e r _ n a m e ' : ' P a t s y ' } , { ' a c t o r _ i d ' : 3 , ' c h a r a c t e r _ n a m e ' : ' S i r B o r s ' } , ] ) r e s u l t s . r o w c o u n t 6
18. UPDATE s t m t = a c t o r s . u p d a t e ( ) . w h e r e ( a c t o r s . c . n a m e = = ' G r a h a m ' ) . v a l u e s ( n a m e = ' G r a m ' ) r e s u l t = c o n n . e x e c u t e ( s t m t ) r e s u l t . r o w c o u n t 1
19. DELETE r e s u l t = c o n n . e x e c u t e ( a c t o r s . d e l e t e ( ) . w h e r e ( a c t o r s . c . n a m e = = ' T e r r y ' ) ) r e s u l t . r o w c o u n t 1
20. SELECTING s = s e l e c t ( [ a c t o r s . c . n a m e , a c t o r s . c . f u l l n a m e ] ) r e s u l t = c o n n . e x e c u t e ( s ) f o r r o w i n r e s u l t : p r i n t r o w ( u ' G r a h a m ' , u ' G r a h a m C h a p m a n ' ) ( u ' J o h n ' , u ' J o h n C l e e s e ' ) ( u ' T e r r y ' , u ' T e r r y G i l l i a m ' )
21. ORDERING s t m t = s e l e c t ( [ a c t o r s . c . n a m e ] ) . o r d e r _ b y ( a c t o r s . c . n a m e . d e s c ( ) ) c o n n . e x e c u t e ( s t m t ) . f e t c h a l l ( ) [ ( u ' T e r r y ' , ) , ( u ' J o h n ' , ) , ( u ' G r a h a m ' , ) ]
22. LIMITING s t m t = s e l e c t ( [ a c t o r s . c . n a m e , a c t o r s . c . f u l l n a m e ] ) . l i m i t ( 1 ) . o f f s e t ( 1 ) c o n n . e x e c u t e ( s t m t ) . f i r s t ( ) ( u ' J o h n ' , u ' J o h n C l e e s e ' )
23. COUNT f r o m s q l a l c h e m y . s q l i m p o r t f u n c s t m t = s e l e c t ( [ f u n c . c o u n t ( a c t o r s ) ] ) c o n n . e x e c u t e ( s t m t ) . s c a l a r ( ) 2
24. SUM s t m t = s e l e c t ( [ f u n c . c o u n t ( a c t o r s ) , f u n c . s u m ( a c t o r s . c . b o d y _ c o u n t ) ] ) c o n n . e x e c u t e ( s t m t ) . f i r s t ( ) ( 2 , 5 )
25. JOINS s = s e l e c t ( [ a c t o r s , r o l e s ] ) . w h e r e ( a c t o r s . c . i d = = r o l e s . c . a c t o r _ i d ) f o r r o w i n c o n n . e x e c u t e ( s ) : p r i n t r o w ( 1 , u ' G r a h a m ' , u ' G r a h a m C h a p m a n ' , 1 , 1 , u ' K i n g A r t h u r ' ) ( 1 , u ' G r a h a m ' , u ' G r a h a m C h a p m a n ' , 2 , 1 , u ' V o i c e o f G o d ' ) ( 2 , u ' J o h n ' , u ' J o h n C l e e s e ' , 3 , 2 , u ' S i r L a n c e l o t ' ) ( 2 , u ' J o h n ' , u ' J o h n C l e e s e ' , 4 , 2 , u ' B l a c k K n i g h t ' ) ( 3 , u ' T e r r y ' , u ' T e r r y G i l l i a m ' , 5 , 3 , u ' P a t s y ' ) ( 3 , u ' T e r r y ' , u ' T e r r y G i l l i a m ' , 6 , 3 , u ' S i r B o r s ' )
26. GROUPING s t m t = s e l e c t ( [ a c t o r s . c . n a m e , f u n c . c o u n t ( r o l e s . c . i d ) ] ) . \ s e l e c t _ f r o m ( a c t o r s . j o i n ( r o l e s ) ) . \ g r o u p _ b y ( a c t o r s . c . n a m e ) c o n n . e x e c u t e ( s t m t ) . f e t c h a l l ( ) [ ( u ' G r a h a m ' , 2 ) , ( u ' J o h n ' , 2 ) , ( u ' T e r r y ' , 2 ) ]
27. FILTERING f r o m s q l a l c h e m y . s q l i m p o r t a n d _ , o r _ , n o t _ s t m t = s e l e c t ( [ a c t o r s . c . n a m e , r o l e s . c . c h a r a c t e r _ n a m e ] ) . \ w h e r e ( a n d _ ( a c t o r s . c . n a m e . l i k e ( ' G r a % ' ) , r o l e s . c . c h a r a c t e r _ n a m e . l i k e ( ' V o % ' ) , a c t o r s . c . i d = = r o l e s . c . a c t o r _ i d ) ) c o n n . e x e c u t e ( s t m t ) . f e t c h a l l ( ) [ ( u ' G r a h a m ' , u ' V o i c e o f G o d ' ) ]
28. AND SO ON...
30. COMMON DIALECTS Informix MS SQL Oracle Postgres SQLite Custom
31. BUT WHAT IF... c l a s s U n l o a d F r o m S e l e c t ( E x e c u t a b l e , C l a u s e E l e m e n t ) : d e f _ _ i n i t _ _ ( s e l f , s e l e c t , b u c k e t , a c c e s s _ k e y , s e c r e t _ k e y ) : s e l f . s e l e c t = s e l e c t s e l f . b u c k e t = b u c k e t s e l f . a c c e s s _ k e y = a c c e s s _ k e y s e l f . s e c r e t _ k e y = s e c r e t _ k e y @ c o m p i l e s ( U n l o a d F r o m S e l e c t ) d e f v i s i t _ u n l o a d _ f r o m _ s e l e c t ( e l e m e n t , c o m p i l e r , * * k w ) : r e t u r n " u n l o a d ( ' % ( q u e r y ) s ' ) t o ' % ( b u c k e t ) s ' c r e d e n t i a l s ' a w s _ a c c e s s _ k e y _ i d = % ( a c c e s s _ k e y ) s ; a w s _ s e c r e t _ a c c e s s _ k e y = % ( s e c r e t _ k e y ) s ' d e l i m i t e r ' , ' a d d q u o t e s a l l o w o v e r w r i t e " % { ' q u e r y ' : c o m p i l e r . p r o c e s s ( e l e m e n t . s e l e c t , u n l o a d _ s e l e c t = T r u e , l i t e r a l _ b i n d s = T r u e ) , ' b u c k e t ' : e l e m e n t . b u c k e t , ' a c c e s s _ k e y ' : e l e m e n t . a c c e s s _ k e y , ' s e c r e t _ k e y ' : e l e m e n t . s e c r e t _ k e y , }
32. EXAMPLE STATEMENT u n l o a d = U n l o a d F r o m S e l e c t ( s e l e c t ( [ f i e l d s ] ) , ' / ' . j o i n ( [ ' s 3 : / ' , B U C K E T , f i l e n a m e ] ) , A C C E S S _ K E Y , S E C R E T _ K E Y )
33. EXAMPLE USAGE u n l o a d ( ' s e l e c t * f r o m v e n u e w h e r e v e n u e i d i n ( s e l e c t v e n u e i d f r o m v e n u e o r d e r b y v e n u e i d d e s c l i m i t 1 0 ) ' ) t o ' s 3 : / / m y b u c k e t / v e n u e _ p i p e _ ' c r e d e n t i a l s ' a w s _ a c c e s s _ k e y _ i d = A C C E S S _ K E Y ; a w s _ s e c r e t _ a c c e s s _ k e y = S E C R E T _ K E Y ' ;
34. DYNAMIC TABLE INTROSPECTION d e f b u i l d _ t a b l e ( e n g i n e , t a b l e _ n a m e ) : r e t u r n T a b l e ( t a b l e _ n a m e , m e t a d a t a , a u t o l o a d = T r u e , a u t o l o a d _ w i t h = e n g i n e )
35. CHECKING FOR NULL COLUMNS b u i l d _ t a b l e ( e n g i n e , ' c e n s u s ' ) u n a v a i l a b l e _ f i e l d s = [ c . n a m e f o r c i n t . c i f i s i n s t a n c e ( c . t y p e , N u l l T y p e ) ]
36. CHAINING s = s e l e c t ( [ t . c . r a c e , t . c . f a c t o r , f u n c . s u m ( g . t . c . v a l u e ) . l a b e l ( ' s u m m e d ' ) ] , t . c . r a c e > 0 ) . w h e r e ( a n d _ ( t . c . t y p e = = ' P O V E R T Y ' , t . c . v a l u e ! = 0 ) ) . g r o u p _ b y ( t . c . r a c e , t . c . f a c t o r ) . o r d e r _ b y ( t . c . r a c e , t . c . f a c t o r )
37. CONDITIONALS s = s e l e c t ( [ t a b l e . c . d i s c h a r g e _ y e a r , f u n c . c o u n t ( 1 ) . l a b e l ( ' p a t i e n t _ d i s c h a r g e s ' ) , t a b l e . c . z i p _ c o d e , ] , t a b l e . c . d i s c h a r g e _ y e a r . i n _ ( y e a r s ) ) . g r o u p _ b y ( t a b l e . c . d i s c h a r g e _ y e a r ) s = s . w h e r e ( t a b l e . c . h o s p i t a l _ n a m e = = p r o v i d e r ) i f ' t o t a l _ c h a r g e s ' n o t i n u n a v a i l a b l e _ f i e l d s : s = s . c o l u m n ( f u n c . s u m ( t a b l e . c . t o t a l _ c h a r g e s ) . l a b e l ( ' p a t i e n t _ c h a r g e s ' ) )
38. s = s . g r o u p _ b y ( t a b l e . c . z i p _ c o d e ) s = s . o r d e r _ b y ( ' d i s c h a r g e s D E S C ' ) c a s e s = c o n n . e x e c u t e ( s ) . f e t c h a l l ( )
39. QUESTIONS THANK YOU / Jason Myers @jasonamyers