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?

Nikolai Zaitsev · Accepted Answer · 2020-09-18 18:07:09Z

9

"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

answered Sep 18, 2020 at 18:07

Nikolai Zaitsev

3235 silver badges12 bronze badges

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

Comments

user4157124 · Accepted Answer · 2022-11-07 21:55:45Z

3

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

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.

edited Nov 7, 2022 at 21:55

user4157124

3,00221 gold badges33 silver badges48 bronze badges

answered Feb 12, 2016 at 18:27

Kenly

27k7 gold badges50 silver badges67 bronze badges

2 Comments

Christian Will Over a year ago

Might not work on every platform. I'm getting the following error: AttributeError: 'Process' object has no attribute 'get_open_files'

Kenly Nov 27, 2024 at 11:24

Try open_files, check the warning for Windows and BSD

suvosem · Accepted Answer · 2017-06-19 21:01:34Z

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.

answered Jun 19, 2017 at 21:01

suvosem

314 bronze badges

1 Comment

Fred Zimmerman Over a year ago

The try/except approach by @nikolai-zaitsev above seems better because this will fail if you forget to set the flag or miss a case where it should change.

Collectives™ on Stack Overflow

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

3 Answers 3

Comments

2 Comments

1 Comment

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

3 Answers 3

Comments

2 Comments

1 Comment

Your Answer

Sign up or log in

Post as a guest

Related