How to download BLOB .docx file from MySQL using python?

Question

Current Code:

import mysql.connector
import sys

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

sampleNum = 0;
# select photo column of a specific author
# read database configuration
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 file FROM test WHERE id=%s"
    cursor.execute(query,(sampleNum,))
    photo = cursor.fetchone()[0]
    write_file(photo, 'User'+str(sampleNum)+'.jpg')

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

Goal of above code

Code above allows me to get the image from MySQL that is stored as a BLOB and save it into a folder where .py script is saved.

It works fine!

Similar code with .docx

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')
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()

Here I am trying to extract and save a docx file from MySQL stored as a BLOB and it does not work.

The output of above script is the following:

    f.write(data)
TypeError: a bytes-like object is required, not 'str'

How can I extract the .docx file from MySQL?

What does type(file) give with .docx? Also can you please post some initial characters of variable file in the second script — Raj
– Raj, Commented Dec 20, 2019 at 11:30
Got it - here it is: C:/Users/<user>/Desktop/Weekly Checks.xlsx — DeveloperLV
– DeveloperLV, Commented Dec 20, 2019 at 11:45
Are you using blob to save the file in the database or are you just saving the path to the file? — Raj
– Raj, Commented Dec 20, 2019 at 11:47
Just as blob - We need to use blob.. cannot be using file paths. This is the query from MySQL: insert into document_control (fileattachment) values ('C:/Users/<user>/Desktop/Weekly Checks.xlsx'); — DeveloperLV
– DeveloperLV, Commented Dec 20, 2019 at 11:48

Raj · Accepted Answer · 2019-12-20 12:00:26Z

1

As per the insert query you mentioned

insert into document_control (fileattachment) values ('C:/Users/<user>/Desktop/Weekly Checks.xlsx');

it seems that you are just inserting the filepath in the database.

You must use LOAD_FILE to insert the actual file in the database blob object. How to use LOAD_FILE to load a file into a MySQL blob?

answered Dec 20, 2019 at 12:00

Raj

6744 silver badges16 bronze badges

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

1 Comment

DeveloperLV Over a year ago

I have saved the file on MySQL server path - now it works!

Collectives™ on Stack Overflow

How to download BLOB .docx file from MySQL using python?

1 Answer 1

1 Comment

Your Answer

Linked

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

1 Comment

Your Answer

Sign up or log in

Post as a guest

Linked

Related