Brief SQlite exploration
Last week I had the privilege to explore sqlite and its python builtin library sqlite3. My point of reference is my work and hobby experience with mysql and postgres, sqlite and django ORM in python.
The python library is nice
sqlite3 is included in python. It handles transactions very naturally as it follows DB-API 2.0.
I particularly liked the use of context manager for commits bypasing the need for cursor entirely:
con = sqlite3.connect(":memory:")
con.execute("CREATE TABLE ...")
with con:
con.execute("INSERT INTO ...", ("...",))
On the other hand, I was wondering what’s the point of transactions given that there is no expectation of any other user using the database at the same time and by default there are almost no data checks.
SQlite data types are wonky
SQLite offers a few datatypes that resize automatically. If that isn’t flexible enough, it also allows to define tables without types and infer it from the column content.
https://www.sqlite.org/datatype3.html
It has a strict mode for creating tables with some type safety, but it is very minor.
Notable types missing:
- dates and datetimes: There are some functions to convert strings into dates and perform some basic operations, but it is a bit too implicit to my taste
- booleans are not a thing: it uses integers which isn’t very efficient.
Other things to note
- The memory storage is very convenient, particularly for testing
- Aside from lack of typing, columns are nullable by default.
- The autoincrement for a primary key needs to be added explicitly for some reason.
- There are both virtual tables and temporary tables. Virtual tables seem 100% in memory and unalterable.
- INSERT OR IGNORE is a bit inflexible as it ignores any error.
- There is a RETURNING clause for INSERT to return the generated id
- As the transactions can be really big without issue, the bulk
operations like
executemanyweren’t that much quicker than standardexecuteoperations.