Different behaviour if the directory exists on the real file system, or not (sqlite3)

[nicolashainaux]

Describe the bug
I quite think this is a bug, though I am surprised no one stumbled upon it before.
What I observe is, if I fake a file that's in a directory that does exist on the real filesystem, then sqlite3 can open it. No problem, or almost, because the database file is then really created on the real filesystem (unexpected).
But if I fake a file in a directory that does not exist on the real filesystem, then sqlite3 cannot open it (raises: sqlite3.OperationalError: unable to open database file).
Is this a bug, or maybe a compatibility problem with sqlite3?
I have used pathlib.Path and os.makedirs(), I thought they're both supported in pyfakefs. I have replaced the call to os.makedirs() by Path.mkdir(), I had to add the parents=True in order for this second option to work, but the results are the same.

How To Reproduce
Here's the complete test file (note: the only difference between the passing and the failing tests is the directory's name: in the passing test, /home/nico/tests/db/ is an existing directory on my real filesystem; whereas in the failing test, /home/nico/tests/db2/ does not exist):

import os
import sys
import sqlite3
from pathlib import Path


class DBCM:
    """
    Simple Context Manager for sqlite3 databases.
    """
    def __init__(self, path):
        self.path = path
        self.conn = None
        self.cursor = None

    def __enter__(self):
        self.conn = sqlite3.connect(str(self.path))
        self.cursor = self.conn.cursor()
        return self.cursor

    def __exit__(self, exc_class, exc, traceback):
        self.conn.close()


def test_passing(fs):
    testdb_path = Path('/home/nico/tests/db/blank.sqlite3')
    assert not testdb_path.exists()
    os.makedirs(testdb_path.parent, mode=0o770)
    # testdb_path.parent.mkdir(mode=0o770, parents=True)
    testdb_path.touch(mode=0o777)
    assert testdb_path.exists()
    sys.stderr.write(f'type(testdb_path)={type(testdb_path)}\n')
    with DBCM(testdb_path) as cursor:
        print(f"alright, cursor is {cursor}")


def test_failing(fs):
    testdb_path = Path('/home/nico/tests/db2/blank.sqlite3')
    assert not testdb_path.exists()
    os.makedirs(testdb_path.parent, mode=0o770)
    # testdb_path.parent.mkdir(mode=0o770, parents=True)
    testdb_path.touch(mode=0o777)
    assert testdb_path.exists()
    sys.stderr.write(f'type(testdb_path)={type(testdb_path)}\n')
    with DBCM(testdb_path) as cursor:
        print(f"alright, cursor is {cursor}")

Here's the complete output of the tests:

pytest -x -vv tests/failing_test.py 
=================================== test session starts ====================================
platform linux -- Python 3.11.4, pytest-7.4.0, pluggy-1.2.0 -- /home/nico/.local/dev/zano/.venv/py311/bin/python
cachedir: .pytest_cache
rootdir: /home/nico/.local/dev/zano
plugins: pyfakefs-5.2.2, mock-3.11.1
collected 2 items                                                                          

tests/failing_test.py::test_passing PASSED                                           [ 50%]
tests/failing_test.py::test_failing FAILED                                           [100%]

========================================= FAILURES =========================================
_______________________________________ test_failing _______________________________________

fs = <pyfakefs.fake_filesystem.FakeFilesystem object at 0x7fe8de7bb310>

    def test_failing(fs):
        testdb_path = Path('/home/nico/tests/db2/blank.sqlite3')
        assert not testdb_path.exists()
        # os.makedirs(testdb_path.parent, mode=0o770)
        testdb_path.parent.mkdir(mode=0o770, parents=True)
        testdb_path.touch(mode=0o777)
        assert testdb_path.exists()
        sys.stderr.write(f'type(testdb_path)={type(testdb_path)}\n')
>       with DBCM(testdb_path) as cursor:

/home/nico/.local/dev/zano/tests/failing_test.py:44: 
_ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ 

self = <tests.failing_test.DBCM object at 0x7fe8de765f50>

    def __enter__(self):
>       self.conn = sqlite3.connect(str(self.path))
E       sqlite3.OperationalError: unable to open database file

/home/nico/.local/dev/zano/tests/failing_test.py:17: OperationalError
----------------------------------- Captured stderr call -----------------------------------
type(testdb_path)=<class 'pyfakefs.fake_pathlib.FakePathlibModule.PosixPath'>
================================= short test summary info ==================================
FAILED tests/failing_test.py::test_failing - sqlite3.OperationalError: unable to open database file
!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!! stopping after 1 failures !!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!
=============================== 1 failed, 1 passed in 0.23s ================================

Your environment

Linux-5.15.0-76-generic-x86_64-with-glibc2.35
Python 3.11.4 (main, Jun 30 2023, 17:25:36) [GCC 11.3.0]
pyfakefs 5.2.2
pytest 7.4.0

[mrbean-bremen]

Thanks for the report! Without looking into that I suspect that this is a case of a pyfakefs limitation, given that sqlite3 is written in C and the Python package is just a wrapper around it - though that is only a guess.

I will have a closer look some time later.

[mrbean-bremen]

Unfortunately, as I suspected, sqlite3 will not work nicely with pyfakefs. Opening files is done using the C interface of squite3 itself (e.g. sqlite3_open and related functions), and there is no way to patch this in Python code.
I will probably add sqlite3 to the list of modules not working with pyfakefs.
I've also just opened a new category "limitations" in the discussions, where I will move this and related issues.

[nicolashainaux]

I think I have found a possible workaround.
I do not perform complex tasks with sqlite3, I mainly need to connect to the db, then simple requests (inserting, removing rows; looking for specific data...). So far, it seems to work fine.

I did it that way: first I store a reference to the original sqlite3.connect function, then the side_effect function mock_sqlite3_conn uses Pause from pyfakefs, just to be able to connect to a true db. The path is created if necessary. This requires to clean up after the test, to ensure no remaining db is still around. Path is from the standard pathlib.

    orig_conn = sqlite3.connect

    def mock_sqlite3_conn(key):
        with Pause(fs):
            Path(key).parent.mkdir(parents=True, exist_ok=True)
            Path(key).touch()
            return orig_conn(key)

    mocker.patch('microlib.database.sqlite3.connect',
                 side_effect=mock_sqlite3_conn)

[mrbean-bremen]

Nice! Yes, this sounds like a workable solution. Unfortunately, handling modules that use C functions for file system access is a case-by-case problem, but using Pause this way is something I haven't thought about. This may be handy in other situations, too.