How can I open a database in read-only mode by sqlite3 in python?

Question

I am writing a Telegram bot in Python and using a database with SQLite3 as the DBMS. As there can be multiple concurrent queries to the database, I want to restrict certain functions to read-only mode. However, despite searching through several websites, I am still unsure about how to achieve this.

Here is a snippet from my code:

with connect('bazanata.db', uri=True) as conn:
    cursor = conn.cursor()
    cursor.execute("SELECT name FROM users")
    ...

I tried using the following:

conn = sqlite3.connect('database.db', uri=True, flags=sqlite3.RO)

but it resulted in an AttributeError: module 'sqlite3' has no attribute 'RO'. I also attempted other complex solutions that involved modifying the database's connection string, but those also returned errors.

Idk what other options or alternatives I can try.

scotscotmcc · Accepted Answer · 2023-12-11 18:11:03Z

0

The sqlite3 module in Python does not provide a direct attribute like sqlite3.RO for specifying read-only connections.

However, you can achieve read-only behavior by setting the connection isolation level to "DEFERRED" or "IMMEDIATE" using the ISOLATION_LEVEL parameter. This will prevent the connection from making changes to the database.

F.e.

conn = sqlite3.connect('bazanata.db', isolation_level='DEFERRED')
cursor = conn.cursor()

For more info see: https://docs.python.org/3/library/sqlite3.html#transaction-control-via-the-isolation-level-attribute

edited Dec 11, 2023 at 18:11

scotscotmcc

3,1631 gold badge10 silver badges35 bronze badges

answered Dec 11, 2023 at 18:08

kabooya

5644 silver badges18 bronze badges

Sign up to request clarification or add additional context in comments.

Comments

Dave Waters · Accepted Answer · 2024-04-10 13:24:00Z

0

Connecting to SQLite in read-only mode is supported through the URI syntax. (https://www.sqlite.org/uri.html). You were close. Just a small change will get you connected:

conn = sqlite3.connect('database.db?mode=ro', uri=True)

You mentioned concurrent queries. If you are going to have a writer to the database, you should use WAL mode, which will allow multiple readers with a single writer. A single writer will not lock-out the readers. Some considerations for WAL mode with read-only access are documented here: https://www.sqlite.org/wal.html#read_only_databases

Enabling WAL mode is simple. Add this command immediately after you connect:

conn.execute('pragma journal_mode=wal')

answered Apr 10, 2024 at 13:24

Dave Waters

811 silver badge3 bronze badges

Collectives™ on Stack Overflow

How can I open a database in read-only mode by sqlite3 in python?

2 Answers 2

Comments

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

Comments

Comments

Your Answer

Sign up or log in

Post as a guest

Related