Help: sqlite3 wont connect to tempfile

Python Help
1

Hi - I’m using sqlite on disk as temporary storage, and would like to use tempfile to create a file for the lifetime of the object, as my calculations are out of memory.
Hereby I presume that the file will be safely deleted once the object is garbage collected. Unfortunately this code raises the error below:

The code:

from tempfile import NamedTemporaryFile
import sqlite3

class A(object):
    def __init__(self):
        self.tempfile = NamedTemporaryFile(suffix='.db')
        self.conn = sqlite3.connect(self.tempfile.name)

obj = A()

The error message:

Traceback (most recent call last):
        self.conn = sqlite3.connect(self.tempfile.name)
    sqlite3.OperationalError: unable to open database file

    Process finished with exit code 1

What am I missing?

2

I’m still looking into the issue. Is this a bug?

3

It is not a bug. I copied your code into tempdb.py and did the following in the REPL:

Python 3.6.10 (default, Jan 16 2020, 09:12:04) [GCC] on linux
Type "help", "copyright", "credits" or "license" for more information.
>>> import tempdb
>>> tempdb.obj
<tempdb.A object at 0x7f74b9dd7b70>
>>> tempdb.obj.conn
<sqlite3.Connection object at 0x7f74b946f030>
>>> tempdb.obj.tempfile
<tempfile._TemporaryFileWrapper object at 0x7f74b9dec438>

The temporary file was in directory /tmp. The issue is not in your code.

4

What version of python are you on? I’m on 3.7.4

5

Likely more important: which platform are each of you on?

6

Win10 enterprise

7

See tempfile — Generate temporary files and directories — Python 3.12.1 documentation

Whether the name can be used to open the file a second time, while the named temporary file is still open, varies across platforms (it can be so used on Unix; it cannot on Windows NT or later).

8

So what would you do that works on both platforms?

Implement a del method? Something like …this?

from tempfile import NamedTemporaryFile
import sqlite3
from pathlib import Path

class A(object):
    def __init__(self):
        self.tempfile = Path(NamedTemporaryFile(suffix='.db').name)
        self.conn = sqlite3.connect(self.tempfile.name)

    def __del__(self):
       self.tempfile.unlink()
       super().__del__()


obj = A()
9

Ok. This works on windows.

from string import ascii_lowercase
from random import choice
from tempfile import NamedTemporaryFile
import sqlite3
from pathlib import Path


class A(object):
    def __init__(self):
        t = NamedTemporaryFile().name
        safe_folder = Path(t).parent
        suffix = '.db'
        while 1:
            n = "".join([choice(ascii_lowercase) for i in range(5)])
            name = f"tmp{n}{suffix}"
            p = safe_folder / name
            if not p.exists():
                break
        self._file = p
        self.conn = sqlite3.connect(p)

    def __del__(self):
        self.conn.close()
        self._file.unlink()


obj = A()
print(obj._file)
del obj

Ugly. but works.

10

I’ve patched my project with this tempfile function for windows:

def windows_tempfile(prefix='tmp', suffix='.db'):
    """ generates a safe tempfile which windows can't handle. """
    t = NamedTemporaryFile().name
    safe_folder = Path(t).parent
    while 1:
        n = "".join(choice(ascii_lowercase) for i in range(5))
        name = f"{prefix}{n}{suffix}"
        p = safe_folder / name
        if not p.exists():
            break
    return p

Seems robust enough. :slightly_smiling_face:

Thanks everyone.

11

I would rather use a temporary directory, in which you can create whatever file you want: https://docs.python.org/3/library/tempfile.html#tempfile.TemporaryDirectory. That way you still get cleanup for free, aren’t creating an unused temporary file, and aren’t subject to headache when somebody has already created the file you’re trying to use for a database.

3 Likes