Monday 1:55 p.m.–2:25 p.m.
SQLite: Gotchas and Gimmes
Dave Sawyer
- Audience level:
- Intermediate
- Category:
- Education
Description
Python's sqlite module provides access to the most deployed database engine
in the world. We'll go beyond the docs to discover how to unlock the full
power of SQLite without additional libraries. We'll identify deadly
pitfalls and produce clean Pythonic code. Find out why the creators say to
think of SQLite not as a replacement for Oracle, but as a replacement for
open().
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 