Determine if an sqlite3 database is read-only

Question

I'd like to open an sqlite3 database, and immediately check that it is not opened read-only. Since I know I'll need to write to it, I want to return an error immediately at start-up, and not have to wait until a later time when the program first tries to do a write.

I see there is a sqlite3 C API function sqlite3_db_readonly(). Is it possible to access this function from the Python API? Or is there some other way to check if a database is read-only from the Python API?

You have to [go to lengths](https://stackoverflow.com/questions/10205744/opening-sqlite3-database-from-python-in-read-only-mode) to open it as read-only. Why would you not know it was read-only? — Peter Wood, Jun 22 '17 at 06:37
@PeterWood If the DB file doesn't have write permissions, then it will be opened in read-only mode. — Craig McQueen, Jun 22 '17 at 07:40
You could use [**`os.access`**](https://docs.python.org/3/library/os.html#os.access), e.g: `os.access(db, os.W_OK)`. — Peter Wood, Jun 22 '17 at 08:12
@PeterWood "I/O operations may fail even when `access()` indicates that they would succeed, particularly for operations on network filesystems which may have permissions semantics beyond the usual POSIX permission-bit model." I don't want something that works except for the times when it doesn't. — Craig McQueen, Jun 22 '17 at 23:06
You can tailor it to your situation. If you want something bulletproof for all situations then you need to consider that permissions can change, that writing can fail for other reasons, and you should be handling exceptions anyway. I guess you could access `sqlite3_db_readonly` from the DLL using `ctypes.CDLL`. Also, see [this related question](https://stackoverflow.com/questions/10154978/determine-if-an-sqlite-3-database-is-read-only-without-using-sqlite3-db-readon) — Peter Wood, Jun 23 '17 at 06:50
Yes, I do want something bulletproof for all situations. I aim to avoid writing crappy code. — Craig McQueen, Feb 09 '21 at 05:08

score 7 · Answer 1 · answered Jun 22 '17 at 18:57

Apart from other possibilities mentioned, you can just try writing to it. For example, if the user_version of the database is 0 (which it will be unless you have changed it), issue pragma user_version=0;. This will have no effect if the database is writable, but will result in an error if the database is read-only.

obgnaw · Accepted Answer · 2017-06-23T02:24:27.023

no and yes.see the source code of the python standard library sqlite3.

rc = sqlite3_open_v2(database, &self->db,
                        SQLITE_OPEN_READWRITE | SQLITE_OPEN_CREATE |
                       (uri ? SQLITE_OPEN_URI : 0), NULL);

this means even if you didn't set the uri,it will open in read-only mode when the file in read-only mode.and search in the code.there are no invoke of sqlite3_db_readonly().so you cannot do it by library sqlite3.

but yes,there are other library,for exampleAPSW.the code

`static PyObject*
Connection_readonly(Connection *self, PyObject *name)
{
  int res=-1;
  res=sqlite3_db_readonly(self->db, PyBytes_AS_STRING(utf8name));
}`

then read the DOC,just call Connection.readonly(name)

Determine if an sqlite3 database is read-only

2 Answers2