cx_Oracle 使用 CLOB 的 executemany

Question

我正在尝试解析多个CSV文件，并使用cx_Oracle将它们的数据插入到数据库表中。我在使用execute方法插入数据时没有问题，但当我尝试用executemany方法时却遇到了错误。我用execute方法成功插入的代码是

with open(key,'r') as file:
    for line in file:
        data = data.split(",")
        query = "INSERT INTO " + tables[key] + " VALUES ("
        for col in range(len(data)):
            query += ":" + str(col) + ","
        query = query[:-1] + ")"            
        cursor.execute(query, data)

但是当我把它换成

with open(key,'r') as file:
    list = []
    for line in file:
        data = data.split(",")
        list.append(data)
    if len(list) > 0:
        query = "INSERT INTO " + tables[key] + " VALUES ("
        for col in range(len(data)):
            query += ":" + str(col) + ","
        query = query[:-1] + ")"            
        cursor.prepare(query)
        cursor.executemany(None,list)

时，我在插入一个包含CLOB列的表时遇到了“ValueError: 字符串数据太大”的错误，因为数据超过了4000个字节。当表中没有CLOB列时，executemany方法运行得很好。有没有办法让我告诉cx_Oracle在执行executemany时把相应的列当作CLOB处理呢？

Answer 1

试着把大列的输入大小设置为 cx_Oracle.CLOB。如果你有二进制数据，这可能不太适用，但对于你在 CSV 文件中的任何文本来说，这应该是可以的。2K 这个值可能比实际需要的要小。

需要注意的是，当涉及到 CLOB 列时，使用 executemany 的速度似乎会慢很多，但比起重复执行来说，还是要好一些：

def _executemany(cursor, sql, data):
    '''
    run the parameterized sql with the given dataset using cursor.executemany 
    if any column contains string values longer than 2k, use CLOBS to avoid "string
    too large" errors.

    @param sql parameterized sql, with parameters named according to the field names in data
    @param data array of dicts, one per row to execute.  each dict must have fields corresponding
                to the parameter names in sql
    '''
    input_sizes = {}
    for row in data:
        for k, v in row.items():
            if isinstance(v, basestring) and len(v) > 2000:
                input_sizes[k] = cx_Oracle.CLOB
    cursor.setinputsizes(**input_sizes)
    cursor.executemany(sql, data)