How to open a BLOB file from MySQL using Python without saving?

Question

Code:

import mysql.connector
import sys

def write_file(data, filename):
    with open(filename, 'wb') as f:
        f.write(data)

sampleNum = 0;
db_config = mysql.connector.connect(user='root', password='test',
                      host='localhost',
                      database='technical')
# query blob data form the authors table
cursor = db_config.cursor()

try:
    sampleNum=sampleNum+1;
    query = "SELECT fileAttachment FROM document_control WHERE id=%s"
    cursor.execute(query,(sampleNum,))
    file = cursor.fetchone()[0]
    write_file(file, 'User'+str(sampleNum)+'.docx')


except AttributeError as e:
    print(e)
finally:
    cursor.close()

What it does

The above code - gets the file from MySQL stored as a BLOB and it saves me a .docx file into a folder.

Question

But instead of saving it, view it then delete it. Am I able to simply open the BLOB in word without saving it?

If so, how can it be done?

Seb · Accepted Answer · 2019-12-20 15:39:22Z

1

In general, passing binary data like a BLOB entity as a file-like object can be done with the built-in module io, for example:

import io

f = io.BytesIO(data)
# f now can be used anywhere a file-object is expected

But your question actually comes more down to MS Word's ability to open files that aren't saved anywhere on the disk. I don't think it can do that. Best practice would probably be to generate a temporary file using tempfile, so that you can at least expect the system to clean it up eventually:

import tempfile

with tempfile.NamedTemporaryFile(suffix='.docx', delete=False) as f:
    f.write(data)
    print(f.name)

Edit:

In your code in particular, you could try the following to store the data in a temporary file and automatically open it in MS Word:

import tempfile, subprocess

WINWORD_PATH = r'C:\Program Files (x86)\Microsoft Office\Office14\winword.exe'

def open_as_temp_docx(data):
    with tempfile.NamedTemporaryFile(suffix='.docx', delete=False) as f:
        f.write(data)
    subprocess.Popen([WINWORD_PATH, f.name])

cursor = db_config.cursor()

try:
    sampleNum=sampleNum+1;
    query = "SELECT fileAttachment FROM document_control WHERE id=%s"
    cursor.execute(query,(sampleNum,))
    open_as_temp_docx(cursor.fetchone()[0])

I don't have a Windows machine with MS Word at hand, so I can't test this. The path to winword.exe on your machine may vary, so make sure it is correct.

Edit:

If it is important to delete the file as soon as MS Word closes, the following should work:

import tempfile, subprocess, os

WINWORD_PATH = r'C:\Program Files (x86)\Microsoft Office\Office14\winword.exe'

def open_as_temp_docx(data):
    with tempfile.NamedTemporaryFile(suffix='.docx', delete=False) as f:
        f.write(data)
    subprocess.Popen([WINWORD_PATH, f.name]).wait()
    if os.path.exists(f.name):
        os.unlink(f.name)

edited Dec 20, 2019 at 15:39

answered Dec 20, 2019 at 14:12

Seb

4,60516 silver badges24 bronze badges

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

9 Comments

DeveloperLV Over a year ago

That'll do! with my current code - what would I replace your code with?

Seb Over a year ago

Are you on Windows, Linux or macOS?

DeveloperLV Over a year ago

Windows I am currently using

DeveloperLV Over a year ago

Hi thanks for this - got Microsoft word error = Sorry, we couldn't find your file. was it moved, renamed, or deleted? (c:...tmpylcf3leg.docx

Seb Over a year ago

Oops, tempfile by default deletes files as soon as you've finished writing to them. I added delete=False above which should make the file persist long enough for Word to open it.

|

Collectives™ on Stack Overflow

How to open a BLOB file from MySQL using Python without saving?

1 Answer 1

9 Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

9 Comments

Your Answer

Sign up or log in

Post as a guest

Related