Inserting and Retrieving PDFs in mySQL BLOB using python

Question

I have a mysql table with LONGBLOB datatype as one of the columns. I wanted to insert PDF into that column. I have tried this.

file = open(r'path to file\myfile.pdf', 'rb').read()
id='2'
q1 = QSqlQuery("INSERT INTO table_1 (id_val,pdf_name) VALUES (%s,%s)"%(id,file))

This code is not inserting id_val and PDF into table and its not showing any error. Then i split the code.

file = open(r'path to file\myfile.pdf', 'rb').read()
id='2'
q1 = QSqlQuery("INSERT INTO table_1 (id_val) VALUES (%s)"%(id))
q2 = QSqlQuery("UPDATE table_1 SET pdf_name=%s WHERE id_val='2'"%(file))

This code inserts the id_val into table but doesnt update the BLOB pdf.

Can someone help in this?

Anuvrat Parashar · Accepted Answer · 2016-05-18 13:13:56Z

2

Its failing because the file content is binary in nature and the sql command is concatenating it with textual data.

In order to achieve this, one would have to encode the data so that it can go along the command without tripping MySQL's command parser.

One way is to base64 encode the content so that it maps to ascii character set.

import base64
with open('path to your pdf', 'rb) as f:
    blob = base64.b64encode(f.read())

now insert this blob in your database.

The other and more standard way is to follow the advice at [1] and use `MySQLdb.escape_string'.

[1] http://flylib.com/books/en/2.785.1.163/1/

PS: I haven't tested them.

answered May 18, 2016 at 13:13

Anuvrat Parashar

3,1605 gold badges32 silver badges55 bronze badges

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

1 Comment

pksree Over a year ago

Thanks.I tried it and its working. I have tried converting back to pdf by base64 decode function and that's also working fine.

pksree · Accepted Answer · 2016-05-18 12:58:42Z

0

I was able to insert PDF as BLOB with a slight modification in code.

file = open(r'path to file\file.pdf', 'rb').read()
id='2'
q1= 'INSERT INTO table_1(id_val,pdf_name) VALUES(%s,%s)'    
a1 = (id,file)
cursor=db.cursor()
cursor.execute(q1,a1)

answered May 18, 2016 at 12:58

pksree

131 silver badge4 bronze badges

Collectives™ on Stack Overflow

Inserting and Retrieving PDFs in mySQL BLOB using python

2 Answers 2

1 Comment

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

1 Comment

Comments

Your Answer

Sign up or log in

Post as a guest