时间戳

首页 > 解决方案 > 在 sql 查询中绑定列表很大的列表

python - 在 sql 查询中绑定列表很大的列表

问题描述

使用https://blogs.oracle.com/oraclemagazine/on-cursors-sql-and-analytics，我创建了一种将数组绑定到 SQL 语句的方法，尽管它可能不是最好的。

最初的：

cursor.execute("Select * from table where column in (:id)",{"id":('A','B')})

我按照上面的说明将其更改为：

cursor.execute(with data as (select trim(substr(txt, instr(txt, ',', 1, level  ) + 1,
                                    instr (txt, ',', 1, level+1) - instr (txt, ',', 1, level) -1 ) ) as token
                               from (select ','||:txt||',' txt from dual) 
                                       connect by level <= length(:txt)-length(replace(:txt,',',''))+1)
               Select *
                 from table
                 where column in (select * from data)",{"txt":"A,B"})

现在我有一个问题，如果列表大于 1000，字符串参数越过 4000 字节标记，无法再处理它。

我怎样才能让它工作？

[我只有“选择”权限，不能创建临时表]

标签： pythonsqloraclecx-oracle

解决方案

将大量 id 列表作为参数传递的整个想法是一种错误的方法。如果您想出于“教育”目的而这样做，那么好吧 - 这样做。对于生产环境，这是一条死胡同。我可以从我的经验中看出它可能会导致严重的问题。您需要大量内存来解析查询。我见过 Django 应用程序，其中开发人员向 Oracle DB 查询一些 id，然后根据这些 id 发送另一个查询以获取其他数据，这些 id 作为列表传递 - 它是像你一样的查询：

select a,b,c
  from table
  where table.d in (<here was the list of all ids>)
;

问题在于，在某些情况下，该列表非常大（大约 400KB 的数据，我已经看到在查询中放置了大约 57000 个 id 的跟踪）。整个查询的大小是 433KB！太疯狂了！最可笑的是解析花费的时间（大约 15 分钟！）比执行查询（几秒钟）要长得多。

在我看来，传递巨大的查询（大小）是一个糟糕的主意，你应该重新设计你的代码。

你可以做什么：

  1. 创建一个临时表或只是一个表（对于后者，您必须处理多个会话）并放置查询所需的 id（如果您不能自己做，请请求授权或创建表）；
  2. 将您的任务分成几部分（也许您可以要求一部分 id，然后再要求另一部分，最后在应用程序级别加入结果？）；
  3. 也许您的“ids”是另一个查询的结果 - 然后将该查询放在 where 子句中。

很难说对最终目标一无所知。

如果您只是为了教育目的而这样做，那么（对于您当前的查询）尝试使用 CLOB 类型（或在 cx_oracle 中表示 CLOB 的对象）作为包含 id 的字符串的存储。

编辑（2019-07-09）

示例（只是基于 cx_oracle 样本的简单草稿）：

#!/usr/bin/env python
# -*- coding: utf-8 -*-

import cx_Oracle

db_login = 'login'
db_target = 'tns_alias_or_host_port_service_name'
db_pass = 'pass'

con = cx_Oracle.connect('{0}/{1}@{2}'.format(db_login, db_pass, db_target))

sql = '''select to_char(regexp_substr(:p_clob, '[^,]+', 1, level)) as token
 from dual
 connect by regexp_substr(:p_clob, '[^,]+', 1, level) is not null
'''


large_string = ",".join(str(i) for i in range(11000, 12000, 1))
print('Size of large_string: {}'.format(len(large_string)))
print('Number of elements in large_string: {}'.format(large_string.count(',') + 1))

cur = con.cursor()
cur.arraysize = 256
cur.setinputsizes(p_clob = cx_Oracle.CLOB)
cur.prepare(sql)
cur.execute(None, {'p_clob': large_string })

# fetch first part of rows (see: cur.arraysize)
result = cur.fetchmany()

print(result)

推荐阅读