How do I check if a SQLite3 database is connected in Python?

Question

Question 1: I have an SQLite3 connection in Python. How can I checked whether it is connected or not? I know that if sqlite3.connect() fails, an exception is raised, but if I or some closes the connection, how can I check this and reopen it if necessary?

Question 2: I can move the file in the file system while the connection is open (deletion is not possible). The database then becomes readonly, for whatever reason. If I move it back, it works as if nothing happened. Can anybody explain this? Should I check isfile(dbpath) before access?

Answer 1

1. Use psutils to check if database file is used by a process:

import os
import psutil
import sqlite3

con = sqlite3.connect("temp.db")

def is_open(path):
    for proc in psutil.process_iter():
        try:
            files = proc.get_open_files()
            if files:
                for _file in files:
                    if _file.path == path:
                        return True
        except psutil.NoSuchProcess as err:
            print(err)
    return False

con = sqlite3.connect("temp.db")
path = os.path.abspath("temp.db")
print(is_open(path))
con.close()
print(is_open(path))

Output:

True  
False

2. For reading, the OS should cache the file anyway so you can read and if you try to write the following error will be raised:

   sqlite3.OperationalError: attempt to write a readonly database

As you said check for db existance before running sqlite3.connect:

if os.path.exists(db):

You can't force the sqlite3.connect function to not create the db file.

Answer 2

"try ... except" works pretty well too

import sqlite3 as mdb
def chk_conn(conn):
     try:
        conn.cursor()
        return True
     except Exception as ex:
        return False

myconn = mdb.connect('test.db')
print(chk_conn(myconn))

Out: True

myconn.close()
print(chk_conn(myconn))

Out: False

Answer 3

This is more of a simple work-around idea that I've been using:

• Create a boolean flag (say, "flagConnOpen") to signify an open connection -- for example in the class that handles your db work.
• Initially, flagConnOpen = False
• Whenever you want to (conditionally) open a connection, check if this flag is already True, if not, open the connection and set the flag to true.

e.g.

...
def OpenConn(self):
    if(self.flagConnOpen == False):
        self.db = sqlite3.connect(self.dbPath);
        self.flagConnOpen = True;

then you can put this OpenConn() at the start of any function that needs a connection, and it will open one as needed.