如何使用python在MySQL数据库中插入/检索存储为BLOB的文件 [英] How to insert / retrieve a file stored as a BLOB in a MySQL db using python

问题描述

我想写一个python脚本，用一些信息填充数据库。我的表中有一列是一个BLOB，我想为每个条目保存一个文件。

如何读取文件（二进制文件）并将其插入进入数据库使用Python？同样的，我怎样才能找回它，并把这个文件写回到硬盘上的任意位置？ 解决方案

code> thedata = open（'thefile'，'rb'）。read（）
sql =INSERT INTO sometable（theblobcolumn）VALUES（％s）
cursor.execute（sql， （thedata，））

当然，这个代码只有在您的表只有BLOB列你想要做的
是INSERT，但是当然你可以很容易地调整它来添加更多的列，
使用UPDATE而不是INSERT，或者无论你真正需要做什么。

我还假设你的文件是二进制文件而不是文本等。再一次，如果我的猜测是
不正确的话，你可以很容易地调整上面的代码相应。

某种 SELECT on cursor.execute ，然后从游标中获取某种类型的数据，就是你如何获取BLOB数据，就像你检索其他类型的数据数据。

I want to write a python script that populates a database with some information. One of the columns in my table is a BLOB that I would like to save a file to for each entry.

How can I read the file (binary) and insert it into the DB using python? Likewise, how can I retrieve it and write that file back to some arbitrary location on the hard drive?

解决方案

thedata = open('thefile', 'rb').read()
sql = "INSERT INTO sometable (theblobcolumn) VALUES (%s)"
cursor.execute(sql, (thedata,))

That code of course works as written only if your table has just the BLOB column and what you want to do is INSERT, but of course you could easily tweak it to add more columns, use UPDATE instead of INSERT, or whatever it is that you exactly need to do.

I'm also assuming your file is binary rather than text, etc; again, if my guesses are incorrect it's easy for you to tweak the above code accordingly.

Some kind of SELECT on cursor.execute, then some kind of fetching from the cursor, is how you retrieve BLOB data, exactly like you retrieve any other kind of data.

这篇关于如何使用python在MySQL数据库中插入/检索存储为BLOB的文件的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！