SQLite provides an excellent development alternative for applications that are predominantly read-only or require a smaller installation footprint. As with all database servers, though, there are some differences that are specific to SQLite that you should be aware of.
For all SQLite versions, there is some slightly counter-intuitive behavior when attempting to match some types of strings. These are triggered when using the iexact or contains filters in Querysets. The behavior splits into two cases:
- For substring matching, all matches are done case-insensitively. That is a filter such as filter(
name__contains="aa") will match a name of "Aabb". - For strings containing characters outside the ASCII range, all exact string matches are performed case-sensitively, even when the case-insensitive options are passed into the query. So the
iexactfilter will behave exactly the same as the exact filter in these cases.
Some possible workarounds for this are documented at sqlite.org, but they aren't utilized by the default SQLite backend in Django, as incorporating them would be fairly difficult to do robustly. Thus, Django exposes the default SQLite behavior and you should be aware of this when doing case-insensitive or substring filtering.
SQLite 3.6.23.1 and older contains a bug when handling query parameters in a CASE expression that contains an ELSE and arithmetic.
SQLite 3.6.23.1 was released in March 2010, and most current binary distributions for different platforms include a newer version of SQLite, with the notable exception of the Python 2.7 installers for Windows.
As of this writing, the latest release for Windows-Python 2.7.10-includes SQLite 3.6.21. You can install pysqlite2 or replace sqlite3.dll (by default installed in C:Python27DLLs) with a newer version from sqlite.org to remedy this issue.
Django will use a pysqlite2 module in preference to sqlite3 as shipped with the Python standard library if it finds one is available.
This provides the ability to upgrade both the DB-API 2.0 interface or SQLite 3 itself to versions newer than the ones included with your particular Python binary distribution, if needed.
SQLite is meant to be a lightweight database, and thus can't support a high level of concurrency. OperationalError: database is locked errors indicate that your application is experiencing more concurrency than sqlite can handle in default configuration. This error means that one thread or process has an exclusive lock on the database connection and another thread timed out waiting for the lock the be released.
Python's SQLite wrapper has a default timeout value that determines how long the second thread is allowed to wait on the lock before it times out and raises the OperationalError: database is locked error.
If you're getting this error, you can solve it by:
- Switching to another database backend. At a certain point SQLite becomes too light for real-world applications, and these sorts of concurrency errors indicate you've reached that point.
- Rewriting your code to reduce concurrency and ensure that database transactions are short-lived.
- Increase the default timeout value by setting the
timeoutdatabase options:
'OPTIONS': { # ... 'timeout': 20, # ... }
This will simply make SQLite wait a bit longer before throwing database is locked errors; it won't really do anything to solve them.
SQLite does not support the SELECT ... FOR UPDATE syntax. Calling it will have no effect.
For most backends, raw queries (Manager.raw() or cursor.execute()) can use the pyformat parameter style, where placeholders in the query are given as '%(name)s' and the parameters are passed as a dictionary rather than a list. SQLite does not support this.
sqlite3 does not provide a way to retrieve the SQL after quoting and substituting the parameters. Instead, the SQL in connection.queries is rebuilt with a simple string interpolation. It may be incorrect. Make sure you add quotes where necessary before copying a query into an SQLite shell.