Some less obvious uses of SQLite by Stephan Van Ellewee

SQLite is arguably the most widely used database system if it's website is to be believed. From applications in aviation to the cellphone industry.

Yet another practical application that has been suggested by it's creator is that of an application file format. To quote the website: "SQLite does not compete with client/server databases. SQLite competes with fopen()."

In other words, instead of rolling out your own file format, why not use a file format that is accessible via a commonly known language (SQL)?

SQLite is included by default in a number of operating systems such as Blackberry 10 OS, Google Android, Net/Open/FreeBSD, Oracle Solaris 10, Apple OSX 10.4+ and Windows 10 amongst others. Programming languages with bindings for SQLite are C, C#, C++, Go, Java, Javascript, PHP, Python, Lisp and others. This means that SQLite can be used across a very wide range of the most popular devices and environments. Major operating systems support Sqlite out of the box, making it easy to port as well without needing to worry about cross-platform compatibility.

One example of how SQLite and Python could be of use is in incoming product information processing. We have various CSV or XML product feeds that provides locations to product images via a cabal of bash scripts. The idea would be to convert the information into a common format, ie SQLite rows and tables and allow jobs to process these rows, instead of creating bespoke scripts to act directly on the feed files themselves.

The obvious advantage of using SQLite and Python for this task is that it keeps overhead down and has the advantage of better structure, exception handling, portability and extensibility

Transcript

