Opening sqlite3 database from python in read-only

Question

While using sqlite3 from C/C++ I learned that it has a open-in-read-only mode option, which is very handy to avoid accidental data-corruption. Is there such a thing in the Python binding?

Answer 1

As of Python 3.4.0 you can open the database in read only mode with the following:

db = sqlite3.connect('file:/path/to/database?mode=ro', uri=True)

Also see the documentation.

Answer 2

Workaround for Python 2.x:

fd = os.open(filename, os.O_RDONLY)
c = sqlite3.connect('/dev/fd/%d' % fd)
os.close(fd)

Not posix, but available on Linux, OS/X and most modern unixes.

Answer 3

As by the link given by @Chris, no. But there is another wrapper for sqlite3, which is less PEP 249-compliant and that wraps sqlite3 more tightly, assimilating new features of the engine: http://code.google.com/p/apsw/ . That wrapper does support opening the database in read-only mode, plus other niceties.

Answer 4

Somewhat related, note that you can enable/disable modifications dynamically with a pragma:

pragma query_only = ON;   -- disable changes
pragma query_only = OFF;  -- enable changes