Telegram API Question posted in
A comprehensive official documentation can be found here.

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

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.

Tags:

2

Answers


  1. 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

    Login or Signup to reply.
  2. 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')
    Login or Signup to reply.
Please signup or login to give your own answer.

Back To Top
Search