An Introduction to SQLAlchemy Core by Jason Myers

Slide 1

Slide 1 text

SQLALCHEMY CORE AN INTRODUCTION / Jason Myers @jasonamyers Background by maul555

Slide 2

Slide 2 text

No content

Slide 3

Slide 3 text

DIFFERENCES BETWEEN CORE AND ORM

Slide 4

Slide 4 text

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 )

Slide 5

Slide 5 text

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 ) , )

Slide 6

Slide 6 text

Slide 7

Slide 7 text

STRUCTURE

Slide 8

Slide 8 text

No content

Slide 9

Slide 9 text

INSTALLING pip install sqlalchemy pip install flask-sqlalchemy bin/paster create -t pyramid_alchemy tutorial

Slide 10

Slide 10 text

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 : ' )

Slide 11

Slide 11 text

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 ) )

Slide 12

Slide 12 text

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 )

Slide 13

Slide 13 text

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 ) ) ]

Slide 14

Slide 14 text

OPENING A CONNECTION c o n n = e n g i n e . c o n n e c t ( )

Slide 15

Slide 15 text

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 ]

Slide 16

Slide 16 text

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 ' }

Slide 17

Slide 17 text

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

Slide 18

Slide 18 text

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

Slide 19

Slide 19 text

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

Slide 20

Slide 20 text

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 ' )

Slide 21

Slide 21 text

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 ' , ) ]

Slide 22

Slide 22 text

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 ' )

Slide 23

Slide 23 text

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

Slide 24

Slide 24 text

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 )

Slide 25

Slide 25 text

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 ' )

Slide 26

Slide 26 text

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 ) ]

Slide 27

Slide 27 text

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 ' ) ]

Slide 28

Slide 28 text

AND SO ON...

Slide 29

Slide 29 text

No content

Slide 30

Slide 30 text

COMMON DIALECTS Informix MS SQL Oracle Postgres SQLite Custom

Slide 31

Slide 31 text

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 , }

Slide 32

Slide 32 text

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 )

Slide 33

Slide 33 text

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 ' ;

Slide 34

Slide 34 text

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 )

Slide 35

Slide 35 text

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 ) ]

Slide 36

Slide 36 text

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 )

Slide 37

Slide 37 text

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 ' ) )

Slide 38

Slide 38 text

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 ( )

Slide 39

Slide 39 text

QUESTIONS THANK YOU / Jason Myers @jasonamyers