HowToAddBLOBValuesWithMySQL

From Dabo Wiki
Jump to: navigation, search
from dabo import dApp
from MySQLdb import escape_string


# Set up dabo
app = dApp()
app.setup(False)

# Set up connection
connection = app.getConnectionByName("Your connection name")
crs = connection.getDaboCursor()
# Make sure the blob type is large enough to hold your binary data
crs.execute("""CREATE TEMPORARY TABLE blobtable (b BLOB)""")

binarydata = "This could be anything, including pictures, executables etc."

# We need to escape our binary value so MySQLdb doesn't try to add it as a string
#crs.execute("""INSERT INTO blobtable SET b="%s" """ % escape_string(binarydata))

# PKM: actually, I've found that this step isn't necessary. Just 
# remove the quotes, e.g.:
crs.execute("INSERT INTO blobtable SET b=%s", (binarydata, ))

# A normal error here is "MySQL server has gone away". This could mean that
# the server has lost connection, or that the query is larger than MySQL is
# configured to accept. To change this value edit My.ini (in the MySQL server directory),
# and add the line "max_allowed_packet=xM" where x is the allowed size of the query.
# This should be at least the size of the file you are trying to add.

# Print our data and verify that it is right
crs.execute("""SELECT b FROM blobtable""")
bindata = crs.getDataSet()[0]["b"]
print "Retrieved data:", bindata
print "It has the same value?", binarydata == bindata