Skip to main content
 首页 » 编程设计

Python+SQL : How to execute a procedure from python [不调用存储过程]

2025年01月19日19sharpest

我正在尝试从表列表中获取行数,其中所有表的列都应具有 Col_year = 1994。

这在 sql 中是可能的,而无需在 python 中编写循环,因为它需要大量的数据库命中。

为此,我编写了如下程序:

DECLARE 
  l_counter NUMBER; 
  tot_counter NUMBER; 
  v_sql varchar2(10000); 
  cursor c1 is  
    select table_name from ALL_TABLES 
    where table_name in  ("Table1", "Table2", "Table3"); 
 
BEGIN 
    tot_counter := 0; 
  FOR rec IN c1 LOOP 
    v_sql := 'select count(*) from '|| rec.table_name ||' where Col_year = 1994'; 
    EXECUTE IMMEDIATE v_sql INTO l_counter; 
    tot_counter := tot_counter +  l_counter; 
    DBMS_OUTPUT.PUT_LINE(rec.table_name || ' l_counter ' || l_counter || ' tot_counter ' || tot_counter); 
 
  END LOOP; 
END; 

如何从 python 中调用它?

情况是我无法在实际数据库中创建它,因为这只是临时目的。因此,我看起来像 cursor.executeproc() insteat of cursor.callproc()?这可能吗?

请您参考如下方法:

首先,您不需要单个 SQL 查询就可以完成的过程:

SQL Fiddle

Oracle 11g R2 架构设置:

查询 1:

SELECT SUM(c)  
FROM ( 
    SELECT COUNT(*) c FROM Table1 WHERE Col_year = 1999 
    Union all 
    SELECT COUNT(*) c FROM Table2 WHERE Col_year = 1999 
    Union all 
    SELECT COUNT(*) c FROM Table3 WHERE Col_year = 1999 
) 

Results :

| SUM(C) | 
|--------| 
|     10 | 

其次,这里有一些链接可以帮助您找到答案:

Return variable from cx_Oracle PL/SQL call in Python

cx_Oracle and output variables

以及如何去做(我无法测试)

cursor = connection.cursor() 
lOutput = cursor.var(cx_Oracle.STRING) 
cursor.execute(""" 
            DECLARE 
              l_counter NUMBER; 
              tot_counter NUMBER; 
              v_sql varchar2(10000); 
              cursor c1 is  
                select table_name from ALL_TABLES 
                where table_name in  (Table1, Table2, Table3); 
 
            BEGIN 
                tot_counter := 0; 
              FOR rec IN c1 LOOP 
                v_sql := 'select count(*) from '|| rec.table_name ||' where Col_year = 1994'; 
                EXECUTE IMMEDIATE v_sql INTO l_counter; 
                tot_counter := tot_counter +  l_counter; 
                DBMS_OUTPUT.PUT_LINE(rec.table_name || ' l_counter ' || l_counter || ' tot_counter ' || tot_counter); 
 
              END LOOP; 
            END; 
            """) 
print lOutput