假定Oracle的表结构是: CREATE TABLE your_table( date_forecast VARCHAR2(10), filename VARCHAR2(24), content BLOB );12345 BLOB写操作 现在我有3个python的数据,分别是: date_forecast = ‘2012-08-12’ filename = …
假定Oracle的表结构是这样的:
CREATE TABLE your_table( date_forecast VARCHAR2(10), filename VARCHAR2(24), content BLOB
);
现在我有3个python的数据,分别是:
date_forecast = ‘2012-08-12’
filename = ‘your_table.jpg’’
file = open(‘your_table.jpg’, ‘rb’)’
content = fp.read()
fp.close()
插入包含blob的记录,需要先插入空的blob对象,然后再update该记录 但是,这里面有个问题无法解决:python如何给Oracle的blob对象赋值?仔细阅读Oracle的文档,发现在SQL语句中,可以使用冒号(:)来定义变量,而cx_Oracle模块中也的确有cx_Oracle.BLOB对象。至此,可以整理出这样的一个思路:
sqlStr = “INSERT INTO your_table(date_forecast, filename, content) VALUES (‘%s’, ‘%s’, :blobData)” % (date_forecast, filename)
cursor.setinputsizes(blobData=cx_Oracle.BLOB)
将blobData变量定义为cx_Oracle.BLOB的实例,终于让python和Oracle握手了。接下来,就是水到渠成的事情了:
cursor.execute(sqlStr, {‘blobData’:content})
cursor.execute(‘commit’)
BLOB读操作
sqlStr = "SELECT date_forecast, filename, content FROM (SELECT date_forecast, filename, content FROM your_tableORDER BY date_forecast DESC) where ROWNUM < 2 "
cursor.execute(sqlStr)
result = cursor.fetchall()
result[0][2]就是BLOB对象,python如何操作它呢?
print dir(cx_Oracle.BLOB)
cx_Oracle.BLOB有一个read方法:
print type(result[0][2].read())
file = open(‘aurora.jpg’, “wb”)
file.write(result[0][2].read())
file.close()