Monday 1:55 p.m.–2:25 p.m.
SQLite: Gotchas and Gimmes
Dave Sawyer
- Audience level:
- Intermediate
- Category:
- Education
Description
Abstract
SQLite is the most deployed database engine in world. It's actively developed and tremendously powerful, yet the Python integration of it hasn't changed since 2.7 (still sqlite.version 2.6.0). Samples in the documentation are riddled with gotchas and code gleaned from the internet is hardly better. Some well written articles suggest the sqlite3 module (particularly transaction handling) is fundamentally broken.
Can sqlite3 be saved? Answer: Yes!
Starting with Python.org's own examples we go through the problems with them and how to fix them. Our resulting code is not only less buggy, it's simpler, it's cleaner, it's dare-we-say-it ... more Pythonic.
While Python may have stood still, SQLite has made some dramatic improvements in performance and features. How do we get these? The good news is, you may already have them. You just need to add a line here, an undocumented parameter there, and presto! You can do things like read and write to a file AT THE SAME TIME - now that's performance! (Mom said you can't do that in Python, but you actually can). One table on a slide will show you which goodies you got for free and which will take a little work.
Transactions are key to database use? What's the "right" way to do this in Python? We'll cover the 3 models SQLite uses and the not-quite-ready-for-prime-time 4'th model Python uses by default. Step one: switch models.
Finally, multi-threaded writing. There are two good ways to go here. We'll look at a few lines of code that implements each way. Each has advantages and disadvantages. The good news is they both work.
All code, comments, and slides available on github.
PyCon 2016 in Portland, OR is a production of the 