使用python在mySQL BLOB中插入和检索PDF

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.

我有一个带有LONGBLOB数据类型的mysql表作为其中一列。我想将PDF插入该列。我试过这个。

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.

此代码未将id_val和PDF插入表中，并且未显示任何错误。然后我拆分代码。

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.

此代码将id_val插入表中，但不更新BLOB pdf。

Can someone help in this?

有人可以帮忙吗？

2 个解决方案

#1

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

它失败了，因为文件内容本质上是二进制的，而sql命令将它与文本数据连接起来。

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.

为了实现这一点，人们必须对数据进行编码，以便它可以在不跳过MySQL的命令解析器的情况下执行命令。

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

一种方法是对内容进行base64编码，使其映射到ascii字符集。

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

now insert this blob in your database.

现在将此blob插入数据库中。

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

另一个更标准的方法是遵循[1]中的建议并使用`MySQLdb.escape_string'。

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

PS: I haven't tested them.

PS：我没有测试过它们。

#2

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

我能够将PDF作为BLOB插入，稍微修改一下代码。

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)

#1