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.