1. SOME LESS OBVIOUS USES OF SQLITE Stephan van Ellewee
2. Who I am Python Developer Works at Takealot Catalogue team Processing product information (CSV? XML?) Storage
3. DISCUSSION POINTS Who uses SQlite it?/What is it? More usecases, examples
4. WHO USES SQLITE? Airbus Skype Mozilla Apple many others...
6. WHAT IS SQLITE? Uses a single le for the database Suited for client side use Just a C library Integrated into python Dynamically typed i m p o r t s q l i t e 3
7. SQLite is also.. the most used database engine in the world does not compete with Postgres/MySQL "competes" with (f)open
8. IN-MEMORY DATABASE i m p o r t s q l i t e 3 w i t h s q l i t e 3 . c o n n e c t ( " : m e m o r y : " ) a s c o n n : # s o m e d a t a b a s e c r e a t i o n . . c o n n . e x e c u t e ( " C R E A T E T A B L E S o m e T a b l e ( i d , N a m e ) " ) ; c u r s o r = c o n n . e x e c u t e ( " " " I N S E R T I N T O S o m e T a b l e ( i d , N a m e ) V A L U E S ( ? , ? ) " " " , ( 1 2 , " S t e p h a n " ) ) # a f t e r c o n t e x t m a n a g e r d a t a b a s e d i s a p p e a r s Note, the lack of required types
9. FILE-BASED DATABASE i m p o r t s q l i t e 3 # c r e a t e s s o m e _ f i l e . d b o n H D D w i t h s q l i t e 3 . c o n n e c t ( " s o m e _ f i l e . d b " ) a s c o n n : # s o m e d a t a b a s e c r e a t i o n . . c o n n . e x e c u t e ( " C R E A T E T A B L E S o m e T a b l e ( i d , N a m e ) " ) ; c u r s o r = c o n n . e x e c u t e ( " " " I N S E R T I N T O S o m e T a b l e ( i d , N a m e ) V A L U E S ( ? , ? ) " " " , ( 1 2 , " S t e p h a n " ) ) # a f t e r c o n t e x t m a n a g e r d a t a b a s e P E R S I S T S !
10. FOREIGN KEYS i m p o r t s q l i t e 3 w i t h s q l i t e 3 . c o n n e c t ( " d e m o . d b " ) a s c o n n : # e n a b l e f o r e i g n k e y c o n s t r a i n t s c o n n . e x e c u t e ( " P R A G M A f o r e i g n _ k e y s = O N " ) # s o m e d a t a b a s e c r e a t i o n . . c o n n . e x e c u t e ( " C R E A T E T A B L E S o m e T a b l e ( i d P R I M A R Y K E Y , n a m e ) ; " ) # s o m e d a t a b a s e c h a n g e s
11. BENEFITS OVER FLAT FILES? No newline issues BLOBS 140TB Great query SQL support
12. SQL SUPPORT example: SQLite (et al) supports Common Table Expressions (CTE's) CTE's are like derived tables Syntax that helps you declare complex queries Allows recursive calls even!
13. A plain CTE W I T H d e m o _ 0 A S ( S E L E C T * F R O M r u n s W H E R E f i l e N a m e = ' d e m o _ 0 _ 2 5 0 0 0 0 . c s v ' ) S E L E C T * F R O M d e m o _ 0 ;
14. You can expand on it W I T H d e m o _ 0 A S ( S E L E C T * F R O M r u n s W H E R E f i l e N a m e = ' d e m o _ 0 _ 2 5 0 0 0 0 . c s v ' ) , s q l i t e 3 _ r e s u l t s A S ( S E L E C T * F R O M d e m o _ 0 I N N E R J O I N r e s u l t s O N r e s u l t s . i d R u n = d e m o _ 0 . i d R u n W H E R E d e m o _ 0 . p y T e s t = ' s q l i t e 3 _ 0 1 . p y ' ) , s i m p l e _ r e s u l t s A S ( S E L E C T * F R O M d e m o _ 0 I N N E R J O I N r e s u l t s O N r e s u l t s . i d R u n = d e m o _ 0 . i d R u n W H E R E d e m o _ 0 . p y T e s t = ' s i m p l e . p y ' ) , s i z e s A S ( S E L E C T ( S E L E C T C O U N T ( 1 ) F R O M s q l i t e 3 _ r e s u l t s ) s i z e 1 , ( S E L E C T C O U N T ( 1 ) F R O M s i m p l e _ r e s u l t s ) s i z e 2 ) S E L E C T * f r o m s i z e s ; - - u s e a n y t h i n g a b o v e ^
15. Recursive Fibonacci W I T H R E C U R S I V E f i b o ( n u m , n e x t _ n u m ) A S ( V A L U E S ( 0 , 1 ) - - i n i t i a l s t a t e U N I O N A L L S E L E C T - - w h a t h a p p e n s n e x t f i b o . n e x t _ n u m , f i b o . n e x t _ n u m + f i b o . n u m F R O M f i b o - - r e c u r s i o n ! ) S E L E C T n u m F R O M f i b o L I M I T 1 0 results in 0,1,1,2,3,5,8,13,21,34
16. Go down a hierarchy with CTE's C R E A T E T A B L E I F N O T E X I S T S o r g ( n a m e T E X T P R I M A R Y K E Y , b o s s T E X T R E F E R E N C E S o r g ) ; I N S E R T I N T O o r g V A L U E S ( ' A l i c e ' , N U L L ) ; I N S E R T I N T O o r g V A L U E S ( ' B o b ' , ' A l i c e ' ) ; I N S E R T I N T O o r g V A L U E S ( ' C i n d y ' , ' A l i c e ' ) ; I N S E R T I N T O o r g V A L U E S ( ' D a v e ' , ' B o b ' ) ; I N S E R T I N T O o r g V A L U E S ( ' E m m a ' , ' B o b ' ) ; I N S E R T I N T O o r g V A L U E S ( ' F r e d ' , ' C i n d y ' ) ; I N S E R T I N T O o r g V A L U E S ( ' G a i l ' , ' C i n d y ' ) ;
17. Go down a hierarchy with CTE's Who works for Alice? W I T H R E C U R S I V E w o r k s _ f o r _ a l i c e ( n a m e , l e v e l ) A S ( V A L U E S ( ' A l i c e ' , 0 ) U N I O N A L L S E L E C T o r g . n a m e , u n d e r _ a l i c e . l e v e l + 1 F R O M o r g J O I N u n d e r _ a l i c e O N o r g . b o s s = w o r k s _ f o r _ a l i c e . n a m e ) S E L E C T s u b s t r ( ' . . . . . . . . . . ' , 1 , l e v e l * 3 ) | | n a m e F R O M w o r k s _ f o r _ a l i c e ;
18. Results in | A l i c e | | . . . B o b | | . . . C i n d y | | . . . . . . D a v e | | . . . . . . E m m a | | . . . . . . F r e d | | . . . . . . G a i l |
19. Summary: SQLite has Great SQL support
20. SQLITE in terms of an "Application File Format" for data manipulation for enhanced automation scripting
21. APPLICATION FILE FORMAT A way to persist state Examples (XLSX, PDF, .git) SQLite ts into this category
22. SQLITE AS APPLICATION FILE FORMAT binary data foreign keys
23. CASE STUDY: DOOM (1993)
24. CASE STUDY: DOOM (1993) WAD les "Where's All the Data" Stores arbitrary binary data in "Lump" entries
25. WAD FILE STRUCTURE Header Index LumpData more LumpData yet more LumpData ...
26. FOR LEVEL IN WADFILE get the vertexes binary data (lump) get the lines binary data (lump)
27. Convenient Python libs: collections.namedtuple - tuples with named elds struct - interprets strings as packed binary data
28. Batteries included i m p o r t c o l l e c t i o n s P i x e l = c o l l e c t i o n s . n a m e d t u p l e ( " P i x e l " , " x y c o l o u r " ) n e w _ p i x e l = P i x e l . _ m a k e ( [ 1 0 , 2 0 , " R E D " ] ) n e w _ p i x e l . x # 1 0 , s a m e a s n e w _ p i x e l [ 0 ] n e w _ p i x e l . c o l o u r # " R E D " i m p o r t s t r u c t b y t e _ l i s t = s t r u c t . u n p a c k ( " < h h h " , b i n a r y _ s t r i n g ) a n o t h e r _ p i x e l = P i x e l . _ u n p a c k ( b y t e _ l i s t ) # u s e n e w _ p i x e l , a n o t h e r _ p i x e l
29. SQLITE RETROFIT Parse Vertex binary data/lump b y t e _ l i s t = s t r u c t . u n p a c k ( " < h h " , b y t e _ d a t a ) V e r t e x = c o l l e c t i o n s . n a m e d t u p l e ( " V e r t e x " , " v e r t e x _ i d x y " ) Parse Line binary data/lump b y t e _ l i s t = s t r u c t . u n p a c k ( " < h h h h h h h " , b y t e _ d a t a ) L i n e = c o l l e c t i o n s . n a m e d t u p l e ( " L i n e " , " l i n e _ i d v 1 v 2 . . . " )
30. SQLITE RETROFIT Store all WAD data into a SQLite db C R E A T E T A B L E I F N O T E X I S T S V e r t e x ( v e r t e x _ i d I N T E G E R P R I M A R Y K E Y , x , y I N T E G E R ) ; C R E A T E T A B L E I F N O T E X I S T S L i n e ( l i n e _ i d I N T E G E R P R I M A R Y K E Y , v 1 I N T E G E R , v 2 I N T E G E R , F O R E I G N K E Y ( v 1 ) R E F E R E N C E S V e r t e x ( v e r t e x _ i d ) , F O R E I G N K E Y ( v 2 ) R E F E R E N C E S V e r t e x ( v e r t e x _ i d ) ) ;
31. SQLITE RETROFIT Using SQL you can create very powerful queries Bounds checks (view window) Line drawing
32. SQLITE RETROFIT Bounds Checking B o x = c o l l e c t i o n s . n a m e d t u p l e ( " B o x " , " m i n _ x m i n _ y w i d t h h e i g h t " ) d e f g e t _ l e v e l _ b o u n d i n g _ b o x ( c o n n ) : " " " P o s t i o n v i e w w i n d o w t o f i t a l l p o i n t s " " " c u r s o r = c o n n . e x e c u t e ( " " " S E L E C T M I N ( x ) , M I N ( y ) , M A X ( x ) - M I N ( x ) a s w i d t h , M A X ( y ) - M I N ( y ) a s h e i g h t F R O M V e r t e x " " " ) r e t u r n B o x . _ m a k e ( c u r s o r . f e t c h o n e ( ) )
33. SQLITE RETROFIT Line drawing V e r t e x = c o l l e c t i o n s . n a m e d t u p l e ( " V e r t e x " , " v e r t e x _ i d x y " ) d e f g e t _ l i n e s ( c o n n ) : c u r s o r = c o n n . e x e c u t e ( " " " S E L E C T l d . l i n e _ i d , - - l i n e r e f e r e n c e v 1 . v e r t e x _ i d , v 1 . x , v 1 . y , - - v e r t e x 1 v 2 . v e r t e x _ i d , v 2 . x , v 2 . y - - v e r t e x 2 F R O M L i n e l d I N N E R J O I N V e r t e x v 1 O N v 1 . v e r t e x _ i d = l d . v 1 I N N E R J O I N V e r t e x v 2 O N v 2 . v e r t e x _ i d = l d . v 2 ; " " " ) d e f u n p a c k ( r e s u l t ) : l i n e _ i d , * v e r t e x e s = r e s u l t v 1 = V e r t e x . _ m a k e ( v e r t e x e s [ : 3 ] ) v 2 = V e r t e x . _ m a k e ( v e r t e x e s [ 3 : ] ) r e t u r n l i n e _ i d , v 1 , v 2 y i e l d f r o m ( u n p a c k ( r e s u l t ) f o r r e s u l t i n c u r s o r )
34. GENERATED FROM SQLITE v0 L0 v1 v1 L1 v2 v3 L2 v0 v4 L3 v3 v2 L4 v5 v6 L5 v4 v5 L6 v7 v8 L7 v6 v7 L8 v9 v10 L9v11 v11 L10 v12 v12 L11 v13 v13 L12 v10 v14 L13 v15 v15 L14 v16 v16 L15 v17 v17 L16 v14 v18 L17 v19 v19 L18 v20 v20 L19 v21 v21 L20 v18 v22 L21 v23 v23 L22 v24 v24 L23 v25 v25 L24 v22 v26 L25 v8 v27 L26 v26 v28 L27 v27 v29 L28 v30 v30 L29 v28 v31 L30 v32 v33 L31 v34 v27 L32 v34 v33 L33 v26 v31 L34 v28 v30 L35 v32 v34 L36 v31 v35 L37 v33 v32 L38 v36 v14 L39 v11 v26 L40 v15 v22 L41 v30 v37 L42 v38 v38 L43 v39 v39 L44 v40 v36 L45 v41 v41 L46 v42 v43 L47 v35 v44 L48 v43 v3 L49 v2 v20 L50 v24 v17 L51 v12 v12 L52 v20 v13 L53 v19 v21 L54 v23 v9 L55 v45 v46 L56 v37 v46 L57 v45 v47 L58 v48 v49 L59 v50 v50 L60 v51 v51 L61 v46 v45 L62 v52 v52 L63 v47 v53 L64 v54 v48 L65 v54 v53 L66 v49 v54 L67 v55 v55 L68 v56 v56 L69 v57 v58 L70 v59 v60 L71 v53 v59 L72 v60 v61 L73 v62 v62 L74 v63 v63 L75 v64 v64 L76 v65 v65 L77 v66 v66 L78 v67 v67 L79 v68 v68 L80 v69 v69 L81 v70 v70 L82 v71 v71 L83 v72 v72 L84 v73 v73 L85 v74 v62 L86 v73 v63 L87 v72 v64 L88 v71 v65 L89 v70 v66 L90 v69 v61 L91 v74 v74 L92 v75 v75 L93 v58 v57 L94 v76 v76 L95 v61 v76 L96 v77 v77 L97 v78 v79 L98 v80 v80 L99 v75 v81 L100 v82 v83 L101 v84 v85 L102 v86 v84 L103 v87 v88 L104 v81 v89 L105 v90 v82 L106 v91 v92 L107 v83 v87 L108 v93 v93 L109 v92 v91 L110 v94 v94 L111 v88 v90 L112 v95 v95 L113 v96 v97 L114 v85 v98 L115 v97 v79 L116 v78 v89 L117 v81 v88 L118 v90 v84 L119 v86 v85 L120 v87 v82 L121 v83 v92 L122 v91 v99 L123 v98 v96 L124 v100 v100 L125 v101 v101 L126 v102 v102 L127 v103 v103 L128 v104 v104 L129 v99 v100 L130 v105 v105 L131 v106 v106 L132 v99 v86 L133 v107 v107 L134 v79 v78 L135 v108 v108 L136 v89 v109L137v110 v110 L138 v111 v111 L139 v112 v112 L140 v109 v113L141v114 v114 L142 v115 v115 L143 v116 v116 L144 v113 v40 L145 v117 v118 L146 v29 v119 L147 v118 v117 L148 v120 v29 L149 v40 v120 L150 v119 v121 L151 v122 v123 L152 v124 v122 L153 v119 v120L154v121 v121 L155 v124 v123 L156 v122 v125 L157 v123 v124 L158 v126 v127 L159 v128 v128 L160 v129 v130 L161 v44 v42 L162 v131 v131 L163 v132 v133 L164 v134 v134 L165 v135 v135 L166 v136 v136 L167 v137 v137 L168 v138 v138 L169 v139 v139 L170 v140 v140 L171 v133 v141 L172 v142 v143 L173 v144 v144 L174 v145 v146 L175 v147 v148 L176 v149 v150 L177 v151 v152 L178 v153 v154 L179 v127 v153 L180 v154 v149 L181 v155 v156 L182 v155 v157 L183 v158 v159 L184 v157 v160 L185 v161 v162 L186 v159 v161 L187 v156 v163 L188 v164 v165 L189 v152 v164 L190 v166 v167 L191 v165 v141 L192 v145 v153 L193 v158 v158 L194 v164 v150 L195 v148 v158 L196 v152 v168 L197 v160 v163 L198 v168 v169 L199 v163 v170 L200 v171 v171 L201 v172 v172 L202 v169 v129 L203 v173 v173 L204 v174 v175 L205 v130 v176 L206 v175 v177 L207 v178 v178 L208 v179 v180 L209 v181 v182 L210 v180 v183 L211 v182 v184 L212 v183 v179 L213 v185 v185 L214 v186 v187 L215 v188 v174 L216 v176 v181 L217 v177 v180 L218 v178 v182 L219 v179 v183 L220 v185 v184 L221 v186 v187 L222 v189 v190 L223 v191 v192 L224 v193 v194 L225 v195 v196 L226 v197 v198 L227 v199 v200 L228 v201 v202 L229 v203 v204 L230 v205 v204 L231 v202 v203 L232 v205 v202L233v200 v200 L234 v198 v198 L235 v196 v196 L236 v194 v194 L237 v192 v193 L238 v195 v195 L239 v197 v197 L240 v199 v199 L241 v201 v201 L242 v203 v192 L243 v206 v207 L244 v193 v208 L245 v207 v206 L246 v167 v208 L247 v167 v206 L248 v207 v209 L249 v208 v127 L250 v210 v210 L251 v211 v211 L252 v35 v211 L253 v130 v129 L254 v210 v205 L255 v212 v212 L256 v191 v190 L257 v213 v213 L258 v204 v186 L259 v214 v214 L260 v189 v188 L261 v215 v215 L262 v184 v150 L263 v162 v147 L264 v216 v216 L265 v141 v145 L266 v217 v217 L267 v148 v218 L268 v170 v170 L269 v219 v219 L270 v169 v132 L271 v220 v220 L272 v221 v221 L273 v222 v222 L274 v223 v153 L275 v223 v222 L276 v154 v223 L277 v150 v224 L278 v225 v226 L279 v227 v227 L280 v228 v229 L281 v230 v231 L282 v232 v233 L283 v224 v234 L284 v235 v235 L285 v236 v236 L286 v237 v238 L287 v239 v239 L288 v240 v240 L289 v241 v228 L290 v242 v242 L291 v229 v237 L292 v243 v243 L293 v234 v232 L294 v244 v244 L295 v233 v241 L296 v245 v245 L297 v238v245 L298 v246 v246 L299 v247 v247 L300 v242 v248 L301 v243 v244 L302 v249 v249 L303 v248 v250 L304 v251 v252 L305 v253 v251 L306 v254 v255 L307 v252 v229 L308 v232 v234 L309 v241 v166 L310 v209 v253 L311 v226 v225 L312 v250 v238 L313 v228 v233 L314 v237 v252 L315 v251 v254 L316 v256 v257 L317 v255 v258 L318 v257 v256 L319 v259 v259 L320 v260 v260 L321 v261 v262 L322 v263 v263 L323 v258 v263L324v260 v259 L325 v258 v261L326v264 v265L327v262 v264 L328 v266 v267 L329 v268 v268 L330 v269 v269 L331 v265 v266 L332 v267 v262L333v261 v270 L334 v209 v166 L335 v271 v230 L336 v272 v272 L337 v270 v271 L338 v273 v273 L339 v231 v271 L340 v270 v272 L341 v273 v274 L342 v275 v276 L343 v277 v277 L344 v274 v275 L345 v276 v256 L346 v257 v278 L347 v279 v280 L348 v281 v281 L349 v278 v279 L350 v280 v151 L351 v146 v282 L352 v283 v283 L353 v284 v284 L354 v285 v285 L355 v286 v286 L356 v287 v287 L357 v288 v288 L358 v289 v289 L359 v282 v290 L360 v291 v291 L361 v292 v293L362v294 v295 L363 v296 v296 L364 v297 v298 L365 v299 v300L366v301 v301 L367 v302 v302L368v303 v303 L369 v295 v292 L370 v300 v290 L371 v304 v304 L372 v305 v305 L373 v297 v299 L374 v306 v306 L375 v307 v307 L376 v308 v308 L377 v293 v298 L378 v309 v309 L379 v294 v310 L380 v311 v142 L381 v312 v313 L382 v143 v312 L383 v310 v125 L384 v126 v313 L385 v310 v314 L386 v315 v315 L387 v316 v316 L388 v317 v317 L389 v314 v294 L390 v318 v318 L391 v125 v126 L392 v319 v319 L393 v290 v297 L394 v320 v320 L395 v313 v311 L396 v321 v321 L397 v298 v322 L398 v323 v323 L399 v324 v324 L400 v325 v325 L401 v326 v326 L402 v327 v327 L403 v328 v328 L404 v329 v329 L405 v330 v330 L406 v331 v331 L407 v332 v332 L408 v333 v333 L409 v334 v334 L410 v335 v335 L411 v322 v327 L412 v334 v328 L413 v333 v329 L414 v332 v336 L415 v337 v337 L416 v338 v338 L417 v339 v339 L418 v336 v340 L419 v341 v341 L420 v342 v342 L421 v343 v343 L422 v340 v344 L423 v345 v345 L424 v346 v346 L425 v347 v347 L426 v344 v142 L427 v144 v348 L428 v349 v350 L429 v351 v352 L430 v353 v349 L431 v352 v219 L432 v354 v354 L433 v355 v353 L434 v356 v356 L435 v169 v349 L436 v357 v357 L437 v350 v351 L438 v358 v358 L439 v352 v191 L440 v359 v359 L441 v360 v360 L442 v187 v189 L443 v361 v361 L444 v362 v362 L445 v190 v174 L446 v363 v363 L447 v177 v181 L448 v364 v364 L449 v176 v355 L450 v365 v366 L451 v367 v368 L452 v366 v369 L453 v370 v370 L454 v371 v371 L455 v372 v372 L456 v373 v374 L457 v368 v375 L458 v374 L459 v375 v373 L460 v377 v378 L461 v377 L462 v379 v380 L463 v378 v155 L464 v218 v365 L465 v348 v369 L466 v367 v155 L467 v381 v381 L468 v380 v379 L469 v382 v382 L470 v218 v365 L471 v383 v383 L472 v369 v367 L473 v384 v384 L474 v348
35. SQLITE AND WAD SQLite can be used to store world state Binary data also supported: SQL can act as your le access API No more spending time writing, debugging, maintaining your own thing s o m e _ b i n a r y _ d a t a = . . . c o n n . e x e c u t e ( " " " C R E A T E T A B L E B i n a r y S t u f f ( s t u f f _ i d I N T E G E R P R I M A R Y K E Y A U T O I N C R E M E N T , n a m e T E X T , s o m e _ b i n a r y _ d a t a B L O B - - B I N A R Y D A T A ! ) " " " ) c o n n . e x e c u t e ( " " " I N S E R T I N T O B i n a r y S t u f f ( n a m e , s o m e _ b i n a r y _ d a t a ) V A L U E S ( ? ) " " " , ( n a m e , s q l i t e 3 . B i n a r y ( s o m e _ b i n a r y _ t o _ i n s e r t ) )
36. MANIPULATION OF FEED DATA We have a feed CSV le Products many Suppliers Suppliers only one Product One (product) has many (suppliers) Denoted by idProduct (Products) idSupplier (Suppliers)
37. REQUIREMENT Use the following CSV structure Batch suppliers per product i d P r o d u c t , i d S u p p l i e r 5 4 3 5 5 6 , 3 9 1 5 9 5 5 4 3 5 5 6 , 6 3 0 8 1 0 5 4 3 5 5 6 , 5 5 4 8 7 6 5 4 3 5 5 6 , 1 3 3 8 5 4 5 5 4 3 5 5 6 , 1 4 3 6 3 3 2 5 4 3 5 5 6 , 1 6 6 7 9 9 8 5 4 3 5 5 6 , 1 8 5 0 8 5 7 5 4 3 5 5 6 , 2 7 4 8 6 7 5 4 3 5 5 6 , 1 8 1 9 1 3 5
38. SIMPLE PYTHON SOLUTION @ c o n t e x t l i b . c o n t e x t m a n a g e r d e f g e t _ d a t a s t o r e ( ) : y i e l d c o l l e c t i o n s . d e f a u l t d i c t ( s e t ) d e f g e t _ s u p p l i e r s _ f o r _ p r o d u c t ( c o n n ) : r e t u r n c o n n . i t e m s ( ) d e f m a i n ( c o d e _ n a m e , f i l e _ n a m e ) : # J u s t a p l a i n d i c t i o n a r y o f s e t - v a l u e s w i t h g e t _ d a t a s t o r e ( ) a s c o n n : d o _ c s v _ p a r s e ( c o n n , f i l e _ n a m e ) f o r p r o d u c t , s u p p l i e r i n g e t _ s u p p l i e r s _ f o r _ p r o d u c t ( c o n n ) : o u t p u t _ p h a s e ( p r o d u c t , s u p p l i e r )
39. SIMPLE PYTHON SOLUTION d e f d o _ c s v _ p a r s e ( c o n n , f i l e _ n a m e ) : l o o k u p = c o n n # t o m a t c h t h e S Q L a p i w i t h o p e n ( f i l e _ n a m e , " r " ) a s s t o c k f i l e : r e a d e r = c s v . D i c t R e a d e r ( s t o c k f i l e ) f o r r o w i n r e a d e r : # N o t L a z y l o o k u p [ r o w [ ' i d P r o d u c t ' ] ] . a d d ( r o w [ ' i d S u p p l i e r ' ] ) Process CSV and add into dictionary
40. SQLITE BASED SOLUTION C R E A T E T A B L E I F N O T E X I S T S P r o d u c t S u p p l i e r s ( i d P r o d u c t I N T E G E R , i d S u p p l i e r I N T E G E R ) ;
41. SQLITE BASED SOLUTION Replace the do_csv_parse with d e f d o _ c s v _ p a r s e ( c o n n , f i l e _ n a m e ) : w i t h o p e n ( f i l e _ n a m e , " r " ) a s s t o c k f i l e : r e a d e r = c s v . D i c t R e a d e r ( s t o c k f i l e ) c o n n . e x e c u t e m a n y ( " " " I N S E R T I N T O P r o d u c t S u p p l i e r s ( i d P r o d u c t , i d S u p p l i e r ) V A L U E S ( : i d P r o d u c t , : i d S u p p l i e r ) " " " , r e a d e r )
42. SQLITE3 PYTHON SOLUTION d e f g e t _ s u p p l i e r s _ f o r _ p r o d u c t ( c o n n ) : c u r s o r = c o n n . e x e c u t e ( " " " S E L E C T i d P r o d u c t , G R O U P _ C O N C A T ( D I S T I N C T ( i d S u p p l i e r ) ) F R O M P r o d u c t S u p p l i e r s G R O U P B Y i d P r o d u c t ; " " " ) f o r p r o d u c t _ i d , s u p p l i e r _ s t r i n g i n c u r s o r : y i e l d ( p r o d u c t _ i d , s u p p l i e r _ s t r i n g . s p l i t ( ' , ' ) d e f m a i n ( c o d e _ n a m e , f i l e _ n a m e , r u n _ i d ) : w i t h s q l i t e 3 . c o n n e c t ( " : m e m o r y : " ) a s c o n n : d o _ c s v _ p a r s e ( c o n n , s y s . a r g v [ 1 ] ) # " s t o c k f i l e - o r i g . c s v " s u p p l i e r _ s o u r c e = g e t _ s u p p l i e r s _ f o r _ p r o d u c t ( c o n n ) f o r p r o d u c t , s u p p l i e r i n s u p p l i e r _ s o u r c e : o u t p u t _ p h a s e ( p r o d u c t , s u p p l i e r )
43. SQLITE3 PYTHON SOLUTION d e f g e t _ s u p p l i e r s _ f o r _ p r o d u c t ( c o n n ) : c u r s o r = c o n n . e x e c u t e ( " " " S E L E C T i d P r o d u c t , G R O U P _ C O N C A T ( D I S T I N C T ( i d S u p p l i e r ) ) F R O M P r o d u c t S u p p l i e r s G R O U P B Y i d P r o d u c t ; " " " ) f o r p r o d u c t _ i d , s u p p l i e r _ s t r i n g i n c u r s o r : y i e l d ( p r o d u c t _ i d , s u p p l i e r _ s t r i n g . s p l i t ( ' , ' ) d e f m a i n ( c o d e _ n a m e , f i l e _ n a m e , r u n _ i d ) : w i t h s q l i t e 3 . c o n n e c t ( " d i s k . d b " ) a s c o n n : d o _ c s v _ p a r s e ( c o n n , s y s . a r g v [ 1 ] ) # " s t o c k f i l e - o r i g . c s v " s u p p l i e r _ s o u r c e = g e t _ s u p p l i e r s _ f o r _ p r o d u c t ( c o n n ) f o r p r o d u c t , s u p p l i e r i n s u p p l i e r _ s o u r c e : o u t p u t _ p h a s e ( p r o d u c t , s u p p l i e r )
44. 3 APPROACHES TOTAL Simple Python Dictionary In Memory SQLite File based SQLite
45. COMPARISON Duration (s) 500,000 1,000,000 1,500,000 2,000,000 2,500,000 3,000,000 3,500,000 0 10 20 30 40 50 60 70 80 90 SIMPLE SQLITE FILE SQLITE MEMORY
46. COMPARISON Average Memory Used (MB) 500,000 1,000,000 1,500,000 2,000,000 2,500,000 3,000,000 3,500,000 0 50 100 150 200 250 300 SIMPLE SQLITE FILE SQLITE MEMORY
47. SUMMARY Speed: SQLite < python dict Memory: SQLite < python dict SQLite can persist SQLite can be queried
48. ENHANCED AUTOMATION SCRIPTING Problem description What we currently have Highlight and contrast
49. PROBLEM: BOOK FEEDS Need images from feed All images in feed only new ones
50. BASH IMAGE FETCHER Written in BASH Really hard to maintain
51. BASH IMAGE FETCHER initialization (checks le existence) generation of ftp scripts (they're o site images) compare with previous ftp scripts (ala left join) on di erence, call ftp, in batches
52. SOME BASH GREATEST HITS # c o m p a r e f i l e s a n d . . . c o m m - 1 3 < ( z c a t $ l a s t _ i m a g e _ l i s t ) < ( z c a t $ t o d a y _ i m a g e _ l i s t ) | \ g z i p - c - > > $ t o d a y _ d i f f _ l i s t # p i p e d i r e c t l y i n t o n e w l i s t ...vs SQL I N S E R T I N T O t o d a y _ d i f f _ l i s t S E L E C T n e w _ i m a g e _ l i s t . i s b n , n e w _ i m a g e _ l i s t . f i l e n a m e F R O M n e w _ i m a g e _ l i s t L E F T J O I N l a s t _ i m a g e _ l i s t O N l a s t _ i m a g e _ l i s t . i s b n = n e w _ i m a g e _ l i s t . i s b n W H E R E l a s t _ i m a g e _ l i s t . f i l e n a m e I S N U L L O R D E R B Y n e w _ i m a g e _ l i s t . i s b n ; - - n o w g o d o t h e f t p
53. SOME BASH GREATEST HITS FTP Batching f u n c t i o n r u n _ f t p ( ) { W I N D O W O F F = $ 1 W I N D O W L E N = $ 2 z c a t $ t o d a y _ d i f f _ l i s t | h e a d - n $ [ $ W I N D O W O F F + 1 ] | t a i l - n $ W I N D O W L E N F I L E S _ T O _ U S E = * u n p r o c e s s e d . j p g i f [ ! - n " $ ( l s $ D O W N L O A D _ D I R $ F I L E S _ T O _ U S E ) " ] ; t h e n e c h o " N o f i l e s t o p r o c e s s " e x i t 1 f i e c h o " D o S o m e C o n v e r s i o n . " # . . . e t c e t c }
54. The e ective bash magic z c a t $ t o d a y _ d i f f _ l i s t | \ h e a d - n $ [ $ w i n d o w _ o f f s e t + 1 ] | \ t a i l - n $ w i n d o w _ l e n g t h | \ x a r g s c u r l . . . # d o t h e f t p ...vs SQL S E L E C T i s b n , f i l e n a m e F R O M t o d a y _ d i f f _ l i s t O R D E R B Y i s b n L I M I T : w i n d o w _ l e n g t h O F F S E T : w i n d o w _ o f f s e t ; - - n o w g o d o t h e f t p
55. CRITIQUE SQL probably not very terse ...but it's explicit ...also you can support continuing A L T E R T A B L E t o d a y _ d i f f _ l i s t A D D C O L U M N w a s _ u p d a t e d B O O L D E F A U L T F A L S E ;
56. SOME TAKE AWAYS SQLite is a very underused library Python can improve it's use context managers, namedtuples
57. @ c o n t e x t l i b . c o n t e x t m a n a g e r d e f m y _ m a n a g e r ( f i l e n a m e , f o r e i g n _ k e y s = T r u e ) : w i t h s q l i t e 3 . c o n n e c t ( f i l e n a m e ) a s c o n n : i f f o r e i g n _ k e y s : c o n n . e x e c u t e ( " P R A G M A f o r e i g n _ k e y s = O N " ) y i e l d c o n n Start creating your own ORM... P i x e l = c o l l e c t i o n s . n a m e d t u p l e ( " P i x e l " , " x y c o l o u r " ) w i t h m y _ m a n a g e r ( " s o m e _ p i x e l _ d a t a b a s e . d b " ) a s d b : n e w _ p i x e l = P i x e l . _ m a k e ( c u r s o r . f e t c h o n e ( ) ) # d o s o m e t h i n g w i t h y o u r p i x e l
58. Thank you! slides and source here
59. INTERESTING LEVEL QUERIES Note, I said interesting, maybe not useful W I T H R E C U R S I V E p a t h s ( p o i n t _ v e r t i c e s , v 1 ) A S ( S E L E C T ' ' , v 1 F R O M l i n e W H E R E l i n e _ i d = 0 U N I O N A L L S E L E C T p . p o i n t _ v e r t i c e s | | ' / ' | | l . v 2 a s p o i n t _ v e r t i c e s , l . v 2 F R O M p a t h s p I N N E R J O I N l i n e l O N l . v 1 = p . v 1 I N N E R J O I N v e r t e x s o n s . v e r t e x _ i d = l . v 2 W H E R E ' / ' | | p . p o i n t _ v e r t i c e s | | ' / ' n o t l i k e ' % / ' | | l . v 2 | | ' / % ' ) S E L E C T * f r o m p a t h s L I M I T 1 0 ;
60. you get paths on the walls! | p o i n t _ v e r t i c e s | v 1 | | | 0 | | / 1 | 1 | | / 1 / 2 | 2 | | / 1 / 2 / 5 | 5 | | / 1 / 2 / 5 / 7 | 7 | | / 1 / 2 / 5 / 7 / 9 | 9 | | / 1 / 2 / 5 / 7 / 9 / 4 5 | 4 5 | | / 1 / 2 / 5 / 7 / 9 / 4 5 / 5 2 | 5 2 | | / 1 / 2 / 5 / 7 / 9 / 4 5 / 5 2 / 4 7 | 4 7 | | / 1 / 2 / 5 / 7 / 9 / 4 5 / 5 2 / 4 7 / 4 8 | 4 8 |
61. MAP CTE iterations represented in red and green
62. v0 L0 v1 v1 L1 v2 v3 L2 v0 v4 L3 v3 v2 L4 v5 v6 L5 v4 v5 L6 v7 v8 L7 v6 v7 L8 v9 v10 L9v11 v11 L10 v12 v12 L11 v13 v13 L12 v10 v14 L13 v15 v15 L14 v16 v16 L15 v17 v17 L16 v14 v18 L17 v19 v19 L18 v20 v20 L19 v21 v21 L20 v18 v22 L21 v23 v23 L22 v24 v24 L23 v25 v25 L24 v22 v26 L25 v8 v27 L26 v26 v28 L27 v27 v29 L28 v30 v30 L29 v28 v31 L30 v32 v33 L31 v34 v27 L32 v34 v33 L33 v26 v31 L34 v28 v30 L35 v32 v34 L36 v31 v35 L37 v33 v32 L38 v36 v14 L39 v11 v26 L40 v15 v22 L41 v30 v37 L42 v38 v38 L43 v39 v39 L44 v40 v36 L45 v41 v41 L46 v42 v43 L47 v35 v44 L48 v43 v3 L49 v2 v20 L50 v24 v17 L51 v12 v12 L52 v20 v13 L53 v19 v21 L54 v23 v9 L55 v45 v46 L56 v37 v46 L57 v45 v47 L58 v48 v49 L59 v50 v50 L60 v51 v51 L61 v46 v45 L62 v52 v52 L63 v47 v53 L64 v54 v48 L65 v54 v53 L66 v49 v54 L67 v55 v55 L68 v56 v56 L69 v57 v58 L70 v59 v60 L71 v53 v59 L72 v60 v61 L73 v62 v62 L74 v63 v63 L75 v64 v64 L76 v65 v65 L77 v66 v66 L78 v67 v67 L79 v68 v68 L80 v69 v69 L81 v70 v70 L82 v71 v71 L83 v72 v72 L84 v73 v73 L85 v74 v62 L86 v73 v63 L87 v72 v64 L88 v71 v65 L89 v70 v66 L90 v69 v61 L91 v74 v74 L92 v75 v75 L93 v58 v57 L94 v76 v76 L95 v61 v76 L96 v77 v77 L97 v78 v79 L98 v80 v80 L99 v75 v81 L100 v82 v83 L101 v84 v85 L102 v86 v84 L103 v87 v88 L104 v81 v89 L105 v90 v82 L106 v91 v92 L107 v83 v87 L108 v93 v93 L109 v92 v91 L110 v94 v94 L111 v88 v90 L112 v95 v95 L113 v96 v97 L114 v85 v98 L115 v97 v79 L116 v78 v89 L117 v81 v88 L118 v90 v84 L119 v86 v85 L120 v87 v82 L121 v83 v92 L122 v91 v99 L123 v98 v96 L124 v100 v100 L125 v101 v101 L126 v102 v102 L127 v103 v103 L128 v104 v104 L129 v99 v100 L130 v105 v105 L131 v106 v106 L132 v99 v86 L133 v107 v107 L134 v79 v78 L135 v108 v108 L136 v89 v109L137v110 v110 L138 v111 v111 L139 v112 v112 L140 v109 v113L141v114 v114 L142 v115 v115 L143 v116 v116 L144 v113 v40 L145 v117 v118 L146 v29 v119 L147 v118 v117 L148 v120 v29 L149 v40 v120 L150 v119 v121 L151 v122 v123 L152 v124 v122 L153 v119 v120L154v121 v121 L155 v124 v123 L156 v122 v125 L157 v123 v124 L158 v126 v127 L159 v128 v128 L160 v129 v130 L161 v44 v42 L162 v131 v131 L163 v132 v133 L164 v134 v134 L165 v135 v135 L166 v136 v136 L167 v137 v137 L168 v138 v138 L169 v139 v139 L170 v140 v140 L171 v133 v141 L172 v142 v143 L173 v144 v144 L174 v145 v146 L175 v147 v148 L176 v149 v150 L177 v151 v152 L178 v153 v154 L179 v127 v153 L180 v154 v149 L181 v155 v156 L182 v155 v157 L183 v158 v159 L184 v157 v160 L185 v161 v162 L186 v159 v161 L187 v156 v163 L188 v164 v165 L189 v152 v164 L190 v166 v167 L191 v165 v141 L192 v145 v153 L193 v158 v158 L194 v164 v150 L195 v148 v158 L196 v152 v168 L197 v160 v163 L198 v168 v169 L199 v163 v170 L200 v171 v171 L201 v172 v172 L202 v169 v129 L203 v173 v173 L204 v174 v175 L205 v130 v176 L206 v175 v177 L207 v178 v178 L208 v179 v180 L209 v181 v182 L210 v180 v183 L211 v182 v184 L212 v183 v179 L213 v185 v185 L214 v186 v187 L215 v188 v174 L216 v176 v181 L217 v177 v180 L218 v178 v182 L219 v179 v183 L220 v185 v184 L221 v186 v187 L222 v189 v190 L223 v191 v192 L224 v193 v194 L225 v195 v196 L226 v197 v198 L227 v199 v200 L228 v201 v202 L229 v203 v204 L230 v205 v204 L231 v202 v203 L232 v205 v202L233v200 v200 L234 v198 v198 L235 v196 v196 L236 v194 v194 L237 v192 v193 L238 v195 v195 L239 v197 v197 L240 v199 v199 L241 v201 v201 L242 v203 v192 L243 v206 v207 L244 v193 v208 L245 v207 v206 L246 v167 v208 L247 v167 v206 L248 v207 v209 L249 v208 v127 L250 v210 v210 L251 v211 v211 L252 v35 v211 L253 v130 v129 L254 v210 v205 L255 v212 v212 L256 v191 v190 L257 v213 v213 L258 v204 v186 L259 v214 v214 L260 v189 v188 L261 v215 v215 L262 v184 v150 L263 v162 v147 L264 v216 v216 L265 v141 v145 L266 v217 v217 L267 v148 v218 L268 v170 v170 L269 v219 v219 L270 v169 v132 L271 v220 v220 L272 v221 v221 L273 v222 v222 L274 v223 v153 L275 v223 v222 L276 v154 v223 L277 v150 v224 L278 v225 v226 L279 v227 v227 L280 v228 v229 L281 v230 v231 L282 v232 v233 L283 v224 v234 L284 v235 v235 L285 v236 v236 L286 v237 v238 L287 v239 v239 L288 v240 v240 L289 v241 v228 L290 v242 v242 L291 v229 v237 L292 v243 v243 L293 v234 v232 L294 v244 v244 L295 v233 v241 L296 v245 v245 L297 v238v245 L298 v246 v246 L299 v247 v247 L300 v242 v248 L301 v243 v244 L302 v249 v249 L303 v248 v250 L304 v251 v252 L305 v253 v251 L306 v254 v255 L307 v252 v229 L308 v232 v234 L309 v241 v166 L310 v209 v253 L311 v226 v225 L312 v250 v238 L313 v228 v233 L314 v237 v252 L315 v251 v254 L316 v256 v257 L317 v255 v258 L318 v257 v256 L319 v259 v259 L320 v260 v260 L321 v261 v262 L322 v263 v263 L323 v258 v263L324v260 v259 L325 v258 v261L326v264 v265L327v262 v264 L328 v266 v267 L329 v268 v268 L330 v269 v269 L331 v265 v266 L332 v267 v262L333v261 v270 L334 v209 v166 L335 v271 v230 L336 v272 v272 L337 v270 v271 L338 v273 v273 L339 v231 v271 L340 v270 v272 L341 v273 v274 L342 v275 v276 L343 v277 v277 L344 v274 v275 L345 v276 v256 L346 v257 v278 L347 v279 v280 L348 v281 v281 L349 v278 v279 L350 v280 v151 L351 v146 v282 L352 v283 v283 L353 v284 v284 L354 v285 v285 L355 v286 v286 L356 v287 v287 L357 v288 v288 L358 v289 v289 L359 v282 v290 L360 v291 v291 L361 v292 v293L362v294 v295 L363 v296 v296 L364 v297 v298 L365 v299 v300L366v301 v301 L367 v302 v302L368v303 v303 L369 v295 v292 L370 v300 v290 L371 v304 v304 L372 v305 v305 L373 v297 v299 L374 v306 v306 L375 v307 v307 L376 v308 v308 L377 v293 v298 L378 v309 v309 L379 v294 v310 L380 v311 v142 L381 v312 v313 L382 v143 v312 L383 v310 v125 L384 v126 v313 L385 v310 v314 L386 v315 v315 L387 v316 v316 L388 v317 v317 L389 v314 v294 L390 v318 v318 L391 v125 v126 L392 v319 v319 L393 v290 v297 L394 v320 v320 L395 v313 v311 L396 v321 v321 L397 v298 v322 L398 v323 v323 L399 v324 v324 L400 v325 v325 L401 v326 v326 L402 v327 v327 L403 v328 v328 L404 v329 v329 L405 v330 v330 L406 v331 v331 L407 v332 v332 L408 v333 v333 L409 v334 v334 L410 v335 v335 L411 v322 v327 L412 v334 v328 L413 v333 v329 L414 v332 v336 L415 v337 v337 L416 v338 v338 L417 v339 v339 L418 v336 v340 L419 v341 v341 L420 v342 v342 L421 v343 v343 L422 v340 v344 L423 v345 v345 L424 v346 v346 L425 v347 v347 L426 v344 v142 L427 v144 v348 L428 v349 v350 L429 v351 v352 L430 v353 v349 L431 v352 v219 L432 v354 v354 L433 v355 v353 L434 v356 v356 L435 v169 v349 L436 v357 v357 L437 v350 v351 L438 v358 v358 L439 v352 v191 L440 v359 v359 L441 v360 v360 L442 v187 v189 L443 v361 v361 L444 v362 v362 L445 v190 v174 L446 v363 v363 L447 v177 v181 L448 v364 v364 L449 v176 v355 L450 v365 v366 L451 v367 v368 L452 v366 v369 L453 v370 v370 L454 v371 v371 L455 v372 v372 L456 v373 v374 L457 v368 v375 L458 v374 L459 v375 v373 L460 v377 v378 L461 v377 L462 v379 v380 L463 v378 v155 L464 v218 v365 L465 v348 v369 L466 v367 v155 L467 v381 v381 L468 v380 v379 L469 v382 v382 L470 v218 v365 L471 v383 v383 L472 v369 v367 L473 v384 v384 L474 v348