pandas 从 Oracle 调用 Python
声明：本页面是StackOverFlow热门问题的中英对照翻译，遵循CC BY-SA 4.0协议，如果您需要使用它，必须同样遵循CC BY-SA许可，注明原文地址和作者信息，同时你必须将它归于原作者（不是我）：StackOverFlow
原文地址: http://stackoverflow.com/questions/22564503/
Warning: these are provided under cc-by-sa 4.0 license. You are free to use/share it, But you must attribute it to the original authors (not me):
StackOverFlow
Calling Python from Oracle
提问by user3311225
Is it possible to call Python within an Oracle procedure? I've read plenty of literature about the reverse case (calling Oracle SQL from Python), but not the other way around.
是否可以在 Oracle 过程中调用 Python？我已经阅读了大量关于反向案例（从 Python 调用 Oracle SQL）的文献，但不是相反。
What I would like to do is to have Oracle produce a database table, then I would like to call Python and pass this database table to it in a DataFrame so that I could use Python to do something to it and produce results. I might need to call Python several times during the Oracle procedure. Does anyone know if this is possible and how could it be done?
我想做的是让 Oracle 生成一个数据库表，然后我想调用 Python 并将这个数据库表传递给 DataFrame 中的它，以便我可以使用 Python 对其执行某些操作并产生结果。我可能需要在 Oracle 过程中多次调用 Python。有谁知道这是否可能以及如何做到？
回答by dwurf
You can write stored procedures in Javaand you can use Java to run Python code, so you can possibly combine the two to achieve what you want.
您可以用 Java 编写存储过程，也可以使用 Java 来运行 Python 代码，因此您可以将两者结合起来实现您想要的。
回答by Mikolas Pansky
On the edge there is a possibility on how to overcome the PL/SQL limitations. You can design a specific interface between Database and Python program. I suppose You'd use one of the Python's library to get some data from the Net. And then exchange it's data with Oracle using the C Library.
在边缘有一个关于如何克服 PL/SQL 限制的可能性。您可以设计数据库和 Python 程序之间的特定接口。我想您会使用 Python 的库之一从网络中获取一些数据。然后使用 C 库与 Oracle 交换它的数据。
call python using c library -> data file -> external table -> data
使用c库调用python->数据文件->外部表->数据
NOTICE: Take it as a proof of concept or rather starting point for deeper exploration. Also I'd strongly discourage You from using it on production. Breaking the PL/SQL jail to call system program could be considered at least as unsafe.
注意：将其作为概念证明或更深入探索的起点。此外，我强烈建议您不要在生产中使用它。打破 PL/SQL 监狱调用系统程序至少可以被认为是不安全的。
So this is the possible way on how to proceed:
所以这是如何进行的可能方法：
--== Prerequisities ==--
--== 先决条件 ==--
pip install quandl
pip 安装 quandl
--== quandl.py ==--
--== quandl.py ==--
#!/usr/bin/python
import quandl
# World Bank Education Statistics
# Population, tertiary, total - Czech Republic
data = quandl.get("WEDU/CZE_SP_TER_TOTL_IN")
data.to_csv("/u01/data/data.txt")
--== exec.c ==--
--== exec.c ==--
//
// gcc -Wall -fPIC -c exec.c
// gcc -shared -o exec.so exec.o
// mkdir -p /u01/lib
// cp exec.so /u01/lib
//
#include <stdlib.h>
int execute() {
system("/u01/bin/get_data.py");
return 0; // We want to make the compiler happy
}
--== LISTENER CONFIGURATION ==--
--== 监听器配置 ==--
SID_LIST_LISTENER =
...
(SID_DESC =
...
(ENVS="EXTPROC_DLLS=ANY")
(PROGRAM = extproc)
...
--== DDL PART ==--
--== DDL 部分 ==--
create or replace library c_exec is '/u01/lib/exec.so';
create or replace procedure exec as external
name "execute"
library c_exec
language c;
/
create directory pydata as '/u01/data';
create table data (
"date" varchar2(14),
"value" varchar2(32)
) organization external (
type oracle_loader
default directory pydata
access parameters (
records delimited by newline
nobadfile nodiscardfile nologfile
fields terminated by ','
) location (pydata:'data.txt')
);
---=== USAGE ===---
---=== 用法 ===---
--== DOWNLOAD DATA FOR PROCESSING ==--
--== 下载处理数据 ==--
Using the external PL/SQL C library You would call the python program that stores the result to the expected location for the external table.
使用外部 PL/SQL C 库 您将调用将结果存储到外部表的预期位置的 Python 程序。
execute exec;
--== QUERY THE DATA ==--
--== 查询数据==--
select
to_date("date",'yyyy-mm-dd') "date",
to_number("value") "value"
from data
where "date" != 'Date';
--== RESULT ==--
--== 结果==--
date value
--------- ----------
31-DEC-70 886414
31-DEC-71 885549
31-DEC-72 877533
31-DEC-73 862859
回答by Netch
I guess this is directly impossible because PL/SQL is specially designed for fast execution inside Oracle server and this isn't place where arbitrary code of other vendor is possible, due to internal limitations.
我想这是不可能的，因为 PL/SQL 是专门为在 Oracle 服务器内快速执行而设计的，由于内部限制，这不是其他供应商的任意代码可能的地方。
OTOH you can interact with another server from a stored procedure via TCP channels, this pagerefers UTL_TCP package. In an external network server, you can utilize any language and any logic.
OTOH 您可以通过 TCP 通道从存储过程与另一台服务器进行交互，此页面指的是 UTL_TCP 包。在外部网络服务器中，您可以使用任何语言和任何逻辑。
回答by user10306208
You can use the Preprocessor feature with external tables, which allows you to invoke a Python script to populate an external table with data. An example can be found in the Using External Table section of this OTN article: https://community.oracle.com/docs/DOC-994731.
您可以对外部表使用预处理器功能，这允许您调用 Python 脚本来使用数据填充外部表。可以在此 OTN 文章的使用外部表部分找到一个示例：https: //community.oracle.com/docs/DOC-994731。
回答by Leandro Lara Tiago
kind of complicated but possible. I have seen it once. You need to
有点复杂但可能。我见过一次。你需要
- create a javaclass inside oracle database. This class calls a .py file in the directory which contains it.
- create a procedure that calls the java class of item 1.
- in your sql query, call the procedure of item 2 whenever you need it.
- 在oracle 数据库中创建一个javaclass。此类调用包含它的目录中的 .py 文件。
- 创建一个调用项目 1 的 java 类的过程。
- 在您的 sql 查询中，随时调用第 2 项的过程。
