CODEWITHSUNDEEP
pythonmysql
DeveloperLV
DeveloperLV

Reputation: 1781

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

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?

Upvotes: 0

Views: 814

Answers (1)

Seb
Seb

Reputation: 4576

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)

Upvotes: 1

Related Questions