使用web2py将BLOB保存到MySQL中(Save BLOB into MySQL using web2py)

使用web2py将BLOB保存到MySQL中(Save BLOB into MySQL using web2py)

使用web2py框架我想通过文件类型输入选择一个文件，并将其内容保存到MySQL BLOB类型字段中。 该文件包含二进制数据。

我需要使用DAL进行连接管理，但我有一个SQL过程来完成任务。 问题是当我在下面尝试这个时，我得到SQL语法的错误。

f = form.vars.element.file.read() db.executesql( "CALL someproc('" + f + "');" )

我试过以很多方式插入原始二进制文件并得到相同或类似的错误。 我也试过直接使用MySQLdb：

f = form.vars.element.file.read() db.cursor().execute( "CALL someproc('" + f + "');" ) db.commit()

这很好用，但我需要使用上面的DAL版本，所以问题仍然存在。

我花了3天没有运气解决这个任务。 ：（ 请帮忙！

Using the web2py framework I want to select a file via a file type input and save it's contents into a MySQL BLOB type field. The file contains binary data.

I need to use DAL for connection management but I have an SQL procedure to do the task. The problem is when I try this below I get error for the SQL syntax.

f = form.vars.element.file.read() db.executesql( "CALL someproc('" + f + "');" )

I've tried inserting the raw binary in lots of ways and got the same or similar error. I've also tried to use MySQLdb directly like:

f = form.vars.element.file.read() db.cursor().execute( "CALL someproc('" + f + "');" ) db.commit()

This works perfectly but I need to use the above DAL version so the problem is still open.

I've spent 3 days solving this task without luck. :( Please help!

最满意答案

鉴于DAL.executesql方法最终以完全相同的方式调用cursor().execute() ，我不确定为什么你的第二个例子有效。 在任何情况下，如果要将后一种语法与web2py DAL对象结合使用，则可以通过db._adaptor.cursor访问游标。 所以，你的第二个例子将改为：

db = DAL('mysql://...') db._adapter.cursor.execute( "CALL someproc('" + f + "');" )

另一种选择可能是使用.callproc方法：

db._adapter.cursor.callproc('someproc', (f, ))

注意， db.executesql()也占用了占位符：

db.executesql('...WHERE name=%s', ('Mary', ))

但我不确定这会将参数传递给存储过程。

I'm not sure why your second example works, given that the DAL.executesql method ultimately calls cursor().execute() in exactly the same way. In any case, if you want to use the latter syntax in conjunction with the web2py DAL object, you can access the cursor via db._adaptor.cursor. So, your second example would change to:

db = DAL('mysql://...') db._adapter.cursor.execute( "CALL someproc('" + f + "');" )

Another option might be to use the .callproc method:

db._adapter.cursor.callproc('someproc', (f, ))

Note, db.executesql() also takes placeholders:

db.executesql('...WHERE name=%s', ('Mary', ))

but I'm not sure that would work for passing arguments to a stored procedure.