我有一个python中的数据库类，用来查询数据库。
class Database():
def __init__(self, user, password, host, port, service_name, mode, *args):
#mode should be 0 if not cx_Oracle.SYSDBA
self.user = user
self.password = password
self.host = host
self.port = port
self.user = user
self.service_name = service_name
self.logger = logging.getLogger(__name__)
self.mode = 0
self.connection = None
self.connect_string = self.user + '/' + self.password + '@' + dsn
try:
self.connection = cx_Oracle.connect(self.connect_string, mode=self.mode, threaded=True)
self.connection.stmtcachesize = 1000
self.connection.client_identifier = 'my_app_scheduler'
self.cursor = self.connection.cursor()
self.cursor.arraysize = 10000
self.idVar = self.cursor.var(cx_Oracle.NUMBER)
except cx_Oracle.DatabaseError, exc:
error, = exc
self.logger.exception('Exception occured while trying to create database object : %s', error.message)
raise exc
def query(self, q):
try:
self.cursor.execute(q)
return self.cursor.fetchall(), self.cursor.rowcount
except cx_Oracle.DatabaseError, exc:
raise exc
这是操作获取的数据并将其转换的代码。
output, rowcount = db_run_query.query(sql_text)
#self.logger.debug('output : %s, type : %s', output, type(output))
end_time=time.time()
time_taken=end_time - start_time
self.logger.debug('Rowcount : %s, time_taken : %s', rowcount, time_taken)
column_name = [d[0] for d in db_run_query.cursor.description]
result = [dict(zip(column_name, row)) for row in output]
#Convert everything to string : Eg: datetime
try:
for each_dict in result:
for key in each_dict:
if isinstance(each_dict[key], cx_Oracle.LOB):
self.logger.debug('%s', each_dict[key].size())
each_dict[key]=each_dict[key].read()
#self.logger.debug('%s %s %s %s %s %s %s', key, each_dict, type(key), type(each_dict[key]), type(each_dict), temp_each_dict, type(temp_each_dict))
else:
each_dict[key]=str(each_dict[key])
except Exception as e:
self.logger.debug(e)
所以没有
self.cursor.arraysize = 10000对于像
select clob_value from table这样的查询，它能够获取数据和记录的行数：4901，所用时间：0.196296930313，但是给了我一个错误，比如LOB variable no longer valid after subsequent fetch
但当我提到arraysize参数时，错误就消失了。
( Is arraysize only for lob columns coz it works fine for select other_column from table where rownum<20000 <- other_column in varchar)为什么会这样？
最佳答案
结果是CLOBs和fetchalldon't place nice together：
Internally, Oracle uses LOB locators which are allocated based on the
cursor array size. Thus, it is important that the data in the LOB
object be manipulated before another internal fetch takes place. The
safest way to do this is to use the cursor as an iterator. In
particular, do not use the fetchall() method.
避免
cursor.fetchall()并像迭代器一样使用它（例如for row in cursor: ...），我就能够解决这个问题。